InnoDB 鎖的完整指南（Jahfer Husain）

Jahfer’s Blog｜2021-01-01｜〈A Comprehensive (and Animated) Guide to InnoDB Locking〉起因：作者在除錯「只有在高吞吐時才出現」的 MySQL 競爭問題，順勢把 InnoDB 的鎖機制摸透。

這就是 Shopify 那篇（Inbox/用 MySQL 取代 Redis 做庫存預留（Shopify）.md）特別感謝的那份指南——他們把隔離等級從 REPEATABLE READ 降到 READ COMMITTED，就是為了避開這篇最後講的那種 gap lock。

關於動畫

標題寫「Animated」，但沒有任何可匯入的媒體檔——動畫是 JS bundle 在 runtime 生成的 DOM（HTML 裡 0 張 <img>、0 個 <svg>、0 個 <canvas>）。四處動畫的圖說文字我原樣保留並標成 🎬，標示它們在原文的位置。要看動畫請回原文。

統整

一切取決於隔離等級

InnoDB 只有少數幾個鎖的概念，但它們的用法與行為，極大程度取決於該連線當下生效的交易隔離等級。

四個等級（由嚴到鬆）：

SERIALIZABLE
REPEATABLE READ   ← MySQL 預設
READ COMMITTED
READ UNCOMMITTED

全文只談前兩個。範例是一張藏書表：

