MySQL可重复读取隔离级别和丢失更新现象 [英] MySQL Repeatable Read isolation level and Lost Update phenomena

问题描述

在高性能Java持久性本书的6.3.3.3部分中MySQL重复读取隔离级别中可能会出现丢失更新"现象.这是屏幕截图:

In High Performance Java Persistence book's 6.3.3.3 section it's written that Lost Update phenomena is possible in MySQL Repeatable Read isolation level. This is the screenshot:

假设以下内容(隔离级别为REPEATABLE READ):

Assuming the following(isolation level is REPEATABLE READ):

              tx1                     |                tx2
-----------------------------------------------------------------------------------
START TRANSACTION;                    |
SELECT * FROM test WHERE id = 1;      |
( say, DB_TRX_ID = 7 at this moment)   |
                                      |
                                      |  START TRANSACTION;
                                      |  SELECT * FROM test WHERE id = 1;
                                      |  UPDATE test SET name="x" WHERE id = 1;
                                      |  COMMIT;(say, makes DB_TRX_ID = 10)
                                      |
UPDATE test SET name="y" WHERE id = 1;|
COMMIT;

问题:

在tx1提交后，MVCC将检测到行版本(DB_TRX_ID)不再等于7(而不是10)并执行回滚吗?否则提交将成功导致更新丢失?

Upon tx1 commit will MVCC detect that the row version(DB_TRX_ID) is not equal to 7 anymore(instead it's 10) and perform a rollback ? Or the commit will be succeeded causing a Lost Update ?

推荐答案

在可重复读取"隔离级别中，MySQL MVCC是否应使用数据库级别的悲观锁定来防止丢失更新，从而导致事务回滚?

In Repeatable Read isolation level shouldn't MySQL MVCC prevent Lost Update using database level Pessimistic Locking resulting into transaction rollback?

根据SQL标准，可重复读取应防止:

According to the SQL standard, Repeatable Read should prevent:

• 脏读
• 不可重复读取

该标准对丢失的更新一无所知，因为该标准是在 2PL(两阶段)时设计的锁定)是事实并发控制机制.

The standard says nothing about lost updates because the standard was designed when 2PL (Two-Phase Locking) was the facto Concurrency Control mechanism.

如果使用2PL，那么可重复读取"隔离级别实际上将阻止

If you use 2PL, then the Repeatable Read isolation level will, indeed, prevent a Lost Update.

但是，MVCC可以通过元组的多个版本提供可重复读取，但是，为了防止丢失更新，它们还需要事务调度程序来跟踪由某个事务读取的记录的元组修改.显然，InnoDB不能那样工作.

However, MVCC can provide Repeatable Reads via multiple versions of a tuple, but, in order to prevent Lost Updates, they also need the transaction scheduler to track tuple modifications for the records read by a certain transaction. Apparently, InnoDB does not work like that.

MySQL MVCC不应使用数据库级悲观锁定来防止丢失更新，从而导致事务回滚

shouldn't MySQL MVCC prevent Lost Update using database-level Pessimistic Locking resulting into transaction rollback

现在，如果您仔细阅读了>高性能Java持久性，您会发现MVCC在可重复阅读中未使用任何悲观锁定.唯一采取的锁定是对聚集索引采取的间隙和下一键锁定，但是这些不能防止丢失更新".

Now, if you read carefully the Transactions chapter in my High-Performance Java Persistence book, you will find out that MVCC does not use any Pessimistic Locking in Repeatable Read. The only locks taken are the gap and next-key locks taken on the clustered index, but those don't prevent Lost Updates.

MySQL仅将悲观锁定用于可序列化，即使使用基于MVCC_的InnoDB存储引擎，它也提供2PL并发控制模型.

MySQL uses Pessimistic Locking for Serializable only, which provides a 2PL Concurrency Control model, even when using the MVCC_based InnoDB storage engine.

这篇关于MySQL可重复读取隔离级别和丢失更新现象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！