MySQL 表鎖操作指南：LOCK TABLES 與 UNLOCK TABLES 實戰詳解

MySQL 表鎖定機制概述

MySQL 的鎖機制根據存儲引擎不同而有所差異。MyISAM、MEMORY 和早期的 ISAM 採用表級鎖；BDB 使用頁級鎖；而 InnoDB 則以行級鎖著稱。理解不同鎖粒度的特性，對於優化數據庫並發性能至關重要。

基本表鎖語法

MySQL 提供了顯式的表鎖命令，適用於需要在會話層面控制並發的場景：

mysql> LOCK TABLES userstat READ;
mysql> UNLOCK TABLES;

• READ LOCK（共享鎖）：允許當前會話讀取，阻塞其他會話的寫操作；其他會話仍可讀取。
• WRITE LOCK（排他鎖）：當前會話可讀寫，阻塞其他會話的所有讀寫操作。

鎖粒度對比與選型建議

行級鎖（InnoDB 預設）

優點：

• 多線程訪問不同行時衝突極低，並發度最高。
• 事務回滾僅影響少量行，數據損失風險小。
• 支持長時間鎖定單行記錄。

缺點：

• 記憶體開銷大，鎖結構佔用更多資源。
• 大範圍掃描或全表 GROUP BY 時性能劣於表鎖。
• 容易發生死鎖，需應用層處理重試邏輯。

表級鎖（MyISAM、MEMORY）

適用場景：

• 以讀操作為主，寫入極少的業務（如報表、日誌查詢）。
• 基於唯一索引的單行精確更新/刪除（WHERE unique_key = value）。
• 大量全表掃描與 GROUP BY，無寫入負載。

頁級鎖（BDB）

介於表鎖與行鎖之間，鎖定相鄰的一組記錄，並發度與開銷均居中，但同樣可能出現死鎖。

實戰案例：大批量寫入優化

當需要在高並發環境下對統計表進行大量 INSERT 與 SELECT，且無法利用並發插入特性時，可採用「臨時表 + 表鎖」策略：

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

此模式將寫入壓力集中在短暫的鎖定窗口內，最大程度減少對線上查詢的阻塞。

PHP 中調用表鎖示例

// 鎖定 stat_num 表進行寫操作
$sql = "LOCK TABLES stat_num WRITE";
$DatabaseHandler->exeCute($sql);

// 執行更新
$sql = "UPDATE stat_num SET `correct_num`=`correct_num`+1 WHERE stat_date='{$cur_date}'";
$DatabaseHandler->exeCute($sql);

// 解鎖
$sql = "UNLOCK TABLES";
$DatabaseHandler->exeCute($sql);

注意： 使用 LOCK TABLES 會隱式提交當前事務，且會話持有鎖期間不可訪問未鎖定的表。生產環境建議優先考慮行級鎖（InnoDB 事務）或樂觀鎖方案，僅在特定批量維護窗口使用表鎖。

鎖等待隊列與優先級控制

• 寫鎖請求：若表無鎖 → 加寫鎖；否則進入寫鎖隊列。
• 讀鎖請求：若表無寫鎖 → 加讀鎖；否則進入讀鎖隊列。

MySQL 預設寫鎖優先級高於讀鎖，可通過 LOW_PRIORITY_WRITE 或 HIGH_PRIORITY_READ 調整。MyISAM 另提供系統變量 low_priority_updates=1 降低寫優先級，緩解讀請求飢餓問題。