MySQL InnoDB的可重复读（REPEATABLE-READ）是否解决了幻读(PHANTOM READS)问题

本文链接：https://blog.csdn.net/LCBUSHIHAHA/article/details/112793480

关于数据库的隔离级别标准的定义是在可重复读级别下可能发生幻读，解决幻读只有使用可序列化。虽然标准是这样说但是数数据库实现的隔离级别可能会和标准有点出入。关于这方面的问题在DDIA中有一些讨论。

关于MySQL的可重复读有没有解决幻读的问题也是偶然在网上看到了。进而去在网上查阅了一些博客并没有得到准确的回答，论证描述的也各自相互矛盾，不过再搜寻的过程中也学到了一些知识。在博客中没有找到答案，尝试去官网查找了一下。

REPEATABLE READ

The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads.

上面是MySQL官方文档给出的RR的描述，大意就是InnoDB的默认隔离级别为可重复读。它防止被其他事务正在修改的行被当前事务查询到，因此能够防止不可重复读但是不能防止幻读。看到这里觉得官网都说可重复读没有解决幻读的问题那肯定就没有了。但是MySQL的可重复读解决了幻读不可能是空穴来风，并且也有很多人持有肯定态度。所以笔者决定自己验证一把。

什么是幻读

要找到答案，首先得清楚问题是什么。幻读，什么称为幻读呢？MySQL官方文档对于幻读的描述如下：

phantom

A row that appears in the result set of a query, but not in the result set of an earlier query.

大意是同一个查询后面查询的结果集有些行在前面查询的结果集中不存在。也就是两个事务，在一个事务A中先执行某个查询，在另一个事务B中插入一条满足事务A中的行并提交事务，在事务A中再执行同样的查询会查询到事务B中刚插入的行。弄清楚了需要验证的问题，就开始实际操作验证吧。

验证使用的环境

MySQL 8.0+

验证使用的表SQL

CREATE TABLE foo (i INT PRIMARY KEY, val INT);
INSERT INTO foo (i, val) VALUES (1, 10), (2, 20), (3, 30);

执行表的创建语句和初始化数据

--创建表
mysql> CREATE TABLE foo (i INT PRIMARY KEY, val INT);
Query OK, 0 rows affected (0.04 sec)

--插入数据
mysql> INSERT INTO foo (i, val) VALUES (1, 10), (2, 20), (3, 30);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

--确认数据
mysql> select * from foo;
+---+-----+
| i | val |
+---+-----+
| 1 |  10 |
| 2 |  20 |
| 3 |  30 |
+---+-----+
3 rows in set (0.05 sec)

查看当前数据库的隔离级别和事务的自动提交的模式

--查询事务的隔离级别
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.02 sec)
--查询session的事务自动提交模式
mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.03 sec)
--查询全局的事务自动提交模式
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.03 sec)

通过查询，当前我的数据的事务隔离级别是RR，事务的自动提交模式都是打开的。关于数据库事务自动提交的模式分为session级别和全局级别的，我们测试使用session级别的。

所以为了测试我需要先将数据库的事务自动提交关闭。

--执行session事务自动提交关闭的指令
mysql> set session autocommit=0;
Query OK, 0 rows affected (0.00 sec)
--验证事务自动提交是否关闭
mysql> show session variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.04 sec)
--全局的并未受到影响
mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.03 sec)

mysql>

做完了上面的准备工作可以开始我们实际的测试环节了。验证的过程如下：

	事务A	事务B	事务C	说明
