最近遇到一個由于唯一性索引，導致并發插入產生死鎖的場景，在分析死鎖產生的原因時，發現這一塊還挺有意思的，涉及到MySQL中不少的知識點，特此總結記錄一下。

一、MySql常見的鎖

談到mysql的鎖，可以說的就比較多了，比如行鎖、表鎖、頁鎖、元數據鎖等，當然我們這里沒打算把所有的都細列出來，我們這里主要針對行鎖、gap鎖進行拓展，以方便分析第二節中，為什么并發插入同樣的數據會產生死鎖的問題

0. 鎖分類

我們最常說的鎖，可以區分為共享鎖(S)和排它鎖(X)，在mysql的innodb引擎中，為了解決幻讀問題，引入了gap鎖以及next key lock；除此之外，還有一種意向鎖的，比如插入意向鎖

本文將主要介紹的以下幾種鎖

• 行鎖(record lock): 請注意它是針對索引的鎖（所以如果沒有索引時，最終行鎖就會導致整個表都會被鎖住）
• 共享鎖(S Lock): 也叫讀鎖，共享鎖之間不會相互阻塞（顧名思義）
• 排它鎖(X Lock): 也叫寫鎖，排它鎖一次只能有一個session（或者說事務？）持有
• 間隙鎖(gap lock): 針對索引之間的間隙
• Next-key鎖（Next-key lock)：可以簡單理解為行鎖 + 間隙鎖

上面雖然介紹了幾種鎖的基本定義，但是什么時候是行鎖，怎樣獲取共享鎖，排它鎖又是在哪些場景下會產生呢？gap lock/next key lock又是怎樣解決幻讀的呢？

下面所有的都是基于mysql5.7.22 innodb引擎，rr隔離級別進行說明

1.共享鎖與排它鎖

下表介紹我們的實際使用的sql中，是否會使用鎖，以及會產生什么鎖

共享鎖與排他鎖區分

2. 行鎖、表鎖、gap鎖、next-key鎖區分

這幾個的區分，主要就是看我們最終鎖住的效果，比如

• 沒有索引，加S/X鎖最終都是鎖整表 （為啥？因為鎖是針對索引而言的）
• 根據主鍵/唯一鍵鎖定確定的記錄：行鎖
• 普通索引或者范圍查詢：gap lock / next key lock

行鎖和gap鎖之間最大的區別是：

• 行鎖針對確定的記錄
• 間隙鎖是兩個確定記錄之間的范圍； next key lock則是除了間隙還包括確定的記錄

3. 實例演示

看上面的兩個說明，自然就想在實際的case中操刀分析一下，不同的sql會產生什么樣的鎖效果

• 針對表中一條確定的記錄加X鎖，是只有行鎖嘛？
• 針對表中多條確定的記錄加X鎖，又會怎樣？
• 針對表中一條不存在的記錄加X鎖，會有鎖產生嗎？如果是gap鎖，那區間怎么定？
• 針對范圍加X鎖，產生的gap鎖范圍怎么確定呢？

在分析上面幾種case之前，我們得先有一個概念，鎖是針對索引而言的，這一點非常非常重要

其次不同的索引，我們需要分別進行測試（其實就是唯一索引與普通索引）

3.1 表準備

接下來針對上面的四種場景，設計我們的測試用例，首先我們準備三張表

• 無索引表 TN
• 唯一索引表 TU
• 普通索引表 TI

對應的表結構和初始化數據如下

CREATE TABLE `tn` (
  `id` int(11) unsigned NOT NULL,
  `uid` int(11) unsigned NOT NULL
) ENGINE=InnoDB;

