Mysql的幻读问题--个人看法

1.什么是幻读？

        "幻读"是ANSI SQL 标准提出的概念，如下所示

P3 (“Phantom”): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search con-dition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.

        翻译：

        P2（“幻读”）：事务 T1 读取了满足特定搜索条件的记录行集合 N，然后事务 T2 又生成了一条或多条满足事务 T1 所使用的搜索条件的记录。如果事务 T1 用相同的搜索条件重复第一次读取，则会得到不同的记录行集合。

        例子如下：

2.幻读和不可重复读的区别？

        ANSI对于不可重复读定义如下：

P2 (“Non-repeatable read”): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

        翻译如下： 

        P2（“不可重复读”）：事务 T1 读取了一行记录。然后事务 T2 修改或删除了该记录行并提交。如果 T1 尝试再次读取该行，会读到该记录修改后的值或发现该行被删除了。

        区别：

• 不可重复读侧重的是原来能够读取到的数据发生变化或者不见了。(再一次读取数据的时候，发现第二次读取到的数据跟第一次不一样或者第二次读不到原来的数据，针对单行)
• 幻读侧重的是读取到了不同的结果行数。（再一次读取数据的时候，发现第二次读取到的数据行数跟第一次读取到的不一样，针对多行）

3.为什么ANSI repeatable-read 隔离级别解决了不可重复读但是允许幻读？

3.1 ANSI隔离级别定义

        首先，在ANSI中，每个隔离级别被一个禁止出现的现象的定义：

• read-uncommitted对应p1脏读
• read-committed对应p2不可重复读
• repeatable-read对应p3幻读
• serializable没有与之对应的异常现象

3.2 ANSI隔离级别定义的不准确性

        A Critique of ANSI SQL Isolation Levels 论文（下面简称论文），指出了这个定义的不正确性：

        首先 ANSI repeatable-read 级别不符合语义，它并不能做到可重复读，应该改名叫 phantom 之类的。论文中有简单交代其背景：

Date and IBM originally used the name “Repeatable Reads” [DAT, DB2] to mean serializable or Locking SERIALIZABLE. This seemed like a more comprehensible name than the [GLPT] term “Degree 3 isolation."
The ANSI SQL meaning of REPEATABLE READ is different from Date’s original definition, and we feel the terminology is unfortunate. Since anomaly P3 is specifically not ruled out by the ANSI SQL REPEATABLE READ isolation level, it is clear from the definition of P3 that reads are NOT repeatable! We repeat this misuse of the term with Locking REPEATABLE READ in Table 2, in order to parallel the ANSI definition.

翻译：

Date和IBM最初使用名称“Repeatable Reads”[DAT，DB2]表示可串行化或锁可串行化。这似乎是一个比[GLPT]术语“隔离度3”更易理解的名称，尽管它们是相同的。可重复读的ANSI SQL含义与Date的原始定义不同，我们认为这是不幸的。Phenomena P3是ANSI SQL 可重复读隔离级别不考虑的，但从P3的定义可以看出，读取是不可重复的！（译者注：P3 违反了“可重复读”中文词义）我们在表2中仍然误用“锁可重复读”术语，来对应ANSI定义。 

        所以 对于问题“为什么ANSI repeatable-read隔离级别解决了不可重复读但是允许幻读？”来说，第一层回答是：“因为ANSI定义就是如此”

3.3基于锁定义的隔离级别

        论文中有一张表表示基于锁定义的隔离级别(注意：这个定义其实指明了用锁来实现隔离级别，ANSI的定义是不考虑具体的实现的，并且基于锁的隔离级别满足ANSI隔离级别的要求，但又不相同，所以对4种隔离级别加了locking前缀)：

        locking repeatable-read隔离级别中，对于读取操作的加锁范围 ：仅对数据项进行长时间加锁（Long duration data-item Read locks）,对于谓词范围仅对时间加锁（Short duration Read Predicate locks）.

        示例：对于select a from t where a > 1 and a < 5;

