mysql - 为什么 MariaDB 上的 REPETEABLE_READ 不产生幻读？

Question

在我的测试中，我发现当使用 MariaDB 时，在REPETEABLE_READ 隔离中执行相同的查询不会产生幻读，而它应该产生幻读。

例如:

我在 bank_account 表中有两行:

  ID |  OWNER | MONEY
------------------------
  1  |  John  | 1000
  2  |  Louis | 2000

预期的流程应该如下图所示:

THREAD 1 (REPETEABLE_READ)                THREAD 2 (READ_UNCOMMITED)
  |                                         |
findAll()->[1|John|1000,2|Louis|2000]       |          
  |                                         |
  |                                       updateAccount(1, +100)
  |                                       createAccount("Charles", 3000)                 
  |                                       flush()
  |                                         |
  |                                         commitTx()
  |                                         |_
  |                                         
findAll()->[1|John|1000,2|Louis|2000,       
  |         3|Charles|3000]                 
  |                                         
  |                                         
 commitTx()                               
  |_                                        

总而言之，在 Thread2.createAccount("Charles", 3000); 及其刷新之后，Thread1 将搜索所有行并获得

  ID |  OWNER   | MONEY
------------------------
  1  |  John    | 1000
  2  |  Louis   | 2000
  3  |  Charles | 3000

Thread1 受到保护，不会看到 [1, John, 1000] 而不是 [1, John, 1100] 但它应该看到新插入的行.

但是，Thread1 在第二个 findAll 中检索到的结果与第一个 findAll() 中的结果完全相同:

  ID |  OWNER   | MONEY
------------------------
  1  |  John    | 1000
  3  |  Charles | 3000

它没有幻读。为什么？？？？

这是Thread1执行的代码:

@Transactional(readOnly=true, isolation=Isolation.REPEATABLE_READ)
@Override
public Iterable<BankAccount> findAllTwiceRepeteableRead(){
    printIsolationLevel();
    Iterable<BankAccount> accounts = baDao.findAll();
    logger.info("findAllTwiceRepeteableRead() 1 -> {}", accounts);
    //PAUSE HERE
    ...
}

我在它说的地方暂停执行//PAUSE HERE。

然后Thread2执行:

bankAccountService.addMoneyReadUncommited(ba.getId(), 200);
bankAccountService.createAccount("Carlos", 3000);

然后 Thread1 恢复:

//PAUSE HERE
...
Iterable<BankAccount> accounts = baDao.findAll();
logger.info("findAllTwiceRepeteableRead() 2 -> {}", accounts);

更新:我已经用我真正做的事情更新了线程事务流(我在插入新行后提交第二个事务)。

这符合什么，根据维基百科是幻读，我认为是完全相同的场景。所以我仍然不明白为什么我没有得到幻读 [3|Charles,3000]

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first.

This can occur when range locks are not acquired on performing a SELECT ... WHERE operation. The phantom reads anomaly is a special case of Non-repeatable reads when Transaction 1 repeats a ranged SELECT ... WHERE query and, between both operations, Transaction 2 creates (i.e. INSERT) new rows (in the target table) which fulfill that WHERE clause.

Transaction 1                             Transaction 2
/* Query 1 */
SELECT * FROM users
WHERE age BETWEEN 10 AND 30;
                                          /* Query 2 */
                                          INSERT INTO users(id,name,age) VALUES ( 3, 'Bob', 27 );
                                          COMMIT;
/* Query 1 */
SELECT * FROM users
WHERE age BETWEEN 10 AND 30;
COMMIT;

Answer 1

您所描述的实际行为实际上是 repeatable_read 的正确行为。您期望的行为可以通过使用 read_committed 实现。

作为 repeatable_read 上的 mariadb 文档说(粗体是我的):

there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read.

在线程 1 中，返回 John 和 Louis 的第一个 FindAll() 调用建立了快照。第二个 FindAll() 只是使用了相同的快照。

Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels 上的 Percona 博客文章进一步证实了这一点:

In REPEATBLE READ, a ‘read view’ ( trx_no does not see trx_id >= ABC, sees < ABB ) is created at the start of the transaction, and this read view (consistent snapshot in Oracle terms) is held open for the duration of the transaction. If you execute a SELECT statement at 5AM, and come back in an open transaction at 5PM, when you run the same SELECT, then you will see the exact same resultset that you saw at 5AM. This is called MVCC (multiple version concurrency control) and it is accomplished using row versioning and UNDO information.

更新

警告:以下引用资料来自 MySQL 文档。但是，由于这些引用与 innodb 存储引擎相关，我坚信它们也适用于 mariadb 的 innodb 存储引擎。

因此，在可重复读隔离级别下的innodb存储引擎中，非锁定选择在同一事务内从第一次读取建立的快照读取。无论在并发提交的事务中插入/更新/删除了多少记录，读取都是一致的。期间。

这是 OP 在问题中描述的场景。这意味着可重复读取隔离级别的非锁定读取将无法产生幻读，对吗？好吧，不完全是。

作为 InnoDB Consistent Nonlocking Reads 上的 MySQL 文档说:

The snapshot of the database state applies to SELECT statements within a transaction, not necessarily to DML statements. If you insert or modify some rows and then commit that transaction, a DELETE or UPDATE statement issued from another concurrent REPEATABLE READ transaction could affect those just-committed rows, even though the session could not query them. If a transaction does update or delete rows committed by a different transaction, those changes do become visible to the current transaction. For example, you might encounter a situation like the following:

SELECT COUNT(c1) FROM t1 WHERE c1 = 'xyz';
-- Returns 0: no rows match. DELETE FROM t1 WHERE c1 = 'xyz';
-- Deletes several rows recently committed by other transaction.

SELECT COUNT(c2) FROM t1 WHERE c2 = 'abc';
-- Returns 0: no rows match. UPDATE t1 SET c2 = 'cba' WHERE c2 = 'abc';
-- Affects 10 rows: another txn just committed 10 rows with 'abc' values. 
SELECT COUNT(c2) FROM t1 WHERE c2 = 'cba';
-- Returns 10: this txn can now see the rows it just updated.

综上所述:如果使用带可重复读隔离模式的innodb，那么如果并发提交事务中的数据修改语句与当前事务中的数据修改语句交互，则可能会出现幻读。

有关隔离级别的链接维基百科文章描述了一个通用的理论模型。您始终需要阅读实际的产品手册，了解特定功能的实现方式，因为可能存在差异。

在维基百科文章中，只有锁被描述为一种防止幻读的方法。而innodb在大多数情况下是通过快照的创建来防止幻读的，因此不需要依赖锁。