mysql数据锁之间隙锁

约 2077 字大约 7 分钟

2025-05-19

数据库版本

mysql> select version() as '数据库版本', @@transaction_isolation as '事务级别';
+-----------------+-----------------+
| 数据库版本      | 事务级别        |
+-----------------+-----------------+
| 8.4.5           | REPEATABLE-READ |
+-----------------+-----------------+
1 row in set (0.00 sec)

什么是间隙锁

间隙锁（Gap Lock）是（RR 级别下）一个在索引记录之间的间隙上的锁，可以是两个索引记录之间，也可能是第一个索引记录之前或最后一个索引之后的空间。

间隙锁是 MySQL 行锁的一种，与行锁不同的是 间隙锁可能锁定的是一行数据，也可能锁住一个间隙。

间隙锁触发条件

事务隔离级别为 REPEATABLE-READ（RR, 可重复读） ，间隙锁是 为了防止幻读 。
使用范围查询条件
使用等值查询但未命中记录时，如果查询的条件列上有索引, 会有间隙锁
索引不唯一的情况

锁定区间

间隙锁会 向左找第一个比当前索引值小的值，向右找第一个比当前索引值大的值（没有则为正无穷） ，将此区间锁住，从而阻止其他事务在此区间插入数据

间隙锁（Gap Lock），左右都是开区间，间隙锁 + 行锁组合成 Next-key lock，左开右闭区间。

加锁规则

1. 加锁的基本单位是 next-key lock，它是左开右闭区间
1. 查找过程中访问到的对象才会加锁, 任何辅助索引上的锁，或者非索引列上的锁，最终都要回溯到主键上，在主键上也要加一把锁。
1. 唯一索引上的等值查询，并且记录存在，next-key lock 退化为行锁
1. 唯一索引上的范围查询会访问到不满足条件的第一个值为止
1. 索引上的等值查询，会将距离最近的左边界和右边界作为锁定范围，如果索引不是唯一索引还会继续向右匹配，直到遇见第一个不满足条件的值，如果最后一个值不等于查询条件，Next-Key Lock 退化为间隙锁。

注意

注意，delete 语句加锁的逻辑，其实跟 select ... for update 是类似的。
这些加锁规则并不全部适应 MySQL 的全部版本，在不同版本的 MySQL 中可能会有一些变动，大体上是符合这些规则的。

间隙锁的作用

与行锁（例如乐观锁高级实现，MVCC）组合成 Next-key lock，在可重复读这种隔离级别下一起工作避免幻读。

实操示例

测试数据表

