一文帶你弄懂 MySQL 的加鎖規則！

大家好，我是樹哥。

在之前的文章裏，我們討論了關於 MySQL 的許多問題，包括：

  1. MySQL 啥時候用表鎖，啥時候用行鎖？

  2. MySQL 不同隔離級別，都使用了什麼鎖？

  3. MySQL 啥時候用記錄鎖，啥時候用間隙鎖？

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

MySQL 的加鎖規則到底是怎樣的？

迷霧找真相

爲了弄清楚這些加鎖規則，我查閱了許多資料。但在這些資料中，我覺得比較有質量的只有兩個：一個是極客時間《MySQL 45 講》第 20/21 節講得內容，另一個是一篇從源碼角度解析加鎖規則的文章。

《MySQL 45 講》是丁奇老師出的一個專欄，現在是騰訊雲數據庫負責人。在該專欄的第 21、22 節中講到了具體的加鎖規則，並且也舉了非常多的例子。本文也將摘取其中一些內容，來跟大家討論學習。

另一篇從源碼角度講加鎖規則的，是網名爲「小孩子」的網友寫得一篇文章，其後續出了一本書叫《從根上了解 MySQL》，內容非常多並且很詳細。這篇文章從源碼角度從頭到尾分析了整個加鎖規則，講得還是比較詳細。

在看着兩份資料之前，我總是嘗試去找到一個簡單好記的加鎖規律，但看完之後覺得：這或許不太可能。丁奇大神在其專欄也提到他是怎麼去分析加鎖規則的。

首先說明一下，這些加鎖規則我沒在別的地方看到過有類似的總結，以前我自己判斷的時候都是想着代碼裏面的實現來腦補的。這次爲了總結成不看代碼的同學也能理解的規則，是我又重新刷了代碼臨時總結出來的。

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

即使後面丁奇老師爲了方便我們理解，也總結出了一些加鎖（如下圖所示）。但實際上這些加鎖規則也沒啥規律，只能是記着就好。此外，他也提出：我們需要用動態的眼光去看加鎖。言外之意就是，這些規則可能都是變化的，也不一定是完全正確的。

看到這裏，我會想：那我們應該怎麼學習 MySQL 的加鎖規則呢？

我思考了片刻，給出的答案是：我們可以按照丁奇老師總結出的加鎖規則先行學習，後續再深入源碼層面不斷地補足一些細節。

MySQL 加鎖全局視角

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

我們知道 MySQL 分成了 Server 層和存儲引擎兩部分，每當執行一個查詢時，Server 層負責生成執行計劃，然後交給存儲引擎去執行。其整個過程可以這樣描述：

  1. Server 層向 Innodb 獲取到掃描區間的第 1 條記錄。

  2. Innodb 通過 B+ 樹定位到掃描區間的第 1 條記錄，然後返回給 Server 層。

  3. Server 層判斷是否符合搜索條件，如果符合則發送給客戶端，不負責則跳過。接着繼續向 Innodb 要下一條記錄。

  4. Innodb 繼續根據 B+ 樹的雙休鏈表找到下一條記錄，會執行具體的 row_search_mvcc 函數做加鎖等操作，返回給 Server 層。

  5. Server 層繼續處理該條記錄，並向 Innodb 要下一條記錄。

  6. 繼續不停執行上述過程，直到 Innodb 讀到一條不符合邊界條件的記錄爲止。

通過上面這個過程，我想讓大家明白兩個重要的認識：

  1. Innodb 並不是一次性把所有數據找到，然後返回給 Server 層的，而是會循環很多次。

  2. row_search_mvcc 這個函數是做具體的加鎖、加什麼鎖的重要邏輯，並且由於 Server 層與 Innodb 會循環多次，因此該函數也是會執行多次的。

弄懂了上面兩個認識，會對後續大家理解有很大幫助。例如：對於 select * from user where id >= 5 進行分析的時候，爲什麼會出現說第一次加鎖是精確查詢？它明明是範圍查詢呀！這是因爲第一次是要尋找到 id = 5 的記錄，對於 Innodb 來說，它就是精確查找，不是範圍查找。隨後找到 id = 5 的記錄之後，就要找 id > 5 的記錄了，此時就變成了範圍查找了。

MySQL 加鎖規則

這裏的加鎖規則，我直接引用丁奇老師的總結：兩個原則、兩個優化、一個 bug。

對於原則 1 說的：加鎖的基本單位是 Next-Key 鎖，意思是默認都是先加上 Next-Key，之後根據 2 個優化點選擇性退化爲行鎖或間隙鎖。

對於原則 2 說的：訪問到的對象纔會加鎖，意思是如果直接索引覆蓋到了，不需要回表，那麼就不會對聚簇索引加鎖。這樣的話，其他事務就可以對聚簇索引進行操作，而不會阻塞。

爲了解釋這些規則，建立表 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);

等值查詢間隙鎖

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

在事務 A 中，要查找 id = 7 的記錄，其查找過程爲：從左到右查找 id 聚簇索引，依次對比 0、5 兩個索引，發現不對。接着，對比 10 這個索引，發現 7 <10，於是停止搜索。根據原則 1 默認給其加上一個 Next-Key 鎖，即 (5, 10]。根據優化 2 退化爲間隙鎖，即 (5,10)。

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

非唯一索引等值鎖

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

由於普通索引可能重複，因此其還會繼續往後搜索，接着搜索到 10，根據原則 2，訪問到的都要加鎖，因此再給其加 Next-Key 鎖，即 (5,10]。由於這個還負責優化 2：等值判斷，向右遍歷，最後一個不滿足等值條件，因此退化爲間隙鎖 (5,10)。

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

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

主鍵索引範圍鎖

對於我們這個表 t，下面這兩條查詢語句，加鎖範圍相同嗎？

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

在邏輯上，這兩條查語句肯定是等價的，但是它們的加鎖規則不太一樣。現在，我們就讓 session A 執行第二個查詢語句，來看看加鎖效果。

我們來分析一下整體的加鎖規則吧。

事務 A 開始執行的時候，要找到 id 爲 10 的記錄，於是從左到右找到了 id 爲 10 的索引。根據原則 1 會給其加 Next-Key 鎖，即 (5,10]。根據優化 1，id = 10 是等值查詢，因此其退化爲行鎖，即只對 id = 10 這行加了行鎖。

接着繼續進行範圍查找，找到 id=15 這一行，繼續加 Next-Key 鎖 (10,15]。這時候 id=15 大於 11，因此其不再查找。TODO

非唯一索引範圍鎖

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

事務 A 開始執行的時候，要找到 id 爲 10 的記錄，於是根據原則 1 加了 Next-Key 鎖，即 (5,10]。由於索引 C 是非唯一索引，沒有優化規則，因此不會退化爲行鎖。因此對於事務 A 來說，索引 C 上加的是 (5,10] 和 (10,15] 兩個 Next-Key 鎖。

所以當 session B 和 session C 要操作 c 值爲 8 和 15 的數據時會被阻塞。

總結

最後我們總結一下 MySQL 的加鎖規則：

其中「兩個原則、兩個優化」是：

通過上面這樣的加鎖規則，我們就可以有一個大致的分析思路，至少能開始分析加鎖規律了。

但要注意的是，實際上的情況非常複雜，例如 limit 參數也會影響加鎖的範圍，非唯一索引多個值夜會影響鎖範圍。簡單地說，就是有很多特例的情況，我們還需要繼續去積累。

參考資料

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/7E9AL3SshbKaeEcZAm-m2w

猜你喜歡