查看原文
其他

innodb存储引擎锁的实现(二)

王波·沃趣科技 沃趣技术 2023-03-10


作者   王波·沃趣科技数据库技术专家

出品   沃趣科技



上期《innodb存储引擎锁的实现(一)》中,我们已经介绍了共享锁和排他锁、意向锁、记录锁、间隙锁,今天为大家介绍Next-Key Lock。


(5)Next-Key Lock

Next-Key Lock 是结合了Gap Lock和Record Lock的合并,其设计目的主要是为解决RR级别下的幻读问题。该锁定方式相对于Gap Lock和Record Lock是带闭合区间的范围锁定。 

以下介绍其特点:

  • Innodb存储引擎使用Next-Key Locks 只在 REPEATABLE READ 隔离级别下。

  • 当进行查询或者索引扫描时,innodb存储引擎以行锁的方式进行锁定,它会将符合条件的索引记录使用S锁或者X锁。因此,行级锁实际上就是对索引记录上锁。Next-Key Locks会影响 gap锁 的上一个索引记录,也就是Next-Key Locks是由索引记录锁加上gap 锁组成。如果一个会话,在索引记录R上有一个共享或者独占锁,在索引记录R与上一个索引记录之间的间隙,另一个会话不可能插入一个新的索引记录。

  • Next-Key Locks在某些情况下可以锁住索引记录的最大值和大于最大值的范围,大于最大索引记录的范围称为 "supremum pseudo-record"伪记录。 
    注:官方文档并没有介绍对Next-Key Lock介绍太多,所以我们通过以下场景来对Next-Key Lock的特点进行说明。

  • 场景一:RR隔离级别下对一张只有主键的表进行操作

    • 主键只是由一列构成

root@localhost : test1 07:58:18> select * from t1;
+----+------+
| id | xid |
+----+------+
|
 1 | 1 |
| 2 | 1 |
|
 4 | 3 |
| 7 | 7 |
|
 10 | 9 |
+----+------+
5 rows in set (0.00 sec)
root@localhost : test1 07:58:10> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t2 | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)



root@localhost : (none) 05:37:52> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 1611:1060 | 1611 | 63 | 94 | test | t1 | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1611:3:4:4 | 1611 | 63 | 94 | test | t1 | NULL | NULL | PRIMARY | 140152030659896 | RECORD | X | GRANTED | 4 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)


事务A造成的锁

| INNODB | 1611:1060 | 1611 | 63 | 94 | test | t1 | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1611:3:4:4 | 1611 | 63 | 94 | test | t1 | NULL | NULL | PRIMARY | 140152030659896 | RECORD | X | GRANTED | 4 |
由于我们显示的定义了主键可以看到这里LOCK_DATA=4就是这个表内建的rowid ,说明聚集索引上的id=4的行记录直接被加了X锁,也并没有用到GAP锁。

该场景与GAP锁的场景相比较可以发现,在RR隔离级别下当过滤条件的列是主键或者唯一索引的情况下,因为该列值都是唯一值,innodb存储引擎会对Next-Key Lock进行优化,Next-Key Lock会降级成为Record Lock。换句话说gap锁只存在于RR隔离级别下的辅助索引中,主键和唯一索引由于本身具有唯一约束,不需要gap锁,只有record lock

  • 如果主键由多列构成,但是只使用其中的一列进行查询呢?

root@localhost : test 05:03:13> select * from t3;
+----+-----+------+
| id | xid | name |
+----+-----+------+
| 1 | 1 | a |
| 2 | 1 | b |
| 4 | 3 | c |
| 7 | 7 | d |
| 10 | 9 | e |
+----+-----+------+
5 rows in set (0.00 sec)
root@localhost : test 05:03:18> show index from t3;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
 t3 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES |
| t3 | 0 | PRIMARY | 2 | xid | A | 5 | NULL | NULL | | BTREE | | | YES |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.29 sec)



