一文帶你弄懂 MySQL 的加鎖規(guī)則！

作者：陳樹義 2023-03-27 17:58:34

在這些文章中，我們大致了解了一些加鎖的情況。但實(shí)際上 MySQL 的加鎖規(guī)則是怎樣的，我還不是特別清楚。所以今天我們就來(lái)深入了解下 MySQL 的加鎖規(guī)則。

?大家好，我是樹哥。

在之前的文章里，我們討論了關(guān)于 MySQL 的許多問題，包括：

MySQL 啥時(shí)候用表鎖，啥時(shí)候用行鎖？
MySQL 不同隔離級(jí)別，都使用了什么鎖？
MySQL 啥時(shí)候用記錄鎖，啥時(shí)候用間隙鎖？

MySQL 的加鎖規(guī)則到底是怎樣的？

迷霧找真相

為了弄清楚這些加鎖規(guī)則，我查閱了許多資料。但在這些資料中，我覺得比較有質(zhì)量的只有兩個(gè)：一個(gè)是極客時(shí)間《MySQL 45 講》第 20/21 節(jié)講得內(nèi)容，另一個(gè)是一篇從源碼角度解析加鎖規(guī)則的文章。

《MySQL 45 講》是丁奇老師出的一個(gè)專欄，現(xiàn)在是騰訊云數(shù)據(jù)庫(kù)負(fù)責(zé)人。在該專欄的第 21、22 節(jié)中講到了具體的加鎖規(guī)則，并且也舉了非常多的例子。本文也將摘取其中一些內(nèi)容，來(lái)跟大家討論學(xué)習(xí)。

另一篇從源碼角度講加鎖規(guī)則的，是網(wǎng)名為「小孩子」的網(wǎng)友寫得一篇文章，其后續(xù)出了一本書叫《從根上了解 MySQL》，內(nèi)容非常多并且很詳細(xì)。這篇文章從源碼角度從頭到尾分析了整個(gè)加鎖規(guī)則，講得還是比較詳細(xì)。

在看著兩份資料之前，我總是嘗試去找到一個(gè)簡(jiǎn)單好記的加鎖規(guī)律，但看完之后覺得：這或許不太可能。丁奇大神在其專欄也提到他是怎么去分析加鎖規(guī)則的。

首先說(shuō)明一下，這些加鎖規(guī)則我沒在別的地方看到過有類似的總結(jié)，以前我自己判斷的時(shí)候都是想著代碼里面的實(shí)現(xiàn)來(lái)腦補(bǔ)的。這次為了總結(jié)成不看代碼的同學(xué)也能理解的規(guī)則，是我又重新刷了代碼臨時(shí)總結(jié)出來(lái)的。

可以看到，就連大神也是想著代碼腦補(bǔ)加鎖規(guī)律的。再結(jié)合「小孩子」從源碼角度去分析加鎖規(guī)則，我一下子就覺得：或許還是該深入到源碼角度，才能一窺真相。

即使后面丁奇老師為了方便我們理解，也總結(jié)出了一些加鎖（如下圖所示）。但實(shí)際上這些加鎖規(guī)則也沒啥規(guī)律，只能是記著就好。此外，他也提出：我們需要用動(dòng)態(tài)的眼光去看加鎖。言外之意就是，這些規(guī)則可能都是變化的，也不一定是完全正確的。

圖片來(lái)自極客時(shí)間專欄

看到這里，我會(huì)想：那我們應(yīng)該怎么學(xué)習(xí) MySQL 的加鎖規(guī)則呢？

我思考了片刻，給出的答案是：我們可以按照丁奇老師總結(jié)出的加鎖規(guī)則先行學(xué)習(xí)，后續(xù)再深入源碼層面不斷地補(bǔ)足一些細(xì)節(jié)。

MySQL 加鎖全局視角

在講一些具體加鎖規(guī)則之前，我覺得有必要先給大家一個(gè) MySQL 加鎖的全局視角。這個(gè)是丁奇老師在文章中沒講到的，但我覺得如果不知道全局視角，那么會(huì)影響到對(duì)一些規(guī)則的理解。

