读取现象和隔离级别

在学习 MySQL 的事务机制时，发现不同的课程，对于读取现象和隔离级别的解释存在较大差异（主要是读取现象中的不可重复读和幻读，以及隔离级别中各级别下可能发生的读取现象）。

上网进一步查询后，发现这方面的解释确实是众说纷纭，为了理清这两个概念，我不得不从官方资料入手来查证。

官方资料

  • SQL-92 Standard

    SQL-92 Standard 应该是这两个概念的源头。

    读取现象：

    • 脏读：SQL 事务 T1 修改了某一行。在 T1 执行 COMMIT 之前，SQL 事务 T2 已经可以读到这一行的修改。如果 T1 执行了 ROLLBACK，那么 T2 将读到从没有提交过的行，因此可以认为是读到了从未存在过的一行数据。

    • 不可重复读：SQL 事务 T1 读到了一行数据，SQL 事务 T2 紧接着提交了对该行数据的修改和删除。此时如果 T1 尝试重新读取该行，它可能接收到的是修改后的值或者发现该行已被删除。

    • 幻影（读）：SQL 事务 T1 读到满足某些<搜索条件>的行集合。然后，SQL 事务 T2 执行了一些 SQL 语句并提交，这些 SQL 语句生成了满足 T1 所使用的<搜索条件>的一行或多行数据。此时如果 T1 再次使用同样的<搜索条件>重复初始的读取，它将获得不同的行集合。

      隔离级别：

    • 读未提交 READ UNCOMMITTED：可能出现脏读，不可重复读和幻影读

    • 读已提交 READ COMMITTED：可能出现不可重复读和幻影读，不会出现脏读

    • 可重复读 REPEATABLE READ：可能出现幻影读，不会出现脏读和不可重复读

    • 串行化 SERIALIZABLE：不会出现脏读，不可重复读和幻影读

  • Wikipedia - Isolation (database systems))

    维基百科的解释中引用了 SQL-92 Standard，指出这两个概念是 SQL-92 Standard 提出的。
    但下定义时，又和 SQL-92 Standard 存在些许偏差。

    读取现象：

    • 脏读：与 SQL-92 Standard 保持一致

    • 不可重复读：SQL 事务 T1 前后进行两次查询都读到了某行数据，SQL 事务 T2 在这两次查询之间提交了对该行数据的修改，T1 第二次查询时该行的数据值已经发生了改变。

      与 SQL-92 Standard 的区别是，T2 事务不再可以做删除
      相同点是，都强调的是行数据的不可重复读

    • 幻影（读）：SQL 事务 T1 前后两次以完全相同的语句进行查询，SQL 事务 T2 在这两次查询之间执行了一些 SQL 语句并提交，T1 第二次查询时，发现相比第一次查询的结果集，有新行出现或者有旧行被删除。

      与 SQL-92 Standard 的区别是，第二次查询的结果集不只是有新增行，也可以是有删除行
      相同点是，都强调的是结果集，而非某行数据的变化，可以认为是结果集的不可重复读。

      在维基百科中，就这三种读取现象，给出了清晰的例子。

      隔离级别：各级别下可能发生的读取现象与 SQL-92 Standard 保持一致

  • MySQL 官网文档：MySQL 术语表 + MySQL 手册

    MySQL - Glossary
    15.7.2.1 Transaction Isolation Levels
    15.7.4 Phantom Rows

    读取现象：

    • 脏读：与 SQL-92 Standard 保持一致

    • 不可重复读：查询检索数据，同一事务中的后续查询检索应该是相同的数据，但查询返回不同的结果（同时有另外一个事务进行了提交所导致的变化）

      这里贴出 MySQL 术语表原文：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).

      相比 SQL-92 Standard 和维基百科，官网对于不可重复读的定义更加模糊：
      对另外一个事务的提交，到底是插入、修改还是删除，完全没有做阐述；
      对两次查询结果的不同，没有明确到底是行数据的不同，还是结果集的不同

      鉴于 MySQL 术语表中对于脏读和幻影读的定义和 SQL-92 Standard 是一致的，暂且认为 MySQL 对不可重复读的定义和 SQL-92 一样！

    • 幻影（读）：一行数据在第一次查询时并没有出现，但在第二次查询时却出现了。

      比如说，一个查询在同一事务中运行了两次，期间同时，另外一个事务插入了新行或者更新了某行并进行提交，使得该行能够匹配上之前的查询从而让第二次查询多出了该行。

      这里贴出 MySQL 术语表原文：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.

      另外还有 MySQL 手册对幻影行的解释：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 官网的幻影读定义应该和 SQL-92 Standard 是一样的：即不关心另外一个事务做了什么操作然后提交，只要当前事务有新行出现即是幻影读。

      隔离级别：

      InnoDB 的默认隔离级别是 repeatable read。

    • 各级别下可能发生的读取现象与 SQL-92 Standard 保持一致

      MySQL 的隔离级别略微有些混乱，但 InnoDB 大致应该还是 SQL-92 标准中的那四种（MySQL 8.0 手册 15.7.2.1 中明确指出的就是 SQL-92 标准中的四种）。

      consistent read 不知道算不算是一种隔离级别（感觉不算），在 MySQL 术语表中，常常将其与其他隔离级别相提并论，比如：

      non-repeatable read
      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
      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.

      consistent read 其在 read committed 和 repeatable read 两个隔离级别中的表现是不同的！
      consistent read 在 read committed 中表现为每次读都是更新快照；在 repeatable read 中表现为每次读都是开启事务后第一次读的快照。

      幻影（读）相比不可重复读，更加难以防范，因为即使锁定第一次查询结果中的所有行，也无法阻止另外一个同期事务的提交，所导致的幻影行的出现

      关于 MySQL 是否在 repeatable read 隔离级别解决了幻影（读）问题，一直是个有争议的话题，就官方文档来看，似乎 MySQL 官方并没有明确声称 MySQL 在 repeatable read 隔离级别做到了杜绝幻影（读），最多是提到了它们为了解决幻影所做的努力，如 MySQL 手册中的幻影行一节：

      To prevent phantoms, InnoDB uses an algorithm called next-key locking that combines index-row locking with gap locking. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index-record locks. In addition, a next-key lock on an index record also affects the “gap” before the index record. That is, a next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

      反而 MySQL 术语表中是多次提到 repeatable read 隔离级别下幻影读仍然存在：

      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.

      phantom
      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.

  • 微软学习文档 SQL Server - Transaction Isolation Levels (ODBC)

    读取现象和隔离级别（各隔离级别下存在的读取现象）和 SQL-92 Standard 完全一致。

