記一次MySQL死鎖排查過程
背景
以前接觸到的數(shù)據(jù)庫死鎖，都是批量更新時(shí)加鎖順序不一致而導(dǎo)致的死鎖，但是上周卻遇到了一個(gè)很難理解的死鎖。借著這個(gè)機(jī)會又重新學(xué)習(xí)了一下 mysql 的死鎖知識以及常見的死鎖場景。在多方調(diào)研以及和同事們的討論下終于發(fā)現(xiàn)了這個(gè)死鎖問題的成因，收獲頗多。雖然是后端程序員，我們不需要像 DBA 一樣深入地去分析與鎖相關(guān)的源碼，但是如果我們能夠掌握基本的死鎖排查方法，對我們的日常開發(fā)還是大有裨益的。
PS：本文不會介紹死鎖的基本知識，mysql 的加鎖原理可以參考本文的參考資料提供的鏈接。
死鎖起因
先介紹一下數(shù)據(jù)庫和表情況，因?yàn)樯婕暗焦緝?nèi)部真是的數(shù)據(jù)，所以以下都做了模擬，不會影響具體的分析。
我們采用的是 5.5 版本的 mysql 數(shù)據(jù)庫，事務(wù)隔離級別是默認(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)很簡單，一個(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 卡?。?/span> | |
| 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，該條語句正在申請索引 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 語句申請的。由于是 RR 隔離模式下的基于***索引的等值查詢（Where a = 2），所以會申請一個(gè)記錄鎖，而非 next-key 鎖。
從日志的 WAITING FOR THIS LOCK TO BE GRANTED 塊中我們可以看到事務(wù) 2 正在申請 S 鎖，也就是共享鎖。該鎖是 insert into test (id,a) values (10,2) 語句申請的。insert 語句在普通情況下是會申請排他鎖，也就是 X 鎖，但是這里出現(xiàn)了 S 鎖。這是因?yàn)?a 字段是一個(gè)***索引，所以 insert 語句會在插入前進(jìn)行一次 duplicate key 的檢查，為了使這次檢查成功，需要申請 S 鎖防止其他事務(wù)對 a 字段進(jìn)行修改。
那么為什么該 S 鎖會失敗呢？這是對同一個(gè)字段的鎖的申請是需要排隊(duì)的。S 鎖前面還有一個(gè)未申請成功的 X 鎖，所以 S 鎖必須等待，所以形成了循環(huán)等待，死鎖出現(xiàn)了。
通過閱讀死鎖日志，我們可以清楚地知道兩個(gè)事務(wù)形成了怎樣的循環(huán)等待，再加以分析，就可以逆向推斷出循環(huán)等待的成因，也就是死鎖形成的原因。
死鎖形成流程圖
為了讓大家更好地理解死鎖形成的原因，我們再通過表格的形式闡述死鎖形成的流程：
| 步驟 |
事務(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 希望申請 a=2 下的 X 鎖，但是由于事務(wù) 2 已經(jīng)申請了一把 X 鎖，兩把 X 鎖互斥，所以 X 鎖申請進(jìn)入鎖請求隊(duì)列。 | |
| 5 | 出現(xiàn)死鎖，事務(wù) 1 權(quán)重較小，所以被選擇回滾（成為犧牲品）。 | insert into test (id, a) values (10, 2); 由于 a 字段建立了***索引，所以需要申請 S 鎖以便檢查 duplicate key，由于插入的 a 的值還是 2，所以排在 X 鎖后面。但是前面的 X 鎖的申請只有在事務(wù) 2commit 或者 rollback 之后才能成功，此時(shí)形成了循環(huán)等待，死鎖產(chǎn)生。 |
拓展
在排查死鎖的過程中，有個(gè)同事還發(fā)現(xiàn)了上述場景會產(chǎn)生另一種死鎖，該場景無法通過手工復(fù)現(xiàn)，只有高并發(fā)場景下才有可能復(fù)現(xiàn)。
該死鎖對應(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 申請 S 鎖進(jìn)行 duplicate key 進(jìn)行檢查。檢查成功。 | |
| 5 | delete from test where a = 2; 事務(wù) 1 希望申請 a=2 下的 X 鎖，但是由于事務(wù) 2 已經(jīng)申請了一把 X 鎖，兩把 X 鎖互斥，所以 X 鎖申請進(jìn)入鎖請求隊(duì)列。 | |
| 6 | 出現(xiàn)死鎖，事務(wù) 1 權(quán)重較小，所以被選擇回滾（成為犧牲品）。 | 【insert 第 2 階段】insert into test (id, a) values (10, 2); 事務(wù) 2 開始插入數(shù)據(jù)，S 鎖升級為 X 鎖，類型為 insert intention。同理，X 鎖進(jìn)入隊(duì)列排隊(duì)，形成循環(huán)等待，死鎖產(chǎn)生。 |
總結(jié)
排查死鎖時(shí)，首先需要根據(jù)死鎖日志分析循環(huán)等待的場景，然后根據(jù)當(dāng)前各個(gè)事務(wù)執(zhí)行的 SQL 分析出加鎖類型以及順序，逆向推斷出如何形成循環(huán)等待，這樣就能找到死鎖產(chǎn)生的原因了。
PS：上述分析都是基于經(jīng)驗(yàn)的推斷，希望其他小伙伴們能夠指出當(dāng)中的錯(cuò)誤以及不足指出，謝謝！
