A bit about Deadlock

This is a very brief post about the causes of Deadlock.

In more or less loaded projects using InnoDB transactions, an error like this may occur

“Deadlock found when trying to get lock; try restarting transaction »
')
The main thing is not to panic at the sight of these terrible words, now we will understand why this is happening.


A little bit about the types of locks

In the official documentation Mysql about the types of locks written quite a bit, namely:

There are 2 types of locks - Shared (S) and Exclusive (X). The first type only allows you to read the data covered by this lock, the second - read, write, delete, and (which is modestly silent) - get a level S lock

It is also said that if Transaction # 1 owns a type S lock on line r, then another Transaction Number 2 can take this lock. To get an X-type lock on this line, the second transaction will have to be quietly waited on the sidelines.

If Transaction 1 holds a type X lock on line r, then Transaction 2 cannot either take the same lock or get a new level S. It goes silently again and waits for Transaction 1 to release the required line.

There is one important point that needs to be learned: S and X locks are 2 different locks. This does not mean that the lock S, this is some kind of a subset of the lock X. These are two different entities.

Let's go back to deadlocks. In some forums, I met the questions "How to get a deadlock in Mysql." Actually very simple.

All the necessary ingredients we have available: two transactions, the lock type S and X and the line on which to get the lock.

A brief recipe for cooking deadlock on one line
1) Transaction 1 receives lock S and continues operation
2) Transaction 2 is trying to get a type X lock and ... begins to wait for Transaction 1 to release the lock S
3) Transaction # 1 tries to get an X-type lock and ... starts waiting for Transaction # 2 to get an X-type lock and release it

Dish is served

There is one slippery moment. It would seem that it prevents Transaction 1 to get lock X if it already has lock S on the same line. And it bothers what we talked about
1) First, X and S are two different locks
2) Secondly, an S-type lock does not entitle you to a X-type lock. There are no privileges in the queue!

The code for the situation above

Transaction # 1

BEGIN; SELECT * FROM `testlock` WHERE id=1 LOCK IN SHARE MODE; /* GET S LOCK */ SELECT SLEEP(5); SELECT * FROM `testlock` WHERE id=1 FOR UPDATE; /* TRY TO GET X LOCK */ COMMIT; 

Transaction # 2

 BEGIN; SELECT * FROM `testlock` WHERE id=1 FOR UPDATE; /* TRY TO GET X LOCK - DEADLOCK AND ROLLBACK HERE */ COMMIT; 

How to deal with this? Off Mysql website advises to commit more often, as well as to recheck the error code and re-roll back the transaction. It seems to me that there is a better option - to immediately get a lock of type X. Then at the third step of our recipe, Transaction # 1 could get its legal lock and calmly end

Finally, I’ll say that the SHOW ENGINE INNODB STATUS team will help determine the cause of the deadlock, which shows which locks are held and waited.