MySQL死鎖的案例詳解

本篇內容介紹了“死鎖的案例詳解”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！

一 前言
   死鎖，其實(shí)是一個(gè)很有意思，也很有挑戰的技術(shù)問(wèn)題，大概每個(gè)DBA和部分開(kāi)發(fā)同學(xué)都會(huì )在工作過(guò)程中遇見(jiàn)過(guò) 。關(guān)于死鎖我會(huì )持續寫(xiě)一個(gè)系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。
二 案例分析
2.1 環(huán)境說(shuō)明
MySQL 5.6 事務(wù)隔離級別為RR

1. CREATE TABLE `ty` (
   

2.   `id` int(11) NOT NULL AUTO_INCREMENT,
   

3.   `a` int(11) DEFAULT NULL,
   

4.   `b` int(11) DEFAULT NULL,
   

5.   PRIMARY KEY (`id`),
   

6.   KEY `idxa` (`a`)
   

7. ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4
   

8. insert into ty(a,b) values(2,3),(5,4),(6,7);

2.2 測試用例

                                                            
2.3 死鎖日志

1. ------------------------
   

2. LATEST DETECTED DEADLOCK
   

3. ------------------------
   

4. 2017-09-09 22:34:13 7f78eab82700
   

5. *** (1) TRANSACTION:
   

6. TRANSACTION 462308399, ACTIVE 33 sec starting index read
   

7. mysql tables in use 1, locked 1
   

8. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
   

9. MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
   

10. delete from ty where a=5
    

11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    

12. RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting
    

13. *** (2) TRANSACTION:
    

14. TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000
    

15. mysql tables in use 1, locked 1
    

16. 5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
    

17. MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
    

18. insert into ty(a,b) values(2,10)
    

19. *** (2) HOLDS THE LOCK(S):
    

20. RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
    

21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    

22. 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
    

23. *** WE ROLL BACK TRANSACTION (1)

2.3分析死鎖日志
首先要理解的是 對同一個(gè)字段申請加鎖是需要排隊. S GAP  于
其次表ty中a為普通索引字段，我們根據事務(wù)執行的時(shí)間順序來(lái)解釋?zhuān)@樣比較好理解。
a 根據死鎖日志顯示 事務(wù)2 也即sess1執行的事務(wù)，根據 HOLDS THE LOCK(S)顯示
   sess1 先執行 delete from ty where a=5 ，該事務(wù)持有索引a=5 的行鎖lock_mode X ,因為是RR隔離級別,所以sess1 還持有兩個(gè)gap鎖[1,2]-[2,5], [2,5]-[3,6] 。
b 事務(wù)1的日志也即sess2執行的事務(wù),申請對 a=5 加鎖，一個(gè)rec lock 和兩個(gè)gap鎖,因為sess1中delete還沒(méi)釋放，故sess2的事務(wù)1等待sess1的事務(wù)2釋放a=5的鎖資源。
c 然后根據WAITING FOR THIS LOCK TO BE GRANTED，提示事務(wù)2 insert語(yǔ)句正在等待 lock_mode X locks gap before rec insert intention waiting，
因為insert語(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 執行 insert into ty(a,b) values(5,10); sess2會(huì )遇到死鎖嗎？


三 案例二
3.1 索引為唯一鍵
MySQL 5.6 事務(wù)隔離級別為RR

1. CREATE TABLE `t2` (
   

2.   `id` int(11) NOT NULL AUTO_INCREMENT,
   

3.   `a` int(11) DEFAULT NULL,
   

4.   `b` int(11) DEFAULT NULL,
   

5.   PRIMARY KEY (`id`),
   

6.   unique KEY `idxa` (`a`)
   

7. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
   

8. insert into t2(a,b) values(2,3),(5,4),(6,7)

3.2 測試用例

3.3 死鎖日志

1. ------------------------
   

2. LATEST DETECTED DEADLOCK
   

3. ------------------------
   

4. 2017-09-10 00:03:31 7f78ea936700
   

5. *** (1) TRANSACTION:
   

6. TRANSACTION 462308445, ACTIVE 9 sec starting index read
   

7. mysql tables in use 1, locked 1
   

8. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
   

9. MySQL thread id 3526009, OS thread handle 0x7f896cc4b700, query id 780047877 localhost root updating
   

10. delete from t2 where a=5
    

11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    

12. RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308445 lock_mode X waiting
    

13. *** (2) TRANSACTION:
    

14. TRANSACTION 462308444, ACTIVE 17 sec inserting, thread declared inside InnoDB 5000
    

15. mysql tables in use 1, locked 1
    

16. 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
    

17. MySQL thread id 3526051, OS thread handle 0x7f78ea936700, query id 780047890 localhost root update
    

18. insert t2(a,b) values(5,10)
    

19. *** (2) HOLDS THE LOCK(S):
    

20. 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
    

21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    

22. RECORD LOCKS space id 221 page no 4 n bits 72 index `idxa` of table `test`.`t2` trx id 462308444 lock mode S waiting
    

23. *** WE ROLL BACK TRANSACTION (1)

3.4 分析死鎖日志
首先我們要特別說(shuō)明delete的加鎖邏輯

1. a 找到滿(mǎn)足條件的記錄，并且記錄有效，則對記錄加X(jué)鎖，No Gap鎖(lock_mode X locks rec but not gap);
   

2. b 找到滿(mǎn)足條件的記錄，但是記錄無(wú)效(標識為刪除的記錄)，則對記錄加next key鎖(同時(shí)鎖住記錄本身，以及記錄之前的Gap：lock_mode X);
   

3. c 未找到滿(mǎn)足條件的記錄，則對第一個(gè)不滿(mǎn)足條件的記錄加Gap鎖，保證沒(méi)有滿(mǎn)足條件的記錄插入(locks gap before rec)

undefinedlock_mode X locks rec but not gapundefined lock mode S waitingundefinedInsert Intention Lock.
undefined
a 根據死鎖日志顯示 事務(wù)2 也即sess1執行的事務(wù)，根據 HOLDS THE LOCK(S)顯示
sess1 先執行 delete from ty where a=5 ，該事務(wù)持有索引a=5 的行鎖lock_mode X locks rec but not gap。因為本例中a是唯一鍵，故沒(méi)有g(shù)ap鎖。
b 事務(wù)1的日志也即sess2執行的事務(wù),申請對 a=5 加鎖(X Next-key Lock)，一個(gè)rec lock 但是因為sess1中delete 已經(jīng)執行完成，記錄無(wú)效沒(méi)有被刪除，鎖還沒(méi)釋放，故sess2的事務(wù)1等待sess1的事務(wù)2釋放a=5的鎖資源，日志中提示 lock_mode X waiting. 
c 然后根據WAITING FOR THIS LOCK TO BE GRANTED，提示事務(wù)2 insert語(yǔ)句正在等待 lock mode S waiting，為什么這次是 S 鎖呢？因為a字段是一個(gè)唯一索引，所以insert語(yǔ)句會(huì )在插入前進(jìn)行一次duplicate key的檢查，需要申請S鎖防止其他事務(wù)對a字段進(jìn)行重復插入。而插入意向鎖與T1已經(jīng)insert語(yǔ)句必須等待前面 sess2中delete 獲取a=5的行鎖并且釋放鎖。
undefinedsess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),循環(huán)等待，造成死鎖undefined