手把手教你分析Mysql死鎖問題 [建議收藏]

前言

發(fā)生死鎖了，如何排查和解決呢？本文將跟你一起探討這個(gè)問題

• 準(zhǔn)備好數(shù)據(jù)環(huán)境

• 模擬死鎖案發(fā)

• 分析死鎖日志

• 分析死鎖結(jié)果

環(huán)境準(zhǔn)備

數(shù)據(jù)庫隔離級(jí)別：

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

自動(dòng)提交關(guān)閉：

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

表結(jié)構(gòu):

//id是自增主鍵，name是非唯一索引，balance普通字段
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

表中的數(shù)據(jù)：

模擬并發(fā)

開啟兩個(gè)終端模擬事務(wù)并發(fā)情況，執(zhí)行順序以及實(shí)驗(yàn)現(xiàn)象如下：

1）事務(wù)A執(zhí)行更新操作，更新成功

mysql> update  account  set balance =1000 where name ='Wei';
Query OK, 1 row affected (0.01 sec)

2）事務(wù)B執(zhí)行更新操作，更新成功

mysql> update  account  set balance =1000 where name ='Eason';
Query OK, 1 row affected (0.01 sec)

3）事務(wù)A執(zhí)行插入操作，陷入阻塞~

mysql>?insert?into?account?values(null,'Jay',100);

這時(shí)候可以用select * from information_schema.innodb_locks;查看鎖情況：

4）事務(wù)B執(zhí)行插入操作，插入成功，同時(shí)事務(wù)A的插入由阻塞變?yōu)樗梨ierror。

mysql> insert into account values(null,'Yan',100);
Query OK, 1 row affected (0.01 sec)

鎖介紹

在分析死鎖日志前，先做一下鎖介紹，哈哈~

主要介紹一下兼容性以及鎖模式類型的鎖：

共享鎖與排他鎖

InnoDB 實(shí)現(xiàn)了標(biāo)準(zhǔn)的行級(jí)鎖，包括兩種：共享鎖（簡(jiǎn)稱 s 鎖）、排它鎖（簡(jiǎn)稱 x 鎖）。

• 共享鎖（S鎖）：允許持鎖事務(wù)讀取一行。

• 排他鎖（X鎖）：允許持鎖事務(wù)更新或者刪除一行。

如果事務(wù) T1 持有行 r 的 s 鎖，那么另一個(gè)事務(wù) T2 請(qǐng)求 r 的鎖時(shí)，會(huì)做如下處理：

• T2 請(qǐng)求 s 鎖立即被允許，結(jié)果 T1 T2 都持有 r 行的 s 鎖

• T2 請(qǐng)求 x 鎖不能被立即允許

如果 T1 持有 r 的 x 鎖，那么 T2 請(qǐng)求 r 的 x、s 鎖都不能被立即允許，T2 必須等待T1釋放 x 鎖才可以，因?yàn)閄鎖與任何的鎖都不兼容。

意向鎖

• 意向共享鎖( IS 鎖)：事務(wù)想要獲得一張表中某幾行的共享鎖

• 意向排他鎖( IX 鎖)：事務(wù)想要獲得一張表中某幾行的排他鎖

比如：事務(wù)1在表1上加了S鎖后，事務(wù)2想要更改某行記錄，需要添加IX鎖，由于不兼容，所以需要等待S鎖釋放；如果事務(wù)1在表1上加了IS鎖，事務(wù)2添加的IX鎖與IS鎖兼容，就可以操作，這就實(shí)現(xiàn)了更細(xì)粒度的加鎖。

InnoDB存儲(chǔ)引擎中鎖的兼容性如下表：

記錄鎖（Record Locks）

• 記錄鎖是最簡(jiǎn)單的行鎖，僅僅鎖住一行。如：SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE

• 記錄鎖永遠(yuǎn)都是加在索引上的，即使一個(gè)表沒有索引，InnoDB也會(huì)隱式的創(chuàng)建一個(gè)索引，并使用這個(gè)索引實(shí)施記錄鎖。