我們知道 MySQL 分成了 Server 層和存儲(chǔ)引擎兩部分，每當(dāng)執(zhí)行一個(gè)查詢時(shí)，Server 層負(fù)責(zé)生成執(zhí)行計(jì)劃，然后交給存儲(chǔ)引擎去執(zhí)行。其整個(gè)過程可以這樣描述：

Server 層向 Innodb 獲取到掃描區(qū)間的第 1 條記錄。
Innodb 通過 B+ 樹定位到掃描區(qū)間的第 1 條記錄，然后返回給 Server 層。
Server 層判斷是否符合搜索條件，如果符合則發(fā)送給客戶端，不負(fù)責(zé)則跳過。接著繼續(xù)向 Innodb 要下一條記錄。
Innodb 繼續(xù)根據(jù) B+ 樹的雙休鏈表找到下一條記錄，會(huì)執(zhí)行具體的 row_search_mvcc 函數(shù)做加鎖等操作，返回給 Server 層。
Server 層繼續(xù)處理該條記錄，并向 Innodb 要下一條記錄。
繼續(xù)不停執(zhí)行上述過程，直到 Innodb 讀到一條不符合邊界條件的記錄為止。

通過上面這個(gè)過程，我想讓大家明白兩個(gè)重要的認(rèn)識(shí)：

Innodb 并不是一次性把所有數(shù)據(jù)找到，然后返回給 Server 層的，而是會(huì)循環(huán)很多次。
row_search_mvcc 這個(gè)函數(shù)是做具體的加鎖、加什么鎖的重要邏輯，并且由于 Server 層與 Innodb 會(huì)循環(huán)多次，因此該函數(shù)也是會(huì)執(zhí)行多次的。

弄懂了上面兩個(gè)認(rèn)識(shí)，會(huì)對(duì)后續(xù)大家理解有很大幫助。例如：對(duì)于 select * from user where id >= 5 進(jìn)行分析的時(shí)候，為什么會(huì)出現(xiàn)說(shuō)第一次加鎖是精確查詢？它明明是范圍查詢呀！這是因?yàn)榈谝淮问且獙ふ业?id = 5 的記錄，對(duì)于 Innodb 來(lái)說(shuō)，它就是精確查找，不是范圍查找。隨后找到 id = 5 的記錄之后，就要找 id > 5 的記錄了，此時(shí)就變成了范圍查找了。

MySQL 加鎖規(guī)則

這里的加鎖規(guī)則，我直接引用丁奇老師的總結(jié)：兩個(gè)原則、兩個(gè)優(yōu)化、一個(gè) bug。

原則 1：加鎖的基本單位是 next-key lock。其中 next-key lock 是前開后閉區(qū)間，例如：(2, 5]。
原則 2：查找過程中訪問到的對(duì)象才會(huì)加鎖。
優(yōu)化 1：索引上的等值查詢，給唯一索引加鎖的時(shí)候，next-key lock 退化為行鎖。
優(yōu)化 2：索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock 退化為間隙鎖。
一個(gè) bug：唯一索引上的范圍查詢會(huì)訪問到不滿足條件的第一個(gè)值為止。

對(duì)于原則 1 說(shuō)的：加鎖的基本單位是 Next-Key 鎖，意思是默認(rèn)都是先加上 Next-Key，之后根據(jù) 2 個(gè)優(yōu)化點(diǎn)選擇性退化為行鎖或間隙鎖。

對(duì)于原則 2 說(shuō)的：訪問到的對(duì)象才會(huì)加鎖，意思是如果直接索引覆蓋到了，不需要回表，那么就不會(huì)對(duì)聚簇索引加鎖。這樣的話，其他事務(wù)就可以對(duì)聚簇索引進(jìn)行操作，而不會(huì)阻塞。

為了解釋這些規(guī)則，建立表 t 并插入一些數(shù)據(jù)。

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

等值查詢間隙鎖

如下圖所示的例子，是一個(gè)等值條件加間隙鎖的例子。

