【MySQL】死鎖案例之四

楊奇龍發表於2017-09-18
MySql
一 前言
  死鎖，其實是一個很有意思，也很有挑戰的技術問題，大概每個DBA和部分開發同學都會在工作過程中遇見過 。關於死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。本文介紹一例三個併發insert 導致的死鎖，根本原因還是在於insert 唯一鍵申請插入意向鎖這個特殊的GAP鎖。其實稱呼插入意向鎖 為 Insert Intention Gap Lock 更為合理。
二 案例分析
2.1 環境準備 
Percona server 5.6 RR模式
  1. CREATE TABLE `t6` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `a` int(11) DEFAULT NULL,
  4.   PRIMARY KEY (`id`),
  5.   unique KEY `idx_a` (`a`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
  7. insert into t6 values(1,2),(2,8),(3,9),(4,11),(5,19)

sess1

sess2

sess3

begin;



insert into t6(id,a) values(6,15);

begin;



insert into t6(id,a) values(7,15);

begin;



insert into t6(id,a) values(8,15);

rollback; 


ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


2.2 死鎖日誌
  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-18 10:03:50 7f78eae30700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308725, ACTIVE 18 sec inserting, thread declared inside InnoDB 1
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
  9. MySQL thread id 3825465, OS thread handle 0x7f78eaef4700, query id 781148519 localhost root update
  10. insert into t6(id,a) values(7,15)
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308725 lock_mode X insert intention waiting
  13. *** (2) TRANSACTION:
  14. TRANSACTION 462308726, ACTIVE 10 sec inserting, thread declared inside InnoDB 1
  15. mysql tables in use 1, locked 1
  16. 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
  17. MySQL thread id 3825581, OS thread handle 0x7f78eae30700, query id 781148528 localhost root update
  18. insert into t6(id,a) values(8,15)
  19. *** (2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308726 lock mode S
  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308726 lock_mode X insert intention waiting
  23. *** WE ROLL BACK TRANSACTION (2)
2.3 死鎖分析
首先依然要再次強調insert 插入操作的加鎖邏輯。
第一階段: 唯一性約束檢查，先申請LOCK_S + LOCK_ORDINARY
第二階段: 獲取階段一的鎖並且insert成功之後,插入的位置有Gap鎖:LOCK_INSERT_INTENTION,為了防止其他insert唯一鍵衝突。
               新資料插入:LOCK_X + LOCK_REC_NOT_GAP
對於insert操作來說，若發生唯一約束衝突，則需要對衝突的唯一索引加上S Next-key Lock。從這裡會發現，即使是RC事務隔離級別，也同樣會存在Next-Key Lock鎖，從而阻塞併發。然而，文件沒有說明的是，對於檢測到衝突的唯一索引，等待執行緒在獲得S Lock之後，還需要對下一個記錄進行加鎖，在原始碼中由函式row_ins_scan_sec_index_for_duplicate進行判斷.
其次 我們需要了解 鎖的相容性矩陣。

從相容性矩陣我們可以得到如下結論:
  1. INSERT操作之間不會有衝突。
  2. GAP,Next-Key會阻止Insert。
  3. GAP和Record,Next-Key不會衝突
  4. Record和Record、Next-Key之間相互衝突。
  5. 已有的Insert鎖不阻止任何準備加的鎖。
這個案例是三個會話併發執行的，我打算一步一步來分析每個步驟執行完之後的事務日誌。
第一步 sess1 執行插入操作
insert into t6(id,a) values(6,15);
  1. ---TRANSACTION 462308737, ACTIVE 5 sec
  2. 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1
  3. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149440 localhost root init
  4. show engine innodb status
  5. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX
因為第一個插入的語句，所以唯一性衝突檢查通過，成功插入(6,15). 此時sess1 會話持有(6,15)的LOCK_X|LOCK_REC_NOT_GAP鎖。參考"INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row."

第二步 sess2 執行插入操作
insert into t6(id,a) values(7,15);
  1. ---TRANSACTION 462308738, ACTIVE 4 sec inserting
  2. mysql tables in use 1, locked 1
  3. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  4. MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update
  5. insert into t6(id,a) values(7,15)
  6. ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
  7. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  8. ------------------
  9. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX
  10. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  11. ---TRANSACTION 462308737, ACTIVE 66 sec
  12. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  13. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149526 localhost root init
  14. show engine innodb status
  15. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX
  16. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap
首先sess2的insert 申請了IX鎖，因為sess1 會話已經插入成功並且持有唯一鍵 a=15的X 行鎖 ，故而sess2 insert 進行唯一性檢查，先申請LOCK_S + LOCK_ORDINARY ,事務日誌列表中提示lock mode S waiting
第三部 sess3 執行插入操作
insert into t6(id,a) values(8,15);
  1. ---TRANSACTION 462308739, ACTIVE 3 sec inserting
  2. mysql tables in use 1, locked 1
  3. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  4. MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root update
  5. insert into t6(id,a) values(8,15)
  6. ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
  7. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308739 lock mode S waiting
  8. ------------------
  9. TABLE LOCK table `test`.`t6` trx id 462308739 lock mode IX
  10. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308739 lock mode S waiting
  11. ---TRANSACTION 462308738, ACTIVE 35 sec inserting
  12. mysql tables in use 1, locked 1
  13. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  14. MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update
  15. insert into t6(id,a) values(7,15)
  16. ------- TRX HAS BEEN WAITING 35 SEC FOR THIS LOCK TO BE GRANTED:
  17. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  18. ------------------
  19. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX
  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting
  21. ---TRANSACTION 462308737, ACTIVE 97 sec
  22. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  23. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149560 localhost root init
  24. show engine innodb status
  25. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX
  26. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap
與會話sess2 的加鎖申請流程一致，都在等待sess1釋放鎖資源。
第四步 sess1 執行回滾操作，sess2 不提交
sess1 rollback;
此時sess2 插入成功，sess3出現死鎖，此時sess2 insert插入成功,還未提交,事務列表如下:
  1. ------------
  2. TRANSACTIONS
  3. ------------
  4. Trx id counter 462308744
  5. Purge done for trx s n:o < 462308744 undo n:o < 0 state: running but idle
  6. History list length 1866
  7. LIST OF TRANSACTIONS FOR EACH SESSION:
  8. ---TRANSACTION 462308737, not started
  9. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149626 localhost root init
  10. show engine innodb status
  11. ---TRANSACTION 462308739, not started
  12. MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root cleaning up
  13. ---TRANSACTION 462308738, ACTIVE 75 sec
  14. 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
  15. MySQL thread id 3825768, OS thread handle 0x7f78eadce700, query id 781149608 localhost root cleaning up
  16. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX
  17. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S
  18. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S
  19. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock_mode X insert intention
  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S locks gap before rec
死鎖的原因
 sess1 insert成功並針對a=15的唯一鍵加上X鎖。
 sess2 執行insert 插入(6,15), 在插入之前進行唯一性檢查發現和sess1的已經插入的記錄重複鍵需要申請LOCK_S|LOCK_ORDINARY, 但與sess1 的(LOCK_X | LOCK_REC_NOT_GAP)衝突,加入等待佇列,等待sess1 釋放鎖。
 sess3 執行insert 插入(7,15), 在插入之前進行唯一性檢查發現和sess1的已經插入的記錄重複鍵需要申請LOCK_S|LOCK_ORDINARY, 但與sess1 的(LOCK_X | LOCK_REC_NOT_GAP)衝突,加入等待佇列,等待sess1 釋放鎖。
 sess1 執行rollback, sess1 釋放索引a=15 上的排他記錄鎖(LOCK_X | LOCK_REC_NOT_GAP),此後 sess2和sess3 獲得S鎖(LOCK_S|LOCK_ORDINARY)成功,sess2和sess3都要請求索引a=15上的排他記錄鎖(LOCK_X | LOCK_REC_NOT_GAP),日誌中提示 lock_mode X insert intention。由於X鎖與S鎖互斥，sess2和sess3都等待對方釋放S鎖，於是出現死鎖，MySQL 選擇回滾其中之一。

四 總結
    死鎖分析是已經很有挑戰的事情，尤其對於insert 唯一鍵衝突,要分多個階段去申請，也要理解鎖的相容矩陣。對於這塊我還有需要在學習瞭解的知識點，本文算是拋磚引玉，如有分析理解不正確的地方，望大家指正。

推薦文章 《MySQL insert 鎖機制》 《insert into 加鎖機制》

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

相關文章