當并發insert on duplicate key update遇見死鎖：更新丢失

資料庫死鎖問題，是一個老生常談且很常見的問題，網上也有非常多對于各類死鎖場景的解析和複現，但凡和死鎖有關，無外乎不涉及資料庫隔離等級、索引、以及innodb鎖等相關原因。但是我這個案例我搜遍了全網也沒能找到比較相似情況。于是我想盡可能的複現出這種情況，找出死鎖的原因，找出可能出現的隐患。

問題的背景：

我們的資料庫中的發生死鎖的表是具有”多列組合建構的唯一索引“（不包含自增的主鍵），且資料庫的隔離等級為Read Committed, 另外對于這個表來說是寫入遠大于讀取的，由于業務的原因，經常會出現同一資料反複插入（同一資料指唯一索引值相同的資料，但其他非索引字段可能不同），是以為了簡化代碼，我們使用insert on duplicate key update來解決這種問題，當mysql檢測到唯一鍵沖突時，僅更新特定（非索引）字段。但是問題就出現在大規模多worker并發插入的時候，會經常出現"Deadlock found when trying to get lock"。開始真的是百思不得其解，于是乎開始瘋狂查閱mysql手冊以及各類博文嘗試找到問題所在。

問題的現象：

一般定位死鎖原因第一步就是執行”show engine innodb status“, 檢視innodb Standard monitor輸出結果，這裡面會有資料庫最後一次的死鎖記錄。會記錄出現死鎖的兩個事務，它們分别在等待什麼鎖，并且手裡持有什麼鎖。mysql在檢測到發生死鎖的時候，會随機復原其中的一個事務，進而解開死鎖。下面的截圖是發生死鎖的時候innodb status截圖(和業務相關的資料已脫敏，這裡均用column_n和value_n表示)

Transaction1:

Transaction2:

現象闡述：從上方兩個截圖可以發現，死鎖均發生在insert on duplicate key update語句執行的時候，并且每個insert語句均為批量插入多個資料。對于事務一，可以看到事務一在等待某個鎖的擷取，且這個鎖是"lock_mode X locks gap before rec insert intention waiting"，直接翻譯過來就是插入意向鎖在等待排他gap鎖的釋放，也就是隻有排他gap鎖釋放後插入意向鎖才能擷取到（關于這些鎖的含義見下一節）。對于事務二，同樣可以看到相同的一句話。并且兩個事務的鎖沖突均發生在”唯一索引“上。再進一步觀察可以看到，事務二所持有（"Holds the Locks"下方展示的索引值）的排它鎖所在的索引（鎖均是加在索引上或者索引區間上的），與事務一等待擷取鎖的索引是一樣的。進一步展示了的确，在同一個索引上出現了一個等待擷取，一個已經擷取的沖突現象。

相關概念：

在分析問題前，有必要概述一下（詳細了解可以見附錄），這裡面涉及到的鎖相關知識。具體可以詳見

Mysql手冊

innodb級鎖按照隔離能力，主要分為共享鎖（S鎖）和排他鎖（X鎖）。事務T1的某行上持有S鎖，則另一事務T2可以在此行擷取S鎖，但是不能擷取此行的X鎖，而如果T1在某行上持有X鎖，則另一事務T2，對此行既無法擷取S鎖，也無法擷取X鎖。（除了S和X鎖外，還有表級鎖，分别是意向共享IS鎖和意向排他IX鎖，這裡不做深入）。

按照鎖的種類：主要有四種。

1. Record鎖：這種鎖會在索引上加鎖，比如sql為select column_1 from table where column_1=1 for update，且column_1上有索引，則會把colunm_1為1的行都加排它鎖，其他事務禁止對此行讀和寫。

