淺入淺出 MySQL Ep5 : 是誰卡住你的 Transaction？Lock 是不是你？

18 min readMay 1, 2024

前言：

在 Ep4 提到在 Transaction Isolation 的特性主要是解決高併發時 Transaction 之間會讀取到髒資料的問題，其中提到有一個讀取問題稱作 Phantom Read，要解決 Phantom Read 的問題需要 Serializable 的 Isolation 層級，而 Serializable 的層級會對查詢上「鎖」，究竟這個鎖是怎麼運作的呢？對 Transaction 會有什麼影響呢？就讓我們一起探討鎖的機制吧。

內文：

在高併發、多線程程式設計，資料的 race condition 是時常被提到的問題，例如銀行的扣款系統流程：

檢查用戶 Alice 餘額是否足夠扣除 100 TWD
確認足夠後，扣除 Alice 餘額 100 TWD
建立一筆轉帳紀錄

如果 Alice 同時操作兩筆 100 TWD 的扣款且 Alice 餘額剛好 100 TWD，在併發執行且沒有對資料上鎖的情況可能會發生：

Session A :

檢查用戶 Alice 餘額是否足夠扣除 100 TWD
確認足夠，扣除 100 並紀錄

Session B :

檢查用戶 Alice 餘額是否足夠扣除 100 TWD
確認足夠，扣除 100 並紀錄

當 Session A & B 同時對 Alice 餘額做檢查，都會判斷餘額足夠並扣款，最後明明只有 100 餘額卻產生了兩筆扣款紀錄，要解決此問題，最直接的方式就是對 Alice 的餘額資料上鎖，當 Session A 在檢查 Alice 餘額時，Session B 要等 Session A 扣除餘額後才能往下執行，當輪到 Session B 檢查時，由於 Alice 餘額已被 Session A 扣除，所以 Session B 會判斷成餘額不足。

那在 MySQL InnoDB 的架構下，我們該如何對資料上鎖呢？首先我們先討論一個本質的問題：

問題一：我們會對哪些資料的情境上鎖？

第一個且最常見的情境就是上述的轉帳情境，也就是「單筆唯一資料」情境，依照上面轉帳範例，基本上我會有兩個 Table user_balances & transfer_records ，而轉帳的 DB Transaction 流程如下：

使用 user_id查詢user_balances table 中的資料，並對該資料上鎖
將扣除後的餘額更新回 user_balances
建立一筆 transfer_records 並 Commit Transaction，此時該資料的鎖會被釋放

具體的 SQL 語法：

BEGIN;
SELECT amount FROM user_balances WHERE user_id = ? FOR UPDATE;
amount = amount - 100
UPDATE user_balances SET amount = ? WHERE user_id = ?;
INSERT transfer_records (user_id, amount, created_at) VALUES (?, ?, ?);
COMMIT;

FOR UPDATE 上 MySQL 對資料上鎖的語法，該情境的需求是我只需要對特定「單筆」用戶的餘額上鎖，但只有語法正確不代表 MySQL InnoDB 真的就只會對「單筆」資料上鎖，必須還有一個額外條件就是：

user_id 這個欄位他必須是 unique index。

如果 user_id 不是 index ，InnoDB 是會將整張 user_balances Table 中所有的資料都上鎖，這就會導致 Alice 在扣款的時候，Bob 會卡住，但 Alice 跟 Bob 之間根本沒有共用任何資料，原本併發處理提升的效能就被卡住了。

而這就 InnoDB 最基礎的兩種鎖：Row Lock & Table Lock

Row Lock : 會鎖單筆或多筆資料，只有在 WHERE 條件中有用到 index 時才會使用該鎖，而如果只 Lock 單筆資料且是 unique index 又稱為 Record Lock。

Table Lock : 會鎖整張表的資料，在 WHERE 條件中沒有用到 index 時會使用該鎖。

而 Row Lock 跟 Table Lock 彼此之間是互相競爭的，當 Table Lock 先拿到鎖，Row Lock 就沒辦法對該 Table 的任何 Row 上鎖，若 Row Lock 先拿到鎖，Table Lock 也無法對整張 Table 資料上鎖。

提到互相競爭，這時你可能會想，如果我是 Table Lock ，我要上鎖前，要怎麼快速知道 Table 裡面有沒有任何一筆 Row Lock？難道我要整張 Table Scan 嗎？

問題二：InnoDB 如何對整張 Table 上鎖？

首先在 Lock Table 前，要先確認是否有 Row Lock，但要 InnoDB 去做整張 Table Scan 是不可能的，不然資料量一大，上鎖會變得相當艱難，因此 InnoDB 在 Row Lock 的機制中多加了一種鎖就做 Intention Lock 。

