【MySQL】死鎖案例之三

楊奇龍發表於2017-09-17
MySql
一 前言
      死鎖，其實是一個很有意思，也很有挑戰的技術問題，大概每個DBA和部分開發朋友都會在工作過程中遇見過。關於死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。
二 背景知識
2.1 insert 鎖機制
在分析死鎖案例之前，我們先學習一下背景知識 insert 語句的加鎖策略。我們先來看看官方定義:
  1. "An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting."
相信大部分的DBA同行都知道在事務執行insert的時候會申請一把插入意向鎖（Insert Intention Lock)。在多事務併發寫入不同資料記錄至同一索引間隙的時候，並不需要等待其他事務完成，不會發生鎖等待。
假設有一個索引記錄包含鍵值4和7，不同的事務分別插入5和6，每個事務都會產生一個加在4-7之間的插入意向鎖，獲取在插入行上的排它鎖，但是不會被互相鎖住，因為資料行並不衝突。
但是如果遇到唯一鍵呢？
  1. "If a duplicate-key error occurs, a shared lock on the duplicate index record is set."
對於insert操作來說，若發生唯一約束衝突，則需要對衝突的唯一索引加上S Next-key Lock。從這裡會發現，即使是RC事務隔離級別，也同樣會存在Next-Key Lock鎖，從而阻塞併發。然而，文件沒有說明的是，對於檢測到衝突的唯一索引，等待執行緒在獲得S Lock之後，還需要對下一個記錄進行加鎖，在原始碼中由函式row_ins_scan_sec_index_for_duplicate進行判斷。via(MySQL REPLACE死鎖問題深入剖析 )。我們可以通過如下例子進行驗證。

2.2 驗證
準備環境 預設事務隔離級別為RC模式。
  1. CREATE TABLE t8 (
  2. a int AUTO_INCREMENT PRIMARY KEY,
  3. b int,
  4. c int,
  5. unique key ub(b)
  6. ) engine=InnoDB;
  7. insert into t8 values (NULL,1,2)

sess1

sess2

begin;


delete from t8 where b = 1;

begin;


insert into t8 values (NULL,1);

commit；



update t8 set  c=13 where b=1;


2.3 過程分析 
我在每次執行一條語句之後檢視innodb engine status，
執行完 delete 語句，事務相關日誌顯示如下:
  1. ---TRANSACTION 462308671, ACTIVE 6 sec
  2. 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  3. MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051370 localhost root init
  4. show engine innodb status
  5. TABLE LOCK table `test`.`t8` trx id 462308671 lock mode IX
  6. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
  7. RECORD LOCKS space id 232 page no 3 n bits 72 index `PRIMARY` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
從日誌中我們可以看到 delete語句獲取了唯一索引ub和主鍵兩個行級鎖(lock_mode X locks rec but not gap) 。

執行完insert 之後 再檢視innodb engine status，事務相關日誌顯示如下:
  1. LIST OF TRANSACTIONS FOR EACH SESSION:
  2. ---TRANSACTION 462308676, ACTIVE 4 sec inserting
  3. mysql tables in use 1, locked 1
  4. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  5. MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root update
  6. insert into t8 values (NULL,1,2)
  7. ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
  8. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S waiting
  9. ------------------
  10. TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
  11. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S waiting
  12. ---TRANSACTION 462308671, ACTIVE 70 sec
  13. 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  14. MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051465 localhost root init
  15. show engine innodb status
  16. TABLE LOCK table `test`.`t8` trx id 462308671 lock mode IX
  17. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
  18. RECORD LOCKS space id 232 page no 3 n bits 72 index `PRIMARY` of table `test`.`t8` trx id 462308671 lock_mode X locks rec but not gap
根據官方的介紹，並結合日誌，我們可以看到insert into t8 values (NULL,1,2)在申請一把S Next-key-Lock , 顯示lock mode S waiting。這裡想給大家說明的是在innodb 日誌中如果提示 lock mode S /lock mode X ，其實都是gap鎖，如果是行記錄鎖 會提示but not gap ,請讀者朋友們在自己分析死鎖日誌的時候注意。

sess1 delete語句提交之後，sess2的insert 不要提交，不要提交，不要提交。再次檢視innodb engine status，事務相關日誌顯示如下:
  1. ------------
  2. TRANSACTIONS
  3. ------------
  4. Trx id counter 462308678
  5. Purge done for trxs n:o < 462308678 undo n:o < 0 state: running but idle
  6. History list length 1845
  7. LIST OF TRANSACTIONS FOR EACH SESSION:
  8. ---TRANSACTION 462308671, not started
  9. MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781051526 localhost root init
  10. show engine innodb status
  11. ---TRANSACTION 462308676, ACTIVE 41 sec
  12. 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
  13. MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781051460 localhost root cleaning up
  14. TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
  15. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S
  16. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S locks gap before rec
