6 MySQL 幻读与间隙锁

宋涛收录于存储

2020-03-06 约 6487 字预计阅读 13 分钟

幻读

1. 幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。对于幻读需要在注意:

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。而当前读的规则，就是要能读到所有已经提交的记录的最新值。因此，幻读只在“当前读”下才会出现。
修改结果，被之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”

1.1 幻读有什么问题？

没有行锁到底会导致什么问题，我们来看下面这个示例:

经过 T1 时刻，id=5 这一行变成 (5,5,100)，当然这个结果最终是在 T6 时刻正式提交的 ;
经过 T2 时刻，id=0 这一行变成 (0,5,5);
经过 T4 时刻，表里面多了一行 (1,5,5);
其他行跟这个执行序列无关，保持不变。

这样看，这些数据也没啥问题，但是我们再来看看这时候 binlog 里面的内容。

T2 时刻，session B 事务提交，写入了两条语句；
T4 时刻，session C 事务提交，写入了两条语句；
T6 时刻，session A 事务提交，写入了 update t set d=100 where d=5 这条语句。

用 binlog 来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。也就是数据库发生了数据不一致。

1.2 间隙锁的作用

锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。基于行锁的并发控制，只能保护已经存在的行，但是对于新插入的行就会出现未保护的临界状态。

行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。锁是加在索引上的，这是 InnoDB 的一个基础设定，在分析问题的时候一定要谨记

2. 间隙锁

间隙锁，锁的就是两个值之间的空隙。数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。

比如行锁，分成读锁和写锁。写锁跟任何其他读锁和写锁都是冲突的，也就是说，跟行锁有冲突关系的是“另外一个行锁”。但是间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。即间隙锁保护的是这个间隙，不允许插入值。但，它们之间是不冲突的。

最后锁就是加在索引上的，这是 InnoDB 的一个基础设定，在分析问题的时候一定要谨记。

2.1 加锁范围

我们创建下表，这个表除了主键 id 外，还有一个索引 c，初始化语句在表中插入了 6 行数据。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10


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);

间隙锁，锁的就是两个值之间的空隙。表 t，初始化插入了 6 个记录，这就产生了 7 个间隙。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。+∞是开区间，实现上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样就符合前面说的“都是前开后闭区间”。

2.2 间隙锁的问题

间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”。我们来看下面这个操作序列

1
2
3
4
5
6
7
8
9


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;

这里，我用两个 session 来模拟并发，并假设 N=9。

你看到了，其实都不需要用到后面的 update 语句，就已经形成死锁了。我们按语句执行顺序来分析一下：

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

至此，两个 session 进入互相等待状态，形成死锁。

2.3 间隙锁的理解

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把 binlog 格式设置为 row。这，也是现在不少公司使用的配置组合。

读提交隔离级别加 binlog_format=row 的组合和可重复度隔离级别应该如何选择，跟业务场景有关。如果读提交隔离级别够用，也就是说，业务不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。

3. 加锁规则

因为间隙锁在可重复读隔离级别下才有效，下面的规则，若没有特殊说明，默认是可重复读隔离级别。丁老师总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”:

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

理解这个规则需要注意两点:

无论是等值查询，还是范围查询，在执行过程中，首先都要通过树搜索的方式定位记录，定位记录用的就是“等值查询”的方法。没有 desc 以下限值进行定位，有 desc 时以上线值进行定位。
锁是“在执行过程中一个一个加的”，而不是一次性加上去的
间隙锁本身是不互斥的
所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。

使用 show engine innodb status 可以查看锁等待，死锁等信息。

下面的表 t 是上面 2.1节创建并初始化的表。

3.1 等值查询间隙锁

第一个例子是关于等值条件操作间隙：

由于表 t 中没有 id=7 的记录，因此加锁为范围:

根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]
同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)

3.2 非唯一索引等值锁

第二个例子是关于覆盖索引上的锁：

这里 session A 要给索引 c 上 c=5 的这一行加上读锁:

根据原则 1，加锁单位是 next-key lock，因此会给 (0,5]加上 next-key lock。
要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。根据原则 2，访问到的都要加锁，因此要给 (5,10]加 next-key lock
但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。满足条件的行指的是索引 c 上所有被加锁的节点对应的主键 id。

