MySQL 行級鎖、表級鎖、頁級鎖機制詳解與 InnoDB 死鎖避坑指南

系統闡述 MySQL 三種鎖粒度（行/表/頁）特點、對應引擎，深入剖析 InnoDB 行鎖實現原理（基於索引）、死鎖產生條件與 3 大避免策略。

三種鎖粒度對比

InnoDB 行鎖核心特性

• **通過索引項加鎖**，非索引條件查詢會退化為表鎖。
• 主鍵索引鎖定該主鍵；非主鍵索引先鎖二級索引再鎖相關主鍵。
• UPDATE/DELETE 會鎖定掃描範圍內所有索引記錄 + 相鄰鍵值 (Next-Key Lock)。

死鎖典型成因

1. 事務 A 鎖主鍵索引 → 等非主鍵索引；事務 B 鎖非主鍵索引 → 等主鍵索引。
2. InnoDB 逐步獲取鎖（非一次性），MyISAM 一次性全獲取故無死鎖。
3. InnoDB 自動檢測並回滾持有較少排他鎖的事務。

3 大避免死鎖實務策略

1. 統一多表存取順序：所有並發事務按相同順序訪問表（如按表名排序）。
2. 單事務一次性鎖定所需資源：減少分步獲取鎖的機會。
3. 高風險業務升級鎖粒度：用 LOCK TABLES 表級鎖序列化，犧牲並發換穩定。
   正確用法：SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ; ... COMMIT; UNLOCK TABLES;

應用層必備

捕獲錯誤碼 1213 (ER_LOCK_DEADLOCK) 自動重試事務，死鎖非數據錯誤，重試通常即可成功。