在什么情况下MySQL的可重复读隔离级别会导致幻读现象？

本文内容转载于 Mysql-可重复读的隔离级别在什么情况下会出现幻读。不过在文章内容格式上进行了优化。

首先先说一下一些网上经常能查到的说法，有的描述和举例难以说服我，根据本文中的实践发现并不可靠。

1.1 实现可重复读的原理是，给 select 加锁。

其实不是，至少 mysql，pg，oracle 不是，他们用的是 MVCC，多版本并发控制，每一行数据都有两个版本号，对应了插入是的事务版本号和删除时的事务版本号。


select 对应的是选择插入插入版本号 <= 当前事务版本号，删除版本号为空或者 > 当前事务版本号的数据。


insert 保存当前事务版本号到插入事务版本号。


delete 保存当前事务版本号到删除事务版本号。


update 对应的是旧数据行的删除版本记录当前事务的版本号，新数据行的插入版本记录当前事务的版本号。

1.2 可重复度的隔离级别不能隔绝幻读

其实不是，幻读的意思是，同一个事务中，相同条件两次读取到的数据量不一致。


有上面的原理可以发现，select 是选择插入版本小于 <= 自己的版本号，删除版本为空或者大于自己版本号的数据。


所以针对如果 事务1 中只有两次 select，事务2 在 事务1 的两次 select 之间插入了影响 select 结果的数据，事务1 的两次 select 结果是一样的。


所以 可重复读 的隔离级别不是完全不能隔离 幻读。


之所以说不是完全不能，因为发现了下面的现象，会产生 幻读 。


那么 MySQL 默认的隔离级别——可重复读到底能不能防止幻读呢?

先说结论，RR（可重复读） 的事务隔离级别还是会出现幻读的情况的，要分情况讨论。


有两种情况无法防止幻读:

• 一是主动用 for share 或者 for update 这样带 锁 的 select 语句， 对应下文实验中的 现象 1；
• 二是开始较早的事务更新了开始较晚但是结束较早的事务的新数据，那么开始较早的事务中会出现幻读，对应下文实验中的 现象 2。

实验使用 mysql 8.0.19, 事务隔离级别为 RR，employee2 表中只有一条 first_name 为 Yishay 的数据，其中 first_name 字段有索引，事务2 总是在 事务1 sleep 的时候执行。

3.1 (现象0) : 事务1 两次 select 一样且 事务1 两次 select 间没有额外操作，可以防止幻读

解读：事务1 的两次 select 得到的结果是一样的，只有一条数据，说明默认情况下，两个 select 之前没有获取其他的锁或者进行其他操作，是可以防止幻读的，这与网上说的 RR 不能防止幻读不一样。

# 现象0 - 事务1 代码如下
start transaction ;
select *, now(3) from employees2 where first_name = 'Yishay';
select sleep(10), now(3);
select *, now(3) from employees2 where first_name = 'Yishay' FOR SHARE; -- FOR UPDATE 也是一样，其中 SHARE 拿共享锁，UPDATE 拿独占锁
commit ;


# 现象0 - 事务2 代码如下
insert into employees2 values (10199,'1960-09-19','Yishay','Tzvieli','M','1990-10-20');


3.2 (现象1) : 事务1 的第 2 次 select 要求锁，会出现幻读

解读：由于事务 1 的第二次 select 要求了锁（任何锁，共享锁或者独占做），导致select 也能使用当前读，也就读了最新（已 commit）的数据，这里已经不是无法防止幻读了，而是客户端使用的语句主动要求当前的数据，自然也必须承担幻读的风险。

# 现象1 - 事务1 代码如下
start transaction ;
select *, now(3) from employees2 where first_name = 'Yishay';
select sleep(10), now(3);
select *, now(3) from employees2 where first_name = 'Yishay' FOR SHARE; # FOR UPDATE 也是一样，其中 SHARE 拿共享锁，UPDATE 拿独占锁
commit ;


# 现象1 - 事务2 代码如下
insert into employees2 values (10199,'1960-09-19','Yishay','Tzvieli','M','1990-10-20');


3.3 (现象2) : 事务1 sleep 后进行了 update，更新 事务2 插入的数据，出现幻读

解读：事务2 在 事务1 的 update 之前执行。上面的情况中，事务1 的第一次 select 会查出只有一条数据，第二次 select 会查出有两条数据，且这两条数据，last_name 都被更新为 Tzvieli1。


此时会产生幻读的情况，也就是 select 与 insert，update，delete 这三者的区别，select 是快照读，后三者是当前读，也就是在执行后面三种操作的时候，获取的是数据库中最新的数据（最新的意思是说，别的事务已经提交的数据）。