锁是加在索引上的；同时，它给我们的指导是，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将 session A 的查询语句改成 select d from t where c=5 lock in share mode。

3.3 主键索引范围锁

第三个例子是关于范围查询的。

session A 的加锁范围:

开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。根据优化 1，主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁
范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

需要注意一点，首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。这就是上面所说的 bug，唯一索引上的范围查询会访问到不满足条件的第一个值为止。并且不是等值查询，也不会优化。

3.4 非唯一索引范围锁

这次 session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：

在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10]这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，
向右遍历的过程就不是等值查询了，最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

3.5 唯一索引范围锁 bug

ession A 是一个范围查询:

按照原则 1 的话，应该是索引 id 上只加 (10,15]这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。
但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20]这个 next-key lock 也会被锁上。

从这个例子也可以看出，在加锁时等值查询加锁，范围查询加锁时独立，并且取的是最后的交集。

3.6 非唯一索引上存在"等值"的例子

接下来的例子，是为了更好地说明“间隙”这个概念。这里，我给表 t 插入一条新记录。

1

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

新插入的这一行 c=10，也就是说现在表里有两个 c=10 的行。你要知道，由于非唯一索引上包含主键的值，所以是不可能存在“相同”的两行的。

虽然有两个 c=10，但是它们的主键值 id 是不同的（分别是 10 和 30），因此这两个 c=10 的记录之间，也是有间隙的。

现在，我们来看一下案例六。这次我们用 delete 语句来验证。注意，delete 语句加锁的逻辑，其实跟 select … for update 是类似的

此时的加锁范围:

session A 在遍历的时候，先访问第一个 c=10 的记录。同样地，根据原则 1，这里加的是 (c=5,id=5) 到 (c=10,id=10) 这个 next-key lock。
然后，session A 向右查找，直到碰到 (c=15,id=15) 这一行，循环才结束。根据优化 2，这是一个等值查询，向右查找到了不满足条件的行，所以会退化成 (c=10,id=10) 到 (c=15,id=15) 的间隙锁。

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

3.7 limit 语句加锁

例子 6 也有一个对照案例，场景如下所示：

案例七里的 delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。

索引 c 上的加锁范围就变成了从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间，如下图所示：

这个例子对我们实践的指导意义就是，在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

3.8 一个死锁的例子

前面的例子中，我们在分析的时候，是按照 next-key lock 的逻辑来分析的，因为这样分析比较方便。最后我们再看一个案例，目的是说明：next-key lock 实际上是间隙锁和行锁加起来的结果。

我们按时间顺序来分析一下为什么是这样的结果。

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

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

3.9 读提交的加锁规则

我们上面的所有案例都是在可重复读隔离级别 (repeatable-read) 下验证的。同时，可重复读隔离级别遵守两阶段锁协议，所有加锁的资源，都是在事务提交或者回滚的时候才释放的。

在最后的案例中，你可以清楚地知道 next-key lock 实际上是由间隙锁加行锁实现的。如果切换到读提交隔离级别 (read-committed) 的话，就好理解了，过程中去掉间隙锁的部分，也就是只剩下行锁的部分。

其实读提交隔离级别在外键场景下还是有间隙锁，相对比较复杂。

另外，在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。

也就是说，读提交隔离级别下，锁的范围更小，锁的时间更短，这也是不少业务都默认使用读提交隔离级别的原因。

3.10 非索引列加锁

对于非索引列而言，因为无法直接精确定位值的位置，因此只能进行全表扫描:

在可重复读隔离级别下，会对所有行和间隙加锁
在读提交隔离级别下，语句执行过程中会对所有行加上行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交。

1
2
3


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

上面这个例子，在读提交隔离级别下，在 select * from t where d=5 for update; 的执行过程中，所有行都会被加锁；语句执行完成之后，只会对 d=5 这一行加锁直至事务提交释放。

3.11 desc 语句加锁

session A 的 select 语句加锁过程是这样的：

