如何验证 MySQL 的 InnoDB 在可重复读下依然会有幻影行问题及其原因

  很多人都知道，MySQL 的 InnoDB 在事务隔离级别 REPEATABLE READ 下解决了不可重复读的问题，但是依然有幻影行问题。不过很多人都不知道这是为什么，也有很多错误的解释与验证。

  下面开始验证。首先要区分两个概念，正在观察的事务、其它事务。正在观察的事务指的是用于界定事务是否发生幻影行的事务，正在观察的事务只存在一个。其它事务是会对正在观察的事务的操作进行干扰的事务，其它事务可不唯一。

验证的流程

验证的算法如下：

1. 在 MySQL 创建任意一个数据库、表，将引擎设为 InnoDB。然后在表中初始化任意的数据。

2. 在不同的客户端下分别开启对同一个 MySQL 数据库的连接。不妨将名字设为客户端 A，客户端 B。其中，客户端 A 是进行正在观察的事务的客户端，客户端 B 是进行其它事务的客户端。

3. 在客户端 A、B 中设置会话范围的可重复读（REPEATABLE READ）事务隔离级别。

4. 分别在客户端 A，客户端 B 中开始事务。不妨将名字分别设为事务 a，事务 b。

5. 分别在客户端 A，客户端 B 查询全表数据。此时，它们的查询结果应该是一样的。

6. 在事务 b 中插入一个新的数据行，但不提交。

7. 在事务 a 中查询全表数据，此时的查询结果应该没有变化，因为这是不会发生脏读的特性。

8. 在事务 b 中提交事务，事务 b 结束。

9. 在事务 a 中查询全表数据，此时的查询结果应该仍然没有变化，因为这是可重复读的特性。

10. 在事务 a 中更新那个在事务 b 被插入的数据行。虽然这个数据行没有在前面事务 a 的查询中出现，但此时在事务 a 中却显示受到影响的行数为 1，这说明更新操作成功。

11. 在事务 a 中查询全表数据，此时会发现在事务 b 中被插入的那个行。这说明出现了幻影行。因为事务 a 并没有插入任何数据，它只是更新了一个在它眼里本来就不会存在的数据。如果没有出现幻影行，那么事务 a 中更新数据的时候，受到影响的行数应该是 0。

12. 至此，验证结束。

---

【验证的误区】

以下情况下，不能算是验证了幻影行。

• 在客户端 A 中，事务 a 查询数据后，提交了本事务（事务 a 结束），然后又开始查询，并发现了数据的变化。

• 在客户端 A 中，没有显式地开始事务，然后发现了两次查询结果之间的差异。

• 在事务 a 中插入数据之后的查询中发现了刚刚由事务 a 插入的数据。

---

自助验证

为了便于读者自行快速验证，这里给出了示例 MySQL 代码，仅供参考。

# 建表
CREATE TABLE test(
	id INT,
    username VARCHAR(20)
)ENGINE=InnoDB;

# 初始化表中数据
INSERT INTO test VALUES(1,'a'), (2,'b'),(3,'c'),(4,'d');

# 设置会话范围的可重复读事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# 设置全局范围的可重复读事务隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# 查看事务隔离级别
select @@transaction_isolation;

# 开始事务
START TRANSACTION;

# 查找全表数据
SELECT * FROM test;

# 更新表中数据
UPDATE test SET username='fff' WHERE id=5;

# 提交
COMMIT;

# 回滚
ROLLBACK;

为什么 MySQL 的 InnoDB 在可重复读下依然会有幻影行问题

  前面已经验证了 MySQL 的 InnoDB 在可重复读下依然会有幻影行问题，现在来谈谈这种情况为什么会发生。

  可重复读是 InnoDB 通过 MVCC（多版本并发控制，Multi-Version Concurrency Control）机制来实现的。InnoDB 会为每个数据行记录行的创建时间、过期时间。以及每次查询的行的版本号、查询所在事务的版本号。这样一来，只要本事务没有变更数据，那么连续同条件的查询结果应该是一样的。

  但是，这种机制只适用于查询，更新数据不会从中获益。更新数据时，就算是更新前面查询中不存在的数据，这种更新也不会引发异常，甚至更新成功了，这就会导致 InnoDB 对行的版本进行更新。由于这个更新是在本事务中进行的，因此在更新之后的下一次查询中将会出现这些数据，也就是幻影行。