MySQL:Innodb 一個死鎖案例

一、準備資料和問題

RR隔離級別

CREATE TABLE `ty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
insert into ty(a,b) values(2,3),(5,4),(6,7);

問：

image.png

這種情況會產生死鎖，如果將
insert into ty(a,b) values(2,10);
改為：
insert into ty(a,b) values(5,10);

則不會產生死鎖為什麼？

二、初始化資料畫圖

本死鎖的堵塞主要集中在二級索引中，我們將二級索KEY  idxa  ( a )和主鍵的資料按照Innodb引擎儲存的方式大概排列一下則如圖：

image.png

三、T2 步驟1

T2 步驟1：delete from ty where a=5;

-----TRX NO:334719 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334719 lock_mode X(LOCK_X) locks gap and rec(LOCK_ORDINARY[next_key_lock])
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;; 1: len 4; hex 80000009; asc     ;;
-----TRX NO:334719 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 653 page no 3 n bits 72 index PRIMARY of table `test`.`ty` trx id 334719 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 4; hex 80000009; asc     ;; 1: len 6; hex 000000051b7f; asc       ;; 2: len 7; hex 760000082b13fd; asc v   +  ;; 3: len 4; hex 80000005; asc     ;; 4: len 4; hex 80000004; asc     ;;
-----TRX NO:334719 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334719 lock_mode X(LOCK_X) locks gap before rec(LOCK_GAP)
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000006; asc     ;; 1: len 4; hex 8000000a; asc     ;;

根據這個記錄我們可以畫圖如下，紅色部分為鎖定的部分箭頭為gap lock：

image.png

四、T1 步驟2

T2 步驟1：delete from ty where a=5; 堵塞

-----TRX NO:334724 LOCK STRUCT(1)(Add by gaopeng)
RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334724 lock_mode X(LOCK_X) locks gap and rec(LOCK_ORDINARY[next_key_lock]) waiting(LOCK_WAIT)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;; 1: len 4; hex 80000009; asc     ;;

根據這個記錄我們可以畫圖如下，黃色部分為事務T1準備上鎖但是被堵塞的部分，包含黃色部分和紅色部分的記錄說明它既被T2鎖定了並且T1拿不到這條記錄的鎖，它實際上就是一個next key lock的堵塞：

image.png

五、T2步驟3

這一步如果是：

insert into ty(a,b) values(2,10);
則發生死鎖，實際上這一條記錄記錄在二級索引的值為(2,11)，11是主鍵的值，則畫圖如下：

image.png

這種情況下則T2也被堵塞，因為這個區域T1也處於堵塞下，則發生死鎖。死鎖記錄如下：

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 653 page no 4 n bits 72 index idxa of table `test`.`ty` trx id 334712 lock_mode X(LOCK_X) locks gap before rec(LOCK_GAP) insert intention(LOCK_INSERT_INTENTION) waiting(LOCK_WAIT)
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 80000005; asc     ;; 1: len 4; hex 80000009; asc     ;;

及插入印象鎖堵塞

這一步如果是：

insert into ty(a,b) values(5,10);
不會發生死鎖，，實際上這一條記錄記錄在二級索引的值為(5,11)，11是主鍵的值，則畫圖如下：

image.png

如果是這種情況，不會發生死鎖，我們可以看到對於二級索引而言這個區域沒有其他事物堵塞，只是T2最開始獲取過，本事務再次獲取不會有問題。

六、總結

本案例實際上就是看最後觸發死鎖的插入操作插入的記錄到底落在二級索引的哪個區域。

作者微信：