MySQL 21 為什么我只改一行的語句，鎖這么多？

上篇文章中，介紹了間隙鎖和臨鍵鎖，但并未說明加鎖規(guī)則。本文首先介紹加鎖規(guī)則，由于間隙鎖在可重復讀隔離級別下才有效，因此接下來的內(nèi)容默認在可重復讀隔離級別下。

加鎖規(guī)則（限5.x系列<=5.7.24, 8.0系列<=8.0.13）：

原則1：加鎖的基本單位是臨鍵鎖，是一個前開后閉區(qū)間；
原則2：查找過程中訪問到的對象才會加鎖；
優(yōu)化1：索引上的等值查詢，給唯一索引加鎖的時候，臨鍵鎖退化為行鎖；
優(yōu)化2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，臨鍵鎖退化為間隙鎖；
一個bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。

后續(xù)例子用到的表：

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);

案例一：等值查詢間隙鎖

由于表中沒有id=7的記錄，用加鎖規(guī)則判斷：

根據(jù)原則1，加鎖單位是臨鍵鎖，session A加鎖范圍是(5,10]；
根據(jù)優(yōu)化2，這是一個等值查詢id=7，而id=10不滿足查詢條件，臨鍵鎖退化成間隙鎖，因此最終加鎖范圍是(5,10)。

所以session B要插入id=8的記錄會被鎖住，但是session C修改id=10這行是可以的。

案例二：非唯一索引等值鎖

這里session A要給索引c上c=5這一行加讀鎖：

根據(jù)原則1，加鎖單位是臨鍵鎖，因此會給(0,5]加臨鍵鎖；
由于c是普通索引，因此僅訪問c=5這一條記錄不能馬上停下來，需要向右遍歷查到c=10才放棄。根據(jù)原則2，訪問到的都要加鎖，因此給(5,10]加臨鍵鎖；
這個遍歷符合優(yōu)化2，由于最后一個值不滿足c=5這個等值條件，臨鍵鎖退化成間隙鎖；
根據(jù)原則2，只有訪問到的對象才會加鎖，這個查詢使用覆蓋索引，不需要訪問主鍵索引，所以主鍵索引上不加任何鎖，因此session B的update語句可以成功。

而session C的插入操作，會被session A的間隙鎖(5,10)鎖住。

在該案例中，lock in share mode只鎖覆蓋索引，但如果是for update就不同了，因為系統(tǒng)會認為接下來更新數(shù)據(jù)，會順便給主鍵索引上滿足條件的行加上行鎖。

該案例說明，鎖是加在索引上的，同時如果要用lock in share mode來給行加讀鎖避免數(shù)據(jù)被更新，就必須繞過覆蓋索引的優(yōu)化，在查詢字段中加入索引中不存在的字段，比如將session A的查詢語句改成select d from t where c=5 lock in share mode。

案例三：主鍵索引范圍鎖

考慮下面這兩條查詢語句，加鎖范圍是否相同：

select * from t where id=10 for update;
select * from t where id>=10 and id<11 for update;

在邏輯上，這兩條查詢語句等價，但加鎖規(guī)則不太一樣。看看第二個語句的加鎖效果：

分析session A的加鎖情況：

先找到第一個id=10的行，本該加臨鍵鎖(5,10]，根據(jù)優(yōu)化1，主鍵是唯一索引，因此該臨鍵鎖退化成行鎖，只加了id=10這一行的行鎖；
范圍查找會繼續(xù)往后找，找到id=15這一行停下來，因此會加臨鍵鎖(10,15]。

這里需要注意的是，session A定位查找id=10的行的時候，是當做等值查詢來判斷的，而向右掃描到id=15的時候，用的是范圍查詢判斷。

案例四：非唯一索引范圍鎖

由于索引c是非唯一索引，與案例三相比，沒有優(yōu)化規(guī)則，因此最終session A加的鎖是：索引c上的(5,10]和(10,15]這兩個臨鍵鎖。

案例五：唯一索引范圍鎖bug

session A是一個范圍查詢，按照原則1的話，應該是索引id上只加(10,15]這個臨鍵鎖，且由于id唯一，所以循環(huán)判斷到id=15這一行就應該停止。

但是實現(xiàn)上，InnoDB會往前掃描到第一個不滿足條件的行為止，即id=20，由于這是范圍掃描，因此索引id上的(15,20]這個臨鍵鎖也會被鎖上。

所以session B和session C的操作都會被鎖住。

案例六：非唯一索引上存在等值的例子

接下來的例子，是為了更好說明間隙的概念。這里插入一條新紀錄：

insert into t values(30,10,30);

新插入一行后，表里有兩個c=10的行。由于非唯一索引上包含主鍵的值，所以不存在完全相同的兩行，此時索引c：

索引c中兩個c=10的記錄之間，也是有間隙的。

接下來看例子：

session A在遍歷時，先訪問第一個c=10的記錄，根據(jù)原則1，會加(c=5,id=5)到(c=10,id=10)的臨鍵鎖。之后繼續(xù)向右查找，直到碰到(c=15,id=15)這一行，根據(jù)優(yōu)化2，這是一個等值查詢，向右查找到了不滿足條件的行，會退化成(c=10,id=10)到(c=15,id=15)的間隙鎖。

因此delete語句的加鎖范圍實際上如下：