鎖的完整體系與死鎖：兩支批次同時跑就互咬

如果這一章只能記一句話，就是這句。它解釋了鎖問題裡至少八成的意外。

推導一次後果

-- memo 上沒有索引
UPDATE orders SET status = 2 WHERE memo = 'X';
-- 實際只有 1 列符合

所以那句常見的建議「更新語句一定要走索引」，理由不只是快——而是不走索引會把行鎖放大成事實上的表鎖。這是「加索引」在效能之外的第二個理由，而且往往更重要。

REPEATABLE READ 下更糟一點

MySQL 預設的 RR 隔離等級下，掃過的列即使不符合條件也不會馬上放鎖（要到交易結束）。RC 隔離等級則會把不匹配的列提前釋放（semi-consistent read）——這是很多線上系統選擇 RC 的真正原因，不是為了效能，是為了減少鎖衝突。

推論二：走哪個索引，鎖就加在哪

-- idx_merchant (merchant_id)，主鍵 id
UPDATE orders SET amount = 0 WHERE merchant_id = 42;

這句會加兩層鎖：先鎖二級索引 idx_merchant 上符合的索引項，再回表鎖對應的聚簇索引記錄。記住這個「兩層、有順序」的細節——它是本章那個死鎖案例的關鍵。

怎麼確認一句 SQL 到底鎖了什麼

-- MySQL 8.0：在交易還沒提交時，直接查目前持有的鎖
SELECT object_name, index_name, lock_type, lock_mode, lock_status, lock_data
FROM performance_schema.data_locks;

這張表是 8.0 的重要改進（取代了舊的 information_schema.innodb_locks）。不要用推理的，直接查——尤其在寫需要加鎖的批次之前，先在測試環境把它列出來看一次。

先把表級的鎖釐清，因為其中一種是線上事故的常客，而它常常不被當成「鎖」。

① 表鎖（LOCK TABLES）

InnoDB 幾乎用不到，日常可以忽略。真正該注意的是被迫升級成表鎖的行鎖——也就是上一節那個「沒走索引」的情況。

② 意向鎖（IS／IX）

這是一個純粹為了效率而存在的機制，理解它就不會被 performance_schema 裡那堆 IX 嚇到：

假設有人要對整張表加表鎖，它必須確認「表裡沒有任何行鎖」。如果要逐列檢查，那是 O(n)。所以 InnoDB 規定：任何交易要加行鎖之前，必須先在表上加一個意向鎖當旗標。於是想加表鎖的人只要看一眼表上有沒有 IX 就知道了。

• 意向鎖之間永遠相容（IS／IX 互不衝突），所以它幾乎不會造成等待。
• 看到 lock_type=TABLE, lock_mode=IX 是正常的，不是問題。

③ MDL（Metadata Lock）——這個才是麻煩

MDL 保護的是表結構，不是資料。規則很簡單：

• 任何 CRUD 會取得 MDL 讀鎖（彼此相容）。
• 任何 DDL（ALTER、ANALYZE、加索引）會取得 MDL 寫鎖（與所有人互斥）。

-- 事故現場長這樣
SHOW PROCESSLIST;
-- id  time  state
-- 101  600  （一個沒提交的交易，可能只是忘了 commit）
-- 202   58  Waiting for table metadata lock   ← ALTER 在排隊
-- 203   57  Waiting for table metadata lock   ← 之後的 SELECT 全部卡住
-- 204   57  Waiting for table metadata lock
-- ...

防禦手法

-- 讓 DDL 等不到就放棄，而不是無限排隊擋住後面所有人
SET SESSION lock_wait_timeout = 5;
ALTER TABLE orders ...;

-- 執行任何 DDL 之前先看有沒有長交易
SELECT * FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10\G

ch02 提過 ANALYZE TABLE 也要 MDL，就是這個機制。DDL 的完整處理方式（線上 DDL 演算法、gh-ost）留到 ch06。

行級鎖有四種，它們的差別在於鎖住的是「記錄」還是「記錄之間的空隙」。

① Record Lock（記錄鎖）

只鎖住一筆索引記錄本身。它擋不住「在它旁邊插入一筆新資料」。

