mysql 死鎖記錄

測試環境： mysql 5.7.18     RR隔離級別

創建表，插入部分測試數據

CREATE TABLE yhtest (
 a INT (11) NOT NULL AUTO_INCREMENT,
 b INT (11) DEFAULT NULL,
 c INT (11) DEFAULT NULL,
 PRIMARY KEY (a),
 unique key(b)
) ENGINE = INNODB ;

INSERT INTO yhtest VALUE (1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5),
(6, 6, 6),
(7, 7, 7),
(8, 8, 8),
(9, 9, 9),
(10, 10, 10),
(11, 11, 11) ;

操作：

事物1：begin;

事物2：begin;

事物1：delete from yhtest where a=2;    可以執行

事物2：delete from yhtest where a=3;    可以執行

事物1：delete from yhtest where a=4;     可以執行

事物2：delete from yhtest where a=5;    可以執行

事物1：insert into yhtest value(2,2,2);     鎖等待，待事物2回滾后，可以執行

事物2： insert into yhtest value(3,3,3);    報出死鎖，回滾，如下：

死鎖日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-10-27 19:38:00 0x7f4b67932700
*** (1) TRANSACTION:
TRANSACTION 3914, ACTIVE 117 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 3
MySQL thread id 4, OS thread handle 139961837504256, query id 53 localhost root update
insert into yhtest value(2,2,2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`yhtest` trx id 3914 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000003; asc     ;;
1: len 4; hex 80000003; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 3919, ACTIVE 93 sec inserting
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3
MySQL thread id 5, OS thread handle 139961836971776, query id 54 localhost root update
insert into yhtest value(3,3,3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`yhtest` trx id 3919 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000003; asc     ;;
1: len 4; hex 80000003; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 31 page no 4 n bits 80 index b of table `test`.`yhtest` trx id 3919 lock mode S waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 80000004; asc     ;;
1: len 4; hex 80000004; asc     ;;

*** WE ROLL BACK TRANSACTION (2)

通過innodb日志可以看出，

事物1執行  insert into yhtest value(2,2,2);  時，會等待b列索引b=3 上的S鎖被添加，b=3 這一行因為被事物2執行了 delete from yhtest where a=3;  排他鎖鎖住， S鎖等待正常。

事物2執行   insert into yhtest value(3,3,3); 時，可以看出其持有b=3 的X鎖， 等待 b=4 上的S鎖被添加，b=4 這一行 因為被事物1執行了delete from yhtest where a=4; 排他鎖鎖住，S鎖等待， 這時候，事物1和事物2互相等待對方持有的鎖資源，形成回環，死鎖出現

這里事物1 插入（2,2,2） 和事物2插入（3,3,3） 因為b列的唯一鍵存在，需要進行唯一鍵校驗，而由于在之前已經進行了該列刪除，需要通過鎖定下一列判斷b=2 的唯一性，b=3的唯一性，而這兩列的鎖已經被對方持有，故出現死鎖。

這種死鎖情況，在RC隔離級別下同樣會出現！