由于是 order by c desc，要拿到满足条件的所有行，优化器必须第一个要定位的是索引 c 上“最右边的”c=20 的行，所以会加上间隙锁 (20,25) 和 next-key lock (15,20]。为什么不对 c=25 加锁，是因为查找 20 的过程是一个非唯一索引的等值查询，25 不满足查询条件无须加锁。
在索引 c 上向左遍历，要扫描到 c=10 才停下来，所以 next-key lock 会加到 (5,10]，这正是阻塞 session B 的 insert 语句的原因。注意向左遍历，在遍历过程中，就不是等值查询了。
在扫描过程中，c=20、c=15、c=10 这三行都存在值，由于是 select *，所以会在主键 id=10、15、20 上加三个行锁。

因此，session A 的 select 语句锁的范围就是：索引 c 上 (5, 25)；主键索引上 id=10、15、20 三个行锁。

每次加锁都会说明是加在“哪个索引上”的。因为，锁是加在索引上的，这是 InnoDB 的一个基础设定，在分析问题的时候一定要谨记。

3.12 in 语句加锁

1
2


begin;
select id from t where c in(5,20,10) lock in share mode;

这条查询语句里用的是 in，我们先来看这条语句的 explain 结果。

in 语句使用了索引 c 并且 rows=3，说明这三个值都是通过 B+ 树搜索定位的。

在查找 c=5 的时候，先锁住了 (0,5]。但是因为 c 不是唯一索引，为了确认还有没有别的记录 c=5，就要向右遍历，找到 c=10 才确认没有了，这个过程满足优化 2，所以加了间隙锁 (5,10)。

同样的，执行 c=10 这个逻辑的时候，加锁的范围是 (5,10] 和 (10,15)；执行 c=20 这个逻辑的时候，加锁的范围是 (15,20] 和 (20,25)。(因为 in 语句会事先对其中的值排序在查找，所以才会先对 c=10 执行加锁么？)

这些锁是“在执行过程中一个一个加的”，而不是一次性加上去的。

如果同时有另外一个语句，是这么写的：

1

select id from t where c in(5,20,10) order by c desc for update;

我们现在都知道间隙锁是不互锁的，但是这两条语句都会在索引 c 上的 c=5、10、20 这三行记录上加记录锁。语句里面是 order by c desc，这三个记录锁的加锁顺序，是先锁 c=20，然后 c=10，最后是 c=5。也就是说，这两条语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁。

4. 如何看待锁等待

由于 session A 并没有锁住 c=10 这个记录，所以 session B 删除 id=10 这一行是可以的。但是之后，session B 再想 insert id=10 这一行回去就不行了。

现在我们一起看一下此时 show engine innodb status 的结果，看看能不能给我们一些提示。锁信息是在这个命令输出结果的 TRANSACTIONS 这一节。你可以在文稿中看到这张图片

我们来看几个关键信息。

index PRIMARY of table test.t ，表示这个语句被锁住是因为表 t 主键上的某个锁。
lock_mode X locks gap before rec insert intention waiting 这里有几个信息：

insert intention 表示当前线程准备插入一个记录，这是一个插入意向锁。为了便于理解，你可以认为它就是这个插入动作本身。
gap before rec 表示这是一个间隙锁，而不是记录锁。

那么这个 gap 是在哪个记录之前的呢？接下来的 0~4 这 5 行的内容就是这个记录的信息。
n_fields 5 也表示了，这一个记录有 5 列：

0: len 4; hex 0000000f; asc ;; 第一列是主键 id 字段，十六进制 f 就是 id=15。所以，这时我们就知道了，这个间隙就是 id=15 之前的，因为 id=10 已经不存在了，它表示的就是 (5,15)。
1: len 6; hex 000000000513; asc ;; 第二列是长度为 6 字节的事务 id，表示最后修改这一行的是 trx id 为 1299 的事务。
2: len 7; hex b0000001250134; asc % 4;; 第三列长度为 7 字节的回滚段信息。可以看到，这里的 acs 后面有显示内容 (% 和 4)，这是因为刚好这个字节是可打印字符。
后面两列是 c 和 d 的值，都是 15。

因此，我们就知道了，由于 delete 操作把 id=10 这一行删掉了，原来的两个间隙 (5,10)、(10,15）变成了一个 (5,15)。也就是说，所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。

目录