MySQL鎖（四）行鎖的加鎖規則和案例
在上一篇文章，我們學習了間隙鎖和next-key lock，但是不知道怎麼加鎖，有哪些規則。間隙鎖的概念不太好理解，尤其是配合上行鎖後，很容易在判斷是否會出現鎖等待的問題上犯錯。
今天我們就來學習一下加鎖規則吧。
在學習前要說明一點，以下的規則只限於版本範圍：5.x系列<=5.7.24，8.0系列<=8.0.13。
加鎖規則
這個加鎖規則包含兩個「原則」、兩個「優化」和一個「bug」。
- 原則1：加鎖的基本單位是next-key lock。希望你還記得，next-key lock是前開後閉區間。
- 原則2：查找過程中訪問到的對象才會加鎖。
- 優化1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化為行鎖。
- 優化2：索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock退化為間隙鎖。
- 一個bug：唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。
下面以表t為例來介紹一下這些規則。表t的建表語句和初始化語句如下。
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);
案例1：等值查詢間隙鎖
圖1 等值查詢間隙鎖
分析：
- 步驟1：根據原則1，加鎖(5, 10]
- 步驟2：根據優化2，id=10不滿足查詢條件，因此退化為間隙鎖(5, 10)
結論：
- Session B阻塞是因為id=8在間隙鎖(5, 10)內
- Session C可以執行是因為沒有鎖住id=10這行
案例2：非唯一索引等值鎖
圖2 非唯一索引等值鎖
分析：
- 步驟1：根據原則1，加鎖(0, 5]
- 步驟2：由於c是普通索引，因此還需要繼續遍歷，直到找到c=10，不滿足條件。根據原則2，訪問到的對象要加鎖，因此要加(5, 10]。
- 步驟3：同時根據優化2，這是一個等值查詢，向右遍歷的不滿足條件的第一個值10，(5, 10]要退化為間隙鎖(5, 10)
- 因此加鎖是索引c的next-key lock(0,5]和間隙鎖(5,10)
結論：
- Session B可以執行是因為加鎖的是索引c，而不是主鍵索引
- Session C阻塞是因為c的插入值是7，在間隙鎖(5, 10)範圍內
案例3：主鍵索引範圍鎖
圖3 主鍵索引範圍鎖
分析：
- 步驟1：根據原則1，加鎖(5, 10]
- 步驟2：根據優化1，退化為id=10的行鎖
- 步驟3：繼續遍歷，找到不滿足id<11的值id=15，加鎖(10, 15]
因此加鎖id=15的行鎖和id的next-key lock(10, 15]
結論：
- 插入id=13被阻塞：next-key lock (10, 15]
- 更新id=15被阻塞：next-key lock (10, 15]
案例4：非唯一索引範圍鎖
圖4 非唯一索引範圍鎖
分析：
- 步驟1：根據原則1，加鎖(5, 10]
- 步驟2：繼續遍歷，找到不滿足c<11的值c=15，根據原則2，加鎖(10, 15]
因此加鎖索引c (5, 10]和(10, 15]
結論：
- 插入c=8，被(5, 10]阻塞
- 更新c=15，被(10, 15]阻塞
案例5：唯一索引範圍鎖bug
圖5 唯一索引範圍鎖bug
分析：
- 步驟1：根據原則1，加鎖(10, 15]，再根據優化1，退化為id=15的行鎖
- 步驟2：向右遍歷，加鎖(10, 15]
- 步驟3：根據BUG，要訪問到不滿足條件的第一個值，即id=20，加鎖(15 ,20]。
因此加鎖為(10, 15]和(15, 20]
結論：
- 更新id=20阻塞，被(15, 20]鎖住
- 插入id=16阻塞，被(15, 20]鎖住
案例6：非唯一索引上存在”等值”的例子
mysql> insert into t values(30,10,30);
圖6 非唯一索引上存在”等值”的例子
分析：
- 步驟1：根據原則1，(c=5,id=5)到(c=10,id=10)這個next-key lock
- 步驟2：向右查找，直到碰到(c=15,id=15)這一行，循環才結束。根據優化2，這是一個等值查詢，向右查找到了不滿足條件的行，所以會退化成(c=10,id=10) 到 (c=15,id=15)的間隙鎖
因此加鎖(c=5,id=5)到(c=10,id=10)這個next-key lock和(c=10,id=10)到(c=15,id=15)這個間隙鎖
結論：
- 插入c=12阻塞，被(c=10,id=10)到(c=15,id=15)這個間隙鎖鎖住
- 更新c=15成功，沒有鎖住c=15
案例7：limit 語句加鎖
先插入一條記錄。
mysql> insert into t values(30,10,30);
圖7 limit 語句加鎖
分析：
- 步驟1：根據原則1，(5, 10]，因為c=10有兩條行，因此遍歷到這裡就結束
- 步驟2：因為是delete，因此加兩個行鎖（id=10和id=30）
因此加鎖c (5, 10)和兩個行鎖（id=10和id=30）
結論：
- 插入c=12成功，因為c=12沒有被鎖住
說明：這個例子對我們實踐的指導意義就是，在刪除數據的時候盡量加limit。
案例8：一個死鎖的例子
這個案例的目的是說明：next-key lock實際上是間隙鎖和行鎖加起來的結果。
圖8 一個死鎖的例子
分析：
- 步驟1：根據原則1，加鎖(5, 10]
- 步驟2：繼續遍歷，直到c=15不滿足條件，加鎖(10, 15]，根據優化2，退化為(10, 15)
結論：
- Session B在等待鎖，此時Session B已經加了間隙鎖(5, 10)，在等待加行鎖c=10。
- Session A插入c=8，也在等待鎖，從而導致死鎖
說明：next-key lock具體執行的時候，是要分成間隙鎖和行鎖兩段來執行的。
案例9：非唯一索引排序範圍鎖
圖9 非唯一索引排序範圍鎖
分析：
- 步驟1：先執行c=20，加鎖(15, 20]
- 步驟2：根據優化2，加間隙鎖(20, 25)
- 步驟3：再執行c=15，加鎖(10, 15]
- 步驟4：繼續向左遍歷，找到記錄id=10為止，加鎖(5, 10]
在掃描過程中，c=20、c=15、c=10這三行都存在值，由於是select *，所以會在主鍵id上加三個行鎖。
因此要加鎖索引c (5, 25)和三個行鎖（id=10，id=15，id=20）。
結論：
- 插入c=6，被c(5, 25)鎖住
案例10：不等號條件里的等值查詢
begin;
select * from t where id>9 and id<12 order by id desc for update;
在執行過程中，通過樹搜索的方式定位記錄的時候，用的是「等值查詢」的方法。
分析：
- 步驟1：根據原則1，加鎖 (10, 15]
- 步驟2：根據優化2，退化為(10, 15)
- 步驟3：向左遍歷，找到id=10，加鎖(5, 10]，繼續找到id=5為止，加鎖(0, 5]
案例11：in範圍鎖
begin;
select id from t where c in(5,20,10) lock in share mode;
分析：
說明：鎖是逐個逐個加的。
- 步驟1：先c=5，加(0, 5]和(5, 10)
- 步驟2：再c=10，加(5, 10]和(10, 15)
- 步驟3：後c=20，加(15, 20]和(20, 25)
間隙鎖是不互斥的，因為加鎖範圍是(0, 25)，除c=15外。
死鎖情況
select id from t where c in(5,20,10) order by c desc for update;
有一種情況，同時執行倒序語句，因為剛好同時執行，逐漸加鎖（倒序加鎖），會出現死鎖情況。
