A bit about Deadlock

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

In less loaded projects using InnoDB transactions, an error like

“Deadlock found when trying to get lock; try restarting transaction »

The main thing is not to panic at the sight of these scary words, now we will figure out why this happens.


A little bit about the types of locks

In the official documentation of Mysql about the types of locks very little has been written, namely:

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

It is also said that if Transaction # 1 owns a lock of type S on line r, then another Transaction # 2 can capture this lock. To get a type X lock on this line, the second transaction will have to quietly wait aside.

If Transaction # 1 owns a type X lock on row r, then Transaction # 2 can neither capture the same lock nor get a new level S. It goes quietly again and waits until Transaction # 1 releases the required row.

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 is S, it is some subset of the lock X. These are two different entities.

Back to the deadlocks. On some forums, I came across the questions “How to get deadlock in Mysql”. Actually very simple.

All the necessary ingredients we have are available: two transactions, locks of type S and X and the line to which the locks are received.

A short recipe for preparing deadlock on one line
1) Transaction # 1 gets a lock S and continues to work
2) Transaction # 2 tries to get a lock of type X and ... starts to wait when Transaction # 1 releases a lock of S
3) Transaction # 1 tries to get a lock of type X and ... begins to wait when Transaction # 2 receives a type X lock and releases it.

The dish is served.

There is one slippery moment. It would seem that it prevents Transaction # 1 from getting a lock X if it already has a lock S on the same line. And what we talked about interferes
1) Firstly, X and S are two different locks
2) Secondly, a lock of type S does not give the right to receive a lock of type X. No privileges - in the queue!

Code for 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 advises you to commit more often, as well as double-check the error code and resend the rolled back transaction. It seems to me that there is a better option - to immediately get a type X lock. Then, in the third step of our recipe, Transaction No. 1 could get its legal lock and end quietly.

Finally, I will say that the SHOW ENGINE INNODB STATUS command will help determine the cause of deadlock , which shows which locks are held and who what awaits