2. Gap鎖（間隙鎖）：這種鎖作用在索引記錄之間。目的隻需要記住：他是為防止其他事務插入間隙（包括防止insert方式插入新資料到間隙，以及update方式将其他行變更到此間隙）。Gap鎖可以有效的防止”幻讀“（因為這些間隙都被上了鎖，其他事務不可能再插入資料到這些間隙中，于是目前事務在連續進行”目前讀“時，每次讀到的都是相同的記錄）。雖然Gap鎖隻作用在隔離級别為RR及以上的資料庫上，但是不意味着隔離等級為RC級别的不會使用，在RC級别，在進行外鍵限制檢測和唯一鍵限制檢測的時候，會使用到Gap鎖，而正是這個duplicate-key checking導緻了上文出現的死鎖發生。關于Gap鎖到底是如何加鎖的，

可以參閱這篇文章

。

3. Next-Key鎖：本質上就是Gap鎖和Record鎖的結合，鎖住索引外還要鎖住索引的間隙。再具體一些就是，一個record鎖，加上，位于此索引記錄前的第一個間隙處的間隙鎖。舉個簡單的例子就是，如果現在有一個索引包含三個值1，3，5，則next-key lock鎖，可能鎖住的範圍就有(-∞,1],(1,3],(3,5],(5,+∞]。同樣在next-key lock一般作用在RR隔離等級的資料庫，但是當出現在insert時候，檢測到唯一鍵沖突的時候，會在沖突所在唯一索引出和之前的間隙處加Next-key lock.

mysq官方手冊中，對Next-key lock在innodb monitor中的列印如下圖所示：

可以發現和我們在”問題的現象“一節貼的日志中事務二”Hold the Locks“處非常相似。是以可以懷疑當時死鎖發生的時候，出現了排他的next-key lock。

4. Insert Intention鎖（插入意向鎖）：顧名思義，這個鎖是在資料插入之前會加此鎖。它是一種輕量的Gap鎖，同僚也是意向排他鎖的一種。它的存在使得多個事務在寫入不同資料到統一索引間隙的時候，不會發生鎖等待。另外由于它是一種意向插入鎖，是以當排他鎖已經處于間隙上的時候，根據鎖的相容矩陣，可以知道，意向插入鎖必須等待此間隙上的排它鎖釋放，才能擷取。

根據上面，對鎖的種類說明，其實我們已經能猜到，大概是什麼鎖導緻了死鎖的出現。（這裡我要再明确一點，我們的資料庫隔離等級為Read Committed級别。）本質上就是兩個事務同時擷取到了不同間隙的X Next-key鎖，而這個兩個事務又同時想要向對方已經擷取了next-key鎖的間隙内插入新的資料，于是乎死鎖出現了。下面我們來完全複現一下。

問題的複現：

資料庫準備：資料庫中能夠包含一個unique key： code

CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `code` int(11) NOT NULL,
  `other` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB            

初始資料：insert into test2 (code, other) values(1,1),(3,3),(5,5)

複現場景：原始的code字段為1,3, 5，現在要在中間插入code為2，3，4，5的row, 如果碰到唯一鍵限制則更新other字段。

Time	Session1	Session2
T1	start transaction; insert into test2(code, other) values (3, 4) on duplicate key update other = VALUES(other);
T2	insert into test2(code, other) values (5, 6) on duplicate key update other = VALUES(other);
T3	insert into test2(code, other) values (4, 4) on duplicate key update other = VALUES(other) 現象：一直阻塞
T4	insert into test2(code, other) values (2, 2) on duplicate key update other = VALUES(other); 現象：出現死鎖，事務復原 (2,2插入失敗， 且code為5時，更新other字段失敗，更新和插入均丢失)

死鎖出現後，我們檢視innodb status中的死鎖記錄，如下：

可以發現，複現出來的結果，和上文中的案例幾乎完全一緻。下面我們對此結果進行分析。

問題的分析：

