Search code examples
mysqlsqltransactionsisolation-levelpessimistic-locking

Deadlock in transaction with isolation level serializable

I was trying to understand how locking works with isolation levels. I have gone through this question but can not understand flow given blow

Here i am starting two transactions in different terminals and reading same row in them. As i try to update them both the terminal keeps waiting for the update. No other query is running apart from this

Here are the series of steps i did

conn1: START TRANSACTION;
conn1: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn2: START TRANSACTION;
conn2: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn1: SELECT * from users WHERE id = 1;
conn2: SELECT * from users WHERE id = 1;
conn1: UPDATE users set name = 'name' WHERE id = 1; waiting...
conn2: UPDATE users set name = 'name' WHERE id = 1; waiting...

Here is my first question
Here i want to understand why both the connections are waiting and if they are who has the lock to update the row ?

If i change above steps to

conn1: START TRANSACTION;
conn1: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn2: START TRANSACTION;
conn2: SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
conn1: UPDATE users set name = 'name' WHERE id = 1;
conn2: SELECT * from users WHERE id = 1; waiting...
conn1: commit
conn2: updated results

In this case the difference is i can see conn1 has the lock and until it either commits or rollback the changes all other request will be waiting and will get updated results if conn1 committed

Here is my second question
Is this the correct way if i want to lock a row and if locked i want other connections to wait(even for read) till this lock releases(commit or rollback) or i should use for update clause

DB - Mysql 5.7

Solution

  • As mysql documentation on SERIALIZABLE isolation level says:

    This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE

    The clause on autocommit does not apply here, since you explicitly start a transaction.

    This means that in the first scenario both transactions obtain a shared lock on the same record. Then the first transaction (T1) tries to execute an update, which needs an exclusive lock. That cannot be granted, since T2 holds a shared lock. Then T2 tries to update, but cannot due to T1 holding a shared lock.

    Whether you use an atomic update or a select ... for update statement to lock records, depends on the application logic you need to apply. If you need to fetch the record's data an do some complex calculations with those before updating the record, the use the select ... for update approach. Otherwise, go for the atomic update.