MySQL 鎖機制深度解析：表鎖、行鎖、頁鎖特性與最佳實踐

MySQL 支援三種鎖粒度：表級鎖、頁級鎖、行級鎖，分別由不同儲存引擎實作。理解其特性差異、適用場景與死鎖處理，是構建高並發應用的關鍵。本文系統整理鎖機制原理、優缺點對比、實務操作範例與優化建議。

一、鎖層級與代表引擎對照

二、行級鎖優缺點詳細分析

優點

• 多連線存取不同記錄時大幅減少鎖衝突
• 異常回滾時僅影響少量資料，減少資料遺失
• 支援長時間對單一記錄加鎖

缺點

• 記憶體佔用較頁級、表級鎖大
• 大量資料操作時 I/O 開銷大，適合寫操作而非全表掃描讀取
• 易發生死鎖

三、鎖相容性與隊列機制

寫鎖（排他鎖）

1. 表未加鎖 → 加寫鎖
2. 否則 → 請求進入寫鎖隊列等待

讀鎖（共享鎖）

1. 表無寫鎖 → 加讀鎖
2. 否則 → 請求進入讀鎖隊列等待

可使用 LOW_PRIORITY（寫/讀）與 HIGH_PRIORITY（讀）調整隊列優先序。

四、大量 INSERT 與 SELECT 並行優化技巧

當並行插入不可行時，可採用暫存表緩衝：

LOCK TABLES real_table WRITE, insert_table WRITE;
INSERT INTO real_table SELECT * FROM insert_table;
TRUNCATE TABLE insert_table;
UNLOCK TABLES;

五、InnoDB 與 BDB 死鎖特性

• InnoDB：自動獲取行鎖，死鎖發生於事務執行過程中
• BDB：SQL 執行時獲取頁鎖，而非事務開始時
• 兩者皆可能產生死鎖，需應用層捕捉錯誤碼重試

六、表級鎖更優適用場景

• 以讀取為主的工作負載
• 嚴格索引條件下的單列更新/刪除：
  UPDATE tbl SET col=val WHERE unique_key=key_val;
DELETE FROM tbl WHERE unique_key=key_val;
• 大量 SELECT、INSERT 並發，極少 UPDATE/DELETE
• 大量全表掃描與 GROUP BY，無寫入操作

七、版本並發控制（MVCC）補充

InnoDB 透過多版本並發控制實現「讀不阻寫、寫不阻讀」，支援基於時間點的一致性視圖，又稱時間旅行、寫時複製或按需複製。

八、PHP 實務範例：表鎖與行鎖操作

// 表鎖（MyISAM 適用）
$sql = "LOCK TABLES stat_num WRITE";
$db->execute($sql);
$sql = "UPDATE stat_num SET correct_num=correct_num+1 WHERE stat_date='{$cur_date}'";
$db->execute($sql);
$sql = "UNLOCK TABLES";
$db->execute($sql);

// 行鎖（InnoDB 適用，配合事務）
$db->query("SET AUTOCOMMIT=0");
$row = $db->fetch_first("SELECT * FROM prizes WHERE id={$id} FOR UPDATE");
$db->query("DELETE FROM prizes WHERE id={$row['id']}");
$db->query("COMMIT");

九、死鎖預防最佳實踐

1. 固定存取資料表與資料列的順序
2. 縮短事務長度，減少鎖持有時間
3. 使用 SELECT ... FOR UPDATE 明確加鎖
4. 合理設計索引，減少掃描範圍
5. 捕捉 ER_LOCK_DEADLOCK (1213) 錯誤碼並指數退避重試