10bet网址
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr)- 42.0Mb
PDF (A4)- 42.0Mb
Man Pages (TGZ)- 266.3Kb
Man Pages (Zip)- 376.1Kb
Info (Gzip)- 4.0Mb
Info (Zip)- 4.0Mb
Excerpts from this Manual

15.7.5.1 An InnoDB Deadlock Example

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 anSlock 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 FOR SHARE; +------+ | 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 anXlock. The lock cannot be granted because it is incompatible with theSlock 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 >删除从t i = 1;错误1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Deadlock occurs here because client A needs anXlock to delete the row. However, that lock request cannot be granted because client B already has a request for anXlock and is waiting for client A to release itsSlock. Nor can theSlock held by A be upgraded to anXlock because of the prior request by B for anXlock. As a result,InnoDBgenerates an error for one of the clients and releases its locks. The client returns this 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.