1. 在T1時刻Session1執行完insert操作後，由于插入的code=2已經存在于表中，發生了唯一鍵沖突，是以觸發了duplicate-checking，導緻在(1,3]這個區間加上了next-key lock。這裡，我為了進一步證明确實隻有(1,3]這個區間加了鎖。在T1時刻執行完後，驗證插入code=0/4/6的資料可以在Session2中執行成功。同時這個時候Session2中可以修改code=1的資料，如update test2 set other=0 where code=1可以執行成功（當然你不能update test2 set code=2 where code=1,因為這個操作是在向(1,3]的間隙内插入了資料，違反了gap鎖的要求）。同時我們可以證明這時code=3肯定是被排他鎖鎖住的，由于當出現唯一鍵沖突時，就會執行on duplicate key update，更新other字段，是以code=3一定在更新結束後處于排它鎖鎖定狀态（補充說明：可以證明如果是共享鎖的話，session2在T2時刻執行insert into test2(code, other) values (3, 33)語句的話，一定會立刻包duplicate error而不會阻塞。但是事實上如果Session2在T2時刻執行這句sql，會一直阻塞，進一步說明code=3加的是排它鎖。另外需要注意的是，其實我目前隻能非常确定code = 3是有排它鎖，但是(1,3)上面，到底是S gap lock 還是X gap lock無法确定，不過無論是S還是X,不影響後續的解釋。）
2. 在T2 完成時，同理也會在（3，5]這個區間上X next-key lock (在上面的截圖中也可以看到插入code=5後，正在插入code=2的時候，寫着HOLD the lock hex 80000005)
3. 當T1和T2執行完成之後，我們可以看到(1,3] 和（3,5]分别被Session1和Session2鎖定，T3時候，Session1嘗試插入code=4, 由于在插入前會加插入意向鎖，（對于插入意向鎖的鎖的範圍，我目前尚無法确認在3~5的區間内加鎖的時候，左右臨界的開合問題）但是很明顯，插入意向鎖一定和(3,5]區間的next-key lock有重合，是以會出現在Session1執行T3的時候，語句被阻塞了，它在等待Session2釋放(3,5]這個區間的X next_key lock 。可以參考下圖——一個非常詳細的鎖相容矩陣，了解阻塞原因（ 相容矩陣圖連結 ）。

   

4. 同理，在T4時刻Session2執行插入語句的時候，由于（1,3]被阻塞了，但是插入的時候又要請求1~3這個區間的插入意向鎖，等待Session1釋放X next-key lock。于是乎死鎖發生，Session2被復原。

至此：死鎖的現象可以順利的解釋通。（當然，這裡還有一個疑惑不是很明白，當出現唯一沖突的時候為什麼要加Next-Key Lock。有知道原因的小夥伴可以告訴我）

問題的拓展：

1. 如果将insert on duplicate key update換成insert ignore語句，是否可以避免死鎖的發生呢？答案是：否定的。其實原理都是一樣的。如果我們将上述複現中的insert on duplicate key update換成insert ignore，同樣會在T4時刻出現死鎖。
2. 同樣，update和insert on duplicate key update組合也可以構造出死鎖的出現。資料庫中表結構不變，資料初始化為(1,1,1),(3,3,3),(5,5,5) 分别對應id, code,other, id是pk.

	update test2 set other=1 where id=3;
	insert into test2(code, other) values (5, 55) on duplicate key update other = VALUES(other);
	update test2 set other=1 where id=5;
	insert into test2(code, other) values (3, 33) on duplicate key update other = VALUES(other); 現象：出現死鎖，復原 (2,2插入失敗)

總結：

說了這麼多，死鎖的原因找到了，解決的辦法其實比較簡單。

1. 将批量insert on duplicate key update,拆分成多個語句。保證一次事務中不要插入過多值，将多個資料，變成多個sql，執行插入。可以有效的減少死鎖命中的發生。
2. 重試：死鎖不可怕，當出現死鎖發生時，多執行重試操作可以有效保證插入成功，更新不丢失。

參考文章：

1. https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-insert-intention-locks
2. https://dba.stackexchange.com/questions/237549/gap-locking-in-read-committed-isolation-level-in-mysql
3. https://www.cnblogs.com/crazylqy/p/7773492.html
4. https://www.jianshu.com/p/dca007208a58
5. https://my.oschina.net/actiontechoss/blog/3068976
6. https://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html
7. https://www.cnblogs.com/zhoujinyi/p/3435982.html
8. http://thushw.blogspot.com/2010/11/mysql-deadlocks-with-concurrent-inserts.html