目录

一、常见说法的不准确

二、结论

三、实验验证

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

现象 1：事务 1 的第 2 次select 要求锁，会出现幻读

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

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

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

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

---

 

一、常见说法的不准确

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

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

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

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

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

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

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

    

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 的时候执行。

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

事务 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';
commit ;

事务 2 代码 如下

insert into employees2 values (10199,'1960-09-19','Yishay','Tzvieli','M','1990-10-20');

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

 

现象 1：事务 1 的第 2 次select 要求锁，会出现幻读

 事务 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 ;

事务 2 代码 如下

insert into employees2 values (10199,'1960-09-19','Yishay','Tzvieli','M','1990-10-20');

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

 

现象 2：事务 1 sleep 后进行了 update，更新事务 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 代码 如下

insert into employees2 values (10199,'1960-09-19','Yishay','Tzvieli','M','1990-10-20');

解读：事务 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 更新后的数据。

 

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

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

 事务 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 ;

事务 2 代码 如下

insert into employees2 values (10199,'1960-09-19','Yishay','Tzvieli','M','1990-10-20');

解读：

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

在 MySql 中，默认情况下修改操作会加 Next-Key Lock，这个锁=Record Lock（索引记录的行锁） + Gap Lock（索引记录间隙锁）。间隙锁是确保索引记录之间的间隙不变，在当前 where 条件命中的索引值的前后索引值之间的间隙不允许变更，也就是不允许导致索引 B+ 树的特定间隙变化的锁。在这个现象中，由于事务 2 插入一条 first_name = 'Yishay' 的数据，需要变更当前 first_name 索引树种 'Yishay' 这个索引节点前后的间隙，导致它需要获取与事务 1 中update 语句相同的锁，所以需要等待事务 1 完成。

 

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

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

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

 事务 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 ;

事务 2 代码 如下

insert into employees2 values (10151,'1953-07-28','Hidefumi','Caine','G','1992-10-15');

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

 

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

如果我们把表的first_name 的索引去掉，仍使用现象 4 中一样的两个事务语句，则事务 2 需要等待事务 1 完成才能插入，此时也能防止幻读的发生。说明 Next-Key Lock 在没有索引的情况下，会退化成全表的锁。但是这时候性能较差。所以生产环境尽量给 where 条件中的字段加上索引。