Some combinations of locks create a situation where some transactions block each other. Since all the involved transactions are waiting for a lock to be released, they will never unblock naturally. Such situations are called deadlocks. InnoDB (like other storage engines which need to deal with such situations) has an internal mechanism to detect deadlocks. It unblocks the situation by terminating the transaction that inserted, deleted, or updated the least number of rows. Such a transaction is called the victim.

Sometimes, a storage engine that uses table-level locks may be involved in a deadlock, which also involves InnoDB tables. In that case, InnoDB can only detect the deadlock if the innodb_table_locks server variable is ON.

In an environment where concurrency exists, deadlocks are normal. When a deadlock occurs, one or more transactions terminate with a 1213 error; this is not a concern, and applications should simply handle this error by restarting the transaction. Of course, this does not mean that deadlocks are expected to happen often. If many deadlocks occur, something should be done to fix the problem. Some of the solutions to fix the problem are:

To diagnose the latest deadlocks, the SHOW ENGINE INNODB STATUS statement can be used, as shown in the following example. It is also possible to set the innodb_print_all_deadlocks server variable to ON. This prints all InnoDB deadlocks into the error log.

Now, let's see a deadlock example. Creating a deadlock is easy. We will create two tables (t1 and t2) that contain one record and one UNIQUE index. We will use two different mysql client instances. For the first one, we will use SELECT … FOR UPDATE to acquire an exclusive record lock on t1. For the second connection, we will acquire the same kind of lock on t2. Then, with the first connection we will try to access the record locked by the second connection; we will do the same with the second connection. At this point, the first connection will wait for a lock held by the second connection, but the lock cannot be free because the second connection will be waiting for a lock held by the first connection. This circular set of locks is clearly a deadlock. Here are the commands that perform this action:

Connection 1:

MariaDB [test]> CREATE TABLE t1 (a INT PRIMARY KEY, b INT UNIQUE) ENGINE = InnoDB;
Query OK, 0 rows affected (0.48 sec) 
MariaDB [test]> CREATE TABLE t2 (a INT PRIMARY KEY, b INT UNIQUE) ENGINE = InnoDB;
Query OK, 0 rows affected (0.47 sec) 
MariaDB [test]> INSERT INTO t1 VALUES (1, 1);
Query OK, 1 row affected (0.06 sec) 
MariaDB [test]> INSERT INTO t2 VALUES (1, 1);
Query OK, 1 row affected (0.06 sec) 
MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> SELECT * FROM t1 WHERE b = 1 FOR UPDATE;
+---+------+ 
| a | b    | 
+---+------+ 
| 1 |    1 | 
+---+------+ 
1 row in set (0.00 sec) 

Connection 2:

MariaDB [test]> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) 
MariaDB [test]> SELECT * FROM t2 WHERE b = 1 FOR UPDATE;
+---+------+ 
| a | b    | 
+---+------+ 
| 1 |    1 | 
+---+------+ 
1 row in set (0.01 sec) 

Connection 1:

MariaDB [test]> SELECT * FROM t2 WHERE b = 1 FOR UPDATE;

Still no output. The connection is now on hold because the record in t2 is locked.

Connection 2:

MariaDB [test]> SELECT * FROM t1 WHERE b = 1 FOR UPDATE;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 

InnoDB detected the error and connection 2 was terminated. Connection 1 will now receive the output of its query.

We know exactly what happened, but suppose we want to diagnose the deadlock. The SHOW ENGINE INNODB status contains information about the last detected deadlocks. Let's execute it and look at the LATEST DETECTED DEADLOCK section:

------------------------ 
LATEST DETECTED DEADLOCK 
------------------------ 
2014-02-21 16:05:26 7f5d48554700 
*** (1) TRANSACTION: 
TRANSACTION 14409, ACTIVE 60 sec starting index read 
mysql tables in use 1, locked 1 
LOCK WAIT 5 lock struct(s), heap size 1248, 3 row lock(s) 
MySQL thread id 4, OS thread handle 0x7f5d4859d700, query id 41 localhost root statistics 
SELECT * FROM t2 WHERE b = 1 FOR UPDATE 
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 44 page no 4 n bits 72 index `b` of table `test`.`t2` trx id 14409 lock_mode X locks rec but not gap waiting 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 80000001; asc     ;; 
 1: len 4; hex 80000001; asc     ;; 

*** (2) TRANSACTION: 
TRANSACTION 14410, ACTIVE 21 sec starting index read 
mysql tables in use 1, locked 1 
5 lock struct(s), heap size 1248, 3 row lock(s) 
MySQL thread id 5, OS thread handle 0x7f5d48554700, query id 42 localhost root statistics 
SELECT * FROM t1 WHERE b = 1 FOR UPDATE 
*** (2) HOLDS THE LOCK(S): 
RECORD LOCKS space id 44 page no 4 n bits 72 index `b` of table `test`.`t2` trx id 14410 lock_mode X locks rec but not gap 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 80000001; asc     ;; 
 1: len 4; hex 80000001; asc     ;; 

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 43 page no 4 n bits 72 index `b` of table `test`.`t1` trx id 14410 lock_mode X locks rec but not gap waiting 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 80000001; asc     ;; 
 1: len 4; hex 80000001; asc     ;; 

*** WE ROLL BACK TRANSACTION (2) 

The most important information we need is quite clear. It says which lock types were used by which transactions. For each transaction, it also says which statements caused the locks. It also says which transactions are waiting for which locks ((2) WAITING FOR THIS LOCK TO BE GRANTED). The last line provides us with information about the connection that was killed to unblock the waits.