其概念是當 InnoDB 在執行 Row Lock 時，他會先對整個 Table 放上一個 Intention Lock，隨後再針對查詢中的 Row 上鎖，而這個 Intention Lock 是一個很和平的傢伙，他大部分情況下都不會跟其他人競爭，例如兩個 Transaction 在執行 Row Lock 時，可以對同一個 Table 上 Intention Lock，Intention Lock 之間不互相競爭，不會因此卡住，他也不會跟 Row Lock 競爭，所以不需要確認 Table 中是否有 Row Lock。

但只有一個情況另外，就是 Table Lock 會跟 Intention Lock 競爭，當你執行 LOCK TABLES user_balances WRITE; 時，代表你要對 user_balances table 上 Table Lock，此時 Table Lock 就會先檢查該 Table 上是否有 Intention Lock，由於 Intention Lock 綁定的層級是 Table，所以只要該 Table 中有任何一筆 Row Lock，那麼 Table Lock 就能在不 Table Scan 的情況下，快速用 Intention Lock 判斷是否有 Row Lock，Table Lock 是否要等。

實務上其實不太常使用到 LOCK TABLES ... WRITE; 畢竟對整個 Table 上鎖可是非常危險的操作，如果 Transaction 執行過久，可能會導致用戶所有操作的卡住，因此我們還是將鎖的使用情境聚焦回 Row Lock，也就是上鎖的對象都是有 Index 的情況。

除了上述 「單筆唯一資料」上鎖情境中，實務上 Row Lock 的資料情境還能分以下這幾種：

「單筆不存在資料」：我想更新某筆資料並對其上鎖，但發現這筆資料不存在。
「單筆非唯一值存在資料」：我想更新某筆資料並對其上鎖，不過這筆資料的 Index 非 Unique。

問題二：當我上鎖到某筆不存在的資料時，會發生什麼事？

舉例來說，當我執行下面 SQL：

BEGIN;
SELECT amount FROM user_balances WHERE user_id = 100000000 FOR UPDATE;
.....

此時資料庫沒有該筆資料， InnoDB 會只鎖 user_id 等於 100000000 嗎？但這筆資料不存在是要鎖個寂寞嗎？還是乾脆就不鎖了？

回答這個問題之前，我們要回顧 Ep4 提到的 Isolation Level 跟 Phantom Read，預設的情況下，Isolation Level 會是 RR (Repeated Read)，而在 RR 的情況下，如果對資料上鎖相當於 Serializable Level 且需要解決 Phantom Read 這個問題。

也就是說，當我有兩個 Transaction

Transaction A :

BEGIN;
SELECT amount FROM user_balances WHERE user_id = 100000000 FOR UPDATE;
SELECT amount FROM user_balances WHERE user_id = 100000000 FOR UPDATE;
SELECT amount FROM user_balances WHERE user_id = 100000000 FOR UPDATE;
....

Transaction B :

BEGIN;
INSERT INTO user_balances (user_id, amount) VALUES (100000000, 0);

在 Transaction A 沒有 Commit 並釋放 user_id = 100000000 這筆資料的鎖之前，Transaction B insert 操作是需要被 Block 住的，不然 Transaction B Commit 且 insert 成功，那邊 Transaction A 在相同 Transaction 中執行相同的 Query 會出現不同的結果，也就是 Phantom Read 問題，而解決這個問題，InnoDB 使用了一個叫做 Gap Lock 的 Row Lock 機制。

Gap Lock 顧名思義就是將資料之間的間隙上鎖，例如，我在 user_balances table 中有以下三筆資料：

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES 
(1, 1, 100, NOW()),
(2, 5, 100, NOW()),
(3, 10, 100, NOW());

那麼這三筆資料的已 user_id 為 index 間隙分別為 (-∞ ~ 1) & (1 ~ 5) & (5~10) & (10 ~ ∞)，而 Gap Lock 就是鎖定這個間隙並防止有人在這個間隙中 insert 資料，與 Record Lock 不同的是，Gap Lock 不會 Lock 資料本身，例如他不會 Lock user_id=5 這筆資料且只會阻擋 insert 這個行為。

SELECT amount FROM user_balances WHERE user_id = 100000000 FOR UPDATE;