圖片來(lái)自極客時(shí)間專欄

在事務(wù) A 中，要查找 id = 7 的記錄，其查找過程為：從左到右查找 id 聚簇索引，依次對(duì)比 0、5 兩個(gè)索引，發(fā)現(xiàn)不對(duì)。接著，對(duì)比 10 這個(gè)索引，發(fā)現(xiàn) 7 <10，于是停止搜索。根據(jù)原則 1 默認(rèn)給其加上一個(gè) Next-Key 鎖，即 (5, 10]。根據(jù)優(yōu)化 2 退化為間隙鎖，即 (5,10)。

所以，session B 要插入 id=8 的記錄會(huì)被鎖住，而 session 修改 id=10 這行是可以的。

非唯一索引等值鎖

圖片來(lái)自極客時(shí)間專欄

在事務(wù) A 中，要查找 c=5 的記錄，其中 c 是非唯一索引。其查找過程為：從左到右查找 c 索引，找到了 c=5 的索引，根據(jù)原則 1 對(duì)其加 Next-Key 鎖，即 (0,5]。

由于普通索引可能重復(fù)，因此其還會(huì)繼續(xù)往后搜索，接著搜索到 10，根據(jù)原則 2，訪問到的都要加鎖，因此再給其加 Next-Key 鎖，即 (5,10]。由于這個(gè)還負(fù)責(zé)優(yōu)化 2：等值判斷，向右遍歷，最后一個(gè)不滿足等值條件，因此退化為間隙鎖 (5,10)。

此外，根據(jù)原則 2，只有訪問到的對(duì)象才會(huì)加鎖。這個(gè)查詢使用查詢覆蓋索引，并不需要訪問主鍵索引，所以主鍵索引上沒有加任何鎖。也就是說(shuō) (0,5] 和 (5,10) 這兩個(gè)鎖，只在索引 c 上加鎖，并不在主鍵索引上加鎖，因此 session B 可以執(zhí)行。

session C 中插入一個(gè) c 為 7 的值，c 為 7 的值在 (5,10) 之間，因此會(huì)被鎖住。

主鍵索引范圍鎖

對(duì)于我們這個(gè)表 t，下面這兩條查詢語(yǔ)句，加鎖范圍相同嗎？

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

在邏輯上，這兩條查語(yǔ)句肯定是等價(jià)的，但是它們的加鎖規(guī)則不太一樣。現(xiàn)在，我們就讓 session A 執(zhí)行第二個(gè)查詢語(yǔ)句，來(lái)看看加鎖效果。

圖片來(lái)自極客時(shí)間專欄

我們來(lái)分析一下整體的加鎖規(guī)則吧。

事務(wù) A 開始執(zhí)行的時(shí)候，要找到 id 為 10 的記錄，于是從左到右找到了 id 為 10 的索引。根據(jù)原則 1 會(huì)給其加 Next-Key 鎖，即 (5,10]。根據(jù)優(yōu)化 1，id = 10 是等值查詢，因此其退化為行鎖，即只對(duì) id = 10 這行加了行鎖。

接著繼續(xù)進(jìn)行范圍查找，找到 id=15 這一行，繼續(xù)加 Next-Key 鎖 (10,15]。這時(shí)候 id=15 大于 11，因此其不再查找。TODO

非唯一索引范圍鎖

下面的 c 字段是非唯一普通索引，使用了范圍查詢。

圖片來(lái)自極客時(shí)間專欄

事務(wù) A 開始執(zhí)行的時(shí)候，要找到 id 為 10 的記錄，于是根據(jù)原則 1 加了 Next-Key 鎖，即 (5,10]。由于索引 C 是非唯一索引，沒有優(yōu)化規(guī)則，因此不會(huì)退化為行鎖。因此對(duì)于事務(wù) A 來(lái)說(shuō)，索引 C 上加的是 (5,10] 和 (10,15] 兩個(gè) Next-Key 鎖。

所以當(dāng) session B 和 session C 要操作 c 值為 8 和 15 的數(shù)據(jù)時(shí)會(huì)被阻塞。