这里为什么 事务1 中的两次 select 没有按照之前的说法，用同一个事务版本号去。其实还是遵循的，假设 事务1 的事务 id 是 1，事务2 的事务 id 是 2，那么 事务2 插入的数据行，会在 事务1 执行 update 语句时被更新为新的数据行，且事务插入版本号为 1，这样在 事务1 进行第 2 次 select 的时候，就能看到之前 事务2 的插入的数据了，其实看到的是 事务1 更新后的数据。

# 现象2 - 事务1 代码如下
start transaction ;
select *, now(3) from employees2 where first_name = 'Yishay';
select sleep(10), now(3);
update employees2 set last_name = 'Tzvieli1' where first_name = 'Yishay'; -- 这里的 update 确实更新了数据，不能执行后不更新数据，影响的 row 大于 1
select *, now(3) from employees2 where first_name = 'Yishay';
commit ;


# 现象2 - 事务2 代码如下
insert into employees2 values (10199,'1960-09-19','Yishay','Tzvieli','M','1990-10-20');


3.4 (现象3) : 事务1 获取了 事务2 需要的锁，让 事务2 等待，自然也能防止幻读

如果把 现象2 中的 update 的顺序调整到 sleep 之前，事务2 将必须等待 事务1 整个事务完成，才能进行 insert 操作。


解读：在这种情况下，由于 事务2 需要等待 事务1 完成，所以能防止幻读的发生，那 事务2 需要等待 事务1 完成，显然在等待锁，它等待的是什么锁呢。


在 MySQL 中，默认情况下修改操作会加 Next-Key Lock 锁。


Next-Key Lock = Record Lock（索引记录的行锁） + Gap Lock（索引记录间隙锁）


间隙锁 是确保索引记录之间的间隙不变，在当前 where 条件命中的索引值的前后索引值之间的间隙不允许变更，也就是不允许导致索引 B+ 树的特定间隙变化的锁。


在这个现象中，由于 事务2 插入一条 first_name = 'Yishay' 的数据，需要变更当前 first_name 索引树种 'Yishay' 这个索引节点前后的间隙，导致它需要获取与 事务1 中 update 语句相同的锁，所以需要等待 事务1 完成。

# 现象3 - 事务1 代码如下
start transaction ;
select *, now(3) from employees2 where first_name = 'Yishay';
update employees2 set last_name = 'Tzvieli1' where first_name = 'Yishay'; # 在事务 2 开始之前，执行 update 语句
select sleep(10), now(3);
select *, now(3) from employees2 where first_name = 'Yishay';
commit ;


# 现象3 - 事务2 代码如下
insert into employees2 values (10199,'1960-09-19','Yishay','Tzvieli','M','1990-10-20');


3.5 (现象4) : Next-Key Lock 能在防止幻读的同时提高写入的效率

Next-Key Lock 在 where 条件中的列完全命中索引的时候，起到了避免锁范围扩大的作用，且能防止幻读。


如果把 现象3 中的语句改成如下情况，表中本来没有 first_name = 'Hidefumi' 的记录，事务2 插入的记录 last_name 为 Caine，则 事务2 不需要等待 事务1 完成才能执行，且 事务1 不会产生幻读，也就是 事务1 中的 第 2 个 select，与第一个 select 出来的结果相同，只要一条数据。


解读：根据 现象3 中的 Next-Key Lock 的解释，只要 事务2 中插入的语句需要的锁，与 事务1 中的 Gap Lock 和 Record Lock 不是同样的范围，则不会受到 事务1 的影响，所以在我的实验环境中，插入 first_name = 'Hidefumi' 不需要等待 事务1。

# 现象4 - 事务1 代码如下
start transaction ;
select *, now(3) from employees2 where first_name = 'Hidefumi' or first_name = 'Yishay';
update employees2 set last_name = 'Tzvieli1' where first_name = 'Yishay'; # 在事务 2 开始之前，执行 update 语句
select sleep(10), now(3);
select *, now(3) from employees2 where first_name = 'Hidefumi' or first_name = 'Yishay';
commit ;


# 现象4 - 事务2 代码如下
insert into employees2 values (10151,'1953-07-28','Hidefumi','Caine','G','1992-10-15');


3.6 (现象5) : where 条件索引字段，Next-Key Lock 退化为表锁，也能防止幻读

如果我们把表的 first_name 的索引去掉，仍使用 现象4 中一样的两个事务语句，则 事务2 需要等待 事务1 完成才能插入，此时也能防止幻读的发生。


说明 Next-Key Lock 在没有索引的情况下，会退化成全表的锁。但是这时候性能较差。所以生产环境尽量给 where 条件中的字段加上索引。