root@localhost : (none) 05:05:51> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 2123:1062 | 2123 | 68 | 40 | test | t3 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2123:5:4:4 | 2123 | 68 | 40 | test | t3 | NULL | NULL | PRIMARY | 139846618225784 | RECORD | X | GRANTED | 4, 3 |
| INNODB | 2123:5:4:5 | 2123 | 68 | 40 | test | t3 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X,GAP | GRANTED | 7, 7 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
3 rows in set (0.00 sec)

我们看到了什么?可以发现当主键由多列构成时,我们只使用主键列中的一列进行查询时,依然使用到了Next_Key Lock ,为什么这样? 

我们都知道主键的键值是唯一的,但是我们这里定义的主键是primary key(id,xid) 表示的是(id,xid) 组成的键值是唯一的,并不能保证id或者xid的键值是唯一的,所以这里依然使用Next_Key Lock 来进行加锁并没有降级使用Record lock 来进行加锁。

  • 那当使用主键所有列进行查询时是什么样子的?

root@localhost : (none) 05:08:52> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 2125:1062 | 2125 | 68 | 44 | test | t3 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2125:5:4:4 | 2125 | 68 | 44 | test | t3 | NULL | NULL | PRIMARY | 139846618225784 | RECORD | X | GRANTED | 4, 3 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

可以看到当我们使用主键的所有列进行查询时Next_Key Lock 降级为Record Lock 。

  • 场景二:RR隔离级别下对一张只有非唯一索引的表做操作 
    在RR隔离级别下,当表中只有一个索引并且为非唯一索引条件上进行等值当前读或者范围当前读时,其加锁是怎样的?

admin@localhost : test 03:55:34> set session transaction_isolation='REPEATABLE-READ' ;
Query OK, 0 rows affected (0.00 sec)
admin@localhost : test 03:29:44> show variables like '%lation';
+---------------+-----------------+
| Variable_name | Value          |
+---------------+-----------------+
|
 transaction_isolation| REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)
root@localhost : test1 12:12:40> select * from t;
+------+------+
| id | xid |
+------+------+
|
 1 | 1 |
| 2 | 1 |
|
 4 | 3 |
| 7 | 7 |
|
 10 | 9 |
+------+------+
5 rows in set (0.00 sec)
root@localhost : test1 12:08:24> show index from t;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t3 | 1 | xid | 1 | xid | A | 3 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)


  • 等值当前读


root@localhost : (none) 06:51:25> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| INNODB | 1622:1059 | 1622 | 64 | 86 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL |
| INNODB | 1622:2:5:5 | 1622 | 64 | 86 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | X,GAP | WAITING | 7, 0x000000000203 |
| INNODB | 1621:1059 | 1621 | 63 | 111 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1621:2:5:4 | 1621 | 63 | 111 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 |
| INNODB | 1621:2:4:4 | 1621 | 63 | 111 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
| INNODB | 1621:2:5:5 | 1621 | 63 | 111 | test | t | NULL | NULL | xid | 140152030660584 | RECORD | X,GAP | GRANTED | 7, 0x000000000203 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
6 rows in set (0.00 sec)


事务A造成的锁

| INNODB | 1621:1059 | 1621 | 63 | 111 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1621:2:5:4 | 1621 | 63 | 111 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 |
| INNODB | 1621:2:4:4 | 1621 | 63 | 111 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
| INNODB | 1621:2:5:5 | 1621 | 63 | 111 | test | t | NULL | NULL | xid | 140152030660584 | RECORD | X,GAP | GRANTED | 7, 0x000000000203 |


事务B造成的锁

| INNODB | 1622:1059 | 1622 | 64 | 86 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL |
| INNODB | 1622:2:5:5 | 1622 | 64 | 86 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | X,GAP | WAITING | 7, 0x000000000203 |
由于事务A在辅助索引记录 (3,7)之间是间隙锁,而事务B插入的数据(id,xid) ===>(5,5) 中的xid=5在(3,7) 间隙锁范围中,所以才会显示LOCK_MODE =X,GAP   LOCK_STATUS=WAITING      LOCK_DATA = 70x000000000203   会等待事务A将锁释放直至超时。

