mysql rr解决幻读吗_mysql在RR的隔离级别下,究竟是通过MVCC解决幻读的还是通过行锁的next key算法解决的?...

在MySQL的文档的隔离级别(innodb transaction isolation)的介绍里，只有说read committed(RC)有幻像(phantom)，而repeatable read(RR)下并没说会不会有幻像。Because gap locking is disabled, phantom problems may occur, as other sessions can insert new rows into the gaps.

Glossary 下关于phantom的介绍，说serializable下不会有幻像，但 RC， RR下可以允许幻像(感谢评论区网友补充)A row that appears in the result set of a query, but not in the result set of an earlier query. For example, if a query is run twice within a transaction, and in the meantime, another transaction commits after inserting a new row or updating a row so that it matches the WHERE clause of the query.

This occurrence is known as a phantom read. It is harder to guard against than a non-repeatable read, because locking all the rows from the first query result set does not prevent the changes that cause the phantom to appear.

Among different isolation levels, phantom reads are prevented by the serializable read level, and allowed by the repeatable read, consistent read, and read uncommitted levels.

在另一页里说什么是幻像(Phantom Rows)The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row.

并举了这个例子SELECT * FROM child WHERE id > 100 FOR UPDATE;

并且它说为了避免幻像， 用了各种locking。To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking.

实际上，在RR下，select不会有phantom，读的是快照；可对于select for update(locking read)这种语句，如果前一次是select读快照(non-locking read)，在RR下是可能会有幻像的，这因为select for update被认为是写。

MySQL实现了MVCC，而一般MVCC数据库在RR的隔离级别下对应的隔离级别是snapshot isolation，这种情形下是不会有幻读的(这里幻像就取MySQL文档上的定义，不包括write skew产生的幻象transaction-iso就写明了在RR下不会有幻读：

snapshot isolation要求两个并发事务的写写冲突要回滚其中一个。MySQL并没有实现严格的snapshot isolation， MySQL认为回滚会影响性能，它允许并发的事务更新已提交的数据。这样，MySQL会产生一些其它MVCC数据库没有的异常。

举个例子，下面的两个事务，在RR隔离级别下， select是没有幻读的，但select for update却可能会产生幻读。因为select是读，通过时间戳读快照，事务2读不到事务1的写入。而select for update被认为是写，是可以更新已提交数据的，所以读到的是最新版本，事务2可以读到事务1的写入。

事务1 事务2

mysql> start transaction; mysql> start transaction;

Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;

Empty set (0.00 sec)

mysql> insert into t values(1);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

// no phantom

mysql> select * from t;

Empty set (0.00 sec)

// phantom

mysql> select * from t for update;

+------+

| c |

+------+

| 1 |

+------+

1 row in set (0.00 sec)

// update committed row

mysql> update t set c=2;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 1 Changed: 0 Warnings: 0

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

最后的结论，MySQL RR下，如果没有locking read(select for update之类)，它不会产生幻读，这种情形下是通过MVCC的快照保证的。如果存在locking read，那么可能会产生幻读。这种幻读一般会在一个locking read跟着一个non-locking read的时候发生，想避免这种情形，要么对所有的读都加锁，要么就是增加隔离级别到 serializable。这种情形，避免幻读是由锁保证的。

我在之前的几个类似问题的回答中没有考虑和说明locking read的幻读(现在我把这个例子也加上了)，看过我之前回答的同学可以注意下。

参考