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?

CodePudding user response：

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).

CodePudding user response：

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.