幻读是什么

假设有如下建表和初始化语句：

  1. CREATE TABLE `t` (
  2.   `id` int(11) NOT NULL,
  3.   `c` int(11) DEFAULT NULL,
  4.   `d` int(11) DEFAULT NULL,
  5.   PRIMARY KEY (`id`),
  6.   KEY `c` (`c`)
  7. ) ENGINE=InnoDB;
  9. INSERT INTO t VALUES(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

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

  1. BEGIN;
  2. SELECT * FROM t WHERE d=5 FOR UPDATE;
  3. COMMIT;

比较好理解的是，这个语句会命中 d=5 的这一行，对应的主键 id=5，因此在 SELECT 语句执行完成后，id=5 这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行 COMMIT 语句的时候释放。由于字段 d 上没有索引，因此这条查询语句会做全表扫描。那其他被扫到的，但不满足条件的 5 行记录上，会不会加锁呢？

我们知道，InnoDB 的默认事务隔离级别是可重复读，如果只在 id=5 这一行加锁，而其他的行不加锁的话会怎么样呢？下面先来看一下这个场景（假设的一个场景）：
image.png
可以看到，session A 里执行了三次查询，分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同，都是查所有 d=5 的行，而且使用的是当前读，并且加上写锁。现在，我们来看一下这三条 SQL 语句，分别会返回什么结果。

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

其中，Q3 读到 id=1 这一行的现象，被称为幻读。也就是说，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。而 session B 的修改结果被 session A 之后的 select 语句用当前读看到，不能称为幻读。幻读仅指新插入的行。

因为这三个查询都是加了 for update，都是当前读。而当前读的规则，就是要能读到所有已经提交的记录的最新值。并且 session B 和 sessionC 的两条语句，执行完成后就会提交，所以 Q2 和 Q3 应该能看到这两个事务的操作效果，而且也看到了，这跟事务的可见性规则并不矛盾。但是，这是不是真的没问题呢？

幻读有什么问题

首先是语义上的。session A 在 T1 时刻就声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。如果现在这样看感觉还不明显的话，我再往 session B 和 session C 里面分别加条 SQL 语句，你再看看会出现什么现象。
image.png
session B 的第二条语句 update t set c=5 where id=0 的语义是：把 id=0、d=5 这一行的 c 值改成了 5。由于在 T1 时刻，session A 还只是给 id=5 这一行加了行锁， 并没有给 id=0 这行加上锁。因此，session B 在 T2 时刻是可以执行这两条 update 语句的。这就破坏了 session A 里 Q1 语句要锁住所有 d=5 的行的加锁声明。同理，session C 对 id=1 这一行的修改，也是破坏了 Q1 的加锁声明。

其次，是数据一致性的问题。

我们知道，锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。为了说明这个问题，我给 session A 在 T1 时刻再加一个更新语句，即：update t set d=100 where d=5。
image.png
update 的加锁语义和 select …for update 是一致的，所以这时候加上这条 update 语句也很合理。session A 声明说“要给 d=5 的语句加上锁”，就是为了要更新数据，新加的这条 update 语句就是把它认为加上了锁的这一行的 d 值修改成了 100。现在，我们来分析一下上图执行完成后，数据库里会是什么结果。

  • 经过 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 这条语句。

我统一放到一起的话，就是这样的：

  1. update t set d=5 where id=0; /*(0,0,5)*/
  2. update t set c=5 where id=0; /*(0,5,5)*/
  4. insert into t values(1,1,5); /*(1,1,5)*/
  5. update t set c=5 where id=1; /*(1,5,5)*/
  7. update t set d=100 where d=5;/*所有d=5的行，d改成100*/

可以看到。这个语句序列，不论是拿到备库去执行，还是以后用 binlog 来克隆一个库，这三行的结果，都变成了 (0,5,100)、(1,5,100) 和 (5,5,100)。即 id=0 和 id=1 这两行发生了数据不一致。这个问题是很严重的。

那这个数据不一致到底是怎么引入的呢？我们分析一下可以知道，这是我们假设“select * from t where d=5 for update 这条语句只给 d=5 这一行，也就是 id=5 的这一行加锁”导致的。所以，我们对于上面的假设不合理，要改。那怎么改呢？我们把扫描过程中碰到的行，也都加上写锁，再来看看执行效果。
image.png
由于 session A 把所有的行都加了写锁，所以 session B 在执行第一个 update 语句的时候就被锁住了。需要等到 T6 时刻 session A 提交以后，session B 才能继续执行。这样对于 id=0 这一行，在数据库里的最终结果还是 (0,5,5)。在 binlog 里面，执行序列是这样的：

  1. insert into t values(1,1,5); /*(1,1,5)*/
  2. update t set c=5 where id=1; /*(1,5,5)*/
  4. update t set d=100 where d=5;/*所有d=5的行，d改成100*/
  6. update t set d=5 where id=0; /*(0,0,5)*/
  7. update t set c=5 where id=0; /*(0,5,5)*/

可以看到，按照日志顺序执行，id=0 这一行的最终结果也是 (0,5,5)。所以，id=0 这一行的问题解决了。但同时你也可以看到，id=1 这一行，在数据库里面的结果是 (1,5,5)，而根据 binlog 的执行结果是 (1,5,100)，也就是说幻读的问题还是没有解决。为什么我们已经把所有记录都上了锁，还阻止不了 id=1 这一行的插入和更新呢？

原因很简单。在 T3 时刻，我们给所有行加锁的时候，id=1 这一行还不存在，不存在也就加不上锁。也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录，这也是为什么幻读会被单独拿来解决的原因。

InnoDB 如何解决幻读

1. 间隙锁（Gap Lock）

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 引入了间隙锁 (Gap Lock)。间隙锁，顾名思义锁的就是两个值之间的空隙。比如表 t 初始化插入了 6 个记录，那这就产生了 7 个间隙。
image.png
这样，当你执行 select * from t where d=5 for update 语句时，就不止是给数据库中已有的 6 个记录加上了行锁，还同时加了 7 个间隙锁（因为会走全表扫描），这样就确保了无法再插入新的记录。也就是说，在一行行扫描的过程中，不仅会给行加上了行锁，还会给行两边的空隙加上了间隙锁。

2. Next-Key Lock

Next-Key Lock 是结合了 Gap Lock 和 Record Lock 的一种锁定算法，锁定一个范围且锁定记录本身，每个 Next-Key Lock 都是前开后闭区间。采用 Next-Key Lock 的锁定技术称为 Next-Key Locking。其设计目的是为了解决幻读问题。
image.png
还是以上图为例，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 Next-Key Lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。因为 +∞是开区间。所以在实现上，InnoDB 存储引擎给每个索引加了一个不存在的最大值 supremum，符合前开后闭。

Next-Key Lock 虽然帮我们解决了幻读的问题，但同时也带来了一些困扰。假设执行以下逻辑，插入数据前先判断是否是否存在，不存在则插入：
image.png
这个逻辑每次操作前用 for 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 的间隙锁挡住了。至此，形成死锁。

你现在知道了，间隙锁的引入可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。间隙锁是在可重复读隔离级别下才会生效的。所以，你如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时也破坏了事务的隔离性，并且可能会导致主从数据的不一致。

3. 为什么 MySQL 默认隔离级别是可重复读

上面分析了在主从复制场景下，幻读带来的数据不一致的问题。实际上，MySQL 的 binlog 有三种格式：Row、Statement 和 Mixed。参考：MySQL binlog 深度解析

当 binlog 设置为 Statement 格式时，binlog 里记录的是原始的 SQL 语句，又因为 MySQL 在主从复制的过程是通过 binlog 进行数据同步的，如果设置为读已提交（RC）隔离级别，当出现事务乱序的时候，就会导致备库在执行 SQL 回放后，结果和主库内容不一致。

因此 MySQL 的默认隔离离别选择了可重复读（RR）而不是读已提交（RC），在可重复读隔离级别下，更新数据的时候不仅对更新的行加行级锁，还会加间隙锁（Gap Lock）。当其他事务要更新或插入数据到被锁住的区间范围内时，因为有间隙锁的存在，会导致其他事务执行被卡住，只有等加锁的事务提交或者回滚后才能继续执行其他事务。并且为了严格保证数据一致性，MySQL 还禁止在使用 Statement 格式的 binlog 的情况下，使用 READ COMMITTED 作为事务隔离级别。

Next-Key Lock 加锁规则

因为间隙锁（Gap Lock）在可重复读隔离级别下才有效，所以下面的内容默认是在可重复读隔离级别。总结的加锁规则里面，包含了两个原则、两个优化和一个 bug。

  • 原则一：加锁的基本单位是 Next-Key Lock，Next-Key Lock 是前开后闭区间
  • 原则二：查找过程中访问到的对象才会加锁
  • 优化一：索引上的等值查询，给唯一索引加锁的时候，Next-Key Lock 降级为行锁
  • 优化二：索引上的等值查询，向右遍历且最后一个值不满足等值条件时，Next-Key Lock 退化为间隙锁
  • BUG：唯一索引上的范围查询会访问到不满足条件的第一个值为止

下面以表 t 为例，和你解释一下这些规则。表 t 的建表语句和初始化语句如下：

  1. CREATE TABLE `t` (
  2.   `id` int(11) NOT NULL,
  3.   `c` int(11) DEFAULT NULL,
  4.   `d` int(11) DEFAULT NULL,
  5.   PRIMARY KEY (`id`),
  6.   KEY `c` (`c`)
  7. ) ENGINE=InnoDB;
  9. insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

1. 等值查询间隙锁

image.png
由于表 t 中没有 id=7 的记录，所以用我们上面提到的加锁规则判断一下的话：

  • 根据原则 1，加锁单位是 next-key lock，又因为查找过程中访问到的对象才会加锁，所以 session A 的加锁范围就是 (5,10]。
  • 同时根据优化 2，这是一个索引上的等值查询 (id=7)，向右遍历到 id=10 时不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

2. 非唯一索引等值锁

image.png
这里 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 语句可以执行完成。但 session C 要插入一个 (7,7,7) 的记录就会被 session A 的间隙锁 (5,10) 锁住。注意，在这个例子中，lock in share mode 只锁覆盖索引，但如果是 for update 就不一样了。 执行 for update 时，系统会认为你要更新数据，因此会顺便给主键索引上满足条件的行加行锁，注意是只对指定行加行锁，不会管其他行，对应就是给 id=5 这一行加行锁。

这个例子说明，锁是加在索引上的。如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。

3. 主键索引范围锁

image.png

  • 开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock (5,10]。 但是根据优化 1， 主键 id 是唯一索引，等值查询给唯一索引加锁会退化成行锁，因此只加了 id=10 这一行的行锁。
  • 之后，向后进行范围查找，找到 id=15 这一行停下来，因此需要加 next-key lock (10,15]。所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock (10,15]。

这里你需要注意一点，首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而之后向右扫描到 id=15 的时候，用的是范围查询判断。

4. 非唯一索引范围锁

image.png
这次 session A 用字段 c 来判断，加锁规则跟案例三唯一的不同是：在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会退化为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

所以从结果上来看，sesson B 要插入（8,8,8) 的这个 insert 语句时就被堵住了。这里需要扫描到 c=15 才停止扫描是合理的，因为 InnoDB 要扫到 c=15，才知道不需要继续往后找了。

