【MySQL死鎖終結者】5分鐘徹底解決數據庫"卡死"難題！

作者：黃敬乾 2025-04-28 04:22:00

對于讀寫事務來說，只有在它第一次對某個表執行增刪改操作時，才會為這個事務分配一個事務id，否則是不分配事務 id 的。有時，雖然我們開啟了一個讀寫事務，但是這個事務中全是查詢語句，并沒有執行增刪改操作的語句，這也就意味著這個事務并不會被分配一個事務id。

1、找到并確定你的死鎖日志

方式1：基于MySQL錯誤日志

方式2：基于SHOW ENGINE INNODB STATUS命令查看最近發生的死鎖日志

方式3：咨詢你的DBA吧！

2、分析你的死鎖日志

確定死鎖發生的時間
確定死鎖發生的順序
確定死鎖發生的位置以及觸發的SQL內容

3、確定死鎖原因

MySQL順序加鎖順序解鎖（公平鎖）
死鎖日志中出現的鎖，不論是等待的鎖，還是持有的，都是每個事務已經擁有的鎖結構

4、拓展：特殊情況加鎖引起的死鎖

?? 是不是每次看到死鎖日志就頭大？

?? 明明只是簡單的INSERT操作，數據庫卻神秘"卡死"？

看完本文，讓你3步快速定位死鎖原因！

1、找到并確定你的死鎖日志

方式1：基于MySQL錯誤日志

進入MySQL
檢查innodb_print_all_deadlocks變量：

SHOW VARIABLES LIKE 'innodb_print_all_deadlocks';

如果innodb_print_all_deadlocks變量的值為OFF，則需要將其設置為ON以開啟死鎖日志。

SET GLOBAL innodb_print_all_deadlocks = 1;

退出MySQL，查看下面的日志

/usr/local/mysql/data/mysqld.local.err

方式2：基于SHOW ENGINE INNODB STATUS命令查看最近發生的死鎖日志

輸入該命令后，你需要去輸出的信息中找到如下關鍵字

------------------------ 
LATEST DETECTED DEADLOCK 
------------------------

后面的內容即是最近一次發生的死鎖的內容

方式3：咨詢你的DBA吧！

專業的事情交給專業的人來！DBA會幫你找到最近的死鎖信息的！

2、分析你的死鎖日志

現在我們已經拿到了死鎖日志

2025-04-19 13:39:45 0x3079da000
*** (1) TRANSACTION:
TRANSACTION 10047, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 8, OS thread handle 13013229568, query id 113 localhost root updating
DELETE FROM t1 WHERE i = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `itsuka`.`t1` trx id 10047 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000273e; asc     >;;
 2: len 7; hex ad000001210110; asc     !  ;;

*** (2) TRANSACTION:
TRANSACTION 10048, ACTIVE 21 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 6, OS thread handle 13012672512, query id 114 localhost root updating
DELETE FROM t1 WHERE i = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `itsuka`.`t1` trx id 10048 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000273e; asc     >;;
 2: len 7; hex ad000001210110; asc     !  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `itsuka`.`t1` trx id 10048 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000273e; asc     >;;
 2: len 7; hex ad000001210110; asc     !  ;;

*** WE ROLL BACK TRANSACTION (1)

通常我們拿到的死鎖日志如上所示

接下來我們要做的事情包括：

（1）確定死鎖發生的時間

2025-04-19 13:39:45 0x3079da000
*** (1) TRANSACTION:

首先，我們基于死鎖日志了解到死鎖發生在2025-04-19 13:39:45分。再根據我們的業務日志，即可確定本次死鎖的日志內容。

（2）確定死鎖發生的順序

在上面的例子中，發生死鎖的兩個事務分別是10047號事務 (1) TRANSACTION。

*** (1) TRANSACTION:
TRANSACTION 10047, ACTIVE 10 sec starting index read

發生死鎖時， (1) TRANSACTION已經進行了10秒的索引查詢動作。

以及10048號事務(2) TRANSACTION

*** (2) TRANSACTION:
TRANSACTION 10048, ACTIVE 21 sec starting index read

發生死鎖時，(2) TRANSACTION已經進行了21秒的索引查詢動作。

我們知道，事務ID是順序增加的，更大的事務ID意味著更晚分配事務ID。

那么有的同學就有疑問了，為什么 (2) TRANSACTION后于 (1) TRANSACTION創建，線程的執行時間卻更長呢。

那是因為，對于讀寫事務來說，只有在它第一次對某個表執行增刪改操作時，才會為這個事務分配一個事務id，否則是不分配事務 id 的。有時，雖然我們開啟了一個讀寫事務，但是這個事務中全是查詢語句，并沒有執行增刪改操作的語句，這也就意味著這個事務并不會被分配一個事務id。因此，雖然有時運行的時間長，反而后分配了事務ID。