以上面這條 SQL 來說，由於 user_id = 100000000 不存在且在 RR Isolation Level 情況下，為了避免 Phantom Read 會採用 Gap Lock ，由於用 B+Tree 去搜尋會發現 user_id = 100000000 會發現這筆資料介於 (10 ~ ∞)，因此會 Lock 這個範圍的 insert 操作，因此下面的 insert 操作都會被 Block 直到這條 SQL Transaction Commit 後釋放鎖。

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES
(11, 11, 11, NOW()); // blocked

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES
(50, 50, 50, NOW()); // blocked

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES
(100000, 100000, 100, NOW()); // blocked

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES
(100001, 100001, 100, NOW()); // blocked

而不會被 block 的操作有：

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES
(7, 7, 7, NOW()); // 不在 (10 ~ ∞) 的範圍內
SELECT * FROM user_balances WHERE user_id = 10 FOR UPDATE; // 不會 lock record 本身
SELECT * FROM user_balances WHERE user_id = 100000000 FOR UPDATE; // 不會 lock record 本身，所以執行一樣的 lock 也不會 block 會回傳空資料
UPDATE user_balances SET amount=100 WHERE user_id = 10; // 只會 block insert，所以 update 不會被 block
UPDATE user_balances SET amount=100 WHERE user_id = 11; // 只會 block insert，所以 update 不會被 block

同理，如果今天 Query 換成 SELECT amount FROM user_balances WHERE user_id = 3 ，那麼 Gap Lock 的 Range 會是哪個呢？

答案是 (1 ~ 5)，也就是說不能 insert user_id 為 2, 3, 4 的資料。

看到這邊你是否會好奇，雖然是不存在的資料，但我明明也只有 Lock 一筆資料，為何要用 Gap Lock 鎖住一個區段的 insert 不能只鎖那一筆的 insert 嗎？

根據我的推論，應該是 InnoDB 不能 Lock 不存在的資料，因此只能從存在的資料下手，所以就衍生出 Gap Lock 這種 Lock 已存在資料之間的間隙的方式。

另外只要 SELECT 欄位不存在，不論 Index 是否為 Unique 都會走 Gap Lock 的機制。

接下來就來看如果是非 Unique Index 且存在的情況下會發生什麼事吧。

問題三：當我上鎖的資料非唯一的 Index 時，會發生什麼事？

如果我在 user_balances 這張 table 的 created_at 欄位加上 index，隨後執行：

BEGIN;
SELECT amount FROM user_balances WHERE created_at = '2024/04/01 13:03:05' FOR UPDATE;
....

雖然 WHERE 條件是 equal 查詢，乍看會是單筆搜尋，但由於 created_at 並非 unique index，所以對 InnoDB 而言會判定為範圍查詢，而在範圍查詢下同樣會有 Phantom Read 問題要解決，例如這筆 INSERT INTO user_balances (id, user_id, amount, created_at) VALUES (99, 99, 0, '2024/04/01 13:03:05') 就應該要被上面的 SELECT BLOCK 住，避免上面 Transaction SELECT 執行第二次後會發現結果多了一筆資料，且除了避免 Phantom Read 以外，鎖的資料是真實存在的，所以不能只單純用 Gap Lock 去鎖 insert 區間，還有鎖其他想鎖相同資料的 SELECT ，而這種 Gap Lock 加 Record Lock 的合併就稱為 Next-Key Lock。

Next-Key Lock 會先針對命中且存在的資料上 Record Lock，並針對這些 Record 的前後一個 Range 上 Gap Lock。

舉例：

create table user_balances (
 id int primary key,
 user_id int,
 amount int,
 created_at datetime,
 unique key idx_user_id (user_id),
 key idx_created_at (created_at)
);

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES 
(1, 1, 100, '2024-05-01 15:20:03'),
(2, 5, 100, '2024-05-05 15:20:03'),
(3, 10, 100, '2024-05-10 15:20:03');


BEGIN; // Transaction A
SELECT id FROM user_balances WHERE created_at = '2024-05-01 15:20:03' FOR UPDATE;

那麼 Transaction A 會對 created_at = "2024–05–01 15:20:03" 這筆資料上 Record Lock 且對 created_at (-∞~2024–05–01 15:20:03) 以及 (2024–05–01 15:20:03~2024–05–05 15:20:03) 這個 range 上 Gap Lock，所以以下的 SQL 都會被 Block 住：

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES 
(100, 100, 100, '2024-05-01 12:20:03'); // 被 gap lock 擋住

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES 
(100, 100, 100, '2024-05-03 12:20:03'); // 被 gap lock 擋住

SELECT id FROM user_balances WHERE created_at = '2024-05-01 15:20:03' FOR UPDATE; // 被 record lock 擋住

SELECT id FROM user_balances WHERE user_id = 1 FOR UPDATE; // 被 record lock 擋住

