您好,登錄后才能下訂單哦!
本篇內(nèi)容介紹了“MySQL死鎖的案例詳解”的有關(guān)知識(shí),在實(shí)際案例的操作過(guò)程中,不少人都會(huì)遇到這樣的困境,接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧!希望大家仔細(xì)閱讀,能夠?qū)W有所成!
一 前言
死鎖,其實(shí)是一個(gè)很有意思,也很有挑戰(zhàn)的技術(shù)問(wèn)題,大概每個(gè)DBA和部分開(kāi)發(fā)同學(xué)都會(huì)在工作過(guò)程中遇見(jiàn)過(guò) 。關(guān)于死鎖我會(huì)持續(xù)寫(xiě)一個(gè)系列的案例分析,希望能夠?qū)ο肓私馑梨i的朋友有所幫助。
二 案例分析
2.1 環(huán)境說(shuō)明
MySQL 5.6 事務(wù)隔離級(jí)別為RR
CREATE TABLE `ty` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
insert into ty(a,b) values(2,3),(5,4),(6,7);
2.2 測(cè)試用例
T2 | T1 |
begin; | |
delete from ty where a=5; | begin; |
delete from ty where a=5; | |
insert into ty(a,b) values(2,10); | |
delete from ty where a=5; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
2.3 死鎖日志
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-09 22:34:13 7f78eab82700
*** (1) TRANSACTION:
TRANSACTION 462308399, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
delete from ty where a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
insert into ty(a,b) values(2,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)
2.3分析死鎖日志
首先要理解的是 對(duì)同一個(gè)字段申請(qǐng)加鎖是需要排隊(duì). S GAP 于
其次表ty中a為普通索引字段,我們根據(jù)事務(wù)執(zhí)行的時(shí)間順序來(lái)解釋,這樣比較好理解。
a 根據(jù)死鎖日志顯示 事務(wù)2 也即sess1執(zhí)行的事務(wù),根據(jù) HOLDS THE LOCK(S)顯示
sess1 先執(zhí)行 delete from ty where a=5 ,該事務(wù)持有索引a=5 的行鎖lock_mode X ,因?yàn)槭荝R隔離級(jí)別,所以sess1 還持有兩個(gè)gap鎖[1,2]-[2,5], [2,5]-[3,6] 。
b 事務(wù)1的日志也即sess2執(zhí)行的事務(wù),申請(qǐng)對(duì) a=5 加鎖,一個(gè)rec lock 和兩個(gè)gap鎖,因?yàn)閟ess1中delete還沒(méi)釋放,故sess2的事務(wù)1等待sess1的事務(wù)2釋放a=5的鎖資源。
c 然后根據(jù)WAITING FOR THIS LOCK TO BE GRANTED,提示事務(wù)2 insert語(yǔ)句正在等待 lock_mode X locks gap before rec insert intention waiting,
因?yàn)閕nsert語(yǔ)句 [4,2] 介于gap鎖[1,2]-[2,5]之間,所以有了提示 "lock_mode X locks gap",insert語(yǔ)句必須等待前面 sess2中delete 獲取鎖并且釋放鎖。于是,sess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),循環(huán)等待,造成死鎖。
問(wèn)題 如果sess1 執(zhí)行 insert into ty(a,b) values(5,10); sess2會(huì)遇到死鎖嗎?
三 案例二
3.1 索引為唯一鍵
MySQL 5.6 事務(wù)隔離級(jí)別為RR
CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
unique KEY `idxa` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
insert into t2(a,b) values(2,3),(5,4),(6,7)
3.2 測(cè)試用例
T2 | T1 |
begin; | |
delete from ty where a=5; | begin; |
delete from ty where a=5; | |
insert into ty(a,b) values(2,10); | |
delete from ty where a=5; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
3.3 死鎖日志
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-09-10 00:03:31 7f78ea936700
*** (1) TRANSACTION:
TRANSACTION 462308445, ACTIVE 9 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3526009, OS thread handle 0x7f896cc4b700, query id 780047877 localhost root updating
delete from t2 where a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308445 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 462308444, ACTIVE 17 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 3526051, OS thread handle 0x7f78ea936700, query id 780047890 localhost root update
insert t2(a,b) values(5,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock mode S waiting
*** WE ROLL BACK TRANSACTION (1)
3.4 分析死鎖日志
首先我們要特別說(shuō)明delete的加鎖邏輯
a 找到滿足條件的記錄,并且記錄有效,則對(duì)記錄加X(jué)鎖,No Gap鎖(lock_mode X locks rec but not gap);
b 找到滿足條件的記錄,但是記錄無(wú)效(標(biāo)識(shí)為刪除的記錄),則對(duì)記錄加next key鎖(同時(shí)鎖住記錄本身,以及記錄之前的Gap:lock_mode X);
c 未找到滿足條件的記錄,則對(duì)第一個(gè)不滿足條件的記錄加Gap鎖,保證沒(méi)有滿足條件的記錄插入(locks gap before rec)
undefinedlock_mode X locks rec but not gapundefined lock mode S waitingundefinedInsert Intention Lock.
undefined
a 根據(jù)死鎖日志顯示 事務(wù)2 也即sess1執(zhí)行的事務(wù),根據(jù) HOLDS THE LOCK(S)顯示
sess1 先執(zhí)行 delete from ty where a=5 ,該事務(wù)持有索引a=5 的行鎖lock_mode X locks rec but not gap。因?yàn)楸纠衋是唯一鍵,故沒(méi)有g(shù)ap鎖。
b 事務(wù)1的日志也即sess2執(zhí)行的事務(wù),申請(qǐng)對(duì) a=5 加鎖(X Next-key Lock),一個(gè)rec lock 但是因?yàn)閟ess1中delete 已經(jīng)執(zhí)行完成,記錄無(wú)效沒(méi)有被刪除,鎖還沒(méi)釋放,故sess2的事務(wù)1等待sess1的事務(wù)2釋放a=5的鎖資源,日志中提示 lock_mode X waiting.
c 然后根據(jù)WAITING FOR THIS LOCK TO BE GRANTED,提示事務(wù)2 insert語(yǔ)句正在等待 lock mode S waiting,為什么這次是 S 鎖呢?因?yàn)閍字段是一個(gè)唯一索引,所以insert語(yǔ)句會(huì)在插入前進(jìn)行一次duplicate key的檢查,需要申請(qǐng)S鎖防止其他事務(wù)對(duì)a字段進(jìn)行重復(fù)插入。而插入意向鎖與T1已經(jīng)insert語(yǔ)句必須等待前面 sess2中delete 獲取a=5的行鎖并且釋放鎖。
undefinedsess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),循環(huán)等待,造成死鎖undefined
“MySQL死鎖的案例詳解”的內(nèi)容就介紹到這里了,感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站,小編將為大家輸出更多高質(zhì)量的實(shí)用文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。