數(shù)據(jù)庫(kù)死鎖，是最難調(diào)試與追蹤的。

場(chǎng)景如下：

同一個(gè)表，事務(wù)內(nèi)先插入一條記錄，再更新這條記錄，并發(fā)時(shí)會(huì)死鎖。

并且能夠復(fù)現(xiàn)。

可以通過(guò)什么工具模擬并發(fā)事務(wù)，查看信息，解決問(wèn)題呢?這是今天要分享的內(nèi)容。

一、前置準(zhǔn)備

set session transaction isolation level repeatable read; 
set session autocommit=0; 
  
create table t ( 
id int(20) primary key AUTO_INCREMENT, 
cell varchar(20) unique 
)engine=innodb; 
  
start transaction; 
insert into t(cell) values(11111111111); 
insert into t(cell) values(22222222222); 
insert into t(cell) values(33333333333); 
commit; 

說(shuō)明：

• 案發(fā)時(shí)，事務(wù)隔離級(jí)別RR;
• 多終端實(shí)驗(yàn)，需要關(guān)閉事務(wù)自動(dòng)提交;
• 建表，設(shè)置PK與unique，初始化數(shù)據(jù);

二、并發(fā)事務(wù)模擬

Session A： 
start transaction; 
insert into t(cell)values(44444444444);  [1] 
        Session B： 
        start transaction; 
        insert into t(cell) values(55555555555); [2] 
update t set cell=123 where cell=44444444444; [3]    
        update t set cell=456 where cell=55555555555; [4] 

開(kāi)啟兩個(gè)終端模擬并發(fā)事務(wù)：

• 紅色SQL為事務(wù)A;
• 黑色SQL為事務(wù)B;
• [1][2][3][4]為執(zhí)行時(shí)序;

三、實(shí)驗(yàn)現(xiàn)象

insert into t(cell)values(44444444444);  [1] 

事務(wù)A插入數(shù)據(jù)，***執(zhí)行

結(jié)果：插入成功

insert into t(cell) values(55555555555); [2] 

事務(wù)B插入數(shù)據(jù)，第二執(zhí)行

結(jié)果：插入成果

update t set cell=123 where cell=44444444444; [3] 

事務(wù)A修改[1]中插入的數(shù)據(jù)，第三執(zhí)行

結(jié)果：阻塞，等待執(zhí)行結(jié)果

畫外音：修改一條自己插入的數(shù)據(jù)，在等待什么呢?

update t set cell=456 where cell=55555555555; [4] 

事務(wù)B修改[2]中插入的數(shù)據(jù)，***執(zhí)行

結(jié)果：

• 事務(wù)B死鎖，事務(wù)B被回滾;



• 事務(wù)A中，[3]語(yǔ)句阻塞結(jié)束，執(zhí)行成功;

畫外音：說(shuō)明事務(wù)A中阻塞的語(yǔ)句，確實(shí)在等事務(wù)B中的某個(gè)鎖。

四、結(jié)果分析

兩個(gè)事務(wù)，各自修改自己插入的數(shù)據(jù)，卻產(chǎn)生了死鎖，確實(shí)詭異。

上述實(shí)驗(yàn)現(xiàn)象的兩個(gè)核心問(wèn)題是：

• 語(yǔ)句[3]阻塞，在等待什么鎖?
• 語(yǔ)句[4]死鎖，此時(shí)事務(wù)A和事務(wù)B一定是彼此占住一把鎖，請(qǐng)求彼此的鎖，這些鎖又是什么呢?

工具一：

show engine innodb status; 

執(zhí)行之后，顯示的內(nèi)容如下(放大仔細(xì)看)：

信息很多，別急，樓主娓娓道來(lái)。

***部分，關(guān)鍵詞是：

• Transaction 1，事務(wù)3998;
• 在執(zhí)行

update t set cell=123 where cell=44444444444; 

• 正在等待鎖釋放(waiting for this lock to be granted)，記錄鎖(record locks)，主鍵索引上(index primary)，互斥鎖(lock_mode X)，物理記錄(physical record)，asc 55555555555;

畫外音：英文比較差沒(méi)事，抓關(guān)鍵詞。

畫外音，InnoDB存儲(chǔ)引擎，聚集索引與非聚集索引的實(shí)現(xiàn)方式，決定了鎖會(huì)加在聚集索引上，詳見(jiàn)文章：

《1分鐘了解MyISAM與InnoDB的索引差異》。

第二部分，關(guān)鍵詞是：

• Transaction 2，事務(wù)3999;
• 正在執(zhí)行

update t set cell=456 where cell=55555555555; 

• 持有鎖(holds the lock)，記錄鎖(record locks)，主鍵索引上(index primary)，互斥鎖(lock_mode X)，物理記錄(physical record)，asc 55555555555;
• 正在等待鎖釋放(waiting for this lock to be granted)，記錄鎖(record locks)，主鍵索引上(index primary)，互斥鎖(lock_mode X)，物理記錄(physical record)，asc 11111111111;
• 事務(wù)2回滾(we roll back transaction 2);

