note
note copied to clipboard
Yhzhtk
Innodb 中 RR 隔离级别能否防止幻读？
问题引出
我之前的一篇博客 数据库并发不一致分析 有提到过事务隔离级别以及相应加锁方式、能够解决的并发问题。
标准情况下，在 RR(Repeatable Read) 隔离级别下能解决不可重复读（当行修改）的问题，但是不能解决幻读的问题。
而之前有看过一篇 mysql 加锁的文章 MySQL 加锁处理分析，里面有提到一点：
对于Innodb，Repeatable Read (RR) 针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
那么问题来了，到底 Innodb 中 RR 隔离级别是否能解决幻读呢？
在 MySQL 加锁处理分析这篇文章下面的评论中，有这样的一个交流：
ontheway 弱弱地问一句，我看的书里面都说的是RR隔离级别不允许脏读和不可重复读，但是可以幻读，怎么和作者说的不一样呢？
hedengcheng（作者） 你说的没错，因此我在文章一开始，就强调了这一点。mysql innodb引擎的实现，跟标准有所不同。
求证官方文档
MySQL Innodb 引擎的实现，跟标准有所不同，针对这个问题，我表示怀疑，于是查看 mysql 官方文档关于 RR的解释，里面有这么一段话：
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.
大致意思就是，在 RR 级别下，如果查询条件能使用上唯一索引，或者是一个唯一的查询条件，那么仅加行锁，如果是一个范围查询，那么就会给这个范围加上 gap 锁或者 next-key锁 (行锁+gap锁)。
从这句话的理解来看，和文章里的解释一样，由于 RR 级别对于范围会加 GAP 锁，这个和 sql 的标准是有一些差异的。
其他解释
后面又发现了一篇文章 Understanding InnoDB transaction isolation levels，文章中又提到：
This isolation level is the default for InnoDB. Although this isolation level solves the problem of non-repeatable read, but there is another possible problem phantom reads.
大概意思是，RR 能解决不可重复读的问题，但仍可能发生幻读，怀疑作者并不了解 Innodb 的特殊实现，评论中也有提到：
Do you mean 'write skew' instead of 'phantom reads'? The 'repeatable read' in SQL standard allows 'phantom reads', however, since InnoDB uses next-key locking this anomaly does not exist in this level. Looks like it's equivalent to 'snapshot isolation' in Postgres and Oracle.
再来看一篇文章 MySQL的InnoDB的幻读问题，这里面提供了一些例子，还没来得及分析，但最后的结论是：
MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证。而这个加锁度使用到的机制就是next-key locks。
最终结论
Innodb 的 RR 隔离界别对范围会加上 GAP，理论上不会存在幻读，但是是否有例外呢，这个还需要进一步求证。
@xiaoma20082008 所以这样还是没法避免 幻读的情况对吧， 但是之前博主的博客，不是说 select * from t where a = 1; 虽然a不是主键，也不存在唯一索引，但是会对a 符合条件的行 加上gap锁么？ 这样事务2应该会阻塞，而不是update成功才对，事务1也不会读到事务2的更新才是
@jonwinters，@xiaoma20082008 举的例子中，第一次的"select * from t where a = 1"是快照读，而不是当前读，所以是不会加锁的，事务2也不会阻塞。但是我认为这个例子反映的并不是幻读的问题。 引用MySQL 加锁处理分析里的定义：
所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。
幻读对比的是两次当前读返回的结果，@xiaoma20082008 例子里对比的是一次快照读和一次当前读，这个不是幻读的问题。
感觉这篇文章讲的不错: http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html Innodb 要想避免幻读, 需要加锁读. "如果使用普通的读，会得到一致性的结果，如果使用了加锁的读，就会读到“最新的”“提交”读的结果。"
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. 原文
Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。 a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。
如果这样理解的话，Mysql的RR级别确实防不住幻读
在快照读读情况下，mysql通过mvcc来避免幻读。 在当前读读情况下，mysql通过next-key来避免幻读。 select * from t where a=1;属于快照读 select * from t where a=1 lock in share mode;属于当前读
不能把快照读和当前读得到的结果不一样这种情况认为是幻读，这是两种不同的使用。所以我认为mysql的rr级别是解决了幻读的。
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. 原文
Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。 a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。
如果这样理解的话，Mysql的RR级别确实防不住幻读
多读出的一行，是因为 "MVCC快照读中，“自己的修改可见”。"还是update的锁把快照读变成了当前读。
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. 原文
Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。 a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作），a事务再select出来的结果在MVCC下还和第一次select一样，接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了，实测在RR级别下确实如此。 如果这样理解的话，Mysql的RR级别确实防不住幻读
多读出的一行，是因为 "MVCC快照读中，“自己的修改可见”。"还是update的锁把快照读变成了当前读。
MVCC快照读本身就包括两部分可见：1、事务开始前已提交的可见 2、自己本事务的修改可见 因此这里应该是本身快照读不可见的记录，由于自己修改过变成了快照读可见
幻读包括count行数这种吗？
select count() from table where condition for update 这种会。 select count() from table where condition for update和select count(*) from table where condition的结果可能不一样。
发表点个人看法，先看定义
P3 (Phantom): Transaction T1 reads a set of data items satisfying some
. Transaction T2 then creates data items that satisfy T1’s and commits. If T1 then repeats its read with the same , it gets a set of data items different from the first read.(A Critique of ANSI SQL Isolation Levels)
ANSI对于隔离级别的定义其实并没有针对具体的实现，所谓的快照读和当前读，是针对MVCC多版本这种特定实现技术的。比如对于单版本就不存在什么快照读了。
InnoDB RR隔离级别下的READ-ONLY事务，是能保证不可重复读和消除幻读的，因为MVCC。但是InnoDB的RR隔离级别存在P4(Lost update)现象，即更新操作自动降级到了RC级别，即更新是在最新版本的数据上进行，故RR隔离级别下的非READ-ONLY事务，可能存在@xiaoma20082008 描述的情况，即存在不可重复读和幻象的情况。
为了保证InnoDB的RR隔离级别达到ANSI-RR级别，对于单纯的SELECT语句，需要显示for update进行gap 锁，更新语句自动会上gap锁，防止其它事务的干扰，这样就能达到可重复读和消除幻读的目的。
InnoDB为什么实现的RR存在P4现象？因为商用系统为了高并发。而Jim Gray们针对MVCC实现定义的Snapshot Isolation，其实是不允许P4现象的，出现更新冲突，遵循first-commiter-win的原则，其它事务需要回滚。
所以InnoDB的RR隔离级别是大于RC级别，小于Snapshot隔离级别。但是又与传统ANSI-RR的定义有差异（Innodb-RR解决了A3幻象，但是没禁止更新丢失、写倾斜的问题，ANSI-RR定义禁止了更新丢失以及读写倾斜，但是允许幻象）。
https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/tr-95-51.pdf https://www.percona.com/blog/2012/03/27/innodbs-gap-locks/
最后给出一个DDIA书中的隔离级别测试链接(其中INNODB RR下的R/O表示只读模式下才生效！！！)： https://github.com/ept/hermitage
| DBMS | So-called isolation level | Actual isolation level | G0 | G1a | G1b | G1c | OTV | PMP | P4 | G-single | G2-item | G2 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| PostgreSQL | "read committed" ★ | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — |
| "repeatable read" | snapshot isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | |
| "serializable" | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | |
| MySQL/InnoDB | "read uncommitted" | read uncommitted | ✓ | — | — | — | — | — | — | — | — | — |
| "read committed" | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | |
| "repeatable read" ★ | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | R/O | — | R/O | — | — | |
| "serializable" | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | |
| Oracle DB | "read committed" ★ | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — |
| "serializable" | snapshot isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | — | some | |
| MS SQL Server | "read uncommitted" | read uncommitted | ✓ | — | — | — | — | — | — | — | — | — |
| "read committed" (locking) ★ | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | |
| "read committed" (snapshot) | monotonic atomic view | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | — | — | — | |
| "repeatable read" | repeatable read | ✓ | ✓ | ✓ | ✓ | ✓ | — | ✓ | some | ✓ | — | |
| "snapshot" | snapshot isolation | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | — | — | |
| "serializable" | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | |
| FDB SQL Layer | "serializable" ★ | serializable | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
RR级别下 幻读例子 Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row就算幻读。 1.a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意dml操作）， 2.a事务再select出来的结果在MVCC下还和第一次select一样， 3.接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的）， 4.a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了
其实这和事务中，先查，再修改记录，然后再查，结果出现不一致，是差不多的场景，但是在RR级别下，同一个事务内，应该不能叫脏读！
#为啥只要 进行了update 等加X锁操作，就会使得MVCC版本失效，或者变化呢 ，求大神解答？ 猜想：只要获取了记录的行锁，mysql innodb 自动会将当前操作完的版本，和对应的变更刷新到最新的版本上。再次查询 会获取最新的行记录
在快照读读情况下，mysql通过mvcc来避免幻读。 在当前读读情况下，mysql通过next-key来避免幻读。 select * from t where a=1;属于快照读 select * from t where a=1 lock in share mode;属于当前读
不能把快照读和当前读得到的结果不一样这种情况认为是幻读，这是两种不同的使用。所以我认为mysql的rr级别是解决了幻读的。
快照读和当前读不是SQL标准定义的内容，是为了更好的理解MVCC实现提出来的。如果按照ChenHaoyuan给出的例子，事务A如果设置了serializable的隔离级，事务B是不能做任何insert/update的。这是标准的避免幻读的做法。 对于幻读，不止是select，update的可见范围也要和select一致，在MySQL的RR模式下，显然做不到这一点，所以不是严格的避免了幻读。
很久了，大家居然还在讨论。前几天看《MySQL技术内幕-Innodb引擎》里面提到了，MySQL跟标准RR不一样，标准RR存在幻读问题，但innodb通过next-key-lock解决了RR的幻读问题
刚刚看到这个问题，现在是2020年7月31号，发现这个问题的创建日期是2016年4月14号。4年了问题还在讨论无非是对mysql里“幻读” 的解读不一致。 两种理解方式其实的都可以找到答案。
-
第一种解读 select * from table where index =1; 这种不加锁的方式没有幻读的解决办法，其实是通过mvcc 一致性非锁定读 的方式解决的，读到的总是事务开始前找到的快照。 有点java 里thread local的感觉， 一次请求进来先把结果和条件保留下来，再次遇到同样的条件直接取出，事务退出后清空。
-
第二种解读 select * from table where index =1 for update; 这种是通过加行所或间隙锁的方式实现，也可以说是next key lock, 一个事务先执行并对数据加了锁，再有其他的事务进来会先检查锁的状态，类似java 里监视器的感觉，先检查监视器是否有持有对象是否是自己。 一旦发现有其他事务持有当前要访问的记录就开始阻塞，等待锁的释放 然后开始竞争
查到一点资料供参考。之前有人反馈过这个 https://bugs.mysql.com/bug.php?id=63870，不过mysql的人表示就是这么设计的，这里有记录https://dev.mysql.com/doc/refman/8.0/en/innodb-consistent-read.html。 从文档里可以看到他们认为连续的快照读或者连续的当前读出现数据不一致才符合幻读的定义，而这里出现问题的是先快照读然后当前读，所以他们是这么说的：To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking。 对于连续的快照读，mvcc会保证其他事务的修改在当前事务不可见；对于连续的当前读，第一个当前读会加间隙锁，别的事务要修改直接就阻塞了。在一个事务里先快照读再当前读，由于第一个快照读mvcc没有加锁，其他事务可以修改并提交，后面的当前读在设计上就可以读到已提交的事务，update之后状态变成了自己的修改，mvcc里自己的修改是可见的，这条记录就完全可见了。 总的来说这是一个他们设计好的feature。https://developer.aliyun.com/article/698749 这里也有一点解释这么设计的原因。
1.事务的隔离级别定义的的是当前读。 2.InnoDB在可重复读级别下的当前读通过Next-Key Lock锁机制解决了幻读问题。 3.InnoDB在可重复读级别下的快照读存在“幻读”问题，解决方案是（1）改为可串行化隔离级别（2）改为当前读（读操作加锁）
同意 @ChenHaoyuan 评论里提到的对于幻读的定义：在一个未提交的事务中，突然会多出若干条记录，就算幻读
做了一个实验
预备
操作系统版本（Mac 装的虚拟机）：
MySQL 的版本：
测试表结构：
初始测试数据：
最后开两个 MySQL 客户端终端，用来模拟两个独立的事务进程，下面简称 T1 和 T2
步骤
- T1 和 T2 分别开启事务：
- T2 插入一条新记录并
COMMIT：
- 此时 T1 并不能读取到 id=13 这条记录（注意：无论 T2 是否已经提交事务，T1 目前都无法读取到 id=13 这条记录）
- T1 将所有的 name 都改成 'z'，然后再次读取整个表：
可以看到，T2 新插入的 id=13 也被修改到了，并且第二次可以读取到新插入的 id=13，也就是说发生了幻读
结论
至少对于 MySQL 5.7.33 版本来说，RR 隔离级别下是有可能发生幻读的
1.事务的隔离级别定义的的是当前读。 2.InnoDB在可重复读级别下的当前读通过Next-Key Lock锁机制解决了幻读问题。 3.InnoDB在可重复读级别下的快照读存在“幻读”问题，解决方案是（1）改为可串行化隔离级别（2）改为当前读（读操作加锁）
事务的隔离级别如上所说，是 ANSI 标准，MySQL 是对标准定义的事务隔离级别进行实现。 「当前读」和「快照读」是 MySQL 用于实现事务隔离级别 MVCC 机制中衍生出的概念，而非 “事务的隔离级别定义的的是当前读” 。
RR 级别下存在幻读问题：
| 事务 A | 事务 B |
|---|---|
begin |
|
begin |
|
insert into t1 values(2,2) |
|
commit |
|
select * from t1 此处查询使用的是 A 开始时的快照读 |
|
update t1 set a=100 此处更新使用的是当前读，B 已提交的数据也被读到，产生了幻读 |
|
select * from t1 此处可以查询到 B 插入的数据 |
「快照读」和「当前读」一起使用时就会出现幻读问题。
解决幻读问题，需要 next-key lock ：
| 事务 A | 事务 B |
|---|---|
begin |
|
select * from t1 for update 此处为了避免幻读，使用 for update 加 next-key lock |
|
begin |
|
insert into t1 values(3,3) 此处由于 A 已经加了 next-key lock ，所以此时的插入会被阻塞，等待 A 事务执行完毕释放锁以后才可以插入新的数据 |
|
commit |
A 事务提交，insert 可以执行 |
commit |
按照人对于概念的第一直觉，「RR 级别解决了幻读」应该体现为在 RR 级别下无需任何操作即可避免幻读，就像 Serializable 级别不论怎么执行事务都不会有幻读。 而实际上 MySQL 的 RR 级别并没有直接解决幻读，而是需要按照实际情况显式加锁去解决，这与直觉不符。 所谓「当前读」和「快照读」是 MySQL 自己的实现，而非标准定义的概念，混淆两者难免对初学者造成误导。 至于 “事务的隔离级别定义的的是当前读” 这种描述只能认为是宣传 MySQL 一种话术了。