MySQL Repeatable Read isolation level and Lost Update phenomena

Question

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:

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 ?

Toughminded

MVCC is by nature optimistic because queries advance in the implementation until a commit conflict occurs. But that doesn't mean optimistic locking is relevant. You keep asking questions about MySQL mentioning locking inappropriately & with other wrong expectations. Don't ask "shouldn't it"--to give an answer we would have to write yet another presentation--that is too broad, and when you don't understand it what do we do then? Instead: Explain exactly what things you think will happen with justifcation per a particular presentation. Then we can tell you where you first go wrong. – Fragile

Please use text, not images/links, for text (including code, tables & ERDs). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. PS Here that means give the table as text. But you also need to give in your post anything that we need to know to answer the question. So either quote that relevant stuff (as text) or if it would not be legal to copy then paraphrase. So, what is that business about leaking & what else do we need to know from that link? – Fragile

The classic paper introducing ACID & describing SQL pheomena & other transaction 'anomalies' & relating them to SQL implementation. – Fragile

where did I mention locking inappropriately as you say ? tx1 selects a row, then tx2 updates and commits that row, then tx1 updates the same row and commits. So I'm asking whther MVCC upon tx1 commit does check that the row version has been updated since he has selected the row and does a rollback(called optimistic locking) or it the commit does succeed causing a Lost Update ? what is wrong ? – Toughminded

Please clarify via post edits, not comments. (Explain all the steps that you think are happening.) Re "where did I mention locking inappropriately": "shouldn't MySQL MVCC prevent Lost Update using database level Optimistic Locking". – Fragile

Hi. Please act on my comments, don't just do bits & pieces. Transaction implementation involves updating the database & certain DBMS data structures according to an algorithm. You don't seem to understand that because you keep not describing everything relevant. Yet again: Say how the DBMS state (DB & MVCC control) changes on each transaction start & read & write & commit & justify it by reference to a presentation of the MVCC algorithm that you have quoted/paraphrased in your post as text. I repeat myself. Please reread my comments & act on all of them. – Fragile

Answer 1

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.

Meathead

Sorry, I should have written "Optimistic Locking" instead of "Pessimistic Locking" in my question. When tx1 selects a row having an MVCC assigned version, say X, then tx2 commits its update, then when tx1 commits its own update, then I was thinking that MVCC should detect that the row version is not X anymore resulting into a rollback of tx1. So as I understand from your answer, Innodb MVCC doesn't track the version change during commit so there is no Optimistic Locking provided by Innodb MVCC, correct ? P.S. I've updated my question. Could you please update your answer as well ? – Toughminded

It's just an implementation detail. The only thing that was standardised is just those anomalies that need to be prevented. So, according to the standard, their implementation is fine. How does it really work behind the hood, it's hard to tell without studying the source code. – Meathead

The row version does not need to be checked during update, but during commit for the previously loaded tuples. – Meathead

@VladMihalcea IMHO, 2PL can prevent lost update regardless of isolation level. (Of course, even if 2PL may produce deadlock..) – Carrico

@VladMihalcea Hmm, first, you NEVER mention SQL server on the paragraph you wrote. Second, IMHO, the reason why lost update occurs at Read Committed on SQL Server is because 2PL is not used at the level. If 2PL is used, I think lost update can NEVER occur. – Carrico

@VladMihalcea I don't understand why you told me SQL:92, SQL:96 or MVCC which I know already what it is. What I pointed out is that 2PL itself can prevent lost update regardless of isolation level. Please let me know if there is some specific case where 2PL can't prevent lost update. – Carrico

Answer 2

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.

Carrico