Isolation Level Read Committed & Repeatable Read Level 主要是解決 Transaction 併發時，Write Transaction 影響 Read Transaction 的問題，然而除了 Write 影響 Read，還有多個 Write Transaction 併發的情境要解決，例如 Write Skew & Phantom Read。

什麼是 Write Skew & Phantom Read 問題？

Write Skew 是多個 Transaction 同時讀取相同資料，用當下資料狀態判斷邏輯後更新，結果更新內容出現異常，例如扣庫存的 API 這樣實作：

BEGIN
SELECT id, quantity FROM products WHERE id = ?;

if quantity > 0 
  UPDATE products SET quantity = quantity - 1 WHERE id = ?
COMMIT

瞬間執行多次會導致產品的庫存變成負數，要解決該問題可加上 FOR UPDATE 語法：

BEGIN
SELECT id, quantity FROM products WHERE id = ? FOR UPDATE;

if quantity > 0 
  UPDATE products SET quantity = quantity - 1 WHERE id = ?
COMMIT

此時 MySQL 會對讀取到的 products 紀錄上 row lock，先取得 lock 的 Transaction 會執行 update 成功，後面的 Transaction 會拿到更新後到 quantity 判斷 <= 0 則不 update。

除了加 FOR UPDATE 語法外也可寫成

UPDATE products SET quantity = quantity - 1 WHERE id = ? AND quantity > 0

UPDATE 語法同樣會上 row lock，瞬間多個 UPDATE 執行時也不會有問題。

Phantom Read 則是相同 Transaction 內，對範圍資料執行兩次 SQL 出現的結果不一樣，例如實作一個任務排程：

BEGIN
SELECT count(1) FROM tasks WHERE status = '待處理'

if count <= 10
   UPDATE tasks SET status = '處理中' WHERE status = '待處理'
COMMIT

原本邏輯是最多一次處理 10 筆，但如果有其他 Transaction 在 SELECT count(1) 之後 UPDATE 之前執行 INSERT INTO tasks (status) VALUES ('待處理') 使得待處理任務數量超過 10，那麼 UPDATE 出來的結果就會超過 10 筆，不符合預期。

如果在 SELECT count(1) 上加上 FOR UPDATE 的 row lock 能解決嗎？

答案是不能，因為 row lock 只會鎖住查詢出的 10 筆資料，INSERT 並沒有對那 10 筆資料做任何讀寫，所以 row lock 不會卡住 INSERT 無法解決 Phantom Read 問題。

那麼什麼樣的鎖可以解決 Phantom Read 問題？

MySQL 使用 Gap Lock 阻擋 Transaction 對特定範圍 INSERT 資料，而會阻擋哪些範圍，是透過 Index 排序跟查詢條件有關，例如 SELECT id FROM orders WHERE id BETWEEN 1 AND 10 FOR UPDATE 除了有 row lock 外，還有 id 1 ~ 10 範圍的 Gap Lock ，用來阻擋 INSERT orders (id) VALUES (5) 這類 SQL。

Gap Lock 準確來是說對資料的間隙上鎖，例如 id 1 ~ 10 ，有些查詢是 WHERE id >= 10 FOR UPDATE 就會對 10 ~ 無限大 範圍上 Gap Lock，另外 Row Lock + Gap Lock 的組合又稱為 Next-Key Lock，例如 orders Table 有 id(1, 3, 5) 三筆資料，執行 SELECT id FROM orders WHERE id BETWEEN 1 AND 10 FOR UPDATE 就會對 (1, 3, 5) 上 Row Lock 跟 id 1 ~ 10 上 Gap Lock。

除了 Row Lock, Gap Lock & Next-Key Lock 還有其他種 Lock 嗎？

Lock 除了 FOR UPDATE 語法的 Write Lock 以外還有 LOCK IN SHARE MODE 的 Read Lock，Read Lock 互相不會卡住，但 Read & Write Lock 互相會卡住。

此外還有 Table Lock 會直接鎖住整張表，例如執行 LOCK TABLE orders WRITE or LOCK TABLE orders READ 會對整張表上寫鎖 or 讀鎖。

而在執行 Table Lock 前，要先確認 Table 中是否有 row lock，但總不能 full table scan 一筆筆檢查，因此有了 Intention Lock，該鎖只會跟 Table Lock 互卡，執行 row lock 會先對 Table 上 Intention Lock，Intention Lock 不會卡住 row lock 跟其他 Intention Lock，只卡 Table Lock，因此執行 Table Lock 時如果有其他 row lock，會被 Table 的 Intention Lock 卡住，不用 full table scan 檢查。

資料什麼時候會被上鎖？

基本上只有 SELECT 加上 FOR UPDATE & LOCK IN SHARE MODE 和 DELETE or UPDATE 等更新語法時會對資料上鎖，但有時上鎖範圍與方式會讓你意想不到。

假如 tasks Table 中有 10 筆待處理任務，執行 SELECT count(1) FROM tasks WHERE status = '待處理' FOR UPDATE 時會只鎖那 10 筆嗎？

答案是不一定，如果 status 有 Index 那確實只會鎖 10 筆，但如果沒有 status index 就會 Lock Table 了，因為 MySQL 架構是 SQL Layer 跟 Storage Engine 分開，沒命中 index 時，要在 Storage Engine Full Table Scan 後交由 SQL Layer 過濾資料，而上鎖必須在 Storage Engine 完成，若等 SQL Layer 過濾完資料，再告訴 Storage Engine 要上鎖哪些資料，會造成部分命中條件的資料沒上鎖到，因為其他 Transaction 可在 SQL Layer 過濾資料時 Insert or Update，因此要在 Storage Engine Full Table Scan 時上鎖，就變成 Table Lock 了。

Gap Lock 主要發生在範圍查詢 WHERE id > 10 FOR UPDATE or 查詢非 unique 索引 WHERE status = 1 FOR UPDATE ，但如果查詢條件沒有命中資料時 WHERE id = 10 FOR UPDATE ， id 是 unique primary key，但 id = 10 資料不存在，也有 Gap Lock 並會鎖住 id=10 前一筆跟後一筆資料的間隙，如果前一筆是 5 後一筆是 15 會鎖 5~15 範圍導致 INSERT INTO t (id) VALUES (11) 卡住。

此外，UPDATE & DELETE 不存在資料，甚至 INSERT ON DUPLICATE KEY UPDATE 到不存在資料直接 INSERT 時也都會上 Gap Lock。

最後是 Isolation Level 如果設定成 Serializable，雖能解決上述所有一致性問題 (Dirty Read, Non-repeatable Read, Write Skew & Phantom Read)，但會自動把所有 SELECT 加上 LOCK IN SHARE MODE 的讀鎖。

About Me
歡迎大家追蹤我的 Thread，平常會在上面分享技術文章
https://www.threads.com/@chill.vic.22