CODEWITHSUNDEEP
mysqldatabaserelational-databaserepeatable-read
Suren Aznauryan
Suren Aznauryan

Reputation: 1094

MySQL Repeatable Read isolation level and Lost Update phenomena

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:

enter image description here

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;

Question:

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 ?

Upvotes: 5

Views: 6314

Answers (2)

obanadingyo
obanadingyo

Reputation: 347

If I know correctly, MySQL use MVCC at repeatable read isolation level.

BTW, MVCC itself can't prevent lost update.

So, at repeatable read isolation level. of MySQL, we have to use select ... for update which use exclusive lock so that we can prevent lost update.

JFYI.

PostgreSQL also uses MVCC.

At repeatable read isolation level of postgreSQL, it uses first-updater-win strategy, so earlier transaction A which has updated X before later transaction B updates X will win if A successfully commits, then B must be rollbacked. If A failed and rolled backed, then B can commit successfully.

Both MySQL and postgreSQL use MVCC at read repeatable isolation level, but they have different strategies for how resolve lost update problem.

Upvotes: 2

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153770

I'm the author of the book that's mentioned in the question.

According to the SQL standard, Repeatable Read should prevent:

  • dirty reads
  • non-repeatable reads

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

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

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.

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

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 uses Pessimistic Locking for Serializable only, which provides a 2PL Concurrency Control model, even when using the MVCC-based InnoDB storage engine.

Upvotes: 13

Related Questions