MySQL：逃不掉的锁事，间隙锁

2023-12-06 微信公众号陆队长

我们知道在MySQL中存在幻读的情况，也就是一个事务在读取某个范围内的记录时，发现了另一个事务在该范围内新增了记录（或者删除了记录），导致两次读取的记录数量不一致，进而产生了“幻觉”一般的现象。也就是说，幻读是指在多个事务同时读取同一范围内的记录时所产生的矛盾现象。

MySQL为了解决幻读一般采用快照读和间隙锁的方式，其中快照读在之前的文章已经多次提及，本篇文章重点介绍间隙锁。

间隙锁意如其名，就是锁定符合条件但是实际不存在的记录，也就是一定的区间，防止其他事务在某个事务执行期间向该区间插入新的记录。

为清楚梳理间隙锁的作用，我们在本文中使用的示例表如下：

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),(25,25,25);

在示例表中执行如下语句：

begin;
select * from t where d=5 for update;
commit;

语句中的select for update就是为了在查询时，对相关语句进行加锁，避免其他用户对该表进行插入、修改、删除等操作，造成表的不一致。

d=5这一行对应主键为Id=5，执行select语句后改行会被加写锁，并在commit后释放。但是由于d列没有索引，所以会被全表扫描，这时候真实的加锁逻辑为：

全表扫描一般指主键索引树扫描；
对于会不会被加锁：

RC级别下，只会在满足条件的行加行锁（直至事务commit/rollback才会释放），不满足条件的是先加锁然后再直接释放锁；

RR级别下会加行锁+全表间隙锁(next-key lock是左开右闭，间隙锁是左开右开）；

这里可以先记住这个逻辑，我们在下面的文章中会逐步开始介绍。

1 幻读

1.1 幻读是什么

注意，如下的结论都是假设存在，从而引入间隙锁的概念。

如果没有间隙锁，只有行锁，即：上面的语句只会锁住：id=5的这一行数据，那么就会出现如下图所示的场景：

MySQL：逃不掉的锁事，间隙锁

for update在当前读可以理解为：MySQL认为for update已经给当前的行加了写锁，因此没有必要再进行快照读，但是这样会造成幻读的问题。

如果没有间隙锁，就会出现如下的结果：

Q1 只返回 id=5 这一行；
在 T2 时刻，session B 把 id=0 这一行的 d 值改成了 5，因此 T3 时刻 Q2 查出来的是 id=0 和 id=5 这两行；
在 T4 时刻，session C 又插入一行（1,1,5），因此 T5 时刻 Q3 查出来的是 id=0、id=1 和 id=5 的这三行。

Q3读到id=1这一行的现象就是”幻读“，即：在同一个事务中，两次读取到的数据不一致的情况可称为幻读和不可重复读，其中幻读针对insert导致的数据不一致，不可重复读针对的delete/update导致的数据不一致。注意：这里的读指的是当前读，比如查询语句中包含for update、in share mode，以及修改删除语句都会开启当前读，否则就是快照读。

快照读：指的是在语句执行之前或者在事务开始的时候创建一个一致性视图，后面的读都是基于这个视图，不会再去查询最新的值；
当前读：指的是更新之前必须先查询当前的值，因此叫做当前读，比如说：select for update或者select in share mode；

SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)，for update之后并不会阻塞其他session的快照读取操作；

除了select ...lock in share mode和select ... for update这种显示加锁的查询操作。通过对比，发现for update的加锁方式无非是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读

1.2 幻读的问题

1.2.1 语义上的问题

sessionA在T1时刻声明：把所有d=5的行锁住，不允许其他的事务进行读写操作，但是sessionB和sessionC却能够随意改变语义，新增或者通过修改了对应行的值。

MySQL：逃不掉的锁事，间隙锁

1.2.2 数据一致性问题

锁的设计不仅仅是数据库内存数据状态的一致性，还包括数据与日志在逻辑上的一致性。

MySQL：逃不掉的锁事，间隙锁

如果没有间隙锁，上面的操作在binlog的记录（binlog是在commit提交时进行记录）就是：

