一文详解 MySQL 中的间隙锁

博客：https://www.emanjusaka.com
博客园：https://www.cnblogs.com/emanjusaka
公众号：emanjusaka的编程栈

by emanjusaka from https://www.emanjusaka.com/archives/mysql-gap-lock 彼岸花开可奈何
本文为原创文章，可能会更新知识点以及修正文中的一些错误，全文转载请保留原文地址，避免产生因未即时修正导致的误导。

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

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

触发条件

• 事务隔离级别为 REPEATABLE-READ（可重复读），间隙锁是为了防止幻读。

• 使用范围查询条件

• 使用等值查询但未命中记录时，如果查询的条件列上有索引

• 索引不唯一的情况

锁定区间

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

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

加锁规则

1. 加锁的基本单位是 next-key lock，它是左开右闭区间

2. 查找过程中访问到的对象才会加锁

3. 唯一索引上的等值查询，并且记录存在，next-key lock 退化为行锁

4. 唯一索引上的范围查询会访问到不满足条件的第一个值为止

5. 索引上的等值查询，会将距离最近的左边界和右边界作为锁定范围，如果索引不是唯一索引还会继续向右匹配，直到遇见第一个不满足条件的值，如果最后一个值不等于查询条件，Next-Key Lock 退化为间隙锁。

注意，delete 语句加锁的逻辑，其实跟 select ... for update 是类似的。

这些加锁规则并不全部适应 MySQL 的全部版本，在新版本的 MySQL 中可能会有一些变动，大体上是符合这些规则的。

我们也可以执行select * FROM performance_schema.data_locks;来查看具体加了什么锁。

• LOCK_TYPE

  • TABLE：表锁

  • RECORD：行级锁

• LOCK_MODE

  • X：next-key 锁

  • X,REC_NOT_GAP：记录锁

  • X,GAP：间隙锁

间隙锁的作用

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

案例演示

环境：MySQL8.3.0，InnoDB引擎， RR 隔离级别

表结构：

CREATE TABLE t (
   id int(11) NOT NULL,
   c int(11) DEFAULT NULL,
   d int(11) DEFAULT NULL,
   PRIMARY KEY (id),
   KEY c (c)
 ) ENGINE=InnoDB;
 ​
 insert into t values(0,0,0),(5,5,5),
 (10,10,10),(15,15,15),(20,20,20);

id	c	d
0	0	0
5	5	5
10	10	10
15	15	15
20	20	20

存在的间隙：

• (-∞,0]

• (0,5]

• (5,10]

• (10,15]

• (15,20]

• (20, supremum]

InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合“左开右闭区间”。

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

时刻	事务 1	事务 2
t1	begin; select * from t where id = 5 for update;
t2		begin; insert into t value(4,4,4); --- success
		insert into t value(6,6,6); --- success

根据规则 3，事务 1 中执行的 select 语句,会退化成行锁，只会锁定 5 这一行记录

索引等值锁定

时刻	事务 1	事务 2
t1	begin; select * from t where id = 3 for update; --- 不存在的数据
t2		begin; insert into t value(6,6,6); --- success
t3		insert into t value(4,4,4); --- block

根据规则 1加锁范围是（0,5],再根据规则 5由于向右遍历最后一个值 5 不满足等值条件退化为间隙锁(0,5)

插入 数据6 不在间隙锁的范围内可以插入成功，而数据 4 是间隙锁锁定范围进入阻塞状态

唯一索引范围锁定

时刻	事务 1	事务 2
t1	begin; select * from t where id >= 5 and id < 6 for update;
t2		begin; insert into t value(7,7,7); --- block

这个可以分为两部分看

首先看 id > = 5，锁定区间(0,5]根据规则 1 和规则 3 会退化成行锁

再看 id < 6 根据规则 4 锁定区间为(5,10]

综合两部分最终锁定区间[5,10]

注意：这个锁定区间在不同版本的 MySQL 中可能表现不同，在 8.3.0 版本中第二部分会退化成间隙锁，锁定区间变成了[5,10)

非唯一索引范围锁定

时刻	事务 1	事务 2	事务 3
t1	begin; select * from t where c >= 10 and c < 11 for update;
t2		begin; insert into t values(7,7,7); --block
t3			begin; update t set d = d 1 where c = 15; --block

用 c =10 定位记录，给索引 c 加上了 next-key lock (5,10],它不会退化成行锁，索引 c 不是唯一索引。因此最终索引 c 上加的(5,10] 和 (10,15]这两个 next-key lock

limit语句加锁

时刻	事务 1	事务 2
t1	begin; select * from t where c = 10 limit 1;
		begin; insert into t values(12,12,12); ---success

如果没有 limit 语句的话在索引 c 上加锁范围是从（c=5,id=5）到 （c =15,id=15）。

但是因为加了 limit 1 的限制，因此在访问（c=10,id=10）这一行时，满足条件的语句已经有1 条了循环结束。

因此索引 c 上的加锁范围就变成了从（c=5，id=5）到（c=10，id=10）。

参考资料

1. 为什么我只改一行的语句，锁这么多？

2. MySQL 记录锁 间隙锁可以防止删除操作而导致的幻读吗？

谦学于心，谷纳万物，静思致远，共筑收获之旅！
原文地址： https://www.emanjusaka.com/archives/mysql-gap-lock