主頁(yè) > 知識(shí)庫(kù) > 一次Mysql死鎖排查過程的全紀(jì)錄

一次Mysql死鎖排查過程的全紀(jì)錄
熱門標(biāo)簽：遂寧400電話申請(qǐng) 邯鄲400電話注冊(cè)辦理 哈爾濱云外呼系統(tǒng)運(yùn)營(yíng)商 永州智能外呼系統(tǒng) 南寧智能電銷機(jī)器人價(jià)格 電銷機(jī)器人市場(chǎng)價(jià) dq8 全地圖標(biāo)注 地圖標(biāo)注直通車 趙縣地圖標(biāo)注

前言

之前接觸到的數(shù)據(jù)庫(kù)死鎖，都是批量更新時(shí)加鎖順序不一致而導(dǎo)致的死鎖，但是上周卻遇到了一個(gè)很難理解的死鎖。借著這個(gè)機(jī)會(huì)又重新學(xué)習(xí)了一下mysql的死鎖知識(shí)以及常見的死鎖場(chǎng)景。在多方調(diào)研以及和同事們的討論下終于發(fā)現(xiàn)了這個(gè)死鎖問題的成因，收獲頗多。雖然是后端程序員，我們不需要像DBA一樣深入地去分析與鎖相關(guān)的源碼，但是如果我們能夠掌握基本的死鎖排查方法，對(duì)我們的日常開發(fā)還是大有裨益的。

PS：本文不會(huì)介紹死鎖的基本知識(shí)，mysql的加鎖原理可以參考本文的參考資料提供的鏈接。

死鎖起因

先介紹一下數(shù)據(jù)庫(kù)和表情況，因?yàn)樯婕暗焦緝?nèi)部真是的數(shù)據(jù)，所以以下都做了模擬，不會(huì)影響具體的分析。

我們采用的是5.5版本的mysql數(shù)據(jù)庫(kù)，事務(wù)隔離級(jí)別是默認(rèn)的RR（Repeatable-Read），采用innodb引擎。假設(shè)存在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;

表的結(jié)構(gòu)很簡(jiǎn)單，一個(gè)主鍵id，另一個(gè)唯一索引a。表里的數(shù)據(jù)如下：

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

出現(xiàn)死鎖的操作如下：

步驟 事務(wù)1 事務(wù)2
1 begin
2 delete from test where a = 2;
3 begin
4 delete from test where a = 2; （事務(wù)1卡住）
5 提示出現(xiàn)死鎖：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)

分析

閱讀死鎖日志

遇到死鎖，第一步就是閱讀死鎖日志。死鎖日志通常分為兩部分，上半部分說明了事務(wù)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 ;;

從日志里我們可以看到事務(wù)1當(dāng)前正在執(zhí)行delete from test where a = 2，該條語句正在申請(qǐng)索引a的X鎖，所以提示lock_mode X waiting。

然后日志的下半部分說明了事務(wù)2當(dāng)前持有的鎖以及等待的鎖：

*** (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)塊中我們可以看到事務(wù)2持有索引a的X鎖，并且是記錄鎖（Record Lock）。該鎖是通過事務(wù)2在步驟2執(zhí)行的delete語句申請(qǐng)的。由于是RR隔離模式下的基于唯一索引的等值查詢（Where a = 2），所以會(huì)申請(qǐng)一個(gè)記錄鎖，而非next-key鎖。

從日志的WAITING FOR THIS LOCK TO BE GRANTED塊中我們可以看到事務(wù)2正在申請(qǐng)S鎖，也就是共享鎖。該鎖是insert into test (id,a) values (10,2)語句申請(qǐng)的。insert語句在普通情況下是會(huì)申請(qǐng)排他鎖，也就是X鎖，但是這里出現(xiàn)了S鎖。這是因?yàn)閍字段是一個(gè)唯一索引，所以insert語句會(huì)在插入前進(jìn)行一次duplicate key的檢查，為了使這次檢查成功，需要申請(qǐng)S鎖防止其他事務(wù)對(duì)a字段進(jìn)行修改。

那么為什么該S鎖會(huì)失敗呢？這是對(duì)同一個(gè)字段的鎖的申請(qǐng)是需要排隊(duì)的。S鎖前面還有一個(gè)未申請(qǐng)成功的X鎖，所以S鎖必須等待，所以形成了循環(huán)等待，死鎖出現(xiàn)了。

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

死鎖形成流程圖

為了讓大家更好地理解死鎖形成的原因，我們?cè)偻ㄟ^表格的形式闡述死鎖形成的流程：

