MySQL 5.7 Reference Manual Including MySQL NDB Cluster 7.5 and NDB Cluster 7.6
The following example illustrates how an error can occur when a lock request causes a deadlock. The example involves two clients, A and B.
First, client A creates a table containing one row, and then
begins a transaction. Within the transaction, A obtains an
S
lock on the row by selecting it in share
mode:
mysql>CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec) mysql>INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec) mysql>START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) mysql>SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+ | i | +------+ | 1 | +------+
Next, client B begins a transaction and attempts to delete the row from the table:
mysql>START TRANSACTION;
Query OK, 0 rows affected (0.00 sec) mysql>DELETE FROM t WHERE i = 1;
The delete operation requires an X
lock. The
lock cannot be granted because it is incompatible with the
S
lock that client A holds, so the request
goes on the queue of lock requests for the row and client B
blocks.
Finally, client A also attempts to delete the row from the table:
mysql> DELETE FROM t WHERE i = 1;
Deadlock occurs here because client A needs an
X
lock to delete the row. However, that lock
request cannot be granted because client B already has a request
for an X
lock and is waiting for client A to
release its S
lock. Nor can the
S
lock held by A be upgraded to an
X
lock because of the prior request by B for
an X
lock. As a result,
InnoDB
generates an error for one of the
clients and releases its locks. The client returns this error:
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
At that point, the lock request for the other client can be granted and it deletes the row from the table.