一次Mysql死鎖排查過程的全紀(jì)錄
前言
之前接觸到的數(shù)據(jù)庫死鎖,都是批量更新時加鎖順序不一致而導(dǎo)致的死鎖,但是上周卻遇到了一個很難理解的死鎖。借著這個機(jī)會又重新學(xué)習(xí)了一下mysql的死鎖知識以及常見的死鎖場景。在多方調(diào)研以及和同事們的討論下終于發(fā)現(xiàn)了這個死鎖問題的成因,收獲頗多。雖然是后端程序員,我們不需要像DBA一樣深入地去分析與鎖相關(guān)的源碼,但是如果我們能夠掌握基本的死鎖排查方法,對我們的日常開發(fā)還是大有裨益的。
PS:本文不會介紹死鎖的基本知識,mysql的加鎖原理可以參考本文的參考資料提供的鏈接。
死鎖起因
先介紹一下數(shù)據(jù)庫和表情況,因為涉及到公司內(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)很簡單,一個主鍵id,另一個唯一索引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卡?。?/td> | |
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),所以會申請一個記錄鎖,而非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鎖。這是因為a字段是一個唯一索引,所以insert語句會在插入前進(jìn)行一次duplicate key
的檢查,為了使這次檢查成功,需要申請S鎖防止其他事務(wù)對a字段進(jìn)行修改。
那么為什么該S鎖會失敗呢?這是對同一個字段的鎖的申請是需要排隊的。S鎖前面還有一個未申請成功的X鎖,所以S鎖必須等待,所以形成了循環(huán)等待,死鎖出現(xiàn)了。
通過閱讀死鎖日志,我們可以清楚地知道兩個事務(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)入鎖請求隊列。 | |
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之后才能成功,此時形成了循環(huán)等待,死鎖產(chǎn)生。 |
拓展
在排查死鎖的過程中,有個同事還發(fā)現(xiàn)了上述場景會產(chǎn)生另一種死鎖,該場景無法通過手工復(fù)現(xiàn),只有高并發(fā)場景下才有可能復(fù)現(xiàn)。
該死鎖對應(yīng)的日志這里就不貼出了,與上一個死鎖的核心差別是事務(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)入鎖請求隊列。 | |
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)入隊列排隊,形成循環(huán)等待,死鎖產(chǎn)生。 |
總結(jié)
排查死鎖時,首先需要根據(jù)死鎖日志分析循環(huán)等待的場景,然后根據(jù)當(dāng)前各個事務(wù)執(zhí)行的SQL分析出加鎖類型以及順序,逆向推斷出如何形成循環(huán)等待,這樣就能找到死鎖產(chǎn)生的原因了。
好了,以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助,上述分析都是基于經(jīng)驗的推斷,希望其他小伙伴們能夠指出當(dāng)中的錯誤以及不足指出,謝謝大家對腳本之家的支持。
相關(guān)文章
mysql數(shù)據(jù)損壞,如何通過ibd和frm文件批量恢復(fù)數(shù)據(jù)庫數(shù)據(jù)
這篇文章主要介紹了mysql數(shù)據(jù)損壞,如何通過ibd和frm文件批量恢復(fù)數(shù)據(jù)庫數(shù)據(jù)問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08Mysql使用insert插入多條記錄 批量新增數(shù)據(jù)
這篇文章主要介紹了Mysql使用insert插入多條記錄批量新增數(shù)據(jù),需要的朋友可以參考下2017-08-08Windows環(huán)境下的MYSQL5.7配置文件定位圖文分析
本文通過圖文并茂的形式給大家介紹了Windows環(huán)境下的MYSQL5.7配置文件定位 ,非常不錯,具有一定的參考借鑒價值,需要的朋友可以參考下2019-05-05