幻读与间隙锁

一、前言

在上一篇《事务隔离与可重复读的实现原理》中说过，MySQL是用MVCC技术实现了读提交和可重复读隔离级别。
​

我们都知道，在标准的隔离级别理论中，读提交（RC）能解决脏读问题，可重复读（RR）能解决脏读、不可重复读问题。而在MySQL（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),(25,25,25);

查看和修改隔离级别的sql：

-- 查询全局事务隔离级别
SELECT @@global.tx_isolation;
-- 查询当前会话隔离级别
SELECT @@session.tx_isolation;

-- 修改会话/全局事务隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL 
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

二、“幻读”是什么？

看下面这个例子（隔离级别为读提交RC）：

图1：执行流程（一）

上图Q3读到id=1这一行的现象，被称为“幻读”。幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
​

这里，需要对“幻读”做一个说明：

1. 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
2. 上面session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

如果只从事务可见性规则来分析的话，上面这三条SQL语句的返回结果都没有问题。
但实际上是有问题的。

三、“幻读”有什么问题？

3.1 语义问题

session A在 T1 时刻就声明了，“我要把所有 d=5 的行锁住，不准别的事务进行读写操作”（for update 排它锁）。而实际上，这个语义被破坏了。

3.2 数据一致性问题

锁的设计是为了保证数据的一致性。而这个一致性，不止是数据库内部数据状态在此刻的一致性，还包含了数据和日志在逻辑上的一致性。

为了说明这个问题，假定如下流程：

图2：执行流程（二）

分析一下上图执行完成后，数据库里会是什么结果。

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

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

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

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

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

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*/

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

那么把扫描过程中碰到的行都加上写锁会怎样？
这样还是存在问题的，因为即使把所有的记录都加上锁，还是阻止不了新插入的记录，这也是为什么“幻读”会被单独拿出来解决的原因。

说明：
以上是逻辑分析过程，实际mysql中RR隔离级别下已经解决了幻读问题。
​

若把隔离级别改为 RC 进行上述实验，binlog_format 要改为 statement，但是 mysql5.7.36 中 RC 隔离级别无法将 binlog_format 设置为 statement（mysql5.7.36 默认binlog_format 为 row 格式）。设置为 statement 之后，执行操作会报错：

select * from t where d=5 for update
> 1665 - Cannot execute statement: impossible to write to binary log since 
BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited 
to row-based logging. InnoDB is limited to row-logging when transaction isolation 
level is READ COMMITTED or READ UNCOMMITTED.

四、MySQL是如何解决“幻读”问题的？

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是 间隙锁（Gap Lock）。注：在RR隔离级别才会有间隙锁。

顾名思义，间隙锁，锁的就是两个值之间的空隙。比如开头的表t，初始化插入了6个记录，这就产生了7个间隙。

图3：数据间隙示意图
​

这样，当执行 select * from t where d=5 for update 的时候，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新的记录。
也就是说这时候，在一行行扫描的过程中，不仅将给行加上了行锁，还给行两边的空隙，也加上了间隙锁。
​

现在知道，数据行是可以加锁的实体，数据行之间的间隙，也是可以加锁的实体。但是间隙锁跟之前碰到过的锁都不太一样。
比如行锁，分成读锁和写锁。下图就是这两种类型行锁的冲突关系。

图4：行锁冲突关系

4.1 “间隙锁”之间不互锁

间隙锁跟行锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一条记录”这个操作。间隙锁之间都不存在冲突关系。
​

举个例子：

图5：示例1

这里session B并不会被堵住。因为表t里并没有c=7这个记录，因此session A加的是间隙锁(5,10)。而session B也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。
​

间隙锁和行锁合称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]。
​

备注：文章中，如果没有特别说明，把间隙锁记为开区间，把next-key lock记为前开后闭区间。
（supremum哪来的? 这是因为+∞是开区间。实现上，InnoDB给每个索引加了一个不存在的最大值supremum，这样才符合前面说的“都是前开后闭区间”。 ）
​

next-key lock(间隙锁和行锁)的引入，解决了幻读的问题，但同时也带来了一些新问题。

4.2 “间隙锁”导致的死锁

这里有并发状态下的两个session：

图5：示例2

这种情况下会导致死锁，下面按语句执行顺序来分析一下：

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

至此，两个session进入互相等待状态，形成死锁。当然，InnoDB的死锁检测马上就发现了这对死锁关系，让session A 的 insert 语句报错返回了。
​

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