/** session B提交语句 */
update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/
/** session C提交语句 */
insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/
/** session A提交语句 */
update t set d=100 where d=5;/*所有d=5的行，d改成100*/

使用该binlog恢复或者备份，三行中d=100，出现异常；

进一步，我们增加写锁。

MySQL：逃不掉的锁事，间隙锁

在binlog的记录为：

insert into t values(1,1,5); /*(1,1,5)*/
update t set c=5 where id=1; /*(1,5,5)*/


update t set d=100 where d=5;/*所有d=5的行，d改成100*/


update t set d=5 where id=0; /*(0,0,5)*/
update t set c=5 where id=0; /*(0,5,5)*/

2 幻读的解决方法

2.1 next-key lock

因此上面的幻读产生的原因就是说，行锁只是锁住了行，但是新插入记录这个动作，要更新的是记录之间的间隙。这也是InnoDB引入间隙锁（Gap Lock）的原因。

MySQL：逃不掉的锁事，间隙锁

间隙锁的增加逻辑为：

对主键或者唯一索引，如果当前读时，where条件全部精准命中（=或者in），这种场景本身就不会产生幻读，所以只会加行记录锁；
没有索引的列，当前读操作时，会加全表的gap锁；
非唯一索引列，如果where条件部分命中（>/</like等）或者全部没有命中，则会加附近Gap间的间隙锁；例如，某表数据如下，非唯一索引2,6,9,9,11,15。如下语句要操作非唯一索引列9的数据，gap锁将会锁定的列是(6,11]，该区间内无法插入数据。
跟间隙锁存在冲突关系的，是“往这个间隙中插入/更新/删除一条新的记录”这个操作，间隙锁之间不存在冲突关系。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。

2.2 next-key lock引入的问题

如下的示例，在索引唯一的时候，Insert ... on duplicate key update可用，但是如果有多个唯一键的时候，会有异常。

begin;
select * from t where id=N for update;


/*如果行不存在*/
insert into t values(N,N,N);
/*如果行存在*/
update t set d=N set id=N;


commit;

MySQL：逃不掉的锁事，间隙锁

在并发情况下，即使没有后续的update操作也会引入死锁。

sessionA执行select ... for update语句，由于id=9不存在，因此会加上间隙锁（5，10）；
sessionB执行select ... for update语句，由于id=9不存在，因此会加上间隙锁（5，10），间隙锁之间不存在冲突，因此可以执行成功；
session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

即：间隙锁的引入，可能会导致同样的语句锁住更大的范围，影响并发度。

2.3 读提交+row模式的Binlog解决幻读

间隙锁在可重复读隔离级别下才会出现，因此，如果把隔离级别设置为读提交，就可以避免幻读的问题。同时，为了解决可能出现的数据和日志不一致的问题，需要将Binlog的格式设置为row。

举例：删除 statement记录的是这个删除的语句，例如： delete from t where age>10 and modified_time<='2020-03-04' limit 1 而row格式记录的是实际受影响的数据是真实删除行的主键id，例如： delete from t where id=3 and age=12 and modified_time='2020-03-05'

那为什么RR级别不需要修改binlog_format呢：

间隙锁是可重复读级别下解决幻读的，同时解决了binlog和数据可能存在的不一致问题，即：binlog日志的写入顺序错误问题；
间隙锁解决了binlog的问题，而不是Binlog解决了间隙锁的问题；
读提交级别也有binlog执行顺序错误的问题，也没有间隙锁，因此，需要将binlog_format修改为row模式，来解决binlog可能带来的错误；
binlog的row模式比statement要记录的更全面，每一行记录改变都记录下来，导致日志大，同时IO次数更多；

如果业务不需要可重复读场景，考虑在读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。

2.4 读提交和可重复读

可重复读的场景举例，比如说：金融业务，财务需要统计过去一段时间内某些数据，需要反复根据某些条件查找，此时如果有新数据行插入，会导致统计时发生数据不一致的情况，此时需要使用可重复读的隔离级别。

