【MySQL】死鎖案例之一

楊奇龍發表於2017-09-17
MySql
一 前言
   死鎖，其實是一個很有意思，也很有挑戰的技術問題，大概每個DBA和部分開發同學都會在工作過程中遇見過 。關於死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。
二 案例分析
2.1 環境說明
MySQL 5.6 事務隔離級別為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 測試用例

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 死鎖日誌
  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分析死鎖日誌
首先要理解的是 對同一個欄位申請加鎖是需要排隊. S GAP  於
其次表ty中a為普通索引欄位，我們根據事務執行的時間順序來解釋，這樣比較好理解。
a 根據死鎖日誌顯示 事務2 也即sess1執行的事務，根據 HOLDS THE LOCK(S)顯示
   sess1 先執行 delete from ty where a=5 ，該事務持有索引a=5 的行鎖lock_mode X ,因為是RR隔離級別,所以sess1 還持有兩個gap鎖[1,2]-[2,5], [2,5]-[3,6] 。
b 事務1的日誌也即sess2執行的事務,申請對 a=5 加鎖，一個rec lock 和兩個gap鎖,因為sess1中delete還沒釋放，故sess2的事務1等待sess1的事務2釋放a=5的鎖資源。
c 然後根據WAITING FOR THIS LOCK TO BE GRANTED，提示事務2 insert語句正在等待 lock_mode X locks gap before rec insert intention waiting，
因為insert語句 [4,2] 介於gap鎖[1,2]-[2,5]之間，所以有了提示 "lock_mode X locks gap",insert語句必須等待前面 sess2中delete 獲取鎖並且釋放鎖。於是,sess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),迴圈等待，造成死鎖。
問題 如果sess1 執行 insert into ty(a,b) values(5,10); sess2會遇到死鎖嗎？


三 案例二
3.1 索引為唯一鍵
MySQL 5.6 事務隔離級別為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 測試用例

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 死鎖日誌
  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 分析死鎖日誌
首先我們要特別說明delete的加鎖邏輯
  1. a 找到滿足條件的記錄，並且記錄有效，則對記錄加X鎖，No Gap鎖(lock_mode X locks rec but not gap);
  2. b 找到滿足條件的記錄，但是記錄無效(標識為刪除的記錄)，則對記錄加next key鎖(同時鎖住記錄本身，以及記錄之前的Gap：lock_mode X);
  3. c 未找到滿足條件的記錄，則對第一個不滿足條件的記錄加Gap鎖，保證沒有滿足條件的記錄插入(locks gap before rec)
 其次需要大家注意的是對比兩個死鎖案例會發現，sess1 事務持有的鎖型別發生了變化 delete持有的鎖變為lock_mode X locks rec but not gap 。 insert語句持有的鎖變為 lock mode S waiting。原因是因為測試表結構發生了變化欄位a由普通索引變為唯一鍵,RR模式下對唯一鍵操作是沒有gap鎖的，而且insert 寫入含有唯一鍵的資料是會申請GAP鎖的特殊情況 Insert Intention Lock.
本例我們依然根據事務執行的時間順序來解釋，這樣比較好理解。
a 根據死鎖日誌顯示 事務2 也即sess1執行的事務，根據 HOLDS THE LOCK(S)顯示
sess1 先執行 delete from ty where a=5 ，該事務持有索引a=5 的行鎖lock_mode X locks rec but not gap。因為本例中a是唯一鍵，故沒有gap鎖。
b 事務1的日誌也即sess2執行的事務,申請對 a=5 加鎖(X Next-key Lock)，一個rec lock 但是因為sess1中delete 已經執行完成，記錄無效沒有被刪除，鎖還沒釋放，故sess2的事務1等待sess1的事務2釋放a=5的鎖資源，日誌中提示 lock_mode X waiting. 
c 然後根據WAITING FOR THIS LOCK TO BE GRANTED，提示事務2 insert語句正在等待 lock mode S waiting，為什麼這次是 S 鎖呢？因為a欄位是一個唯一索引，所以insert語句會在插入前進行一次duplicate key的檢查，需要申請S鎖防止其他事務對a欄位進行重複插入。而插入意向鎖與T1已經insert語句必須等待前面 sess2中delete 獲取a=5的行鎖並且釋放鎖。
於是,sess2(delete) 等待sess1(delete) ,sess1(insert)等待sess2(delete),迴圈等待，造成死鎖。

四 小結
   本文研究了RR事務隔離級別下，普通索引與唯一鍵兩種情況的死鎖場景。如何避免解決此類死鎖？推薦使用RC隔離級別+ ROW BASE BINLOG . 但是對於RC/RR模式下 ，insert 遇到唯一鍵衝突的時候的死鎖不可避免。需要開發在設計表結構的時候 減少unique 索引設計。推薦文章 《不同語句模式下的鎖型別》
如果您覺得能從本文收益，可以請北在南方一瓶飲料 ^_^



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-2145073/，如需轉載，請註明出處，否則將追究法律責任。

相關文章