基于事務ID的大小，我們可以確定， (2) TRANSACTION后于 (1) TRANSACTION:分配事務ID，但是(2) TRANSACTION更早運行。

（3）確定死鎖發生的位置以及觸發的SQL內容

我們現在回到死鎖日志。

MySQL thread id 8, OS thread handle 13013229568, query id 113 localhost root updating
DELETE FROM t1 WHERE i = 1

這兩行提示了死鎖發生時當前事務執行的sql內容。

死鎖發生時正在執行一條delete語句。

接著往下看。

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `itsuka`.`t1` trx id 10047 lock_mode X locks rec but not gap waiting

這里展示了死鎖發生時等待的鎖的位置與內容本次死鎖發生在PRIMARY即主鍵索引行，死鎖的表為‘itsuka’庫的‘t1’表，并且正在等待一個‘lock_mode X locks rec but not gap waiting’鎖。

那么‘lock_mode X locks rec but not gap waiting’鎖是一個什么東西呢？

數據庫中鎖的類型大家都很熟悉，這里就不做介紹。只給出日志中各種鎖對應的關鍵字：

鎖類型	關鍵字
記錄鎖（LOCK_REC_NOT_GAP）	lock_mode X locks rec but not gap
間隙鎖（LOCK_GAP）	lock_mode X locks gap before rec
Next-key 鎖（LOCK_ORNIDARY）	lock_mode X
插入意向鎖（LOCK_INSERT_INTENTION）	lock_mode X locks gap before rec insert intention

基于此，我們可以確定，死鎖發生時，10047號事務 (1) TRANSACTION，正在等待一個主鍵索引上的排他記錄鎖

那么等待的鎖的具體內容是什么呢，或者說，他正在等待誰呢，我們接著往下看

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000273e; asc     '>;;
 2: len 7; hex ad000001210110; asc     !  ;;

這一段，表示等待的鎖的具體信息，包括一些行的物理存儲位置信息。

針對主鍵索引來著，這里保存的內容是

列編號	內容
0	主鍵值
1	事務ID
2	回滾段指針
3	第二列值
4	第三列值
5	第四列值
.....	以此類推

對應到上面的案例只有0、1、2的原因是，我的測試表的結構為

CREATE TABLE `t1`(
    `i` int(11) NOT NULL,
    PRIMARY KEY (`i`)
) ENGINE = InnoDB

因為只有主鍵列，自然就沒有后續的其他內容。對于更加復雜的表，我們也許會看到類似如下的信息：

image

原理是一樣的，事務ID 和回滾段指針列不需要過多關注，這里不展開說明。

那么，我們如何把其中對應的數值解析出來呢。

針對有符號數值型存儲，MySQL為了確保正數的數值一定大于負數，因此會將每一個數值拆成單個字節，再對最高位字節（最高的8個二進制位）的最高位與128（1000,0000）進行異或操作，相當于將正數和負數的符號位反過來。

我們找一條相對復雜的日志為例

0: len 8; hex 85558556e13000e1; asc  U V 0  ;;

將16進制值85558556e13000e1貼入計算器

image

可以觀察到最高位二進制數字為1，說明在進行異或計算前這一位為0，我們將其高位修改為0。

image

于是我們得到16進制數字0x5558556E13000E1，再將其轉為10進制。

image

這樣一來我們就解析到了得到真正存儲的數據：384359951401746657。

如法炮制，我們同樣得到本次案例中的主鍵數據

0: len 4; hex 80000001; asc     ;;

解析后得到1。即，鎖加在了主鍵為1的這一條記錄上。

如果是字符類型，只需要按照對應的字符集切分成相同大小的字節塊，每個字節塊單獨映射即可。

通常針對字符類型的反算就是deepseek出場的時候了，但是數值類型還是我們手動來比較好，deepseek似乎算不太明白。

上面重點講述了主鍵索引在死鎖日志中的日志結構，二級索引結構很類似。

針對二級索引來說，這里保存的內容是

列編號	內容
0	二級索引列1
1	二級索引列2
2	二級索引列3
.....	以此類推
最后一行	主鍵值

現在回到案例上來，我們現在已經確定了，事務1的死鎖發生在‘itsuka’庫的‘t1’表的主鍵索引上，死鎖發生時正在等待自己的排他記錄鎖的獲取，鎖的位置位于主鍵索引上主鍵為1（80000001）那條記錄

事務2的死鎖日志大部分與事務1相同，只是多了如下內容

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 44 page no 3 n bits 72 index PRIMARY of table `itsuka`.`t1` trx id 10048 lock mode S locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 00000000273e; asc     '>;;
 2: len 7; hex ad000001210110; asc     !  ;;

這段表明，死鎖發生時，事務2已經持有一把鎖，鎖的類型是共享記錄鎖，鎖的位置為主鍵索引上主鍵為1