② Gap Lock（間隙鎖）

鎖住兩筆記錄之間的空隙，不鎖記錄本身。它存在的唯一目的是防止幻讀——不讓別人在這個範圍裡插入新資料。

• 間隙鎖之間互相相容：兩個交易可以同時持有同一個間隙的鎖。它只擋插入，不擋其他間隙鎖。
• 只在 REPEATABLE READ 存在。RC 隔離等級沒有間隙鎖——這是兩者最大的差別。

③ Next-Key Lock（臨鍵鎖）

記錄鎖 + 它前面的間隙，是左開右閉的區間 (前一筆, 這一筆]。RR 下的預設加鎖單位就是它，所以你以為只鎖一列，實際上連前面那段空隙一起鎖了。

④ Insert Intention Lock（插入意向鎖）

一種特殊的間隙鎖，代表「我想在這個間隙插入資料」。多個交易想在同一個間隙的不同位置插入時互相相容——但只要那個間隙上有別人的間隙鎖，插入就要等。這是插入類死鎖的主角，下面會看到。

臨鍵鎖的三種退化（實務上最需要記的）

InnoDB 會盡量把鎖縮小，規則是：

怎麼看到這些鎖

SELECT index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks WHERE object_name = 'orders';

-- lock_mode 的讀法：
--   X,REC_NOT_GAP    純記錄鎖
--   X,GAP            純間隙鎖
--   X                臨鍵鎖（記錄 + 前面的間隙）
--   X,INSERT_INTENTION  插入意向鎖

隔離等級的教科書說法是「解決髒讀／不可重複讀／幻讀」。但在維運的視角，它真正的差別是加鎖範圍——而這決定了系統在高併發下會不會互咬。

為什麼很多線上系統改用 RC

不是因為效能，是因為鎖範圍小得多：沒有間隙鎖，而且掃過的不匹配列會提前放掉。前面第一節那個「沒走索引的 UPDATE 鎖住整張表」，在 RC 下衝擊會小很多（掃過即放，只留下真的要改的那列）。

快照讀與當前讀（回扣 backend ch02）

哪些語句會加鎖，是很多人搞混的地方：

• 快照讀（不加鎖）：普通的 SELECT。它讀的是 MVCC 快照，所以「讀不會擋住寫、寫不會擋住讀」。
• 當前讀（加鎖）：SELECT ... FOR UPDATE、SELECT ... FOR SHARE、以及所有的 UPDATE／DELETE／INSERT。它們必須讀到最新版本，所以要加鎖。

-- 8.0 新增：拿不到鎖就立刻失敗／跳過，不要傻等 50 秒
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;
SELECT * FROM orders WHERE status = 0 FOR UPDATE SKIP LOCKED;

SKIP LOCKED 特別有用：用資料庫做任務佇列時，多個 worker 各自撈自己的任務、跳過別人正在處理的，不需要外部的分散式鎖。

這兩個錯誤看起來都是「鎖的問題」，但它們的成因與修法幾乎完全相反。看到錯誤碼就要能分辨方向。

ERROR 1205：Lock wait timeout exceeded

• 意思：我等一個鎖等超過 innodb_lock_wait_timeout（預設 50 秒）。
• 根因：有人長時間持有鎖不放——通常是一個交易開太久（裡面夾了 HTTP 呼叫、檔案處理、或忘了提交）。
• 要找的是「誰持有」，不是「誰在等」。

-- 誰擋住誰，一句就看得出來（8.0）
SELECT * FROM sys.innodb_lock_waits\G
-- waiting_pid / waiting_query   誰在等
-- blocking_pid / blocking_query 誰擋著   ← 要處理的是這個

-- 找出開很久的交易
SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

ERROR 1213：Deadlock found

• 意思：兩個交易互相持有對方要的鎖，形成環。
• 根因：加鎖順序不一致——與交易長短無關，再短的交易只要順序相反就會死鎖。
• InnoDB 會用 wait-for graph 主動偵測，選一個「回滾成本較小」的交易犧牲掉，另一個立刻繼續。

看死鎖現場