sess1中的事務因為提交已經結束。innodb中的事務列表中只剩下sess2 中的insert 的事務了。從獲取鎖的狀態上看insert獲取一把S Next-key Lock 鎖和插入行之前的S 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."
會對insert成功的記錄加上一把X 行鎖，為什麼看不見呢？我們再在sess1 中執行update t8 set  c=13 where b=1; 並檢視事務日誌

  1. ------------
  2. TRANSACTIONS
  3. ------------
  4. Trx id counter 462308679
  5. Purge done for trxs n:o < 462308678 undo n:o < 0 state: running but idle
  6. History list length 1845
  7. LIST OF TRANSACTIONS FOR EACH SESSION:
  8. ---TRANSACTION 462308678, ACTIVE 12 sec starting index read
  9. mysql tables in use 1, locked 1
  10. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
  11. MySQL thread id 3796960, OS thread handle 0x7f78eaabe700, query id 781059217 localhost root updating
  12. update c set c=13 where b=1
  13. ------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
  14. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308678 lock_mode X locks rec but not gap waiting
  15. ------------------
  16. TABLE LOCK table `test`.`t8` trx id 462308678 lock mode IX
  17. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308678 lock_mode X locks rec but not gap waiting
  18. ---TRANSACTION 462308676, ACTIVE 5113 sec
  19. 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
  20. MySQL thread id 3796966, OS thread handle 0x7f78ea5c4700, query id 781059230 localhost root init
  21. show engine innodb status
  22. TABLE LOCK table `test`.`t8` trx id 462308676 lock mode IX
  23. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S
  24. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock mode S locks gap before rec
  25. RECORD LOCKS space id 232 page no 4 n bits 72 index `ub` of table `test`.`t8` trx id 462308676 lock_mode X locks rec but not gap
從日誌中可以看到 sess2的事務持有的鎖多了一把 lock_mode X locks rec but not gap,也即是 sess2 對 insert 成功的記錄加上的X 行鎖。 
分析至此，對於併發insert造成唯一鍵衝突的時候 insert的加鎖策略是 
  1. 第一階段 唯一性約束檢查，先申請LOCK_S + LOCK_ORDINARY
  2. 第二接入 獲取階段一的鎖並且insert成功之後
  3. 插入的位置有Gap鎖:LOCK_INSERT_INTENTION,為了防止其他insert 唯一鍵衝突。
  4. 新資料插入:LOCK_X + LOCK_REC_NOT_GAP
三 案例分析
本案例是兩個事務併發insert 唯一鍵衝突 和gap鎖一起導致的死鎖案例。
3.1 環境 
  1. create table t7(
  2.   id int not null primary key auto_increment,
  3.   a int not null ,
  4.   unique key ua(a)
  5. ) engine=innodb;
  6. insert into t7(id,a) values(1,1),(5,4),(20,20),(25,12)
3.2 測試用例

T1

T2

begin;

begin;


insert into t7(id,a) values(26,10);

insert into t7(id,a) values(30,10);



insert into t7(id,a) values(40,9);


3.3 死鎖日誌
  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2017-09-17 15:15:03 7f78eac15700
  5. *** (1) TRANSACTION:
  6. TRANSACTION 462308661, ACTIVE 6 sec inserting
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
  9. MySQL thread id 3796966, OS thread handle 0x7f78ead9d700, query id 781045166 localhost root update
  10. insert into t7(id,a) values(30,10)
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308661 lock mode S waiting
  13. *** (2) TRANSACTION:
  14. TRANSACTION 462308660, ACTIVE 43 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 3796960, OS thread handle 0x7f78eac15700, query id 781045192 localhost root update
  18. insert into t7(id,a) values(40,9)
  19. *** (2) HOLDS THE LOCK(S):
  20. RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks rec but not gap
  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  22. RECORD LOCKS space id 231 page no 4 n bits 72 index `ua` of table `test`.`t7` trx id 462308660 lock_mode X locks gap before rec insert intention waiting
  23. *** WE ROLL BACK TRANSACTION (1)
日誌分析
我們從時間線維度分析:
T2 insert into t7(id,a) values(26,10)語句insert 成功，持有a=10 的X 行鎖(X locks rec but not gap)
T1 insert into t7(id,a) values(30,10),因為T2 的第一條insert已經插入a=10的記錄，T1的 insert a=10 則發生唯一約束衝突，需要申請對衝突的唯一索引加上S Next-key Lock (也即是 lock mode S waiting ) 這是一個間隙鎖會申請鎖住[4,10],[10,20]之間的gap區域。從這裡會發現，即使是RC事務隔離級別，也同樣會存在Next-Key Lock鎖，從而阻塞併發。
T2 insert into t7(id,a) values(40,9) 該語句插入的a=9 的值在 T1申請的gap鎖[4,10]之間，故需T2的第二條insert語句要等待T1的S-Next-key Lock鎖釋放，在日誌中顯示lock_mode X locks gap before rec insert intention waiting

四 總結 
  首先感謝登博和姜承堯兩位德藝雙馨的MySQL技術大牛對死鎖技術知識的無私分享。本文案例和知識點一方面從官方文件獲取，另一方面是根據兩位大牛的分享整理，算是站在巨人的肩膀上的學習總結。在研究分析死鎖案例的過程中，insert 的意向鎖 和 gap 鎖這種型別的鎖是比較難分析的，相信通過上面的分析總結大家能夠學習到 insert的鎖機制 ，如何加鎖，如何進行 insert 方面死鎖分析。
如果各位覺得閱讀本文能夠有所收穫 歡迎 打賞一瓶飲料 

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

相關文章