T1		开始事务B		开始事务B
T2		SELECT * FROM foo;		事务B查询foo表的所有记录
T3	开始事务A			开始事务A
T4	UPDATE foo SET val=33 WHERE i=3;			事务A修改foo表i=3的行,修改成功。
T5	SELECT * FROM foo;			事务A查询foo表记录，验证成功更新。
T6		SELECT * FROM foo;		事务B查询foo表的所有记录
T7		SELECT * FROM foo for update;		事务B当前读查询，查询失败。失败原因：获取锁超时。
T8		UPDATE foo SET val=33 WHERE i=2;		事务B修改foo表i=2的行,成功更新。
T9		UPDATE foo SET val=333 WHERE i=3;		事务B修改foo表i=3的行,修改失败。失败原因：获取锁超时。
T10
T11	提交事务A			提交事务A
T12		SELECT * FROM foo;		事务B查询foo表的所有记录，快照读查询成功，并未查询到事务A提交的数据。
T13		SELECT * FROM foo for update;		事务B查询foo表的所有记录，由于事务A已提交，next-key锁释放。所以当前读查询成功，并查询到事务A提交的数据。
T14			开始事务C	开始事务C
T15			SELECT count(1) FROM foo;	事务C查询foo表记录总数。
T16			UPDATE foo SET val=4444 WHERE i=1;	事务C修改foo表i=1的行,修改失败。失败原因：获取锁超时。
T17			INSERT INTO foo (i, val) VALUES (11, 10), (22, 20), (33, 30);	事务C插入几条记录，插入失败。失败原因：获取锁超时。
T18		INSERT INTO foo (i, val) VALUES (111, 10), (222, 20), (333, 30);		事务B插入几条记录，插入成功。
T19		SELECT count(1) FROM foo;		事务B查询foo表的总数。
T20		SELECT count(1) FROM foo for update;		事务B查询foo表的所有记录。
T21			SELECT count(1) FROM foo;	事务C查询foo表的总数,快照读，未读取到事务B中新插入的的数据。
T22			SELECT count(1) FROM foo for update;	事务C当前读查询foo表总数，查询失败。失败原因：获取锁超时。
T23		提交事务B
T24			SELECT COUNT(1) FROM foo for update;	事务B查询foo表总数，查询成功。当前读能成功看到事务B提交的记录。
T25			SELECT count(1) FROM foo;	事务B查询foo表总数，查询成功。快照读不能成功看到事务B提交的记录。
T26			提交事务C

T2:先在事务B中执行查询所有行，得到了三条结果。

mysql>T2 事务B SELECT * FROM foo;
+---+-----+
| i | val |
+---+-----+
| 1 |  10 |
| 2 |  20 |
| 3 |  30 |
+---+-----+
3 rows in set (0.05 sec)

T3:然后再开启事务A。

T4:在事务A中对i=3的行进行修改

--T4 事务A 修改数据
mysql> 	UPDATE foo SET val=33 WHERE i=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
--T5 事务A 查看数据已被成功修改
mysql> SELECT * FROM foo;
+---+-----+
| i | val |
+---+-----+
| 1 |  10 |
| 2 |  20 |
| 3 |  33 |
+---+-----+
3 rows in set (0.03 sec)

T5:在事务A中查询foo表的所有数据，数据i=3被成功修改。

T6:在事务B中查询foo表的所有数据，返回的结果并没有被修改。此时，我们验证了在RR级别下不会读取未提交事务的数据。

T7:接着在事务B中执行SELECT * FROM foo for update;会发现无法执行被获取锁超时了。

T8:在事务B中对i=2的行进行修改，执行成功。

T9:在事务B中对i=3的行进行修改，执行失败，获取锁超时。

--T6 在事务B中查询foo表的所有数据，并未查询到事务A中对i=3的修改记录。
mysql> SELECT * FROM foo;
+---+-----+
| i | val |
+---+-----+
| 1 |  10 |
| 2 |  20 |
| 3 |  30 |
+---+-----+
3 rows in set (0.24 sec)

--T7 事务B 查询数据，获取锁超时
mysql> SELECT * FROM foo for update;
Lock wait timeout exceeded; try restarting transaction

--T8 事务B 更新i=2的行，成功。
mysql> UPDATE foo SET val=33 WHERE i=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--T9 事务B 更新i=3的行，失败。
mysql> UPDATE foo SET val=333 WHERE i=3;
1205 - Lock wait timeout exceeded; try restarting transaction

执行到T9时刻，事务A和事务B都是活跃状态，都还未执行事务提交操作。

这个时候需要解释一下为什么执行SELECT * FROM foo for update;会获取锁失败，而SELECT * FROM foo;没有这样的情况。要想弄清楚其中的原因要先了解一下MySQL读的种类。

在MySQL中数据的查询分为两种：快照读和当前读。

快照度：MySQL利用MVCC实现快照读。在RR下，事务开启时会产生快照，在RC下，每一条语句执行都相当于产生一个快照。快照读只会读取事务开启时已提交事务的数据，之后提交的都无法查看。