CREATE TABLE `books` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `author_id` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL,
  `borrowed` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_books_on_author_id` (`author_id`)   -- 注意：非唯一索引
);

id	author_id	title
1	101	The Pragmatic Programmer
2	102	Clean Code
3	102	The Clean Coder
4	104	Ruby Under a Microscope

共享（S）vs 排他（X）

共享（S）鎖允許持有它的交易讀取一列。 排他（X）鎖允許持有它的交易更新或刪除一列。

共享鎖可以被多個並行交易同時持有（它們只能讀）。
要寫就必須拿到排他鎖，同一時間只能有一個交易持有。
只要別的交易還持有共享鎖，排他鎖就拿不到——這就是 InnoDB 保證「共享鎖底下讀到的結果在讀取期間不會變」的方式。

一個作者特別點出的不對稱：

共享鎖只鎖住「執行這次讀取所用的那個索引」裡的列，因為讀取只需要在「記錄是怎麼被找到的」這個脈絡下可重現。 排他鎖則是針對記錄的主鍵取得，影響可能更大，因為所有次級索引都會受影響。

三種鎖：next-key = record + gap

這篇文章本來只想講一種鎖：next-key lock。結果 next-key lock 其實是另外兩種鎖的組合：record lock ＋ gap lock。

Record lock（記錄鎖）

最簡單的一種：在某個索引裡、對特定一列取得的鎖。

BEGIN;
SELECT * FROM `books`
WHERE `id` = 2 LOCK IN SHARE MODE;   -- MySQL 8.0 寫 FOR SHARE

作者用 performance_schema.metadata_locks 看，得到 SHARED_READ。換成排他：

BEGIN;
SELECT * FROM `books`
WHERE `id` = 2 FOR UPDATE;           -- 排他（X）鎖

這次看到的是 SHARED_WRITE，而作者自己承認這裡很混亂：

令人困惑的是，這和前面講的 shared 語意不是同一回事；因為它是為了寫而取得的鎖，這個鎖實際上是排他的。

他把這個坑留給「intention locks」，沒有展開。⚠️ 見矛盾點 1。

Gap lock（間隙鎖）

Gap lock 是在索引中跨越一段值域的鎖。InnoDB 用它來確保被選中的記錄與其周圍記錄之間的關係得以維持。 只要某個間隙被鎖住，其他語句就不准 INSERT、UPDATE 或 DELETE 落在那個間隙裡的記錄。

要看懂它得先看 InnoDB 實際怎麼存索引記錄（作者用 innodb_ruby 直接把索引 dump 出來）：

$ innodb_space -s ./data/ibdata1 -T test/books -I idx_books_on_author_id index-recurse

ROOT NODE #4: 4 records, 84 bytes
  RECORD: (author_id=101) → (id=1)
  RECORD: (author_id=102) → (id=2)
  RECORD: (author_id=102) → (id=3)
  RECORD: (author_id=104) → (id=4)

關鍵在於次級索引的記錄是 (author_id, id) 的組合，不是只有 author_id。於是所有可能的間隙就是：

(-Infinity,       (101 → 1)]
((101 → 1),       (102 → 2)]
((102 → 2),       (102 → 3)]
((102 → 3),       (104 → 4)]
((104 → 4),       +Infinity)

作者用區間記號：左開右閉。拿第三個當例子：

((102 → 2), (102 → 3)]

如果我們拿下這個 gap lock，任何其他連線要執行 INSERT／UPDATE／DELETE，只要它的記錄 author_id >= 102 且 2 < id <= 3，就必須等我們。

🎬 Gap locks apply to the space between records.

SERIALIZABLE：讀到的每一列都要鎖

在 SERIALIZABLE 底下，查詢讀到的每一筆記錄都必須被鎖住，以確保沒有別的連線能修改正在被檢視的記錄。官方文件：SERIALIZABLE 等級下，搜尋會對它遇到的索引記錄設下共享的 next-key lock。

例外：

如果 SELECT 用的是唯一索引（例如 PRIMARY KEY），它不需要 gap lock，因為它能保證只影響到唯一的記錄。

REPEATABLE READ：快照讀，但寫要讀最新的

一個使用 REPEATABLE READ 的交易，所有讀取都像是在「交易裡第一次讀取」的那個時間點執行的（快照）。這讓跨查詢的資料庫視圖保持一致，不會遇到 phantom rows——在後續讀取中憑空出現或消失的記錄。

唯一的例外正是問題所在：

InnoDB 唯一不能使用快照記錄的時候，是它為了 UPDATE 或 DELETE 而選取列的時候。 這些情況下 InnoDB 必須讀取資料庫裡最新的版本，以免對著過期的值操作。雖然不用像 SERIALIZABLE 那樣鎖住遇到的每一列，但仍然必須阻止其他連線寫入會影響我們語句的記錄。

情況一：用主鍵或唯一索引

BEGIN;
UPDATE `books` SET `borrowed` = TRUE WHERE `id` = 3;

用唯一索引（例如主鍵）時，InnoDB 不需要在選中記錄的左右取 gap lock，因為它確定查詢命中的值和其他所有值都不同。這個例子只需要在命中的那一列取一個 record lock。

🎬 A record lock is acquired on the clustered index for our distinct record.

所以這個會等：

UPDATE `books` SET `borrowed` = TRUE WHERE `id` = 3;   -- 衝突，要等

這個不會：

INSERT INTO `books` (`author_id`, `id`, `title`)
VALUES (103, 5, "Database Internals");                  -- id = 5 不衝突

「唯一」的判準寫得很死：

InnoDB 認定一筆記錄是唯一的，條件是：

這筆記錄是用 PRIMARY KEY 選取的，或者……

這筆記錄是用 UNIQUE 索引選取的，而且選取時用上了該索引的所有組成欄位

如果搜尋用的索引符合「唯一」的判準，就不需要用 gap lock，因為 InnoDB 可以確定不會有任何可能衝突的值被插入。

情況二：用非唯一索引（next-key lock 登場）

表變成 5 列（多了 id=5, author_id=103），索引順序是：

RECORD: (author_id=101) → (id=1)
RECORD: (author_id=102) → (id=2)
RECORD: (author_id=102) → (id=3)
RECORD: (author_id=103) → (id=5)
RECORD: (author_id=104) → (id=4)

BEGIN;
UPDATE `books` SET `borrowed` = TRUE
WHERE `author_id` = 103;    -- idx_books_on_author_id 不是 UNIQUE 索引

InnoDB 必須取得兩個鎖：

叢集索引上 id = 5 的 record lock（那是 author_id = 103 這筆的主鍵）
idx_books_on_author_id 上 author_id = 103 的 next-key lock

🎬 The next-key lock on idx_books_on_author_id: a record lock with a gap lock on each side.

而那個 next-key lock 攤開來其實是三個鎖：

在 idx_books_on_author_id 上：
  1. (author_id=103) → (id=5) 的 record lock
  2. 該記錄與「下一個較小值」之間的 gap lock
  3. 該記錄與「下一個較大值」之間的 gap lock

最容易看漏的一點：鎖的是組合，不是 author_id 的範圍

這個會被擋：

INSERT INTO `books` (`author_id`, `id`, `title`) VALUES (102, 6, "Clean Architecture");
-- (author_id=102) → (id=6) 會被插進我們記錄「正前方」的那個間隙

這個不會：

INSERT INTO `books` (`author_id`, `id`, `title`) VALUES (104, 6, "Clean Architecture");
-- (author_id=104) → (id=6) 落在 gap lock 範圍之外

我們被擋住的不是嚴格意義上的 author_id 值域（那會是 102–104），而是索引裡 author_id 與 PRIMARY KEY 的「組合」。

情況三：索引邊緣 → 鎖到無限大

一個特別令人意外的情況出現在索引的兩端。當 next-key lock 落在索引的第一筆或最後一筆記錄上、而且搜尋又不符合唯一性判準時，InnoDB 必須鎖住往正無限或負無限的所有值，以確保沒有其他記錄會和這筆記錄的位置重疊。

官方文件的用詞：

對於最後一個區間，next-key lock 鎖住的是「索引中最大值以上的間隙」以及那個 supremum 偽紀錄——一個值高於索引中任何實際存在的值的東西。

BEGIN;
UPDATE `books` SET `borrowed` = TRUE
WHERE `author_id` = 104;    -- 最大的 author_id

🎬 One of the gap locks on the next-key lock continues all the way to +Infinity.

這個 next-key lock 持有的排他寫入範圍，是從 (author_id=103) → (id=5) 之後一路到 +Infinity！由於索引裡沒有 author_id > 104，InnoDB 必須把間隙鎖到 +Infinity。我們現在擋住了所有 author_id 大於 104 的插入；糟了！

INSERT INTO `books` (`author_id`, `title`)
VALUES (153, "A Series of Unfortunate Events");
-- 慘了！lock wait，因為 104 < 153 < +Infinity

作者的收尾就是全文最實用的一句：

這可能是特別討厭的問題，因為「插入發生在索引尾端」是非常常見的。 只要你確保是用唯一索引或主鍵來選取，就不必擔心這種鎖競爭。

反覆出現的關鍵字

唯一（unique）：整篇的分水嶺。唯一 → 只要 record lock；不唯一 → 整套 next-key lock。 而且作者把「唯一」定義得很死（PK，或 UNIQUE 索引且用上所有組成欄位），因為這條判準決定了後面所有行為。
間隙（gap）／區間記號：他用 ((102 → 2), (102 → 3)] 這種左開右閉的寫法，強迫讀者意識到鎖的對象是「記錄之間的空間」而不是記錄本身。
組合（combination）：索引記錄是 (次級索引欄位, 主鍵) 的組合，所以間隙也是按組合排序的。這是全文最容易看漏、作者卻特別加粗的一點。
可重現（reproducible / repeatable）：所有鎖的存在理由。REPEATABLE READ 這個名字本身就是規格——為了讓同一個查詢重跑結果相同，才需要擋住可能插進來的記錄。
supremum / +Infinity：結尾的高潮，也是唯一一個作者用「oops!」「nasty」形容的情況。
取決於隔離等級：開場第一句就設定了整篇的框架——沒有「InnoDB 會怎麼鎖」這種脫離隔離等級的答案。

矛盾點

文章內部

他用來觀察鎖的那張表，其實看不到他正在講的鎖。 文中兩次查 performance_schema.metadata_locks，得到 SHARED_READ / SHARED_WRITE——但那是表層級的 metadata lock（意向鎖 IS/IX），不是他整篇在講的列層級 record／gap／next-key lock。作者自己承認「令人困惑，這和前面講的 shared 語意不是同一回事」，然後把它推給 intention locks 就跳過了。（推論）示範的查詢和被解釋的現象對不上，讀者照做只會更混亂。要看真正的列鎖，MySQL 8.0 要查 performance_schema.data_locks，或用 SHOW ENGINE INNODB STATUS。
共享／排他鎖範圍的說法，和後面的例子有張力。（推論）開頭說「共享鎖只鎖讀取所用的那個索引，排他鎖是針對主鍵取得」；但情況二的例子裡，一個 UPDATE（排他）同時在叢集索引和次級索引上取鎖。兩者可以調和（排他鎖「至少」會碰主鍵，不代表只碰主鍵），但開頭那句話容易讓人以為排他鎖不碰次級索引。
文章是 2021 年、MySQL 5.7 語境。（推論）LOCK IN SHARE MODE 他有註明 8.0 改叫 FOR SHARE，但 performance_schema.metadata_locks 這個示範沒有更新，而 8.0 才有的 data_locks 剛好是這篇最需要的那張表。
「只要用唯一索引或主鍵就不用擔心」這句結論範圍偏窄。（推論）它對「用非唯一索引查」成立，但沒有涵蓋根本沒用到索引的情況——那時候掃描到的每一列都可能被鎖，問題比 next-key lock 更大。

與 vault 既有筆記

ACID 的隔離性是可調的，預設擋不住 write skew 需要一條重要的但書。 那篇說 MySQL 預設的 REPEATABLE READ 擋不住訂房那種 write skew——對「普通 SELECT」成立（快照讀，不取鎖）。但這篇說清楚了：一旦是 SELECT ... FOR UPDATE 或 UPDATE／DELETE，InnoDB 會取 next-key lock，反而擋得住幻影插入。（推論）所以那篇該補的是：MySQL 的 REPEATABLE READ 在「鎖定讀」上比標準規定的更強，漏洞在非鎖定讀那一側。這是那篇目前寫得太籠統的地方。
它直接解釋了 Shopify 那篇的核心決定。 Inbox/用 MySQL 取代 Redis 做庫存預留（Shopify）.md 記的是「在空表上跑 SELECT ... FOR UPDATE SKIP LOCKED 會看到 gap lock（包含 supremum），擋住補貨交易插入，所以把隔離等級降到 READ COMMITTED」。這篇的情況三就是那個現象的機制說明——空表或查到索引尾端時，next-key lock 會鎖到 +Infinity。（推論）兩篇一起讀，Shopify 那個決定從「他們做了一個調整」變成「那是唯一的選擇」。
索引是拿寫入換查詢，寫多讀少的表加索引是純虧少了鎖這個維度。 那篇算的是「寫入時要多維護幾棵樹」。這篇加的是：用哪個索引查，決定了會鎖住多少東西——唯一索引只鎖一列，非唯一索引鎖一列加兩側間隙，索引尾端鎖到無限大。（推論）值得補進那篇：索引的成本不只是維護，還包括它在高併發下決定的鎖範圍。
和 WHERE 決定回傳幾列，索引才決定要看過幾列是同一個形狀的第三個版本。（推論）那篇說「索引決定看過幾列」，這篇說「索引決定鎖住多少範圍」。合起來是：WHERE 決定回傳幾列，索引決定看過幾列、也決定鎖住幾列。第三個量在高併發下才現形，而那正是作者除錯的起點（「只有在高吞吐時才出現」）。
把欄位包進函數，WHERE 就用不到那個欄位的索引在這裡多了一個後果。（推論）那篇講的後果是回到全表掃描。這篇補的是：用不到索引時，鎖的範圍也跟著失控——在 REPEATABLE READ 底下掃過的列都可能被鎖住。sargable 不只是效能問題，也是併發問題。

待確認

四段動畫實際畫了什麼？圖說文字有了，但動畫本身（特別是 next-key lock 那段「左右各一個 gap lock」的視覺）沒看到。
READ COMMITTED 底下到底怎麼鎖？文章明說「只談前兩個等級」，但那正是 Shopify 選的那個。補完這一塊才能完整解釋他們的決定。
SHARED_READ / SHARED_WRITE（意向鎖）和 record／gap／next-key lock 的關係，作者跳過了。
作者提到的 Bill Karwin〈InnoDB Locking Explained with Stick Figures〉值得找來看。
innodb_ruby / innodb_space 這個能直接 dump 索引記錄的工具，本身就值得記一筆——它是「把鎖行為看見」的關鍵手段。

知識冒險者

探索

InnoDB 鎖的完整指南（Jahfer Husain）

InnoDB 鎖的完整指南（Jahfer Husain）

統整

一切取決於隔離等級

共享（S）vs 排他（X）

三種鎖：next-key = record + gap

Record lock（記錄鎖）

Gap lock（間隙鎖）

SERIALIZABLE：讀到的每一列都要鎖

REPEATABLE READ：快照讀，但寫要讀最新的

情況一：用主鍵或唯一索引

情況二：用非唯一索引（next-key lock 登場）

最容易看漏的一點：鎖的是組合，不是 author_id 的範圍

情況三：索引邊緣 → 鎖到無限大

延伸閱讀（作者推薦）

反覆出現的關鍵字

矛盾點

文章內部

與 vault 既有筆記

待確認

關係圖譜

目錄