• 會(huì)阻塞其他事務(wù)對(duì)其插入、更新、刪除

記錄鎖的事務(wù)數(shù)據(jù)（關(guān)鍵詞：lock_mode X locks rec but not gap），記錄如下：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` 
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

間隙鎖（Gap Locks）

• 間隙鎖是一種加在兩個(gè)索引之間的鎖，或者加在第一個(gè)索引之前，或最后一個(gè)索引之后的間隙。

• 使用間隙鎖鎖住的是一個(gè)區(qū)間，而不僅僅是這個(gè)區(qū)間中的每一條數(shù)據(jù)。

• 間隙鎖只阻止其他事務(wù)插入到間隙中，他們不阻止其他事務(wù)在同一個(gè)間隙上獲得間隙鎖，所以 gap x lock 和 gap s lock 有相同的作用。

間隙鎖的事務(wù)數(shù)據(jù)（關(guān)鍵詞：gap before rec），記錄如下：

RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

Next-Key Locks

• Next-key鎖是記錄鎖和間隙鎖的組合，它指的是加在某條記錄以及這條記錄前面間隙上的鎖。

插入意向鎖（Insert Intention）

• 插入意向鎖是在插入一行記錄操作之前設(shè)置的一種間隙鎖，這個(gè)鎖釋放了一種插入方式的信號(hào)，亦即多個(gè)事務(wù)在相同的索引間隙插入時(shí)如果不是插入間隙中相同的位置就不需要互相等待。

• 假設(shè)有索引值4、7，幾個(gè)不同的事務(wù)準(zhǔn)備插入5、6，每個(gè)鎖都在獲得插入行的獨(dú)占鎖之前用插入意向鎖各自鎖住了4、7之間的間隙，但是不阻塞對(duì)方因?yàn)椴迦胄胁粵_突。

事務(wù)數(shù)據(jù)類似于下面：

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

鎖模式兼容矩陣（橫向是已持有鎖，縱向是正在請(qǐng)求的鎖）：

如何讀懂死鎖日志？

show engine innodb status

可以用show engine innodb status，查看最近一次死鎖日志哈~，執(zhí)行后，死鎖日志如下：

2020-04-11 00:35:55 0x243c
*** (1) TRANSACTION:
TRANSACTION 38048, ACTIVE 92 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 53, OS thread handle 2300, query id 2362 localhost ::1 root update
insert into account values(null,'Jay',100)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38048 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 38049, ACTIVE 72 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 52, OS thread handle 9276, query id 2363 localhost ::1 root update
insert into account  values(null,'Yan',100)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X locks gap before rec
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 576569; asc Wei;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 177 page no 4 n bits 80 index idx_name of table `test2`.`account` 
trx id 38049 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (1)

我們?nèi)绾畏治鲆陨纤梨i日志呢？

第一部分

1）找到關(guān)鍵詞TRANSACTION，事務(wù)38048

2）查看正在執(zhí)行的SQL

insert?into?account?values(null,'Jay',100)

3）正在等待鎖釋放(WAITING FOR THIS LOCK TO BE GRANTED)，插入意向排他鎖（lock_mode X locks gap before rec insert intention waiting），普通索引（idx_name），物理記錄(PHYSICAL RECORD)，間隙區(qū)間（未知，Wei）;

第二部分

1）找到關(guān)鍵詞TRANSACTION，事務(wù)38049

2）查看正在執(zhí)行的SQL

insert?into?account?values(null,'Yan',100)

3）持有鎖(HOLDS THE LOCK)，間隙鎖(lock_mode X locks gap before rec)，普通索引(index idx_name)，物理記錄(physical record)，區(qū)間（未知，Wei）;

4）正在等待鎖釋放(waiting for this lock to be granted)，插入意向鎖(lock_mode X insert intention waiting)，普通索引上(index idx_name)，物理記錄(physical record)，間隙區(qū)間（未知，+∞）;

5）事務(wù)1回滾(we roll back transaction 1)；

查看日志結(jié)果

查看日志可得：

• 事務(wù)A正在等待的插入意向排他鎖（事務(wù)A即日志的事務(wù)1，根據(jù)insert語句來對(duì)號(hào)入座的哈），正在事務(wù)B的懷里~

• 事務(wù)B持有間隙鎖，正在等待插入意向排它鎖

這里面，有些朋友可能有疑惑，

• 事務(wù)A持有什么鎖呢？日志根本看不出來。它又想拿什么樣的插入意向排他鎖呢？

• 事務(wù)B拿了具體什么的間隙鎖呢？它為什么也要拿插入意向鎖？

• 死鎖的死循環(huán)是怎么形成的？目前日志看不出死循環(huán)構(gòu)成呢？

我們接下來一小節(jié)詳細(xì)分析一波，一個(gè)一個(gè)問題來~

死鎖分析

死鎖死循環(huán)四要素

• 互斥條件：指進(jìn)程對(duì)所分配到的資源進(jìn)行排它性使用，即在一段時(shí)間內(nèi)某資源只由一個(gè)進(jìn)程占用。如果此時(shí)還有其它進(jìn)程請(qǐng)求資源，則請(qǐng)求者只能等待，直至占有資源的進(jìn)程用畢釋放。

• 請(qǐng)求和保持條件：指進(jìn)程已經(jīng)保持至少一個(gè)資源，但又提出了新的資源請(qǐng)求，而該資源已被其它進(jìn)程占有，此時(shí)請(qǐng)求進(jìn)程阻塞，但又對(duì)自己已獲得的其它資源保持不放。

• 不剝奪條件：指進(jìn)程已獲得的資源，在未使用完之前，不能被剝奪，只能在使用完時(shí)由自己釋放。

• 環(huán)路等待條件：指在發(fā)生死鎖時(shí)，必然存在一個(gè)進(jìn)程——資源的環(huán)形鏈，即進(jìn)程集合{P0，P1，P2，···，Pn}中的P0正在等待一個(gè)P1占用的資源；P1正在等待P2占用的資源，……，Pn正在等待已被P0占用的資源。

事務(wù)A持有什么鎖呢？它又想拿什么樣的插入意向排他鎖呢？

為了方便記錄，例子用W表示W(wǎng)ei，J表示Jay，E表示Eason哈~

我們先來分析事務(wù)A中update語句的加鎖情況~

update?account?set?balance =1000?where?name?='Wei';

間隙鎖：

• Update語句會(huì)在非唯一索引的name加上左區(qū)間的間隙鎖，右區(qū)間的間隙鎖(因?yàn)槟壳氨碇兄挥衝ame='Wei'的一條記錄，所以沒有中間的間隙鎖~)，即（E,W) 和（W，+∞）

• 為什么存在間隙鎖？因?yàn)檫@是RR的數(shù)據(jù)庫隔離級(jí)別，用來解決幻讀問題用的~

記錄鎖

• 因?yàn)閚ame是索引，所以該update語句肯定會(huì)加上W的記錄鎖

Next-Key鎖

• Next-Key鎖=記錄鎖+間隙鎖，所以該update語句就有了（E，W]的 Next-Key鎖

綜上所述，事務(wù)A執(zhí)行完update更新語句，會(huì)持有鎖：

• Next-key Lock：（E，W]

• Gap Lock ：（W，+∞）

我們?cè)賮矸治鲆徊ㄊ聞?wù)A中insert語句的加鎖情況

insert?into?account?values(null,'Jay',100);

間隙鎖：

• 因?yàn)镴ay(J在E和W之間)，所以需要請(qǐng)求加(E,W)的間隙鎖

插入意向鎖（Insert Intention）

• 插入意向鎖是在插入一行記錄操作之前設(shè)置的一種間隙鎖，這個(gè)鎖釋放了一種插入方式的信號(hào)，即事務(wù)A需要插入意向鎖(E,W)

因此，事務(wù)A的update語句和insert語句執(zhí)行完，它是持有了?（E，W]的 Next-Key鎖，（W，+∞）的Gap鎖，想拿到?(E,W)的插入意向排它鎖，等待的鎖跟死鎖日志是對(duì)上的，哈哈~

事務(wù)B擁有了什么間隙鎖？它為什么也要拿插入意向鎖？

同理，我們?cè)賮矸治鲆徊ㄊ聞?wù)B，update語句的加鎖分析：

update?account?set?balance =1000?where?name?='Eason';

間隙鎖：

• Update語句會(huì)在非唯一索引的name加上左區(qū)間的間隙鎖，右區(qū)間的間隙鎖(因?yàn)槟壳氨碇兄挥衝ame='Eason'的一條記錄，所以沒有中間的間隙鎖~)，即（-∞，E）和（E，W）

記錄鎖

• 因?yàn)閚ame是索引，所以該update語句肯定會(huì)加上E的記錄鎖

Next-Key鎖

• Next-Key鎖=記錄鎖+間隙鎖，所以該Update語句就有了（-∞，E]的 Next-Key鎖

綜上所述，事務(wù)B執(zhí)行完update更新語句，會(huì)持有鎖：

• Next-key Lock：（-∞，E]

• Gap Lock ：（E，W）

我們?cè)賮矸治鲆徊˙中insert語句的加鎖情況

insert?into?account?values(null,'Yan',100);

間隙鎖：

• 因?yàn)閅an(Y在W之后)，所以需要請(qǐng)求加(W,+∞)的間隙鎖

插入意向鎖（Insert Intention）

• 插入意向鎖是在插入一行記錄操作之前設(shè)置的一種間隙鎖，這個(gè)鎖釋放了一種插入方式的信號(hào)，即事務(wù)A需要插入意向鎖(W,+∞)

所以，事務(wù)B的update語句和insert語句執(zhí)行完，它是持有了?（-∞，E]的 Next-Key鎖，（E，W）的Gap鎖，想拿到?(W,+∞)的間隙鎖，即插入意向排它鎖，加鎖情況跟死鎖日志也是對(duì)上的~

死鎖真相還原

接下來呢，讓我們一起還原死鎖真相吧~哈哈~

• 事務(wù)A執(zhí)行完Update Wei的語句，持有（E，W]的Next-key Lock，（W，+∞）的Gap Lock ，插入成功~

• 事務(wù)B執(zhí)行完Update Eason語句，持有（-∞，E]的 Next-Key Lock，（E，W）的Gap Lock，插入成功~

• 事務(wù)A執(zhí)行Insert Jay的語句時(shí)，因?yàn)樾枰‥，W）的插入意向鎖，但是（E，W）在事務(wù)B懷里，所以它陷入心塞~

• 事務(wù)B執(zhí)行Insert Yan的語句時(shí)，因?yàn)樾枰?W,+∞) 的插入意向鎖，但是(W,+∞) 在事務(wù)A懷里，所以它也陷入心塞。

• 事務(wù)A持有（W，+∞）的Gap Lock，在等待（E，W）的插入意向鎖，事務(wù)B持有（E，W）的Gap鎖，在等待(W,+∞) 的插入意向鎖，所以形成了死鎖的閉環(huán)~（Gap鎖與插入意向鎖會(huì)沖突的，可以看回鎖介紹的鎖模式兼容矩陣哈~）

• 事務(wù)A,B形成了死鎖閉環(huán)后，因?yàn)镮nnodb的底層機(jī)制，它會(huì)讓其中一個(gè)事務(wù)讓出資源，另外的事務(wù)執(zhí)行成功，這就是為什么你最后看到事務(wù)B插入成功了，但是事務(wù)A的插入顯示了Deadlock found ~

總結(jié)

最后，遇到死鎖問題，我們應(yīng)該怎么分析呢？

• 模擬死鎖場(chǎng)景

• show engine innodb status;查看死鎖日志

• 找出死鎖SQL

• SQL加鎖分析，這個(gè)可以去官網(wǎng)看哈

• 分析死鎖日志（持有什么鎖，等待什么鎖）

• 熟悉鎖模式兼容矩陣，InnoDB存儲(chǔ)引擎中鎖的兼容性矩陣。