非官方资料

  • 在数据库中不可重复读和幻读到底应该怎么分？ - 暖猫Suki的回答 - 知乎：

    该回答对于不可重复读和幻影读的解释应该是有问题的，不过大致也在强调不可重复读是聚焦于行数据的改变，幻影读是聚焦于结果集的改变。
    配合高赞评论进行纠正，也算是大概和 SQL-92 Standard 标准定义保持了一致。

    该回答及高赞评论，给出了一个有趣的视角，即为什么要将不可重复读和幻影读区分开来：

    删除应该不算幻读，幻读应该特指加入行。delete和update可以对记录加锁，保证事务安全。而insert，由于插入行不存在，无法加锁，只能引入间隙锁解决，这也是幻读单独拿出来的原因。

    这一点，其实在 MySQL 术语表也隐隐约约提到过：

    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.

  • 在数据库中不可重复读和幻读到底应该怎么分？ - 普通熊猫的回答 - 知乎：
    该回答提供了另外一个区分不可重复读和幻影读的视角，不可重复读更像是读异常，幻影读更像是写异常。
    不过后半句幻影读更像是写异常，感觉需要建立在不可重复读靠一致性快照的基础上，否则其实直接读就能读出来了（因为读的不是快照所以立刻就能看到新加入的数据，而不是等到写时才发现异常）！

  • 关于幻读，可重复读的真实用例是什么？ - 湾区极客的回答 - 知乎：
    大部分内容看不太懂，以后再细看。

    作者认为 MySQL 在 RR 级别已经完全解决了幻影现象的，核心观点是在 RR 级别下，事务 A 开启时，其视图/快照就已经固定了，后续查询都是在查固定的视图/快照。

    • 如果事务 A 的两次查询都是快照读 consistent read，那么两次读取的其实都是同一个旧的快照，因此结果必然总是一样的，不论事务 B 做什么操作并提交。

    • 如果事务 A 的两次查询都是当前读 locking read，那么在第一次读取后，InnoDB 就已经做好了一系列上锁（行锁 + 间隙锁），事务 B 在相应范围的修改删除和插入操作都会被阻塞，因此事务 A 的第二次查询依然与第一次一致。

      但是在 RR 级别下，如果当前读和快照读混用（先快照读（第一次查询）+ 更新（隐式触发当前读）+ 再快照读（第二次查询）），确实会出现一些奇怪的现象，不过这种情况作者认为属于是当前读和快照读的数据不一致问题，不算是幻影读。
      另外，如果快照读后接一个插入操作或更新操作，触发了主键冲突，这种情况下，算不算幻影读呢？作者没有解释，按 SQL-92 定义的话，个人感觉应该是不算的，毕竟第二次查询还没有进行呢？不过这种情况感觉似乎更加贴合幻影行的说法了，明明查出来没有，却死活插不进去！

  • MySQL 是如何解决幻读的？ - 飞天小牛肉的回答 - 知乎：
    大部分内容看不太懂，以后再细看。