CREATE TABLE `tu` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u_uid` (`uid`)
) ENGINE=InnoDB;

CREATE TABLE `ti` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `uid` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `u_uid` (`uid`)
) ENGINE=InnoDB;


INSERT INTO `tn` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);
INSERT INTO `tu` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);
INSERT INTO `ti` (`id`, `uid`) VALUES (1, 10), (5, 20), (10, 30);

3.2 精確匹配

即我們的sql可以精確命中某條記錄時，鎖的情況如下：

請注意上面的結論，無索引時鎖全表好理解，但是普通索引的TI表，居然還有一個[10, 30)的gap鎖就有點超乎我們的想象了；

接下來我們驗證一下

上圖基本流程如下：

從上面的實測也可以看出，普通索引下添加x鎖，居然會加一個gap鎖，而且這個gap區間是前一個記錄（并包含它），到下一個記錄

如 uid = 20， 前后兩個記錄為(1, 10), (10, 30)

• gap lock: 范圍為 [10, 30)
• 因此無法插入uid=[10,30)
• 注意，uid=10上有gap鎖只是不能插入記錄，但是加X鎖是沒有問題的（有興趣的可以測試一下）

3.3 精確查詢未匹配

當我們鎖的記錄不存在時，鎖情況如下：

實測case如下(TN省略，鎖全表的沒啥測試必要性)

基本流程就不畫圖了，上面圖中已經有文字描述了

從上面的測試也可以看出，uid=30沒有被鎖住，這里只在uid=(20, 30)這一區間添加了gap鎖

唯一索引與普通索引表現一致，會阻塞insert的插入意向鎖（后面說這個東西）

3.4 范圍查詢

當我們鎖一段區間時，鎖的情況如下：

簡單來說，范圍查詢時，添加next key lock，根據我們的查詢條件，找到最左邊和最右邊的記錄區間

如 uid > 15 and uid < 25，找到的記錄是(1, 10), (10, 30)

• gap鎖為(10, 30)
• next key lock會為右邊添加行鎖，即uid=30加X鎖
• 因此針對uid=30記錄加鎖會被阻塞（但是針對uid=28,29加x鎖則不會被阻塞，插入會，有興趣的小伙伴可以實測一下）

說明：范圍加x鎖時，可能鎖住不在這個區間的記錄，一不小心可能導致死鎖哦

3.5 小結

在RR隔離級別中，我們一般認為可以產生鎖的語句為:

• SELECT ... FOR UPDATE: X鎖
• SELECT ... LOCK IN SHARE MODE: S鎖
• update/delete: X鎖

| 普通索引 | 精確匹配，且命中 | 行鎖 + gap lock (上一個記錄和下個記錄區間，左閉右開，左邊記錄非行鎖) | 普通索引 | 精確匹配，未命中 | gap lock | | 普通索引 | 范圍查詢 | next key lock |

4. 鎖沖突

上面介紹了不同場景下會產生什么樣的鎖，但是看完之后會有一個疑問，針對行鎖其他會話競爭的時候，可以按照X/S鎖的規則來，但是這個GAP LOCK貌似只針對insert有效，insert除了加X鎖之外是不是還有其他的特殊邏輯？

4.1 插入意向鎖

插入意向鎖其實是一種特殊的 gap lock，但是它不會阻塞其他鎖。假設存在值為 4 和 7 的索引記錄，嘗試插入值 5 和 6 的兩個事務在獲取插入行上的排它鎖之前使用插入意向鎖鎖定間隙，即在（4，7）上加 gap lock，但是這兩個事務不會互相沖突等待；但是如果這個區間存在gap lock，則會被阻塞；如果多個事務插入相同數據導致唯一沖突，則在重復的索引記錄上加讀鎖

簡單來說，它的屬性為：

• 它不會阻塞其他任何鎖；
• 它本身僅會被 gap lock 阻塞

其次一個重要知識點：

• 通常insert語句，加的是行鎖，排它鎖
• 在insert之前，先通過插入意向鎖，判斷是否可以插入（僅會被gap lock阻塞）
• 當插入唯一沖突時，在重復索引上添加讀鎖
  • 原因如下：
  • 事務1 插入成功未提交，獲取了排它鎖，但是事務1最終可能會回滾，所以其他重復插入事務不應該直接失敗，這個時候他們改為申請讀鎖（疑問點：為什么要改成讀鎖呢？）

4.2 鎖沖突矩陣

簡單版矩陣

當我們將gap lock(間隙鎖), next key lock(next-key鎖), Insert Intention lock(插入意向鎖)也加入矩陣時，就會復雜很多了

說明：

• not gap: 行鎖
• gap: gap lock
• next-key: gap + 行鎖

小結：

針對上面的矩陣，理解下面幾個原則即可推導上面矩陣

• gap lock只會與插入意向鎖沖突
• X行鎖會與行鎖沖突
• next key lock: 行鎖 + gap鎖 鎖區間內，插入沖突； 行鎖的X鎖沖突

二、并發插入死鎖分析

上面屬于基本知識點，接下來我們看一個實際導致死鎖的case

• 并發插入相同記錄導致死鎖

0. 表準備

創建一個最簡單最基礎的表，用于演示

CREATE TABLE `t` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `t` (`id`) VALUES (1);

1. 事務回滾的死鎖問題

場景復現:

step1：

-- session1: 
begin; insert into t values (2);

-- session2:
begin; insert into t values (2);
-- 阻塞

-- session3:
begin; insert into t values (2);
-- 阻塞

step2：

-- session1:
rollback;

原因分析：

死鎖日志查看

SHOW ENGINE INNODB STATUS;

step1：

• session1: 插入(id=2)，會添加一個X + Next Lock鎖
• session2/3: 插入(id=2)，插入意向鎖被阻塞，改為持有S + Next Lock鎖

step2：

• session1: 回滾，釋放X鎖
• session2/3: 競爭X鎖，只有對方釋放S鎖，才能競爭成功；相互等待，導致死鎖

2. delete導致死鎖問題

和前面操作基本一致，只是第一個會話是刪除記錄

step1：

-- session1: 
begin; delete from t where id=1;

-- session2:
begin; insert into t values (1);
-- 阻塞

-- session3:
begin; insert into t values (1);
-- 阻塞

step2：

-- session1:
commit;

原因分析和前面基本一致

3. insert加鎖邏輯

insert中對唯一索引的加鎖邏輯

1. 先做UK沖突檢測，如果存在目標行，先對目標行加S Next Key Lock（該記錄在等待期間被其他事務刪除，此鎖被同時刪除）
2. 如果1成功，對對應行加X + 插入意向鎖
3. 如果2成功，插入記錄，并對記錄加X + 行鎖（有可能是隱式鎖）

根據上面這個的邏輯，那么就會有一個有意思的死鎖場景

step1：

-- session1
begin; delete from t where id = 1;


-- session2
begin; delete from t where id = 1;

step2：

-- session1
insert into t values(1)

對應的死鎖日志

關于這個場景詳情博文可以參考：記錄一次Mysql死鎖排查過程

4. 怎么避免死鎖呢?

• 將大事務拆成小事務
• 添加合理的索引，走索引避免為每一行加鎖，降低死鎖的概率
• 避免業務上的循環等待（如加分布式鎖之類的）
• 降低事務隔離級別（如RR -> RC 當然不建議這么干）
• 并發插入時使用replace/on duplicate也可以避免死鎖

三、總結

盡信書則不如，以上內容，純屬一家之言，因個人能力有限，難免有疏漏和錯誤之處，如發現bug或者有更好的建議，歡迎批評指正，不吝感激。

作者：一灰灰
鏈接：https://juejin.cn/post/6927197371227095047
來源：掘金