SHOW ENGINE INNODB STATUS\G
-- 找 LATEST DETECTED DEADLOCK 這一段：
--   *** (1) TRANSACTION:   誰、在等什麼鎖、SQL 是什麼
--   *** (2) TRANSACTION:   另一個、它持有什麼、又在等什麼
--   *** WE ROLL BACK TRANSACTION (2)   誰被犧牲

SET GLOBAL innodb_print_all_deadlocks = ON;   -- 每次死鎖都寫進 error log

死鎖偵測本身也有代價

innodb_deadlock_detect 預設開啟。它在每次等待時檢查 wait-for graph，而當大量交易搶同一列時（熱點更新），這個檢查是 O(n²)，可能吃掉大量 CPU——症狀是「CPU 很高但每句 SQL 都不慢」（回扣 ch01 的盲點清單）。可以關掉改靠超時，但那是極端場景的選擇，而且關掉之後死鎖會變成 50 秒的卡死，通常更糟。

徵狀

兩支排程批次，各自跑了半年都沒事。某次排程時間被調整後，變成每晚同時跑——從此每晚都有幾十筆 DeadlockLoserDataAccessException，資料處理到一半中斷。

• 兩支批次更新的是同一張表，但不同欄位，業務上完全不相干。
• 單獨跑任何一支都正常，重跑也正常。
• 錯誤是 ERROR 1213（死鎖），不是 1205。

// 批次 A：對帳，依訂單 id 逐批更新
@Transactional
public void reconcile(List<Long> ids) {
    orderRepo.markReconciled(ids);   // UPDATE orders SET reconciled=1 WHERE id IN (...)
}

// 批次 B：結算，依商家逐個更新
@Transactional
public void settle(Long merchantId) {
    orderRepo.applyFee(merchantId);  // UPDATE orders SET fee=... WHERE merchant_id=?
}

診斷

先看死鎖現場（幸好有開 innodb_print_all_deadlocks）：

*** (1) TRANSACTION: UPDATE orders SET reconciled=1 WHERE id IN (...)
    WAITING FOR: index PRIMARY of table `shop`.`orders`  lock_mode X locks rec but not gap

*** (2) TRANSACTION: UPDATE orders SET fee=... WHERE merchant_id=8821
    HOLDS THE LOCK(S): index PRIMARY of table `shop`.`orders`
    WAITING FOR: index idx_merchant of table `shop`.`orders`

兩邊都在 orders 上，但一個等 PRIMARY、一個等 idx_merchant。線索就是這個「兩個不同的索引」——回到第一節那條原理：鎖是加在索引上的，而走不同索引就意味著不同的加鎖順序。

根因：加鎖順序相反

為什麼半年都沒事

因為死鎖需要「時間窗重疊」。排程時間錯開時，兩者的加鎖順序再怎麼相反也撞不到一起。這正是鎖問題最麻煩的性質：程式碼裡的缺陷可以潛伏很久，直到某個與程式碼無關的改動（排程時間、資料量、機器變快）把它引爆。

修法（依序）

這個案例的教訓

把實務上最常見的幾種收在一起。這些在 Code Review 時看得出來，比事後追查便宜太多。

① 交易裡做 I/O

@Transactional
public void pay(Long orderId) {
    Order o = repo.findByIdForUpdate(orderId);   // 拿到行鎖
    paymentGateway.charge(o);                    // ← 呼叫外部 API，2 秒
    o.setStatus(PAID);
}   // 到這裡才放鎖

那 2 秒鐘，這一列完全不能被別人動。而且外部服務一慢，鎖就跟著持有更久——把別人的延遲變成自己的鎖等待。這也正是上面說的「trx_query 是空的」那種最難查的 1205。

② 先查再插（check-then-insert）

if (repo.findByCode(code) == null) {   // 沒查到 → 唯一索引等值未命中 → 加了 Gap Lock
    repo.insert(...);                   // 插入意向鎖，要等別人的間隙鎖
}

兩個交易同時做這件事，就會互相等對方的間隙鎖——經典的插入死鎖。正解是讓資料庫保證唯一性：建唯一索引，直接 INSERT ... ON DUPLICATE KEY UPDATE 或捕捉唯一鍵衝突，不要在應用層先查。

③ 用 SELECT ... FOR UPDATE 當分散式鎖

