InnoDB deadlocks explained with example

A deadlock happens when transaction holds lock that the another one needs, This usually happens when multiple transactions are waiting for a resource  to become available, neither ever release the locks it holds. A deadlock can occur when transactions lock rows in multiple tables (through statements such like UPDATE or SELECT … FOR UPDATE) . We have explained dead lock scenario in the example below :

Transaction T1 

Transaction T1 creates a table “tab1” with one row and begins a transaction. Transaction T1 obtains an S lock on the row by selecting in a shared mode:

mysql> CREATE TABLE tab1 (col1 INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO tab1 (col1) VALUES(108);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tab1 WHERE col1 = 108 LOCK IN SHARE MODE;
+------+
| col1 |
+------+
|    1 |
+------+

Transaction T2

Begins a transaction and attempts to delete the row from the table tab1

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM tab1 WHERE col1 = 108;

The delete operation above requires an X (exclusive) lock, This lock cannot be granted because it is not compatible with S (shared) lock that Transaction T1 holds, So the request from Transaction T2 goes on the queue of lock requests for the row and Transaction T2 blocks.

Now Transaction T1 also attempts to delete the same row in the table tab1, Let’s see what happens :

Transaction T1

mysql> DELETE FROM tab1 WHERE col1 = 108;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

Deadlock happened because Transaction T1 needs an X (exclusive) lock to delete the row, the lock request from Transaction T1 cannot be granted because Transaction T2 already has a request for an X (exclusive) lock and is waiting for Transaction T1 to release its S lock. Nor can the S (shared) lock held by Transaction T1 can be upgraded to an X (exclusive) lock due the prior request by Transaction T2 for an X (exclusive) lock, So InnoDB generated error for one of the transaction and release its lock, The transaction returns the error below

ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

How can we reduce the possibility of deadlocks ?

• Use transactions than LOCK TABLES statements, Also keep transactions that INSERT or UPDATE data small enough that they do not stay open for long periods of time.
• When different transactions UPDATE multiple tables or large range of rows, use the same order of operations (like SELECT … FOR UPDATE) in each transaction.
• Create indexes on the columns used in SELECT …. FOR UPDATE and UPDATE …. WHERE  statements.
• Isolation level will not influence deadlock (we have explained isolation level in a separate blog – https://minervasql.com/index.php/2018/02/21/innodb-transaction-isolation/ ) because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations.
• When deadlock detection is enabled (the default) and a deadlock does occur, InnoDB detects the condition and rolls back one of the transactions (the victim).
• If deadlock detection is disabled using the innodb_deadlock_detect configuration option,  InnoDB relies on the innodb_lock_wait_timeout setting to roll back transactions in case of a deadlock.
• To know about most recent deadlock in an InnoDB user transaction, use SHOW ENGINE INNODB STATUS command.
• If you are suspecting serious deadlock issue persists in your application, enable innodb_print_all_deadlock to log all information about deadlock to mysqld error log.