An InnoDB Deadlock Example

An InnoDB Deadlock Example

下面的例子说明了当锁请求导致死锁时，错误是如何发生的。该示例涉及两个客户机，A和B。

首先，客户机A创建一个包含一行的表，然后开始一个事务。在事务中，A通过在共享模式下选择该行来获得该行的S锁

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 |
+------+

接下来，客户机B开始一个事务，并试图从表中删除该行

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

mysql> DELETE FROM t WHERE i = 1;

delete操作需要一个X锁。这个锁不能被授予，因为它与客户端A持有的S锁不兼容，所以请求进入到行和客户端B块的锁请求队列中。

最后，客户机A还试图从表中删除该行

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

这里发生死锁是因为客户端A需要一个X锁来删除WHERE i = 1的这行。但是，这个锁请求不能被授予，因为客户端B已经有一个X锁的请求，且正在等待客户端A释放它的S锁。A持有的S锁也不能升级为X锁，因为B之前请求了X锁。因此，InnoDB会为其中一个客户端生成一个错误，并释放其锁。客户端返回这个错误

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

此时，可以授予对另一个客户机的锁请求，并从表中删除行(因为客户端B抛出异常后，客户端A操作成功)。

mysql> DELETE FROM t WHERE i = 1;
Query OK, 1 row affected (0.00 sec)

排它锁(X锁)和共享锁(S锁)：

所谓X锁,是事务T对数据A加上X锁时,只允许事务T读取和修改数据A

所谓S锁,是事务T对数据A加上S锁时,其他事务只能再对数据A加S锁,而不能加X锁,直到T释放A上的S锁

若事务T对数据对象A加了S锁,则T就可以对A进行读取,但不能进行更新(S锁因此又称为读锁),在T释放A上的S锁以前,其他事务可以再对A加S锁,但不能加X锁,从而可以读取A,但不能更新A.

我们可以通过 show engine innodb status 命令来获取最近一次的死锁日志

我们来查看一下错误日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-04-17 09:22:26 0x700006baf000
*** (1) TRANSACTION:
TRANSACTION 121889, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 9, OS thread handle 123145424609280, query id 50 localhost root updating
DELETE FROM t WHERE i = 1

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 896 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 121889 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000600; asc       ;;
 1: len 6; hex 00000001dc1f; asc       ;;
 2: len 7; hex 82000001130110; asc        ;;
 3: len 4; hex 80000001; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 896 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 121889 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000600; asc       ;;
 1: len 6; hex 00000001dc1f; asc       ;;
 2: len 7; hex 82000001130110; asc        ;;
 3: len 4; hex 80000001; asc     ;;


*** (2) TRANSACTION:
TRANSACTION 121890, ACTIVE 25 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 8, OS thread handle 123145424306176, query id 51 localhost root updating
DELETE FROM t WHERE i = 1

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 896 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 121890 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000600; asc       ;;
 1: len 6; hex 00000001dc1f; asc       ;;
 2: len 7; hex 82000001130110; asc        ;;
 3: len 4; hex 80000001; asc     ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 896 page no 4 n bits 72 index GEN_CLUST_INDEX of table `test`.`t` trx id 121890 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000600; asc       ;;
 1: len 6; hex 00000001dc1f; asc       ;;
 2: len 7; hex 82000001130110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------