CREATE TABLE `people` (
    `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    `code` varchar(20)  COLLATE  utf8mb4_bin  NOT NULL DEFAULT  '',
    `age`  int unsigned  NOT NULL DEFAULT  '0',
    `name`  varchar(30)  COLLATE  utf8mb4_bin  NOT NULL DEFAULT  '',
    `height`  int unsigned  NOT NULL DEFAULT  '0',
    `address`  varchar(30)  COLLATE  utf8mb4_bin  NOT NULL DEFAULT  '',
    PRIMARY  KEY  (`id`),
    KEY  `idx_code_age_name`  (`code`,`age`,`name`),
    KEY  `idx_height`  (`height`)
)  ENGINE=InnoDB  AUTO_INCREMENT=1  DEFAULT  CHARSET=utf8mb4  COLLATE=utf8mb4_bin;
INSERT INTO people (id, code, age, name, height, address) VALUES (1, '101', 21, '周星驰', 175, '香港'), (2, '102', 18, '周杰伦', 173, '台湾'), (3, '103', 23, '苏三', 174, '成都'), (8, '103', 18, '李四', 175, '北京'), (9, '104', 18, '王五', 175, '北京'), (10, '103', 18, '赵六', 175, '北京');

唯一索引等值锁定存在的数据

时间	事务1	事务2
t1	begin; select * from people where id = 10 for update; -- 数据存在
t2		begin; insert into people value(6, '101', 21, '陈福', 175, '香港'); --- success
		insert into people value(11, '101', 21, '那小谁', 175, '香港'); --- success

t1步骤, 加锁完成之后, 查询当前锁信息, 会看到如下数据:

mysql> begin; 
mysql> select * from people where id = 10 for update;
mysql> 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 | 140213280087176:3366:1554:140213442601344    |                 98151 |     71931 |       59 | blog_test     | people      | NULL           | NULL              | NULL       |       140213442601344 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 140213280087176:3366:296:4:6:140213443756064 |                 98151 |     71931 |       59 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756064 | RECORD    | X,REC_NOT_GAP | GRANTED     | 9         |
+--------+----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
3 rows in set (0.00 sec)
mysql> rollback;

从结果，可以看出, 此条sql语句加锁的是主键索引, 并且因为是主键等值查询, 加的是行锁(LOCK_TYPE=RECORD), 并且是记录锁(LOCK_MODE=X,REC_NOT_GAP) 而不是间隙锁。

同时还加了表级别的意向锁, 具体意向锁(lock_mode=IX), 会在后面介绍.

唯一索引等值锁定不存在的数据

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

mysql> select * from people where id = 6 for update;
Empty set (0.00 sec)

mysql> 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 | 140213280087176:3370:1554:140213442601344    |                 98153 |     71931 |       70 | blog_test     | people      | NULL           | NULL              | NULL       |       140213442601344 | TABLE     | IX        | GRANTED     | NULL      |
| INNODB | 140213280087176:3370:296:4:5:140213443756064 |                 98153 |     71931 |       70 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756064 | RECORD    | X,GAP     | GRANTED     | 8         |
+--------+----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+
2 rows in set (0.00 sec)

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

从结果，可以看出, 此条sql语句加锁的是主键索引, 并且因为是主键等值查询, 加的是行锁(LOCK_TYPE=RECORD), 并且是间隙锁(LOCK_MODE=X,GAP)。

此逻辑命中加锁规则第1条, 间隙锁锁定范围为 (3, 8], 右侧第一条不满足条件数据id = 8, 所以 lock_data = 8

唯一索引范围锁定

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

mysql> select * from people where id >= 3 and id < 10 for update;
+----+------+-----+--------+--------+---------+
| id | code | age | name   | height | address |
+----+------+-----+--------+--------+---------+
|  3 | 103  |  23 | 苏三   |    174 | 成都    |
|  8 | 103  |  18 | 李四   |    175 | 北京    |
|  9 | 104  |  18 | 王五   |    175 | 北京    |
+----+------+-----+--------+--------+---------+
3 rows in set (0.00 sec)

mysql> 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 | 140213280087176:3376:1554:140213442601344    |                 98156 |     71931 |       90 | blog_test     | people      | NULL           | NULL              | NULL       |       140213442601344 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 140213280087176:3376:296:4:7:140213443756752 |                 98156 |     71931 |       90 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756752 | RECORD    | X,GAP         | GRANTED     | 10        |
| INNODB | 140213280087176:3376:296:4:5:140213443756408 |                 98156 |     71931 |       90 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756408 | RECORD    | X             | GRANTED     | 8         |
| INNODB | 140213280087176:3376:296:4:6:140213443756408 |                 98156 |     71931 |       90 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756408 | RECORD    | X             | GRANTED     | 9         |
| INNODB | 140213280087176:3376:296:4:4:140213443756064 |                 98156 |     71931 |       90 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756064 | RECORD    | X,REC_NOT_GAP | GRANTED     | 3         |
+--------+----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
5 rows in set (0.00 sec)

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

首先条件 >= 3，命中规则1, 锁定间隙 (0,3], 然后退退化成行锁, 所以LOCK_DATA=3, LOCK_MODE=X,REC_NOT_GAP 然后条件 < 10, 命中规则1, 锁定间隙 (3,10], 所以LOCK_DATA=8/9, 直接持有排它锁(LOCK_MODE=X) LOCAK_DATA=10, 除了排它锁还有一个间隙锁最终整体锁定区间为 [3,10]

非唯一索引范围锁定

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

mysql> select * from people where height > 170 and height < 200 for update;
+----+------+-----+-----------+--------+---------+
| id | code | age | name      | height | address |
+----+------+-----+-----------+--------+---------+
|  2 | 102  |  18 | 周杰伦    |    173 | 台湾    |
|  3 | 103  |  23 | 苏三      |    174 | 成都    |
|  1 | 101  |  21 | 周星驰    |    175 | 香港    |
|  8 | 103  |  18 | 李四      |    175 | 北京    |
|  9 | 104  |  18 | 王五      |    175 | 北京    |
| 10 | 103  |  18 | 赵六      |    175 | 北京    |
+----+------+-----+-----------+--------+---------+
6 rows in set (0.00 sec)

mysql> 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 | 140213280087176:3378:1554:140213442601344    |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | NULL       |       140213442601344 | TABLE     | IX            | GRANTED     | NULL                   |
| INNODB | 140213280087176:3378:296:6:1:140213443756064 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | idx_height |       140213443756064 | RECORD    | X             | GRANTED     | supremum pseudo-record |
| INNODB | 140213280087176:3378:296:6:2:140213443756064 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | idx_height |       140213443756064 | RECORD    | X             | GRANTED     | 175, 1                 |
| INNODB | 140213280087176:3378:296:6:3:140213443756064 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | idx_height |       140213443756064 | RECORD    | X             | GRANTED     | 173, 2                 |
| INNODB | 140213280087176:3378:296:6:4:140213443756064 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | idx_height |       140213443756064 | RECORD    | X             | GRANTED     | 174, 3                 |
| INNODB | 140213280087176:3378:296:6:5:140213443756064 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | idx_height |       140213443756064 | RECORD    | X             | GRANTED     | 175, 8                 |
| INNODB | 140213280087176:3378:296:6:6:140213443756064 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | idx_height |       140213443756064 | RECORD    | X             | GRANTED     | 175, 9                 |
| INNODB | 140213280087176:3378:296:6:7:140213443756064 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | idx_height |       140213443756064 | RECORD    | X             | GRANTED     | 175, 10                |
| INNODB | 140213280087176:3378:296:4:2:140213443756408 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756408 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1                      |
| INNODB | 140213280087176:3378:296:4:3:140213443756408 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756408 | RECORD    | X,REC_NOT_GAP | GRANTED     | 2                      |
| INNODB | 140213280087176:3378:296:4:4:140213443756408 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756408 | RECORD    | X,REC_NOT_GAP | GRANTED     | 3                      |
| INNODB | 140213280087176:3378:296:4:5:140213443756408 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756408 | RECORD    | X,REC_NOT_GAP | GRANTED     | 8                      |
| INNODB | 140213280087176:3378:296:4:6:140213443756408 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756408 | RECORD    | X,REC_NOT_GAP | GRANTED     | 9                      |
| INNODB | 140213280087176:3378:296:4:7:140213443756408 |                 98157 |     71931 |       95 | blog_test     | people      | NULL           | NULL              | PRIMARY    |       140213443756408 | RECORD    | X,REC_NOT_GAP | GRANTED     | 10                     |
+--------+----------------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+------------------------+
14 rows in set (0.00 sec)

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

此处注意 supremum pseudo-record 这个特殊记录, 它表示了索引的最大值为正无穷, 即左右数据 height > 170 均满足, 所以锁定区间为 (170,正无穷)，换句话说, 相当于变相锁表了, 此时所有事务的写入/修改/加锁均陷入锁等待

命中 height索引的数据, LOCK_DATA=height值,主键ID, 同时满足规则2，因为不是对主键索引加锁, 所以回溯到主键上，在主键上也要加一把锁。

版权所有

版权归属：白茶清欢

许可证：署名 4.0 国际 (CC-BY-4.0)