把生命浪费在美好事物上

产品产品产品

358篇博客

最新博客

最新资源下载

编辑推荐

网易数帆开源API网关与容器云项目，让云原生生产落地“多快好网易汪源：统一负载与多云环境的“开放姿态”，才是云原生网易数帆如何用 Kubernetes“原语”搞定云原生中间件快手打新挤爆券商系统，网易数帆推出券商稳定性保障方案探索智慧校园新模式，网易有数在教育行业的实践分享金融行业大数据治理之路——数据模型篇

Innodb实践总结（二）

把生命浪费在美好事物上2018-07-11 10:51

7. Innodb加锁分析

由于Innodb默认的隔离级别是 Repeatable Read，接下来都是以该级别为基础进行分析；

创建数据库test，数据库表user：id是主键，phone是唯一索引，age是非唯一索引。

mysql> show create table user\G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `id` int(4) NOT NULL DEFAULT '0',
  `age` int(4) DEFAULT NULL,
  `phone` int(20) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`),
  KEY `idx_k` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> select * from user;
+----+------+-------+-------+
| id | age  | phone | name  |
+----+------+-------+-------+
|  3 |   12 |   122 | wang  |
|  8 |   12 |   123 | zhang |
| 14 |   15 |   128 | zhang |
+----+------+-------+-------+

7.1 非索引字段的当前读操作

  • session1: 第一个事务首先去更新name(无索引)为"wang"的记录，并没有提交； 

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> update user set name='www' where name='wang';
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
  • session2：第二个事务去更新id为8的记录：

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> update user set name='zzz'where id=8;
  • session3: 第三个事务去插入id为2（该主键原来并不存在）的记录：

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into user values(2,8,111,'li');
  • session4: 第四个事务去更新id为7（该主键原来并不存在）的记录：

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> update user set name='li' where id = 7;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 0  Changed: 0  Warnings: 0

分析

首先看innodb_trx表，查看具体事务情况，可以看到执行了四个事务，通过查看trx_query得知，56D为更新id为8的操作，56E为插入id为2的操作，这两个事务的trx_started都未LOCK WAIT状态，说明都给堵塞住了，当然通过事务的执行时间也可以将四个事务进行区分。

mysql> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
                    trx_id: 56F
                 trx_state: RUNNING
               trx_started: 2017-11-12 03:18:16
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 69
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
         ......
*************************** 2. row ***************************
                    trx_id: 56E
                 trx_state: LOCK WAIT
               trx_started: 2017-11-12 03:17:54
     trx_requested_lock_id: 56E:0:327:9
          trx_wait_started: 2017-11-12 03:17:54
                trx_weight: 2
       trx_mysql_thread_id: 65
                 trx_query: insert into user values(2,8,111,'li')
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
         ......
*************************** 3. row ***************************
                    trx_id: 56D
                 trx_state: LOCK WAIT
               trx_started: 2017-11-12 03:17:30
     trx_requested_lock_id: 56D:0:327:7
          trx_wait_started: 2017-11-12 03:18:24
                trx_weight: 2
       trx_mysql_thread_id: 63
                 trx_query: update user set name='zzz'where id=8
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
         ......
*************************** 4. row ***************************
                    trx_id: 56C
                 trx_state: RUNNING
               trx_started: 2017-11-12 03:17:05
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 68
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
        ......
4 rows in set (0.00 sec)

然后看innodb_lock_waits表，查看锁的等待情况，可以看出事务id为56C持有的锁将56D有56E都给堵塞住了；

mysql> select * from information_schema.innodb_lock_waits\G;
*************************** 1. row ***************************
requesting_trx_id: 56E
requested_lock_id: 56E:0:327:9
  blocking_trx_id: 56C
 blocking_lock_id: 56C:0:327:9
*************************** 2. row ***************************
requesting_trx_id: 56D
requested_lock_id: 56D:0:327:7
  blocking_trx_id: 56C
 blocking_lock_id: 56C:0:327:7
2 rows in set (0.00 sec)

接下来看innodb_locks表，分析造成阻塞的具体锁的类型，可以看到56D需要的X锁，此时被56C占有，而56E需要X和GAP锁，此时锁也被56C占有。

mysql> select * from information_schema.innodb_locks\G;
*************************** 1. row ***************************
    lock_id: 56E:0:327:9
lock_trx_id: 56E
  lock_mode: X,GAP
  lock_type: RECORD
 lock_table: `test`.`user`
 lock_index: `PRIMARY`
 lock_space: 0
  lock_page: 327
   lock_rec: 9
  lock_data: 3
*************************** 2. row ***************************
    lock_id: 56C:0:327:9
lock_trx_id: 56C
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`user`
 lock_index: `PRIMARY`
 lock_space: 0
  lock_page: 327
   lock_rec: 9
  lock_data: 3
*************************** 3. row ***************************
    lock_id: 56D:0:327:7
lock_trx_id: 56D
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`user`
 lock_index: `PRIMARY`
 lock_space: 0
  lock_page: 327
   lock_rec: 7
  lock_data: 8
*************************** 4. row ***************************
    lock_id: 56C:0:327:7
lock_trx_id: 56C
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`user`
 lock_index: `PRIMARY`
 lock_space: 0
  lock_page: 327
   lock_rec: 7
  lock_data: 8
4 rows in set (0.00 sec)

最后分析：由于innodb行锁的前提是对索引加锁，而name并没有建立索引，索引这个时候是对user表加了表锁，由于执行的是更新操作，那么进行的是当前读，从56C堵塞了56D可以得知，表锁包含了对所有的记录加的是X锁，而56C将56E堵塞了，说明表锁对所有的间隙加了GAP锁，而56C并没有堵塞56F，说明间隙锁不会阻止其他事务在该间隙上继续获得间隙锁。

总结：对非索引字段进行当前读操作时，那么会对该表加表锁，表锁包括对表中所有记录加排它锁，同时对所有记录的间隙加间隙锁。间隙锁不会阻止不同的事务在同一间隙上获得，只是阻止其他事务插入到当前间隙当中。

7.2 当前读与快照读

  • session1: 第一个事务首先去查询id为3的记录，并没有提交；

    mysql> begin;                        
    Query OK, 0 rows affected (0.00 sec)

    mysql> select * from user where id=3;
    +----+------+-------+------+
    | id | age  | phone | name |
    +----+------+-------+------+
    |  3 |   12 |   122 | wang |
    +----+------+-------+------+
    1 row in set (0.00 sec)
  • session2: 第二个事务去执行更新id为3的记录，将age更新为10，并进行提交

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> update user set age = 10 where id=3;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
  • session1：第一个事务在第二个事务提交之后进行普通查询与强制查询，可以看到普通查询是快照读，for update查询是当前读。

    mysql> select * from user where id=3;
    +----+------+-------+------+
    | id | age  | phone | name |
    +----+------+-------+------+
    |  3 |   12 |   122 | wang |
    +----+------+-------+------+
    1 row in set (0.00 sec)

    mysql> select * from user where id=3 for update;
    +----+------+-------+------+
    | id | age  | phone | name |
    +----+------+-------+------+
    |  3 |   10 |   122 | wang |
    +----+------+-------+------+
    1 row in set (0.00 sec)

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

7.3 主键索引

  • session1: 第一个事务首先去更新id为3的记录，并没有提交； 

    mysql> begin; 
    Query OK, 0 rows affected (0.00 sec)

    mysql> update user set name='www' where id = 3;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
  • session2：第二个事务去删除id为3的记录：

     mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> delete from user where id = 3;
    ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
  • session3: 第三个事务去插入id为2（该主键原来并不存在）的记录：

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into user values(2,8,111,'lll');
    Query OK, 1 row affected (0.00 sec)
  • session4: 第四个事务去更新id为8的记录：

    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)

    mysql> update user set name='zzz' where id = 8;
    Query OK, 0 rows affected (0.00 sec)
    Rows matched: 1  Changed: 0  Warnings: 0

分析

结合innodb_lock_waits、innodb_locks、innodb_trx三个表及上面的结果分析可以，通过条件为主键索引进行操作时，并不影响其他记录及间隙操作，只影响了该主键所在记录的操作，说明主键索引操作为行排它锁。

相关阅读：

Innodb实践总结（一）

Innodb实践总结（三）

Innodb实践总结（四）

本文来自网易实践者社区，经作者王志泳授权发布。

推荐博客