MySQL 鎖機制深度解析：MyISAM 表鎖 vs InnoDB 行鎖、間隙鎖、死鎖與調優實戰

MySQL 鎖機制深度解析：MyISAM 表鎖 vs InnoDB 行鎖、間隙鎖、死鎖與調優實戰

鎖是資料庫並發控制的核心。MySQL 採用插件式存儲引擎架構，鎖實現下推至引擎層：**MyISAM 表級鎖**、**InnoDB 行級鎖**（含意向鎖、間隙鎖、Next-Key Lock）。本文系統對比兩大引擎鎖特性、演示實戰場景、給出生產級調優建議。全文約 6000 字，建議收藏細讀。

一、三大鎖粒度對比

二、MyISAM 表級鎖機制

2.1 內部四隊列模型

• Current read-lock queue：持有讀鎖線程（按獲取時間序）。
• Pending read-lock queue：等待讀鎖線程。
• Current write-lock queue：持有寫鎖線程。
• Pending write-lock queue：等待寫鎖線程。

2.2 11 種內部鎖類型（thr_lock_type）

除常見 READ/WRITE 外，關鍵特殊類型：

• READ_NO_INSERT：禁止 Concurrent Insert。
• WRITE_CONCURRENT_INSERT：允許尾部並發插入。
• WRITE_ALLOW_READ：DDL 重建表階段，允許讀舊表。
• WRITE_DELAYED：INSERT DELAYED 專用。
• READ_HIGH_PRIORITY：高優先級讀（HIGH_PRIORITY hint）。

2.3 兼容矩陣核心規則

• 讀讀兼容、讀寫互斥、寫寫互斥。
• 寫鎖優先級高於讀鎖（預設）：Pending write 阻塞後續所有讀請求。
• LOW_PRIORITY_UPDATES=1 可反轉優先級，適合讀多寫少場景。

2.4 Concurrent Insert 機制

• concurrent_insert=2：無論中間有無空洞，允許尾部插入。
• =1（預設）：無空洞才允許尾部插入。
• =0：禁用。

三、InnoDB 行級鎖機制

3.1 四大鎖模式兼容矩陣

• 意向鎖（IS/IX）是表級鎖，僅表示「某線程打算在表裡加行鎖」，不阻塞除全表掃描外的 DDL。
• 行鎖（S/X）加在 **索引記錄** 上（聚簇索引或二級索引）。

3.2 Next-Key Lock（間隙鎖）機制

• InnoDB 在 **索引鍵值之間的間隙** 加鎖，防止幻讀（REPEATABLE READ 隔離級別）。
• 鎖定範圍 = (前一鍵值, 當前鍵值]（左開右閉）。
• **副作用**：不存在的鍵值也被鎖定，導致範圍內無法插入新行。

3.3 三大性能隱患

1. 無索引升級表鎖：WHERE 無索引 → 全表掃描 → 加表級 X 鎖，並發度歸零。
2. 索引過濾不全：複合索引未覆蓋所有條件 → 掃描到不符合條件的行也被鎖定。
3. 相同索引鍵不同行：WHERE a=1 AND b='x' vs WHERE a=1 AND b='y'，若只走 idx_a，兩事務互鎖。

四、實戰演示場景（摘要）

五、隔離級別與鎖關係

• READ UNCOMMITTED：不加 S 鎖，可能髒讀。
• READ COMMITTED：無間隙鎖，僅鎖定存在的索引記錄（RC 下半一致性讀）。
• REPEATABLE READ（預設）：**有間隙鎖**，防止幻讀。
• SERIALIZABLE：讀隱式加 S 鎖，等同表級共享鎖。

六、死鎖處理機制

• InnoDB 後台死鎖檢測線程（~毫秒級）檢測到 → 回滾 **修改行數較少** 的事務（非持有鎖時間）。
• 跨引擎/外部鎖無法檢測 → 依賴 innodb_lock_wait_timeout（預設 50s）超時回滾。
• SHOW ENGINE INNODB STATUS 可見最新死鎖詳情（LATEST DETECTED DEADLOCK）。

七、調優建議總攬

MyISAM 表鎖優化

1. 縮短查詢時間：拆分大 SQL、建高效索引、控制列類型、定期 OPTIMIZE TABLE。
2. 開啟 concurrent_insert=2，讓讀寫並行。
3. 讀多寫少設 low_priority_updates=1，提升讀優先級。

InnoDB 行鎖優化

1. 所有 WHERE 條件盡可能走索引，避免升級表鎖。
2. 設計**覆蓋索引**，精準鎖定目標行，縮小 Next-Key 範圍。
3. 減少範圍查詢（<, >, BETWEEN, LIKE '%...'），降低間隙鎖影響。
4. 控制事務大小：少改行、短持有、拆分長事務。
5. 業務允許下調低隔離級別（RC），消除間隙鎖。
6. 死鎖預防：
   a) 固定多表訪問順序。
   b) 單事務一次性鎖定所需資源。
   c) 熱點業務嘗試應用層排隊或升級表鎖（LOCK TABLES）。

八、鎖爭用監控指標

-- 表鎖
SHOW STATUS LIKE 'Table_locks%';
Table_locks_immediate: 立即獲取次數
Table_locks_waited: 等待次數（此值高需優化）

-- 行鎖
SHOW STATUS LIKE 'Innodb_row_lock%';
Innodb_row_lock_waits: 總等待次數
Innodb_row_lock_time_avg: 平均等待毫秒
Innodb_row_lock_time_max: 最大等待毫秒
Innodb_row_lock_time: 總等待毫秒

-- 詳細即時資訊
CREATE TABLE innodb_monitor (a INT) ENGINE=InnoDB;  -- 開啟監控
SHOW ENGINE INNODB STATUS\G  -- 查看事務、鎖、等待圖

九、小結

MyISAM 表鎖簡單低開銷但並發低；InnoDB 行鎖並發高但複雜、有間隙鎖副作用。生產環境**核心業務務必用 InnoDB**，並遵循「索引覆蓋、事務精簡、隔離級別適配、訪問順序固定」四大原則，將鎖爭用壓制在可接受水平。