5. 唯一索引范围锁 bug

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

所以你看到了，session B 要更新 id=20 这一行，是会被锁住的。同样地，session C 要插入 id=16 的一行，也会被锁住。照理说，这里锁住 id=20 这一行的行为，其实是没有必要的。因为扫描到 id=15，就可以确定不用往后再找了。但实现上还是这么做了。

6. 死锁案例

这个案例的目的是说明：next-key lock 实际上是间隙锁和行锁加起来的结果。
image.png

  • 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 的间隙锁锁住。此时发生了死锁

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

7. 不等号条件里的等值查询

  1. select * from t where id>9 and id<12 order by id desc for update;
  • 这个查询语句的语义是 order by id desc，要拿到满足条件的所有行，优化器必须先找到第一个 id<12 的值。
  • 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到 id=12 的这个值，只是最终没找到，但找到了 (10,15) 这个间隙，由于是等值查询，所以只加了（10,15）上的间隙锁。
  • 然后向左遍历，遍历就不是等值查询了，会扫描到 id=5 这一行，所以会加一个 next-key lock (0,5]。

也就是说，在执行过程中，通过树搜索的方式定位记录的时候，用的是等值查询的方法。通过上面的分析，我们知道这个语句的加锁范围就是主键索引上的 (0,5]、(5,10]和 (10, 15)。

8. 等值查询的过程

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

这条查询语句里用的是 in，我们先来看这条语句的 explain 结果。
image.png
可以看到这条 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)。

通过这个分析，我们可以知道，这条语句在索引 c 上加的三个记录锁的顺序是：先加 c=5 的记录锁，再加 c=10 的记录锁，最后加 c=20 的记录锁。为什么要分阶段说呢？因为这些锁是在执行过程中一个一个加的，而不是一次性加上去的。