本文深入探讨了MySQL中InnoDB引擎的幻读与不可重复读现象，详细解释了两者的定义与区别，以及MySQL如何通过不同的事务隔离级别解决这些问题。
mysql可重复读和幻读的理解
很多教程和书籍对mysql的可重复读和幻读的解释都比较含糊，本文结合原理和其他的考证，深入分析下。
这里讨论的引擎是常用的InnoDB。
可重复读和幻读的定义
先看下《高性能MySQL（第三版）》对隔离级别的描述。
这里对幻读的描述为
所谓幻读，指的是当某个事物在读取某个范围内的记录时，另外一个事物又在该范围内插入了新的记录，当之前的事务再次读取该范围内的记录时，会产生幻行（Phantom Row）。
实际上这里的解释不太准确，先看下官网上对这些的定义。
phantom
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.
这里说明了一个事务里查询的结果集里的一行数据，但是这一数据并不在更早一点的查询结果集里，原因是另外一个事务里对数据进行了新增、修改操作。这种现象被称之为幻读，总之就是多读出一条数据。而且RR也不能完全解决幻读问题（手动加锁应该是可以的），只有更低的事务级别serializable read可以完全避免。
non-repeatable read
The situation when a query retrieves data, and a later query within the same transaction retrieves what should be the same data, but the queries return different results (changed by another transaction committing in the meantime).
This kind of operation goes against the ACID principle of database design. Within a transaction, data should be consistent, with predictable and stable relationships.
Among different isolation levels, non-repeatable reads are prevented by the serializable read and repeatable read levels, and allowed by the consistent read, and read uncommitted levels.
介绍了不可重复读的现象，和幻读非常相似。再看下Phantom Rows的解释。
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.
不可重复读的现象，和幻读非常相似，甚至可以觉得不可重复读就是幻读的一个特例。个人觉得美团上面的解释最到位，摘录如下，
不可重复读和幻读的区别
很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。
如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。
Mysql解决不可重复读用的MVCC多版本控制，或者叫做snapshot解决的，但RR也只解决了部分的幻读问题，官网定义如下
REPEATABLE READ
This is the default isolation level for InnoDB. Consistent reads within the same transaction read the snapshot established by the first read. This means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 15.7.2.3, “Consistent Nonlocking Reads”.
For locking reads (SELECT with FOR UPDATE or FOR SHARE), 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. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.
这里还介绍了间隙锁，暂不展开了。
也就是说在RR下，select不会有phantom，因为读的是当前事开始时的快照；可对于select FOR UPDATE or FOR SHARE这种语句，如果前一次是select读快照(non-locking read)，在RR下是可能会有幻像的，这因为select FOR UPDATE or FOR SHARE按照Mysql的定义是获取当前最新的快照。
| session1 | session2 |
|---|---|
| begin | |
| select * from test_lock where t.id=6 | begin |
| insert into(id,value) valus(6,‘def’); | |
| commit; | |
| select * from test_lock where t.id=6 //不会幻读 | |
| select * from test_lock for update //会幻读 |
最后总结
总的来说，不可重复读和幻读都是违反了事务的隔离性。其实大可不必纠结字面的含义，重要的还是要搞明白内部的实现原理，并且能在工作当中避免。本质来说如果业务涉及到check-and-do的场景，必然会有数据一致性问题，因为check-and-do并非原子性的（当然redis lua脚本可以做成原子性）。如果要完全解决幻读问题，熟悉Java的并发编程的话，就知道最好的办法就是加锁，顺序执行，但是这样会很影响并发的性能，在实际使用过程中要具体分析，在并发和满足业务场景的同时选择最优解。
扫一扫
1507
到【灌水乐园】发言