事务C说明事务A造成的间隙锁实际上并没有将辅助索引记录xid=7也锁住,不包含记录xid=7。

事务A的加锁方式下图所示:

  • 范围当前读


root@localhost : (none) 07:08:05> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| INNODB | 1624:1059 | 1624 | 64 | 93 | test | t | NULL | NULL | NULL | 140152030668888 | TABLE | IX | GRANTED | NULL |
| INNODB | 1624:2:5:5 | 1624 | 64 | 93 | test | t | NULL | NULL | xid | 140152030665848 | RECORD | X | WAITING | 7, 0x000000000203 |
| INNODB | 1623:1059 | 1623 | 63 | 115 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
| INNODB | 1623:2:5:4 | 1623 | 63 | 115 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 |
| INNODB | 1623:2:5:5 | 1623 | 63 | 115 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 1623:2:4:4 | 1623 | 63 | 115 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
6 rows in set (0.00 sec)


事务A 造成的锁

1| INNODB | 1623:1059 | 1623 | 63 | 115 | test | t | NULL | NULL | NULL | 140152030662936 | TABLE | IX | GRANTED | NULL |
2| INNODB | 1623:2:5:4 | 1623 | 63 | 115 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 3, 0x000000000202 |
3| INNODB | 1623:2:5:5 | 1623 | 63 | 115 | test | t | NULL | NULL | xid | 140152030659896 | RECORD | X | GRANTED | 7, 0x000000000203 |
与 select * from t where xid=3 for update; 当前读相比通过mysql 8.0的performance_schema.data_locks表可以看到辅助索引记录xid=7 的LOCK_MODE =X,GAP  其不会锁定辅助索引xid=7记录,而范围当前读会将其锁住
4| INNODB | 1623:2:4:4 | 1623 | 63 | 115 | test | t | NULL | NULL | GEN_CLUST_INDEX | 140152030660240 | RECORD | X | GRANTED | 0x000000000202 |
。 



  • 如果是select * from t where xid>1 and xid<=7 for update; 又是怎么样的呢?


root@localhost : (none) 09:49:17> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
| INNODB | 2061:1059 | 2061 | 64 | 24 | test | t | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2061:2:5:4 | 2061 | 64 | 24 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3, 0x000000000202 |
| INNODB | 2061:2:5:5 | 2061 | 64 | 24 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 2061:2:5:6 | 2061 | 64 | 24 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9, 0x000000000205 |
| INNODB | 2061:2:4:4 | 2061 | 64 | 24 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000202 |
| INNODB | 2061:2:4:5 | 2061 | 64 | 24 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000203 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+-------------------+
6 rows in set (0.62 sec)


其加锁下图所示: 


说明:由于该表没有主键只有一个非唯一的辅助索引key(xid ),使用6字节rowid做聚集索引。


  • 如果是select * from t where xid>3 for update; 又是怎么样的呢?


root@localhost : test 03:25:06> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
| INNODB | 2100:1059 | 2100 | 65 | 46 | test | t | NULL | NULL | NULL | 139846618246760 | TABLE | IX | GRANTED | NULL |
| INNODB | 2100:2:5:1 | 2100 | 65 | 46 | test | t | NULL | NULL | xid | 139846618243720 | RECORD | X | WAITING | supremum pseudo-record |
| INNODB | 2099:1059 | 2099 | 67 | 28 | test | t | NULL | NULL | NULL | 139846618240760 | TABLE | IX | GRANTED | NULL |
| INNODB | 2099:2:5:6 | 2099 | 67 | 28 | test | t | NULL | NULL | xid | 139846618237880 | RECORD | X,GAP | WAITING | 9, 0x000000000205 |
| INNODB | 2098:1059 | 2098 | 66 | 40 | test | t | NULL | NULL | NULL | 139846618234776 | TABLE | IX | GRANTED | NULL |
| INNODB | 2098:2:5:5 | 2098 | 66 | 40 | test | t | NULL | NULL | xid | 139846618231848 | RECORD | X,GAP | WAITING | 7, 0x000000000203 |
| INNODB | 2097:1059 | 2097 | 64 | 75 | test | t | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2097:2:5:1 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | supremum pseudo-record |
| INNODB | 2097:2:5:5 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 2097:2:5:6 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9, 0x000000000205 |
| INNODB | 2097:2:4:5 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000203 |
| INNODB | 2097:2:4:6 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000205 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+-----------------+-----------------------+-----------+-----------+-------------+------------------------+
12 rows in set (0.00 sec)


