'Does MySQL rollback transaction on conflict

I am using Mysql with Innodb 5.7 with REPEATBLE READ isolation level. I know that with MVCC, readers dont block writers and vice versa. I want to understand how InnoDB behaves when there are 2 concurrent transactions working on same row for below cases:

  1. Updating same column
  2. Updating different column

Imagine the below scenario with time increasing downwards:

Tx1 starts here
Tx1 reads row R                   Tx2 starts here
Tx1 reads few other rows          Tx2 reads row R
......                            Tx2 sets column value here on row R       
Tx1 sets column value here on row R

How will Innodb behave for update on same row for above 2 cases?
Will it abort one transaction(Tx1) returning error to client as MVCC version has changed?
Or will it process both overwriting value if column is same?

mysqltransactionsinnodb


Solution 1:[1]

I did some experiment and found that InnoDB does not rollback Tx1 in such a situation leading to the obvious lost update scenario. It does however take an exclusive lock for the update statement until the Tx is committed(or rollbacked).

Solution 2:[2]

If Tx1 tries to update a row that is locked by Tx2, then Tx1 waits for the lock to be released. If the lock is not released by the time a number of seconds (innodb_lock_wait_timeout) passes, then Tx1 receives an error. But this does not roll back Tx1.

Tx1 may have an unknown number of updates pending that it did successfully earlier in its transaction, and it would not be good to discard all of those just because the latest update failed.

Tx1 still has its uncommitted transaction. It may choose to commit those without the update to row R, or it may try to update row R again, or try something different.

Solution 3:[3]

In your example, you need FOR UPDATE when you Select row R. Otherwise, you might have made the wrong decision because of another thread modifying it before you get to your Update.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Saurav Prakash
Solution 2 Bill Karwin
Solution 3 Rick James

Related Questions