UPDATE user_balances SET amount = 100 WHERE id = 1; // 被 record lock 擋住

其實 Next-Key Lock 的觸發時機並非只有 Non-Unique Index 的時候，主要是範圍查詢時都算，例如下面使用 Unique Index 的範圍查詢：

INSERT INTO user_balances (id, user_id, amount, created_at) VALUES 
(1, 1, 100, NOW()),
(2, 5, 100, NOW()),
(3, 10, 100, NOW());

SELECT id FROM user_balances WHERE user_id >= 5 AND user_id < 10 FOR UPDATE;

此時 Next-Key Lock 的範圍就有 user_id=5 本身的 Record Lock 以及 (1~5) 和 (5~10) 的 Gap Lock，大家可以自己驗證看看有哪些 insert 或者 select 操作會被 Block。

另外提醒 SELECT id FROM user_balances WHERE user_id IN (5,10) for update; 這種查詢雖然多查出多個 Record，但並不是範圍查詢，不需要解決 Phantom Read 問題，所以只會用到 Record Lock ，以及如果不想要有 Gap Lock 影響效能，可以把 Isolation Level 調整成 Read Committed。

最後還漏了一個情境，由於上面所說的上鎖前提都是我接下來要對該值做更新的情況，那麼如果我單純需要資料被鎖在某個狀態下等我完成其他事情，但沒有要更新該資料，我該用什麼鎖呢？

問題四：我只想要鎖著狀態，不需要更新時，需要用什麼鎖？

InnoDB 中 Lock 的分類除了 Row Lock 和 Table Lock 之外，還有分 Write Lock 以及 Read Lock。

上面範例的 FOR UPDATE 語法都是 Write Lock 的寫法，所謂的 Write Lock 代表，我對這個資料上鎖，接下來我要更新他，所以其他人要更新或者要競爭同把鎖都要等我做完事情。

而 Read Lock 的寫法為 LOCK IN SHARE MODE ，概念是我對這個資料上鎖，代表我需要他的資料內容是鎖定在當下狀態，其他人不能被更改，所以如果有人要更新資料或拿 Write Lock 跟我競爭都要等我做完事情，但是如果你拿的是 Read Lock 那我可以跟你分享這筆資料的狀態，反正你也不會亂改他。

簡單舉個範例：

BEGIN  // transaction a
SELECT id FROM user_balances WHERE user_id = 1 FOR UPDATE;
...


BEGIN // transaction b
SELECT id FROM user_balances WHERE user_id = 1 FOR UPDATE; // 會被 tx a 卡住

BEGIN // transaction c
SELECT id FROM user_balances WHERE user_id = 1 LOCK IN SHARE MODE; // 會被 tx a 卡住

但如果 Transaction A 是使用 Read Lock：

BEGIN  // transaction a
SELECT id FROM user_balances WHERE user_id = 1 LOCK IN SHARE MODE;
...

BEGIN // transaction b
SELECT id FROM user_balances WHERE user_id = 1 FOR UPDATE; // 會被 tx a 卡住

BEGIN // transaction c
SELECT id FROM user_balances WHERE user_id = 1 LOCK IN SHARE MODE; /／ 不會被 tx a 卡住

而實務上會用 Read Lock 的情境可能是，你要對 A 資料做一些 update 的操作，但這些 update 的前提是 B 資料的狀態必須維持在某個特定的值，那麼你就可以對 B 資料上 Read Lock，像是用戶狀態必須維持在可用狀態下才能下單，而有其他 process 會依照用戶下單的內容來修改用戶狀態，那麼你就必須先對該用戶狀態上 Read Lock 確保他這筆單下單成功了，另外一個 process 在更新用戶狀態時，才能依照新的下單內容去更新。

總結

InnoDB 中，如果上鎖的欄位沒有 Index 那麼會直些用 Table Lock，鎖整張表會悲劇。
如果上鎖欄位是 Unique Index 會使用 Record Lock，Lock 顆粒度最小。
如果上鎖欄位不存在，為了解決 Phantom Read 問題，會採用 Gap Lock。
如果是範圍查詢的上鎖，會使用 Next-Key Lock 也就是 Record Lock 加 Gap Lock 。

下集預告

這集介紹了 MySQL InnoDB 各種 Lock 的機制，雖然能懂原理，但我想大家在使用上還是會怕怕的，畢竟用了 Lock 多少都會影響效能，下集我們來談談，如何降低 Lock 的效能的影響，像是何謂樂觀鎖跟悲觀鎖，以及如何用 MySQL 指令去檢查是否有 DeadLock 的發生。