当前读：读取数据库中最新的已提交数据，可以理解为RC。

在T5时刻执行的SELECT * FROM foo for update;为当前读。SELECT * FROM foo;是快照读。

可能大家都会有一个疑惑，在快照级别下有快照读就够了，为什么还要当前读？当前读解决了什么问题？

通过前面的解释，快照读能解决不可重复读的问题。那当前读存在的意义是什么？当前读是用来解决覆盖写的问题的。在MySQL中insert，update，delete， select ... for update， select ... lock in share mode 都属于当前读。

拿update来举例，在一个事务A中修改某个数据，事务A还未提交，事务B中也同样修改这个数据，事务B提交，事务A提交。这个时候事务A就会覆盖事务B的提交记录。除了覆盖提交记录还有如果是快照读，可能update的条件在其他事务中修改，满足的行会发生变化。

在RR的快照读对于这种情况是有问题的。需要当前读获取当前最新的提交数据确保数据不会丢失，除了读取最新的数据外，在写入数据时还需要获取锁。这也就说明了在T7时刻执行当前读语句时出现了获取锁超时的原因：事务A没有释放对应的锁，事务B无法获取到锁。

我们了解到在一个事务做当前读的时候会获取锁，那这个锁是什么类型的锁？持有多久呢？

很多人在阐述MyISAM和InnoDB区别时都会提到一点：MyISAM是表锁，而InnoDB是行锁。那这里会不是行锁呢？可以看到在T4，T8，T9时刻在A，B两个事务中分别对i=3和i=2的行进行了更新操作，根据执行结果我们可以做出如下的推测：

T4，T8成功，T9失败。T4,T8执行成功说明它们之间没有锁的互斥，可以先排除时表锁。
T4成功，T9失败说明T4和T9是互斥的。也就是说他们需要持有相同的锁，它们都是修改i=3这行的数据。可以得出至少需要修改行的行锁。

这个时候可以得出一个临时的结论：在RR隔离级别下，当前读不会持有表锁，会持有修改数据修改行的行锁。未被修改的行不会被锁定，其他事务仍然可以对其进行修改。

根据现有的结果只能推测出上面的临时结论，要更近一步得到更准确的结论还需要继续执行后面的语句。

T11:提交事务A

T12:执行快照读获取foo表所有数据，并未读取到事务A提交的修改数据。此时，我们验证了RR级别下，不会读取事务开始后其他事务提交的数据。也就是可重复读。

T13:执行当前读获取foo表所有数据。当前读能读取到事务A提交的修改数据。

--T12 执行快照读获取foo表所有数据，并未读取到事务A提交的修改数据。
mysql> SELECT * FROM foo;
+---+-----+
| i | val |
+---+-----+
| 1 |  10 |
| 2 |  33 |
| 3 |  30 |
+---+-----+
3 rows in set (0.24 sec)

--t13 执行当前读获取foo表所有数据。当前读能读取到事务A提交的修改数据。
mysql> SELECT * FROM foo for update;
+---+-----+
| i | val |
+---+-----+
| 1 |  10 |
| 2 |  33 |
| 3 |  33 |
+---+-----+
3 rows in set (0.24 sec)

T14:开启了一个新事务C

T15:统计了foo表的数据总数。

T16:更新i=1的行，修改失败。失败原因：获取锁超时。

T17:插入几行，修改失败。失败原因：获取锁超时。

mysql> SELECT count(1) FROM foo;
+----------+
| count(1) |
+----------+
|        3 |
+----------+
1 row in set (0.29 sec)

mysql> UPDATE foo SET val=4444 WHERE i=1;
1205 - Lock wait timeout exceeded; try restarting transaction
mysql> INSERT INTO foo (i, val) VALUES (11, 10), (22, 20), (33, 30);
1205 - Lock wait timeout exceeded; try restarting transaction

现在，我们又得到了几个新的推测依据。首先是T16时刻修改i=1的行失败，之前我们的临时是在RR隔离级别下，当前读不会持有表锁，会持有修改数据修改行的行锁。未被修改的行不会被锁定，其他事务仍然可以对其进行修改。但是我们之前只修改了i=2和i=3的行，为什么i=1的行也被锁住了，是不是这个结论被推翻了？

