MySQL 鎖機制深度解析：表鎖、行鎖、死鎖與優化策略

鎖是資料庫並發控制的核心。MySQL 不同儲存引擎支援不同粒度的鎖：MyISAM/MEMORY 用表鎖，BDB 用頁鎖，InnoDB 用行鎖。本文系統分析三種鎖級別特性、加鎖規則、死鎖成因與實戰優化策略。

一、三大鎖級別對比

維度	表鎖	頁鎖	行鎖
典型引擎	MyISAM, MEMORY, CSV	BDB	InnoDB
鎖定粒度	整張表	一頁（通常 16KB）	單行記錄
並發效能	低（讀寫互斥）	中	高（細粒度）
記憶體開銷	極小	中等	大（每行鎖結構）
死鎖可能	無（一次性獲取所有鎖）	有	有
適用場景	唯讀、全表掃描、批量載入	較少用	高併發 OLTP、頻繁點查/更新

二、InnoDB 行鎖核心機制

1. 鎖模式

• 共享鎖 (S Lock / Read Lock)：SELECT ... LOCK IN SHARE MODE，兼容 S 鎖，阻塞 X 鎖。
• 排他鎖 (X Lock / Write Lock)：SELECT ... FOR UPDATE、UPDATE、DELETE、INSERT，阻塞 S/X 鎖。
• 意向鎖 (IS/IX)：表級標記，表示事務擬在行級加 S/X 鎖，用於快速判斷表級是否可加鎖。

2. 鎖算法（索引決定鎖型態）

• Record Lock：唯一索引精確匹配，鎖定單行。
• Gap Lock：範圍查詢鎖定間隙，防止幻讀（RR 隔離級別）。
• Next-Key Lock：Record + Gap 組合，鎖定區間 (前值, 當前值]。
• 無索引/全表掃描：退化為表鎖（鎖全表所有行 + 間隙），並發極差。

三、表鎖加鎖邏輯與佇列

-- 寫鎖 (WRITE LOCK)
LOCK TABLES tbl_name WRITE;
-- 讀鎖 (READ LOCK)
LOCK TABLES tbl_name READ;
-- 解鎖
UNLOCK TABLES;

隊列規則：

1. 無鎖 → 直接加請求鎖。
2. 寫鎖請求 → 進寫鎖隊列（高優先級）。
3. 讀鎖請求 → 進讀鎖隊列（低優先級）。
4. 釋放鎖時：優先喚醒寫鎖隊列 → 再喚醒讀鎖隊列。

可用 LOW_PRIORITY WRITE、HIGH_PRIORITY READ 調整優先級。

四、死鎖成因與排查

典型死鎖場景

-- Session A                          -- Session B
BEGIN;                                 BEGIN;
UPDATE t SET v=1 WHERE id=1;           UPDATE t SET v=2 WHERE id=2;
UPDATE t SET v=3 WHERE id=2;  (阻塞)   UPDATE t SET v=4 WHERE id=1;  (死鎖)
COMMIT;                                COMMIT;

InnoDB 自動檢測死鎖並回滾代價較小的事務（通常是更新行數少者）。

排查步驟

1. SHOW ENGINE INNODB STATUS\G 查看 LATEST DETECTED DEADLOCK 區塊。
2. 分析兩事務的 SQL 順序、索引使用情況。
3. 修正：統一訪問順序（如按主鍵遞增）、縮短事務、避免長事務、合理設計索引 將鎖降級為 Record Lock。

五、實戰優化策略

1. 選對引擎：高併發寫入務必用 InnoDB。
2. 索引覆蓋 WHERE 條件：避免全表掃描升級為表鎖。
3. 拆分大事務：批量更新分批提交，減少鎖持有時間。
4. 使用 INSERT ... ON DUPLICATE KEY UPDATE 替代「先查後插/更新」模式，減少間隙鎖。
5. 並發插入優化：MyISAM 設 concurrent_insert=2；InnoDB 自動支援尾部並發插入。
6. 調整鎖等待超時：innodb_lock_wait_timeout=50（預設 50s），防止長時間阻塞拖垮連接池。
7. 監控指標：Innodb_row_lock_waits、Innodb_row_lock_time_avg、Innodb_deadlocks。

六、PHP 代碼示範：表鎖保護計數器更新

$db->execute("LOCK TABLES stat_num WRITE");
$db->execute("UPDATE stat_num SET correct_num = correct_num + 1 WHERE stat_date = '{$cur_date}'");
$db->execute("UNLOCK TABLES");

⚠️ 僅適用於 MyISAM/MEMORY 或極少數強制串行化場景。InnoDB 應改用 UPDATE ... WHERE id = ? 配合行鎖，或 SELECT ... FOR UPDATE 顯式加鎖。