mysql并發insert死鎖問題——gap、插入意向鎖沖突

mysql并發insert死鎖問題——gap、插入意向鎖沖突

問題描述

線上出現MySQL死鎖報警，通過show engine innodb status指令檢視死鎖日志，結合異常代碼，還原發生死鎖的事務場景如下：

環境： mysql5.7,事務隔離級别REPEATABLE-READ

表結構

CREATE TABLE `ta` (

  `id` int AUTO_INCREMENT,

  `a` int,

  `b` int ,  `c` int ,

  PRIMARY KEY (`id`),

  UNIQUE KEY `ux_a_b` (`a`,`b`)

) ENGINE=InnoDB

資料：

mysql> select * from ta;

+----+------+------+------+

| id | a    | b    | c    |

|  1 |    1 |   10 |  100 |

|  2 |    3 |   20 |   99 |

|  3 |    5 |   50 |   80 |

并發事務

T1

T2

begin；

begin

delete from ta where a = 4;//ok, 0 rows affected

delete from ta where a = 4; //ok, 0 rows affected

insert into ta(a,b,c) values(4, 11, 3),(4, 2, 5);//wating,被阻塞

insert into ta(a,b,c) values(4, 11, 3),(4, 2, 5); //ERROR 1213 (40001): Deadlock found when trying to get lock;

T1執行完成， 2 rows affected

從上面可以看出，并發事務都成功執行delete後（影響行數為0），執行insert出現死鎖。

死鎖分析

檢視死鎖日志，顯示事務T1的insert語句在等待插入意向鎖，<code>lock_mode X locks gap before rec insert intention waiting</code>;事務T2持有a=4的gap lock，同時也在等待插入意向鎖。另外，T1能執行delete，說明它也拿到了gap lock，是以，兩個事務都持有gap lock，導緻循環等待插入意向鎖而發生死鎖。

delete的where子句沒有滿足條件的記錄，而對于不存在的記錄 并且在RR級别下，delete加鎖類型為gap lock，gap lock之間是相容的，是以兩個事務都能成功執行delete；關于gap lock可以參考文章加鎖分析。這裡的gap範圍是索引a列(3,5)的範圍。

insert時，其加鎖過程為先在插入間隙上擷取插入意向鎖，插入資料後再擷取插入行上的排它鎖。又插入意向鎖與gap lock和 Next-key lock沖突，即一個事務想要擷取插入意向鎖，如果有其他事務已經加了gap lock或 Next-key lock，則會阻塞。

場景中兩個事務都持有gap lock，然後又申請插入意向鎖，此時都被阻塞，循環等待造成死鎖。

鎖相容矩陣：

死鎖解決

方案如下幾種選擇：

不采用事務包裝這部分邏輯，本文實際業務場景中可以不需要事務，是以直接取消事務包裝即可，采用insert ON DUPLICATE KEY UPDATE的方式

調整事務隔離級别為read commit，RC級别不會産生gap lock

利用分布式鎖

附：排查過程

檢視死鎖日志，注意這裡并不會包含整個事務的相關sql，僅僅會把等待鎖的SQL列印出來，死鎖日志内容含義參考 ：http://blog.itpub.net/22664653/viewspace-2145133/

根據服務異常log定位到具體事務執行代碼，找出該事務相關的sql

根據積累的經驗知識分析加鎖、鎖等待情況，找出死鎖原因

參考

insert加鎖分析，參考文章http://www.aneasystone.com/archives/2017/12/solving-dead-locks-three.html