步驟 事務(wù)1 事務(wù)2
1 begin
2 delete from test where a = 2; 執(zhí)行成功，事務(wù)2占有a=2下的X鎖，類型為記錄鎖。
3 begin
4 delete from test where a = 2; 事務(wù)1希望申請(qǐng)a=2下的X鎖，但是由于事務(wù)2已經(jīng)申請(qǐng)了一把X鎖，兩把X鎖互斥，所以X鎖申請(qǐng)進(jìn)入鎖請(qǐng)求隊(duì)列。
5 出現(xiàn)死鎖，事務(wù)1權(quán)重較小，所以被選擇回滾（成為犧牲品）。 insert into test (id, a) values (10, 2); 由于a字段建立了唯一索引，所以需要申請(qǐng)S鎖以便檢查duplicate key，由于插入的a的值還是2，所以排在X鎖后面。但是前面的X鎖的申請(qǐng)只有在事務(wù)2commit或者rollback之后才能成功，此時(shí)形成了循環(huán)等待，死鎖產(chǎn)生。

拓展

在排查死鎖的過程中，有個(gè)同事還發(fā)現(xiàn)了上述場(chǎng)景會(huì)產(chǎn)生另一種死鎖，該場(chǎng)景無法通過手工復(fù)現(xiàn)，只有高并發(fā)場(chǎng)景下才有可能復(fù)現(xiàn)。

該死鎖對(duì)應(yīng)的日志這里就不貼出了，與上一個(gè)死鎖的核心差別是事務(wù)2等待的鎖從S鎖換成了X鎖，也就是lock_mode X locks gap before rec insert intention waiting。

我們還是通過表格來詳細(xì)說明該死鎖產(chǎn)生的流程：

步驟 事務(wù)1 事務(wù)2
1 begin
2 delete from test where a = 2; 執(zhí)行成功，事務(wù)2占有a=2下的X鎖，類型為記錄鎖。
3 begin
4 【insert第1階段】insert into test (id, a) values (10, 2); 事務(wù)2申請(qǐng)S鎖進(jìn)行duplicate key進(jìn)行檢查。檢查成功。
5 delete from test where a = 2; 事務(wù)1希望申請(qǐng)a=2下的X鎖，但是由于事務(wù)2已經(jīng)申請(qǐng)了一把X鎖，兩把X鎖互斥，所以X鎖申請(qǐng)進(jìn)入鎖請(qǐng)求隊(duì)列。
6 出現(xiàn)死鎖，事務(wù)1權(quán)重較小，所以被選擇回滾（成為犧牲品）。 【insert第2階段】insert into test (id, a) values (10, 2); 事務(wù)2開始插入數(shù)據(jù)，S鎖升級(jí)為X鎖，類型為insert intention。同理，X鎖進(jìn)入隊(duì)列排隊(duì)，形成循環(huán)等待，死鎖產(chǎn)生。

總結(jié)

排查死鎖時(shí)，首先需要根據(jù)死鎖日志分析循環(huán)等待的場(chǎng)景，然后根據(jù)當(dāng)前各個(gè)事務(wù)執(zhí)行的SQL分析出加鎖類型以及順序，逆向推斷出如何形成循環(huán)等待，這樣就能找到死鎖產(chǎn)生的原因了。

好了，以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作能帶來一定的幫助，上述分析都是基于經(jīng)驗(yàn)的推斷，希望其他小伙伴們能夠指出當(dāng)中的錯(cuò)誤以及不足指出，謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:
  • 詳解MySQL中的死鎖情況以及對(duì)死鎖的處理方法
  • mysql 數(shù)據(jù)庫(kù)死鎖原因及解決辦法
  • 查找MySQL線程中死鎖的ID的方法
  • MySQL數(shù)據(jù)庫(kù)的一次死鎖實(shí)例分析
  • 一個(gè)mysql死鎖場(chǎng)景實(shí)例分析
  • 細(xì)說MySQL死鎖與日志二三事
  • MYSQL性能優(yōu)化分享(分庫(kù)分表)
  • MyBatis實(shí)現(xiàn)Mysql數(shù)據(jù)庫(kù)分庫(kù)分表操作和總結(jié)（推薦）
  • MYSQL數(shù)據(jù)庫(kù)數(shù)據(jù)拆分之分庫(kù)分表總結(jié)
  • mysql死鎖和分庫(kù)分表問題詳解

標(biāo)簽：浙江 南寧 上海 張家界 鄂州 阿里 中衛(wèi) 定西

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《一次Mysql死鎖排查過程的全紀(jì)錄》，本文關(guān)鍵詞  一次,Mysql,死鎖,排查,過程,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《一次Mysql死鎖排查過程的全紀(jì)錄》相關(guān)的同類信息！
  • 本頁(yè)收集關(guān)于一次Mysql死鎖排查過程的全紀(jì)錄的相關(guān)信息資訊供網(wǎng)民參考！
    • 推薦文章