Mysql一次死鎖排查過程的全紀錄分享

2814

在測試環境測試給用戶并發發送卡券時，出現了死鎖，通過查找相關的資料解決了這個，所以想著總結出來，所以下面這篇文章主要是關于一次mysql死鎖排查過程的全紀錄，需要的朋友可以參考下，希望大家從中能有所幫助。

前言

之前接觸到的數據庫死鎖，都是批量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，另一個唯一mysqla。表里的數據如下：

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卡?。?/td>
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隔離模式下的基于唯一索引的等值mysql（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鎖必須等待，所以形成了mysql等待，死鎖出現了。

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

死鎖形成流程圖

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

步驟 事務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 mysql 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分析出加鎖類型以及順序，逆向推斷出如何形成循環等待，這樣就能找到死鎖產生的原因了。

? 版權聲明
THE END
數據庫
# 數據庫
喜歡就支持一下吧
點贊14 分享