可以用，但它的生命週期綁在交易上——交易多長，鎖就多長。一旦有人在這個交易裡多做了一件事（見①），鎖範圍就被動擴大。需要跨服務的互斥時，用真正的分散式鎖（Redis／ZooKeeper），語意清楚得多。

④ 沒走索引的 UPDATE / DELETE

第一節那條。批次刪除特別容易中：DELETE FROM logs WHERE created_at < ?，如果 created_at 沒索引，就是掃全表加全表的鎖。

⑤ 大交易

@Transactional
public void importAll(List<Row> rows) {   // 5 萬列在同一個交易裡
    rows.forEach(this::insertOne);
}

三個代價一次付清：鎖持有很久、undo log 爆炸（其他交易的快照讀要沿著它回溯，全體變慢）、回滾很慢（失敗時可能回滾比執行還久）。拆批提交幾乎永遠是對的。

鎖問題與前四章最大的不同是：它必須在「案發當下」抓現場，事後多半查不到。所以第一件事是把觀測手段先準備好。

先做的三件事（平常就要做）

-- ① 每次死鎖都留紀錄（不然只看得到最後一次）
SET GLOBAL innodb_print_all_deadlocks = ON;

-- ② 別讓鎖等待卡滿 50 秒（依業務調整，通常 5~10 秒就該失敗）
SET GLOBAL innodb_lock_wait_timeout = 10;

-- ③ 長交易告警：超過 N 秒的交易直接報出來
SELECT trx_mysql_thread_id, trx_started, trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 10;

案發時的排查順序

什麼時候不該用鎖解決問題

• 純粹的計數／統計：用 UPDATE ... SET n = n + 1（原子更新）就好，不要 SELECT FOR UPDATE 讀出來再寫回去——後者多鎖了一段時間，還多一次往返。
• 寫入衝突很少的場景：用樂觀鎖（版本號欄位 + WHERE version = ?，更新 0 列就重試）。它完全不加額外的鎖，衝突多時才會退化。JPA 的 @Version 就是這個。
• 只是要避免重複執行：用唯一索引讓資料庫擋掉，比用鎖去協調便宜且可靠得多。

把這一章收成三句

下一章換一個角度：前面五章都在處理「查詢怎麼跑」，但有些成本是在建表那一刻就決定的——主鍵選什麼、欄位用什麼型別，會一路影響到寫入速度、索引大小、甚至一次 DDL 要鎖多久。

這一章的每個手段同樣有帳單，而且有幾筆特別容易被忽略。

五筆帳

• 改用 RC 隔離等級 → 鎖範圍小很多，但會有幻讀，而且 binlog 必須是 ROW 格式。這是改變系統語意的決定，要在設計階段做。
• 加重試（處理 1213） → 讓錯誤消失了，但衝突還在，而且被藏起來了。重試次數要有上限、要記錄，否則哪天衝突率上升，你會先看到延遲飆高而不是錯誤。
• 縮短 innodb_lock_wait_timeout → 失敗變快（好事），但原本能等到的請求現在會直接失敗。要配合應用端的重試策略一起調。
• 關閉死鎖偵測 → 省下高併發時的 O(n²) 檢查成本，但死鎖從「立刻回滾一個」變成「兩邊都卡到超時」。除非有明確量測，不要動。
• 樂觀鎖 → 不加鎖，但衝突高時會變成重試風暴，實際吞吐可能比悲觀鎖更差。

這一章的工具看不到的東西

• 鎖等待不會出現在慢查詢日誌的「掃描列數」裡。一句等了 40 秒的 UPDATE，rows_examined 可能是 1。所以用「掃描列數」排序慢查詢時，鎖問題會完全隱形。
• EXPLAIN ANALYZE 測不到等待。它測的是執行時間；而且在正式環境對一句會加鎖的語句下 EXPLAIN ANALYZE，等於真的去搶那把鎖。
• 應用端的連線池排隊會偽裝成鎖問題。「所有請求都變慢」可能是連線池滿了（backend ch03），而連線池滿了往往又是因為前面有人在等鎖——這兩件事會互相放大，是同一場故障的兩個面向（ch07 會把這條完整串起來）。