通過(guò)show engine innodb status; 能夠看到很多事務(wù)與鎖之間的信息，對(duì)分析問(wèn)題十分有幫助，這些信息，能夠解釋一些問(wèn)題，但仍有兩個(gè)疑惑：

(1)事務(wù)1為啥想拿55555555555的鎖?

畫外音：這正是，事務(wù)1被阻塞的原因。

(2)事務(wù)2為啥想拿11111111111的鎖?死鎖的發(fā)生，說(shuō)明事務(wù)1此時(shí)真占著11111111111的鎖，這又是為什么呢?

畫外音：***個(gè)事務(wù)占111搶555，第二個(gè)事務(wù)占555搶111，循環(huán)嵌套，才會(huì)死鎖。

工具二：

explain 

為了進(jìn)一步尋找原因，可以通過(guò)explain看下導(dǎo)致死鎖語(yǔ)句的執(zhí)行計(jì)劃。

explain update t set cell=456 where cell=55555555555; 

(1) select_type：SIMPLE

這是一個(gè)簡(jiǎn)單類型的SQL語(yǔ)句，不含子查詢或者UNION。

(2) type：index

訪問(wèn)類型，即找到所需數(shù)據(jù)使用的遍歷方式，潛在的方式有：

• ALL(Full Table Scan)：全表掃描;
• index：走索引的全表掃描;
• range：***where子句的范圍索引掃描;
• ref/eq_ref：非唯一索引/唯一索引單值掃描;
• const/system：常量掃描;
• NULL：不用訪問(wèn)表;

上述掃描方式，ALL最慢，逐步變快，NULL最快。

懷疑點(diǎn)1：明明cell字段有uniq索引，為何要進(jìn)行走PK索引的全表掃描呢?

(3) possible_keys：NULL

可能在哪個(gè)索引找到記錄。

(4) key：PRIMARY

實(shí)際使用索引。

畫外音：使用PK進(jìn)行的全表掃描。

(5) ref：NULL

哪些列，或者常量用于查找索引上的值。

懷疑點(diǎn)2：where條件中的查詢條件55555555555，本來(lái)應(yīng)該作為在索引上被檢索的值呀?

(6) rows：5

找到所需記錄，預(yù)估需要讀取的行數(shù)。

懷疑點(diǎn)3：明明修改的是5，為何初始化的1，2，3，以及***個(gè)事務(wù)插入的4，以及第二個(gè)事務(wù)插入的5，都要被讀取呢?不應(yīng)該全表掃描呀。

通過(guò)explain，基本已經(jīng)可以判斷：

update t set cell=456 where cell=55555555555; 

并沒(méi)有和我們預(yù)想一樣，走cell索引進(jìn)行查詢，而是走了PK索引進(jìn)行了全表掃描。

再仔細(xì)一看：

create table t ( 
id int(20) primary key AUTO_INCREMENT, 
cell varchar(20) unique 
)engine=innodb; 

建表的時(shí)候cell定義的是字符串類型。

而更新的時(shí)候，

update t set cell=456 where cell=55555555555; 

使用的是整數(shù)類型。

類型轉(zhuǎn)換，會(huì)導(dǎo)致全表掃描，出現(xiàn)鎖升級(jí)，鎖住全部記錄。

加上引號(hào)，再次通過(guò)explain驗(yàn)證一下：

explain update t set cell= '456 ' where cell= '55555555555 '; 

果然印證了猜想：

• type：range，變?yōu)榱俗咚饕淖址葘?duì)，范圍掃描;
• possible_keys：cell，通過(guò)cell索引找到了記錄;
• key：cell，實(shí)際使用cell索引;
• ref：const，使用了常量' 555'進(jìn)行比對(duì);
• rows：1，預(yù)估讀取行數(shù)是1;

這下全部可以解釋了。

總結(jié)

就本例而言：需要注意字符串與整數(shù)之間的強(qiáng)制類型轉(zhuǎn)換，有時(shí)候少一個(gè)引號(hào)，就會(huì)使得行鎖升級(jí)為表鎖。

死鎖是MySQL中非常難調(diào)試的問(wèn)題，常見(jiàn)的思路與方法有：

• 通過(guò)多終端模擬并發(fā)事務(wù)，復(fù)現(xiàn)死鎖;
• 通過(guò)show engine innodb status; 可以查看事務(wù)與鎖的信息;
• 通過(guò)explain可以查看執(zhí)行計(jì)劃;

思路比結(jié)論更重要，希望大家有收獲。

【本文為51CTO專欄作者“58沈劍”原創(chuàng)稿件，轉(zhuǎn)載請(qǐng)聯(lián)系原作者】

戳這里，看該作者更多好文