MySQL 可重复读如何“避免”幻读？

摘要

大多数数据库教材中明确可重复读（RR）不能防止幻读。在本文中，我们展示了 RR 在幻读下的一些表现，分析了 RR 的实现原理，并论证了为何 MySQL 的 RR 隔离级别下几乎不会出现幻读。本文内容来自南京大学软件学院课程《数据库开发》。

可重复读的实验

上图是可重复读隔离级别下的一个实验，事务 1 的两次查询得到的结果完全相同。看起来在这里可重复读防止了幻读，但我们其实希望读到 Dong 老师的数据。

RR 的实现原理

在 InnoDB 中，会在每行数据后添加两个额外的隐藏的值来实现 MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。

在 RR 事务隔离级别下：

SELECT 时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
INSERT 时，保存当前事务版本号为行的创建版本号
DELETE 时，保存当前事务版本号为行的删除版本号
UPDATE 时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行（即 UPDATE 相当于删除旧的，插入新的）

Read 的差异

我们平时只用使用 select 就是快照读，这样可以减少加锁所带来的开销。

对于会对数据修改的操作（update、insert、delete）都是采用当前读的模式。在执行这几个操作时会读取最新的记录，即使是别的事务提交的数据也可以查询到。假设要 update 一条记录，但是在另一个事务中已经 delete 掉这条数据并且 commit 了，如果 update 就会产生冲突，所以在 update 的时候需要知道最新的数据。读取的是最新的数据，需要加锁。

select * from table where ?; # 加共享锁
select * from table where ? for update; # 加排他锁
insert; # 加排他锁
update; # 加排他锁
delete; # 加排他锁

SELECT FOR UPDATE 语句的意思是当前读到的语句接下来会被修改，相当于提前声明“我要写这些数据”，因此要加排他锁。程序员应当谨慎使用 SELECT FOR UPDATE。

另外，因为数据库事务几乎没有“盲写”（即 write(x) 之前没有 read(x)），所以笔者认为对于准备修改的事务使用 FOR UPDATE 子句是良好的。

RC 和 RR 的对比

RC 的隔离级别

RR 的隔离级别

RR 如何“避免”幻读？

在 RR 隔离级别下：

事务 1 在 update 后，对该数据加锁，事务 2 在事务 1 结束前无法插入新的数据；
这样事务 1 在 update 前后数据保持一致，避免了幻读。

怎么做到的呢？

可以明确的是，update 锁住的肯定不只是已查询到的几条数据，因为这样无法阻止 insert
你嘴角一撇，靠，那不就是表锁嘛？
MySQL 这么成熟，怎么会如此抵销的使用表锁，它使用的是 NEXT-key 锁。

Next-Key 锁（考研不会考的我想）

很简单，还记得我们说过的外键加锁是一个普遍的状况嘛？

Users 表中，dept_id 如果有非聚簇索引，MySQL 维护一个非聚簇索引与主键的关系，我们通过 dept_id 找到这个索引所对应所有的节点，这些节点存储着对应数据的主键信息，我们再通过主键 id 找到我们需要的数据，这个过程叫回表。

讲到这里，你就知道，B 树索引所有的数据都在叶节点上，只要在外键索引上构建一个区间锁，就可以保证 dept_id=1 的数据无法被插入（当然也无法被删除和修改），但是可以任意处理 dept_id!=1 的相关增删改，这种区间锁叫 GAP 锁。

RR 处理幻读的表现

教材明明说 RR 不能避免幻读，那么 RR 的表现到底如何？

Mysql 官方给出的幻读解释是：只要在一个事务中，第二次 select 多出了 row 就算幻读，所以在刚刚钦哥的那个这个场景下，是算出现幻读的。

SQL 标准中的术语幻象问题指非可重复谓词读（在这种情况下 SI 解决了幻象问题）；但在另一些领域（如《数据库系统概念（第 6 版）》中译版第 390 页脚注也提到了这一问题，该书中 P369 对幻象问题的定义是“查询结果会因另一并发事务是先于还是后于执行的查询而有所不同”，因此该书不认为 SI 解决了幻象问题。

所以，理论上，可重复读（Repeatable Read）是无法避免幻读的。

实际上，可重复读（Repeatable Read）几乎都不会产生幻读。

你只要记住钦老师的那个行为能奏效的场景，在也许能用到的场景下注意就好了，只有这一种特例。