下面的例子說(shuō)明了當(dāng)鎖請(qǐng)求導(dǎo)致死鎖時(shí)，錯(cuò)誤是如何發(fā)生的。該示例涉及兩個(gè)客戶機(jī)，A和B。
首先，客戶機(jī)A創(chuàng)建一個(gè)包含一行的表，然后開(kāi)始一個(gè)事務(wù)。在事務(wù)中，A通過(guò)在共享模式下選擇該行來(lái)獲得該行的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 |
+------+
接下來(lái)，客戶機(jī)B開(kāi)始一個(gè)事務(wù)，并試圖從表中刪除該行
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;
delete操作需要一個(gè)X鎖。這個(gè)鎖不能被授予，因?yàn)樗c客戶端A持有的S鎖不兼容，所以請(qǐng)求進(jìn)入到行和客戶端B塊的鎖請(qǐng)求隊(duì)列中。
最后，客戶機(jī)A還試圖從表中刪除該行
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
這里發(fā)生死鎖是因?yàn)榭蛻舳薃需要一個(gè)X鎖來(lái)刪除WHERE i = 1的這行。但是，這個(gè)鎖請(qǐng)求不能被授予，因?yàn)榭蛻舳薆已經(jīng)有一個(gè)X鎖的請(qǐng)求，且正在等待客戶端A釋放它的S鎖。A持有的S鎖也不能升級(jí)為X鎖，因?yàn)锽之前請(qǐng)求了X鎖。因此，InnoDB會(huì)為其中一個(gè)客戶端生成一個(gè)錯(cuò)誤，并釋放其鎖?？蛻舳朔祷剡@個(gè)錯(cuò)誤
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
此時(shí)，可以授予對(duì)另一個(gè)客戶機(jī)的鎖請(qǐng)求，并從表中刪除行(因?yàn)榭蛻舳薆拋出異常后，客戶端A操作成功)。
mysql> DELETE FROM t WHERE i = 1;
Query OK, 1 row affected (0.00 sec)
排它鎖(X鎖)和共享鎖(S鎖)：
所謂X鎖,是事務(wù)T對(duì)數(shù)據(jù)A加上X鎖時(shí),只允許事務(wù)T讀取和修改數(shù)據(jù)A
所謂S鎖,是事務(wù)T對(duì)數(shù)據(jù)A加上S鎖時(shí),其他事務(wù)只能再對(duì)數(shù)據(jù)A加S鎖,而不能加X(jué)鎖,直到T釋放A上的S鎖
若事務(wù)T對(duì)數(shù)據(jù)對(duì)象A加了S鎖,則T就可以對(duì)A進(jìn)行讀取,但不能進(jìn)行更新(S鎖因此又稱為讀鎖),在T釋放A上的S鎖以前,其他事務(wù)可以再對(duì)A加S鎖,但不能加X(jué)鎖,從而可以讀取A,但不能更新A.
我們可以通過(guò)
show engine innodb status命令來(lái)獲取最近一次的死鎖日志
我們來(lái)查看一下錯(cuò)誤日志
------------------------
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
------------