事务A造成的锁

| INNODB | 2097:1059 | 2097 | 64 | 75 | test | t | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2097:2:5:1 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | supremum pseudo-record |
这里可以看到与之前的不同,上确界添加了X锁,由于使用 select * from t where xid>3 for update; 该表的xid的上确界为9,它需要将9到正无穷也要锁住,supremum pseudo-record 上确界伪记录
| INNODB | 2097:2:5:5 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 0x000000000203 |
| INNODB | 2097:2:5:6 | 2097 | 64 | 75 | test | t | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9, 0x000000000205 |
| INNODB | 2097:2:4:5 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000203 |
| INNODB | 2097:2:4:6 | 2097 | 64 | 75 | test | t | NULL | NULL | GEN_CLUST_INDEX | 139846618226128 | RECORD | X | GRANTED | 0x000000000205 |


下图所示: 


在RR隔离级别下,对一张只有非唯一辅助索引等值当前读和范围当前读造成的锁,以t表为例。

  • 我们可以得出以下结论: 
    在RR隔离级别下,如果表中只有一个非唯一的辅助索引,当进行等值当前读时会与下一行记录形成间隙锁,但不会锁住下一行记录;范围当前读时会与下一行记录形成间隙锁并且会锁住该行。

    • 那么可能有人会问在RR隔离级别下,为什么在对辅助索引做范围当前读时会与下一行记录形成间隙锁并且还会锁住该行呢? 
      因为对于非唯一的辅助索引不能保证键值的唯一,所以需要锁住满足条件的下一行进行判断,当然这里还有查询优化器的功劳。

  • 场景三:RR隔离级别下对一张有主键和辅助索引的表做操作

root@localhost : test 10:20:09> select * from t2;
+----+------+
| id | xid |
+----+------+
|
 1 | 1 |
| 2 | 1 |
|
 4 | 3 |
| 7 | 7 |
|
 10 | 9 |
+----+------+
5 rows in set (0.00 sec)
root@localhost : test 10:20:02> show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
|
 t2 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | | YES |
| t2 | 1 | xid | 1 | xid | A | 4 | NULL | NULL | YES | BTREE | | | YES |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
2 rows in set (0.04 sec)


  • 等值当前读


root@localhost : (none) 10:29:14> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 2070:1061 | 2070 | 65 | 14 | test | t2 | NULL | NULL | NULL | 139846618246760 | TABLE | IX | GRANTED | NULL |
| INNODB | 2070:4:4:5 | 2070 | 65 | 14 | test | t2 | NULL | NULL | PRIMARY | 139846618243720 | RECORD | X | GRANTED | 7 |
| INNODB | 2069:1061 | 2069 | 67 | 18 | test | t2 | NULL | NULL | NULL | 139846618240760 | TABLE | IX | GRANTED | NULL |
| INNODB | 2069:4:5:5 | 2069 | 67 | 18 | test | t2 | NULL | NULL | xid | 139846618237880 | RECORD | X,GAP | WAITING | 7, 7 |
| INNODB | 2068:1061 | 2068 | 66 | 22 | test | t2 | NULL | NULL | NULL | 139846618234776 | TABLE | IX | GRANTED | NULL |
| INNODB | 2068:4:5:4 | 2068 | 66 | 22 | test | t2 | NULL | NULL | xid | 139846618231848 | RECORD | X,GAP | WAITING | 3, 4 |
| INNODB | 2065:1061 | 2065 | 64 | 43 | test | t2 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2065:4:5:4 | 2065 | 64 | 43 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3, 4 |
| INNODB | 2065:4:4:4 | 2065 | 64 | 43 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 4 |
| INNODB | 2065:4:5:5 | 2065 | 64 | 43 | test | t2 | NULL | NULL | xid | 139846618226472 | RECORD | X,GAP | GRANTED | 7, 7 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
10 rows in set (0.01 sec)