再仔细回想一下之前执行的语句，在T13时刻事务B成功执行了SELECT * FROM foo for update;的当前读语句。这个语句会设计到整个表的数据，所以整个表的数据都是被锁住了。之前的临时结论并没有被推翻。

接下来我们执行了T14时刻的语句，插入语句失败了。失败的原因同样是获取锁失败，但是这些行都是以前并不存在的行，它要获取的是什么锁？此时就引出了MySQL的一个新的锁概念---间隙锁。

间隙锁

在mysql的innoDB存储引擎中，如果更新操作是针对一个区间的，那么它会锁住这个区间内所有的记录，比如update xxx where id between a and b那么它会锁住a到b之间所有记录，注意是所有记录，甚至这个记录并不存在也会被锁住，这个时候，如果另外一个连接需要插入一条记录到a到b之间，那么它就必须等到上一个事务结束。

由于我们执行的查询全表的当前读，所以会锁住所有行并且阻止新的行插入。

此时，我们可以更新之前得到的临时结论得到新的临时结论：在RR隔离级别下，会持有修改数据修改行的行锁和间隙锁（next-key锁）。不在修改区间的已存在的行不会被锁定，在修改区间的未插入行空间也会被锁定。

既然之前的当前读涉及的区间被锁定了，未插入的行区间都被锁定了也就是没有行能新插入到之前当前读的区间，那么我们也可以推测出幻读也就不会发生了。带着推测我们继续执行下面的语句。

T18:事务B插入几行数据成功。

T19,T20:事务B验证数据成功。

--T18 事务B插入几行数据
mysql> INSERT INTO foo (i, val) VALUES (111, 10), (222, 20), (333, 30);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

--T19,20 事务B验证数据插入成功
mysql> SELECT count(1) FROM foo;
+----------+
| count(1) |
+----------+
|        6 |
+----------+
1 row in set (0.30 sec)

mysql> SELECT count(1) FROM foo for update;
+----------+
| count(1) |
+----------+
|        6 |
+----------+
1 row in set (0.29 sec)

T21:事务C统计foo表所有数据。快照读，并未读取到事务B中新插入的行。

T22:事务C当前读统计foo表所有数据，获取锁失败。

mysql> SELECT count(1) FROM foo;
+----------+
| count(1) |
+----------+
|        3 |
+----------+
1 row in set (0.30 sec)

mysql> SELECT count(1) FROM foo for update;
Lock wait timeout exceeded; try restarting transaction

T23:提交事务B。

T24:事务B查询foo表总数，查询成功。当前读能成功看到事务B提交的记录。

T25:事务B查询foo表总数，查询成功。快照读不能成功看到事务B提交的记录。再根据T15的执行结果，我们可以得到RR级别下并未发生幻读的结论。

T24 事务B查询foo表总数，查询成功。当前读能成功看到事务B提交的记录。
mysql> SELECT COUNT(1) FROM foo for update;
+----------+
| COUNT(1) |
+----------+
|        6 |
+----------+
1 row in set (0.29 sec)

T25 事务B查询foo表总数，查询成功。快照读不能成功看到事务B提交的记录。
mysql> SELECT COUNT(1) FROM foo;
+----------+
| COUNT(1) |
+----------+
|        3 |
+----------+
1 row in set (0.29 sec)

执行到这里我们可以得到RR级别下确实没有发生幻读的问题。

看到这里你会觉得MySQL对于RR的处理是不是觉得很赞？但是在RR级别下的next-key 锁解决了幻读问题，同样也带来了性能问题。只要涉及到锁，在并发的情况下都会影响性能，如果影响不大业务可以容忍。那next-key对性能的影响呢？

首先我们看一下next-key的加锁规则：

两个“原则”、两个“优化”和一个“bug

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

--引用自《MySQL实战45讲》林晓斌

我们可以把重点放在原则二上，访问过的对象都会加锁。在查询数据的过程中，遍历到了某个数据，那个数据就会被锁上。如果在RR级别，某个当前读操作没有使用索引走了全表扫描，那么就相当于加了表锁。所以在RR级别下进行当前读的操作（不如UPDATE）就一定要走索引。如果不使用RR就不会有这个问题，可以使用RC再加binlog的ROW模式（RC级别下如果不是ROW模式会出现binlog重放数据不一致的情况）。

参考资料

mysql官方文档

《MySQL实战45讲》林晓斌