•  读取数据前：对于数据项(2,3,4)或者谓词范围(1,5)请求读锁，这叫"Well-formed Reads"
• 读取到数据后，直到事务终止后（提交或者回滚），才对数据项(2,3,4)的读锁释放。这叫“Long duration data-item Read locks”
• 读取到数据后，立马就把谓词范围(1,5)的读锁释放了，这叫"Short duration Read Predicate locks".

        用Mysql的概念，对数据项加锁就是记录锁，对谓词范围加锁就是Next-key锁(记录锁加间隙锁)。这样我们就知道了，原来在ANSI定义中，RR隔离级别中的读只是对数据记录加了锁，间隙锁只存在SQL执行期间，这样事务T2对T1读取的数据做修改、删除操作时会被阻塞，但是在间隙中插入数据却可以执行（不要跟mysql的RR混淆，下面解释）。所以根据“不可重复读”和“幻读”的定义，repeatable-read不可能出现不可重复读，但是可能会出现幻读。

4.Mysql的repeatable-read隔离级别允许幻读吗？为什么？

        答案是不允许。

4.1 MVCC能实现快照读的“可重复读”，不会出现“不可重复读”和‘幻读“

        因为MVCC实际上就是数据存在多个版本，只需要在实现上保证同一事务的两次快照读读相同的数据版本即可，还不需要用锁来实现。

4.2 Next-key lock 实现了当前读的”可重复读“，也不会出现”不可重复读“和“幻读”

        当前都：读取数据的最新版本（意味着即使是repeatable-read,当前读也能读到其他事务提交的数据），并且会加对应的锁。

ANSI repeatable-read允许幻读的原理：因为事务T1的读只对满足条件的数据行加锁，而事务T2插入的位置是数据行之间的间隙，不会被阻塞。

在Mysql的repeatable-read的实现中，当前读的加锁范围是数据行+间隙，所以事务T2的插入会被阻塞，T1的两次当前读结果不会变。例子如下：

5.快照读和当前读混用造成的异常，不能算是幻读 

        网上由文章说Mysql的repeatable-read是会出现幻读的，例子如下：

因为当前读的效果就是要读取最新的版本，实际上是把隔离级别从repeatable-read降级到了read-committed,所以快照读和当前读混用不算幻读。 

个人理解：其实就是文字游戏，Mysql实际上是解决ANSI提出的”幻读“，

1.基于MVCC的快照读可实现ANSI SQL标准的”可重复读“，并且是不会有ANSI SQL标准的”幻读问题“，因为读取的都是版本快照。

2.基于Next-key lock的当前都也可以实现ANSI SQL标准的”可重复读“，并且是不会有ANSI SQL标准的”幻读问题“，因为会锁住值和间隙。

3.我感觉争论的点其实就是看怎么理解幻读。

ANSI SQL的幻读定义：

P3 (“Phantom”): SQL-transaction T1 reads the set of rows N that satisfy some <search condition>. SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the <search con-dition> used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.

翻译：

P2（“幻读”）：事务 T1 读取了满足特定搜索条件的记录行集合 N，然后事务 T2 又生成了一条或多条满足事务 T1 所使用的搜索条件的记录。如果事务 T1 用相同的搜索条件重复第一次读取，则会得到不同的记录行集合。

严格来说，ANSI SQL 的幻读的解释对应的操作序列是: r1[P]，w2[y in P] , c2, r1[P] . 

按照这个操作顺序，

• 如果T1第一次用的是快照读r1，那么在T2提交事务之后，T1再使用快照读r1是不可能有幻读问题的。
• 如果T1第一次用的是当前读R1，那么在T2提交事务之后，T1再使用当前读R1是不可能有幻读问题的。
• 如果T1第一次用的是快照读r1，那么在T2提交事务之后，T1再使用当前读R1,然后在使用快照读r1这个时候出现的幻读显然跟ANSI SQL定义的幻读是同一种幻读。

结论：一共有两种幻读，

1.ANSI SQL定义的幻读(Mysql在RR级别下已经解决)

2.Mysql在RR级别下快照读和当前混合使用产生的幻读（使用MVCC+Next-key lock解决，例如一开始使用快照读的时候就加上Next-key lock）。

因为幻读这个github的帖子热闹了4年，有兴趣看一下：Innodb 中 RR 隔离级别能否防止幻读？ · Issue #42 · Yhzhtk/note · GitHub

借鉴：技术分享 | 隔离级别：正确理解幻读 (actionsky.com)​​​​​

ANSI SQL借鉴： 重新理解务隔离级别（1）：对 ANSI SQL 事务隔离级别的批判 - 知乎 (zhihu.com)