事务A造成的锁下图所示:

| INNODB | 2065:1061 | 2065 | 64 | 43 | test | t2 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2065:4:5:4 | 2065 | 64 | 43 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3, 4 |
| INNODB | 2065:4:4:4 | 2065 | 64 | 43 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 4 |
| INNODB | 2065:4:5:5 | 2065 | 64 | 43 | test | t2 | NULL | NULL | xid | 139846618226472 | RECORD | X,GAP | GRANTED | 7, 7 |



分析:在RR隔离级别下,一张带有主键和非唯一的辅助索引表,使用辅助索引进行当前读时与只有非唯一的辅助索引表进行等值当前读时,都不会将辅助索引的下一行记录进行加锁,只会将下一行记录与前一行记录添加间隙锁。

  • 范围当前读

事务A的造成的锁:

root@localhost : test 11:07:47> select * from performance_schema.data_locks;
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
| INNODB | 2086:1061 | 2086 | 64 | 52 | test | t2 | NULL | NULL | NULL | 139846618228824 | TABLE | IX | GRANTED | NULL |
| INNODB | 2086:4:5:4 | 2086 | 64 | 52 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 3, 4 |
| INNODB | 2086:4:5:5 | 2086 | 64 | 52 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 7, 7 |
| INNODB | 2086:4:5:6 | 2086 | 64 | 52 | test | t2 | NULL | NULL | xid | 139846618225784 | RECORD | X | GRANTED | 9, 10 |
| INNODB | 2086:4:4:4 | 2086 | 64 | 52 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 4 |
| INNODB | 2086:4:4:5 | 2086 | 64 | 52 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 7 |
| INNODB | 2086:4:4:6 | 2086 | 64 | 52 | test | t2 | NULL | NULL | PRIMARY | 139846618226128 | RECORD | X | GRANTED | 10 |
+--------+----------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
7 rows in set (0.00 sec)




总结

综上所述,在RR隔离级别下的加锁方式:


说明:对于唯一键值的锁定,next_key lock 降级为 Record lock 仅存在于查询所有的唯一索引列。如果唯一索引有多个列组成,而查询仅是多个唯一索引列中的一个,那么查询其实是rang类型的查询,而不是point类型的查询,故innodb存储引擎依然使用next_key lock进行锁定。

该篇文章包含了在RR隔离级别下的DML语句的加锁情况,DML语句都是当前读,可能有人会问那insert是如何进行加锁的,其实在整个过程中可以发现,在innodb存储引擎中,对于insert操作,其都会检查插入记录的下一条记录是否已经被锁定,若被锁定,则不允许查询,所以我们的每次插入都会失败,返回请求锁超时。下篇将为大家详细描述insert是如何加锁!


参考资料: 
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html 
http://mysql.taobao.org/monthly/2018/05/04/ 
http://mysql.taobao.org/monthly/2016/01/01/ 
http://hedengcheng.com/?p=771


|  作者简介

王波,沃趣科技数据库技术专家

熟悉MySQL内部机制、丰富的主从复制故障诊断解决、性能优化、不同场景下数据库备份恢复及迁移经验。

相关链接

innodb存储引擎锁的实现(一)

错过“kubernetes存储系列”这门课,你肯定会后悔

MySQL 的 help 命令你真的会用吗?

Oracle Data Guard功能12cR2系列(二)

MySQL8.0 - Resource Group(资源组)

SQL优化案例| 从云服务器迁移到国产沃趣一体机一般方法探究(四)


更多干货,欢迎来撩~

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存