（ 0: len 4; hex 80000001; asc ;;）

的那條記錄。

同時，事務2的死鎖發生時還在等待自己的排他記錄鎖的獲取，鎖的位置位于主鍵索引上主鍵為1

（ 0: len 4; hex 80000001; asc ;;）

的那條記錄

至此，我們的死鎖日志就分析就全部結束了。

3、確定死鎖原因

基于上一節我們知道了，死鎖發生時我們收集到了兩個事務的信息。

事務1的事物ID為10047，事務執行的時間較短為10秒鐘，死鎖時正在等待獲取一把獨占型記錄鎖，這把鎖加在了‘itsuka’庫的‘t1’表的主鍵索引上主鍵為1的那條記錄上。

事務2的事物ID為10048，事務執行的時間較長為21秒鐘，死鎖時正持有一把共享型記錄鎖，這把鎖加在了‘itsuka’庫的‘t1’表的主鍵索引上主鍵為1的那條記錄上，同時事務2又嘗試獲取一把獨占型記錄鎖，這把鎖加在了‘itsuka’庫的‘t1’表的主鍵索引上主鍵為1的那條記錄上。

在開始我們下一階段的思考之前，我們需要明確幾個問題。

（1）InnoDB順序加鎖順序解鎖（公平鎖）

image

基于MySQL的官方文檔，我們可以知道。一個事務成功加鎖的前提是：這條記錄的鎖等待隊列中，當前事務前面所有不兼容的加鎖請求都已釋放（提交或回滾）

（2）死鎖日志中出現的鎖，不論是等待的鎖，還是持有的，都是每個事務已經擁有的鎖結構

有別于java中的鎖，例如ReentrantLock。不管有幾個線程來爭搶這把鎖，自始至終都只有一個鎖結構，拿到鎖的線程擁有這把鎖，沒拿到鎖的線程不擁有這把鎖。

而MySQL每次加鎖都會在內存中生成一個獨屬于這個事務的鎖結構，只不過鎖結構里有一個等待狀態的標志，表示這個鎖獲取成功還是失敗。

在進行MySQL的加鎖分析時，一定要明白，不論當前事務有沒有成功獲取到鎖，都已經建立了鎖結構。

因此，上述案例中的鎖結構示意圖如下所示。

image

接下來我們要做的就是逐個分析鎖結構，判斷他的來源以及為什么沒能成功獲取。

所以，該案例的死鎖原因就找到了:

T2事務先獲取了了S型記錄鎖，T1事務再嘗試獲取X型記錄鎖，與S型記錄鎖沖突，因此T1事務陷入等待。此時T2事務再嘗試獲取這條記錄的X型記錄鎖，根據請求鎖的原則：這條記錄的鎖等待隊列中，與T2事務的加鎖請求沖突的鎖都已釋放（提交或回滾），T2事務才能加鎖成功。因此T2事務也陷入等待，并且T2事務需要等待T1事務先獲取鎖，但T1事務要等待T2事務的S型記錄鎖釋放，死鎖因此產生。

再結合我們的sql，我們就可以完整還原現場：

時間	事務1	事務2
T1		begin;select * from t1 where i=1 LOCK IN SHARE MODE;
T2	begin;DELETE FROM t1 WHERE i = 1;
T3		DELETE FROM t1 WHERE i = 1;

4、拓展：特殊情況加鎖引起的死鎖

當然，很多時候死鎖的產生并不完全是由兩條 SQL 顯式加鎖導致的。MySQL 可能會背著我們偷偷的加一些鎖，從而引發死鎖。但是不論是如何加鎖，我們都要先找到死鎖發生時，每個事務都涉及到了哪些鎖結構，這些鎖加在了哪里。然后再逐個分析，或者說‘猜’，這些鎖是如何產生的。

例如，當MySQL在插入或者更新記錄時出現唯一鍵沖突，那么會對重復的key加S類型的next-key鎖。因為對于 MySQL 來說，不能直接報錯，要先檢查當前沖突記錄是否為有效記錄，如果發現沖突的記錄被標記刪除了，說明他不是有效記錄，新紀錄可以插入，否則要報錯。為了防止其它事務更新或者刪除這條記錄、或者往這條記錄前面的間隙里插入記錄，開始檢查工作之前，MySQL 會對這條記錄加共享鎖。

而當insert 語句帶上on duplicate key update這個小尾巴時，這個小尾巴的作用是發現沖突記錄時執行更新操作，既然是更新操作則需要加排他鎖，所以這種情況下發生唯一鍵沖突，就直接加排他鎖。

更多加鎖情況這里不展開講，大家可以自行查閱官方文檔，針對每種加鎖場景都有明確的描述：https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

關于作者

黃敬乾俠客匯Java開發工程師

責任編輯：武曉燕來源：轉轉技術