五、为什么MySQL默认隔离级别为可重复读？

Oracle，SqlServer 的默认隔离级别是读已提交(Read Commited)，但Mysql却是可重复读(Repeatable Read)。
​

这是有历史原因的，Mysql在5.0版本以前，binlog只支持STATEMENT这种格式。而这种格式在 读提交（RC） 这个隔离级别下主从复制是有bug的，因此Mysql将 可重复读（RR）作为默认的隔离级别。
​

当 binlog 为 STATEMENT 格式，且隔离级别为读已提交（RC）时，有什么bug呢？

回看3.2节的过程，binlog_format = STATEMENT 时，binlog 保存的是做了修改动作的sql语句，就是相当于如下sql。

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

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/

这样的话，使用这个binlog进行主从复制时就会与主库的数据不一致。

解决方法有两种：

1. 隔离级别设置为 可重复读（RR）：前文说过了，RR状态下会引入间隙锁，这样本文3.2节中

sessionA T1执行时加了 next-key lock，sessionB T2、sessionC T4都会被阻塞住，只到 sessionA中的事务提交，这样就保证了实际sql的执行顺序与 binlog中sql的执行顺序一致，也就保证了数据的一致性。
​

2. binlog_format 使用 ROW：ROW格式的日志是基于数据行的，记录的是数据做了什么修改，这样就

没有sql执行顺序不一致的问题了。比如本文3.2节，sessionA中只实际只修改了d=5这一行的数据，那binlog中也只会记录这一行的数据变化，而不是原sql。
原sql：

update t set d=100 where d=5;

binlog：

### UPDATE `demo`.`t`
### WHERE
###   @1=5
###   @2=5
###   @3=5
### SET
###   @1=5
###   @2=5
###   @3=100

六、为什么互联网项目中常用“读提交”？

1. RR隔离级别下引入了间隙锁，相对于RC，增加了死锁的概率，而互联网项目并发量相对也大，就更增加了出现死锁的频率。（死锁示例参考本文4.2节 ）

2. RC隔离级别下只锁行。在RR隔离级别下，条件列未命中索引会锁表。

如本文4.1节图5所示，加的是间隙锁，因为字段c有索引，查询的时候不会全表扫描，所以只会对（5，10）这个间隙加上间隙锁。如果sql改为 select * from t where d=7 lock in share mode; d字段建索引，会全表扫描，导致锁表。

3. RC隔离级别下，半一致性读(semi-consistent)特性增加了update操作的并发性。

这是一种夹在普通读和锁定读之间的一种读取方式。它只在READ COMMITTED隔离级别下（或者在开启了innodb_locks_unsafe_for_binlog系统变量的情况下）使用UPDATE语句时才会使用。具体的含义就是当UPDATE语句读取已经被其他事务加了锁的记录时，InnoDB会将该记录的最新提交的版本读出来，然后判断该版本是否与UPDATE语句中的WHERE条件相匹配，如果不匹配则不对该记录加锁，从而跳到下一条记录；如果匹配则再次读取该记录并对其进行加锁。这样子处理只是为了让UPDATE语句尽量少被别的语句阻塞。
​

半一致性读只适用于对聚簇索引记录加锁的情况，并不适用于对二级索引记录加锁的情况。
（详细分析见参考3文章）
​

实验一：sessionA会对二级索引和聚簇索引上锁，sessionB会对聚簇索引上锁。

sessionA	sessionB
begin;
select * from t where c <= 5 for update;
	update t set d=11 where d=10;
commit;

这时，sessionB不会被阻塞，说明对聚簇索引加锁时，半一致性读是可以的。
​

实验2：sessionA会对二级索引和聚簇索引上锁，sessionB会对二级索引和聚簇索引上锁。

sessionA	sessionB
begin;
select * from t where c <= 5 for update;
	update t set c=11 where c=10;
commit;

这时，sessionB会被阻塞，说明对二级索引加锁时，不可半一致性读。
​

在RC级别下，不可重复读问题需要解决么？
不用解决，这个问题可以接受。数据都已经提交了，读出来本身就没有太大问题。
​

在RC级别下，主从复制用什么binlog格式？
在该隔离级别下，row格式，是基于行的复制。Innodb的创始人也是建议binlog使用该格式。

参考

1. 极客时间《MySQL实战45讲》
2. https://www.cnblogs.com/rjzheng/p/10510174.html
3. https://juejin.cn/post/6844904022499917838
4. https://blog.csdn.net/yue_hu/article/details/121874840