記錄一次 MySQL 死鎖排查過程

wooyoo發表於2017-02-19
MySql

背景

以前接觸到的資料庫死鎖，都是批量更新時加鎖順序不一致而導致的死鎖，但是上週卻遇到了一個很難理解的死鎖。藉著這個機會又重新學習了一下mysql的死鎖知識以及常見的死鎖場景。在多方調研以及和同事們的討論下終於發現了這個死鎖問題的成因，收穫頗多。雖然是後端程式設計師，我們不需要像DBA一樣深入地去分析與鎖相關的原始碼，但是如果我們能夠掌握基本的死鎖排查方法，對我們的日常開發還是大有裨益的。

PS：本文不會介紹死鎖的基本知識，mysql的加鎖原理可以參考本文的參考資料提供的連結。

死鎖起因

先介紹一下資料庫和表情況，因為涉及到公司內部真是的資料，所以以下都做了模擬，不會影響具體的分析。

我們採用的是5.5版本的mysql資料庫，事務隔離級別是預設的RR（Repeatable-Read），採用innodb引擎。假設存在test表：

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;複製程式碼

表的結構很簡單，一個主鍵id，另一個唯一索引a。表裡的資料如下：

mysql> select * from test;
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  4 |    4 |
+----+------+
3 rows in set (0.00 sec)複製程式碼

出現死鎖的操作如下：

步驟 事務1 事務2
1 begin
2 delete from test where a = 2;
3 begin
4 delete from test where a = 2; （事務1卡住）
5 提示出現死鎖：ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction insert into test (id, a) values (10, 2);

然後我們可以通過SHOW ENGINE INNODB STATUS;來檢視死鎖日誌：

------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;

*** WE ROLL BACK TRANSACTION (1)複製程式碼

分析

閱讀死鎖日誌

遇到死鎖，第一步就是閱讀死鎖日誌。死鎖日誌通常分為兩部分，上半部分說明了事務1在等待什麼鎖：

170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD, ACTIVE 11 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 448218, OS thread handle 0x2abe5fb5d700, query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;複製程式碼

從日誌裡我們可以看到事務1當前正在執行delete from test where a = 2，該條語句正在申請索引a的X鎖，所以提示lock_mode X waiting。

然後日誌的下半部分說明了事務2當前持有的鎖以及等待的鎖：

*** (2) TRANSACTION:
TRANSACTION 2A8BC, ACTIVE 18 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 2
MySQL thread id 448217, OS thread handle 0x2abe5fd65700, query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id,a) values (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 4; hex 00000002; asc     ;;
 1: len 4; hex 00000002; asc     ;;複製程式碼

從日誌的HOLDS THE LOCKS(S)塊中我們可以看到事務2持有索引a的X鎖，並且是記錄鎖（Record Lock）。該鎖是通過事務2在步驟2執行的delete語句申請的。由於是RR隔離模式下的基於唯一索引的等值查詢（Where a = 2），所以會申請一個記錄鎖，而非next-key鎖。

從日誌的WAITING FOR THIS LOCK TO BE GRANTED塊中我們可以看到事務2正在申請S鎖，也就是共享鎖。該鎖是insert into test (id,a) values (10,2)語句申請的。insert語句在普通情況下是會申請排他鎖，也就是X鎖，但是這裡出現了S鎖。這是因為a欄位是一個唯一索引，所以insert語句會在插入前進行一次duplicate key的檢查，為了使這次檢查成功，需要申請S鎖防止其他事務對a欄位進行修改。

那麼為什麼該S鎖會失敗呢？這是對同一個欄位的鎖的申請是需要排隊的。S鎖前面還有一個未申請成功的X鎖，所以S鎖必須等待，所以形成了迴圈等待，死鎖出現了。

通過閱讀死鎖日誌，我們可以清楚地知道兩個事務形成了怎樣的迴圈等待，再加以分析，就可以逆向推斷出迴圈等待的成因，也就是死鎖形成的原因。

死鎖形成流程圖

為了讓大家更好地理解死鎖形成的原因，我們再通過表格的形式闡述死鎖形成的流程：

步驟 事務1 事務2
1 begin
2 delete from test where a = 2; 執行成功，事務2佔有a=2下的X鎖，型別為記錄鎖。
3 begin
4 delete from test where a = 2; 事務1希望申請a=2下的X鎖，但是由於事務2已經申請了一把X鎖，兩把X鎖互斥，所以X鎖申請進入鎖請求佇列。
5 出現死鎖，事務1權重較小，所以被選擇回滾（成為犧牲品）。 insert into test (id, a) values (10, 2); 由於a欄位建立了唯一索引，所以需要申請S鎖以便檢查duplicate key，由於插入的a的值還是2，所以排在X鎖後面。但是前面的X鎖的申請只有在事務2commit或者rollback之後才能成功，此時形成了迴圈等待，死鎖產生。

擴充

在排查死鎖的過程中，有個同事還發現了上述場景會產生另一種死鎖，該場景無法通過手工復現，只有高併發場景下才有可能復現。

該死鎖對應的日誌這裡就不貼出了，與上一個死鎖的核心差別是事務2等待的鎖從S鎖換成了X鎖，也就是lock_mode X locks gap before rec insert intention waiting。我們還是通過表格來詳細說明該死鎖產生的流程：

步驟 事務1 事務2
1 begin
2 delete from test where a = 2; 執行成功，事務2佔有a=2下的X鎖，型別為記錄鎖。
3 begin
4 【insert第1階段】insert into test (id, a) values (10, 2); 事務2申請S鎖進行duplicate key進行檢查。檢查成功。
5 delete from test where a = 2; 事務1希望申請a=2下的X鎖，但是由於事務2已經申請了一把X鎖，兩把X鎖互斥，所以X鎖申請進入鎖請求佇列。
6 出現死鎖，事務1權重較小，所以被選擇回滾（成為犧牲品）。 【insert第2階段】insert into test (id, a) values (10, 2); 事務2開始插入資料，S鎖升級為X鎖，型別為insert intention。同理，X鎖進入佇列排隊，形成迴圈等待，死鎖產生。

總結

排查死鎖時，首先需要根據死鎖日誌分析迴圈等待的場景，然後根據當前各個事務執行的SQL分析出加鎖型別以及順序，逆向推斷出如何形成迴圈等待，這樣就能找到死鎖產生的原因了。

PS：上述分析都是基於經驗的推斷，希望其他小夥伴們能夠指出當中的錯誤以及不足指出，謝謝！

參考資料

本文首發於www.kissyu.org/2017/02/19/… 歡迎評論和轉載，轉載請註明來源，謝謝！

相關文章