又比如说逻辑备份时，mysqldump备份线程会设置为可重复读，这样在导数据时就会启动一个事务，确保拿到一致性视图。由于MVCC的支持，过程中数据可正常更新。使用可重复读，是为了保证备份的数据都是那一时刻的最新数据，然后通过binlog再做后续的恢复即可。

业务线程是读提交，备份线程是可重复读，同时存在两种事务隔离级别，是否会冲突？

答案是不会，因为不管是RC还是RR，都是MVCC支持，唯一不同在于生成快照的时间点不同，也就是能够看到的数据版本不同，所以并不影响。备份完成后，恢复为RC即可。

3 间隙锁的加锁规则

加锁规则总结如下：

原则1：加锁的基本单位是next-key lock，是前开后闭；
原则2：查找过程中访问到的对象（索引）才会加锁；
优化1：索引上的等值查询，如果可以匹配到对应数据，则给唯一索引加锁，next-key lock退化为行锁；如果匹配不到，按照原则2加锁；
优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件时，next-key lock退化为间隙锁；
一个bug：唯一索引的范围查询会访问到不满足条件的第一个值为止；【该bug已经在MySQL8.0.18版本开始修复，但是也有提出实际上只修复了主键上的问题，唯一索引没有修复，需要验证】

原则2也就解释了：

为什么未命中索引的查询要走全表扫描后导致全表加锁的原因；
这里说的访问到的对象，是从底层结构来看待，而不是数据表的一行。例如，普通索引和主键索引，如果访问到的是普通索引，而且通过索引覆盖并不需要回表查主键索引，那么主键索引上并不需要加任何的锁，因为并没有访问主键索引树上的对象。

本节还是使用章节组开始的表进行说明。

3.1 等值查询间隙锁

MySQL：逃不掉的锁事，间隙锁

表中没有id=7的记录，因此：

根据原则1，加锁单位为next-key lock，sessionA的加锁范围为：（5，10]；由于是根据id进行检索，所以会锁住主键索引对象；
根据优化2，sessionA为等值查询，id=10不满足查询条件，退化为间隙锁，因此加锁的最终范围为(5,10)；

因此，插入id=8的记录会被锁住，等待sessionA锁释放，sessionC修改id=10这一行可以正常执行。

3.2 非唯一索引等值锁

MySQL：逃不掉的锁事，间隙锁

这个例子说明的就是原则2中的对象。

注意：sessionA要给索引c=5加读锁，而且是索引c获取主键，实际上就是覆盖索引，不需要回表。

根据原则1，加锁单位为next-key lock，给(0,5]加next-key lock；
c为普通索引，且非唯一，需要向右遍历到第一个不符合条件的值才能停止，即：直到c=10放弃。根据原则2，被访问到的对象都需要加锁，因此，(5,10]加next-key lock；
根据优化2，因为是等值判断，最后一个值不满足c=5，因此退化为间隙锁（5,10）；
根据原则2，只有被访问到的对象才会加锁，这个查询使用覆盖索引，并不需要主键索引，所以主键索引没有加任何索，sessionB的update语句可以执行完成；sessionC的语句被sessionA的间隙锁锁住。

同时需要注意的是：

for update：系统认为接下来会更新数据，因此会将主键索引满足条件的行加行锁；
in share mode：如果有覆盖索引优化，没有访问到主键索引，那么主键索引不会加锁；

因此，这里也就存在说，如果要使用lock in share mode给行家读锁防止数据行被更新，就必须绕过覆盖索引的优化

3.3 主键索引范围锁

对于表t，如下两条语句的加锁范围完全不同，语句1只会加行锁，那么语句2呢?

mysql> select * from t where id=10 for update;
mysql> select * from t where id>=10 and id<11 for update;

MySQL：逃不掉的锁事，间隙锁

开始执行时，要找到第一个id=10的行，由于是主键，所以是唯一索引，由next-key lock(5,10]退化为行锁id=10；
范围查找继续往后查找，找到id=15停止，因此需要加next-key lock(10,15]，从8.0.18版本，间隙锁退化为(10,15)；

