下面的例子说明了当锁请求导致死锁时，错误是如何发生的。该示例涉及两个客户机，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
------------
网友评论