mysql 死結記錄

標籤：mysql 死結 唯一鍵

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

650) this.width=650;" style="width:632px;height:261px;" title="QQ圖片20171027105421.png" src="https://s4.51cto.com/oss/201710/27/27fa6f262f376b65de32918df8266dfb.png" width="741" height="323" alt="27fa6f262f376b65de32918df8266dfb.png" />

建立表，插入部分測試資料

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);    報出死結，復原，如下：

650) this.width=650;" style="width:642px;height:62px;" title="QQ圖片20171027114029.png" src="https://s4.51cto.com/oss/201710/27/3d79886f16bc751ed12b0ca345d8f85b.png" width="775" height="11" alt="3d79886f16bc751ed12b0ca345d8f85b.png" />

死結日誌：

------------------------
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隔離等級下同樣會出現！ 

mysql 死結記錄