此时sessionA锁的范围为id=10的行锁和(10,15]的间隙锁，因此sessionB和sessionC被阻塞；

可以使用语句“select * from performance_schema.data_locks”表获取加锁的数据。

3.4 非唯一索引范围锁

使用索引c进行范围查询：

MySQL：逃不掉的锁事，间隙锁

由于c不是唯一索引，因此需要加(5,10]和(10,15]两个next-key lock，因此后两个会话的操作全部被阻塞。

3.5 唯一索引范围锁bug

注意，这个bug在8.0.18版本及之后的版本已经优化，不再存在。

MySQL：逃不掉的锁事，间隙锁

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。

但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

3.6 非唯一索引上存在“等值”的问题

执行插入语句：

mysql> insert into t values(30,10,30);

MySQL：逃不掉的锁事，间隙锁

虽然有两个c=10的索引，但是主键不同，因此，c=10记录存在间隙。

MySQL：逃不掉的锁事，间隙锁

sessionA在遍历的时候，先访问到第一个c=10的记录，根据原则1，加锁为：(c=5,id=5)到(c=10,id=10)这个next-key lock，即c的索引为(5,10]。

然后sessionA向右查找，直至(c=15,id=15)，循环结束。根据优化2，等值查询，退化为(c=10,id=10)到(c=15,id=15)的间隙锁，即c的索引为(10,15)；

主键索引上，增加了行锁id=10和id=30；

因此，索引c上的加锁范围为下图蓝色区域：

MySQL：逃不掉的锁事，间隙锁

蓝色两边是虚线，表示开区间，即 (c=5,id=5) 和 (c=15,id=15) 这两行上都没有锁。

这里再次举例：如果session b插入(4,5,50)，不会被锁，如果插入（6,5,50）会被锁住，因为二级索引的叶子节点存储的是主键值，二级索引的叶子节点也是有序的，这样6,5,50根据二级索引来排的话是在5,5,10后面的。

3.7 limit语句加锁

MySQL：逃不掉的锁事，间隙锁

sessionA的delete语句加了limit 2，表内只有两条数据，删除效果一样，但是加锁效果不同。

delete语句加了limit 2的限制，遍历到(c=10,id=30)这一行之后，满足条件的语句已经有两条，循环结束。因此，索引c的加锁范围变成了（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间。

MySQL：逃不掉的锁事，间隙锁

因此说，在执行删除的时候尽量加Limit，但是这里需要注意的是，删除的行数不清楚，可能会带来业务的bug。

3.8 一个死锁的例子

MySQL：逃不掉的锁事，间隙锁

sessionA启动事务后执行查询语句加lock in share mode，在索引c加next-key lock(5,10]和间隙锁(10,15)；
sessionB的update语句也要在索引c上加next-key lock(5,10]，进入锁等待；
然后sessionA要再插入(8,8,8)这一行，被sessionB的间隙锁锁住。由于出现了死锁，InnoDB让sessionB回滚；

session B 的“加 next-key lock(5,10] ”操作，实际上分成了两步，先是加 (5,10) 的间隙锁，加锁成功；然后加 c=10 的行锁，这时候才被锁住的。也就是说，我们在分析加锁规则的时候可以用 next-key lock 来分析。但是要知道，具体执行的时候，是要分成间隙锁和行锁两段来执行的。

就算分成了两步，为什么session B加(5,10)就能成功呢？session A不是加了(5, 10]的锁吗？前面应该也是提到过的，间隙锁和间隙锁之间并不冲突，间隙锁和insert到这个间隙的语句才会冲突，因此session B加间隙锁(5, 10)是可以成功的，但是如果往(5, 10)里面插入的话会被阻塞。但是如果直接加next-key lock(5, 10]，那么肯定是会被阻塞的，因此这个例子确实说明，加锁的步骤是分两步的，先是间隙锁，后是行锁。而且只要理解了间隙锁和行锁之间冲突的原则是不一样的，也就很容易理解这两个锁并不是一起加的了。