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

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

鎖是資料庫並發控制的核心同步機制，用於滿足互斥存取需求。MySQL 按鎖粒度分為三大類：行級鎖（InnoDB）、表級鎖（MyISAM/MEMORY）、頁級鎖（BDB）。本文系統梳理三者特性、InnoDB 行鎖實現細節與死錯應對策略。

一、三大鎖類型對比

二、行級鎖深度剖析

兩種基本模式

• 共享鎖（S Lock / 讀鎖）：SELECT ... LOCK IN SHARE MODE，兼容 S 鎖，阻塞 X 鎖。
• 排他鎖（X Lock / 寫鎖）：UPDATE/DELETE/SELECT ... FOR UPDATE，互斥 S/X 鎖。

InnoDB 行鎖實現關鍵：索引綁定

InnoDB 是透過索引項加鎖，而非直接鎖資料行（Oracle 鎖資料塊）。意味著：

• SQL 條件走索引 → 行鎖
• SQL 條件未走索引（全表掃描） → 退化為表鎖

實戰警示：未命中索引的 UPDATE/DELETE 會鎖全表，併發度驟降，極易引發鎖等待風暴。

三、表級鎖機制

• 表共享讀鎖（Table Read Lock）：LOCK TABLES t READ，多讀兼容，阻塞寫。
• 表獨占寫鎖（Table Write Lock）：LOCK TABLES t WRITE，互斥所有鎖。

MyISAM/MEMORY 僅支援表鎖；InnoDB 在未走索引、DDL、鎖升級等場景會使用表鎖。

四、頁級鎖（BDB 引擎）

折衷方案：一次鎖定一頁（通常 16KB，含多行）。BDB 預設頁鎖，可退化為表鎖。現已較少使用，了解即可。

五、InnoDB 行鎖與死鎖實戰

行鎖加鎖規則

1. 主鍵索引條件 → 鎖定該主鍵記錄（記錄鎖）
2. 唯一二級索引條件 → 鎖定二級索引記錄 + 回表鎖定對應主鍵記錄
3. 非唯一二級索引條件 → 鎖定掃描到的所有二級索引記錄 + 間隙鎖 + 回表鎖定主鍵記錄
4. 無索引條件 → 表鎖

Next-Key Lock = 記錄鎖 + 間隙鎖，防止幻讀（RR 隔離級別預設）。

死鎖經典場景

-- 事務 A
UPDATE t SET v=1 WHERE pk=1;  -- 鎖主鍵 1
UPDATE t SET v=2 WHERE pk=2;  -- 等待主鍵 2

-- 事務 B
UPDATE t SET v=3 WHERE pk=2;  -- 鎖主鍵 2
UPDATE t SET v=4 WHERE pk=1;  -- 等待主鍵 1 → 死鎖

或跨索引死鎖：事務 A 鎖主鍵等二級索引，事務 B 鎖二級索引等主鍵。

InnoDB 死鎖檢測與處理

• 內建死鎖偵測器（Wait-for Graph），發現環路 → 選擇代價較小的事務回滾（釋放鎖），另一事務繼續。
• SHOW ENGINE INNODB STATUS 可查看最近死鎖詳情。

三大避免死鎖最佳實踐

1. 統一多表存取順序：所有事務按相同順序訪問表（如先訂單後庫存），打破循環等待條件。
2. 單次事務鎖定所有所需資源：避免分步加鎖，減少持鎖時間與交叉機率。
3. 熱點業務升級鎖粒度：極高併發且難優化時，考慮表鎖或應用層分佈式鎖（Redis Redlock）替代行鎖。

六、總結

理解鎖粒度與引擎支援矩陣是效能調優基石。InnoDB 行鎖「依賴索引」特性決定了：索引設計即鎖設計。死鎖不可完全消除，但透過統一存取順序、縮短事務、必要時升級粒度，可將影響降至最低。