【MySQL】死鎖案例之三

一 前言
      死鎖，其實是一個很有意思，也很有挑戰的技術問題，大概每個DBA和部分開發朋友都會在工作過程中遇見過。關於死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。
二 背景知識
2.1 insert 鎖機制
在分析死鎖案例之前，我們先學習一下背景知識 insert 語句的加鎖策略。我們先來看看官方定義:
相信大部分的DBA同行都知道在事務執行insert的時候會申請一把插入意向鎖（Insert Intention Lock)。在多事務併發寫入不同資料記錄至同一索引間隙的時候，並不需要等待其他事務完成，不會發生鎖等待。
假設有一個索引記錄包含鍵值4和7，不同的事務分別插入5和6，每個事務都會產生一個加在4-7之間的插入意向鎖，獲取在插入行上的排它鎖，但是不會被互相鎖住，因為資料行並不衝突。
但是如果遇到唯一鍵呢？
對於insert操作來說，若發生唯一約束衝突，則需要對沖突的唯一索引加上S Next-key Lock。從這裡會發現，即使是RC事務隔離級別，也同樣會存在Next-Key Lock鎖，從而阻塞併發。然而，文件沒有說明的是，對於檢測到衝突的唯一索引，等待執行緒在獲得S Lock之後，還需要對下一個記錄進行加鎖，在原始碼中由函式row_ins_scan_sec_index_for_duplicate進行判斷。via( )。我們可以透過如下例子進行驗證。

2.2 驗證
準備環境 預設事務隔離級別為RC模式。

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 語句，事務相關日誌顯示如下:
從日誌中我們可以看到 delete語句獲取了唯一索引ub和主鍵兩個行級鎖(lock_mode X locks rec but not gap) 。

執行完insert 之後 再檢視innodb engine status，事務相關日誌顯示如下:
根據官方的介紹，並結合日誌，我們可以看到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，事務相關日誌顯示如下:
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; 並檢視事務日誌

從日誌中可以看到 sess2的事務持有的鎖多了一把 lock_mode X locks rec but not gap,也即是 sess2 對 insert 成功的記錄加上的X 行鎖。 
分析至此，對於併發insert造成唯一鍵衝突的時候 insert的加鎖策略是 
三 案例分析
本案例是兩個事務併發insert 唯一鍵衝突 和gap鎖一起導致的死鎖案例。
3.1 環境 
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 死鎖日誌
日誌分析
我們從時間線維度分析:
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 方面死鎖分析。
如果各位覺得閱讀本文能夠有所收穫 歡迎 打賞一瓶飲料