MySQL 表鎖操作指南：LOCK TABLES 語法、優先級控制、並發插入與效能優化建議

前言：表鎖是 MyISAM 並發控制的唯一手段

除 InnoDB/BDB 外，所有引擎採用表級鎖。理解 LOCK TABLES、鎖優先級、並發插入機制、爭用優化，是 MyISAM 環境效能調優的必修課。本文系統整理語法、行為、優化策略與避坑指南。

LOCK TABLES 基本語法

-- 鎖定單表寫
LOCK TABLES t1 WRITE;

-- 鎖定多表：讀寫混合
LOCK TABLES t1 READ, t2 WRITE, t3 READ LOCAL;

-- 釋放所有鎖
UNLOCK TABLES;

• READ：共享鎖，允許並發讀，阻塞寫
• WRITE：排他鎖，阻塞所有其他鎖
• READ LOCAL：允許非衝突並發插入 (MyISAM 專用)
• 同一會話中，鎖定的表**必須在同一 LOCK TABLES 中一次性申請**，否則報錯

鎖優先級機制：寫優於讀

• 寫鎖請求插隊讀鎖佇列前端
• 長時間 SELECT 導致後續 UPDATE 等待，再導致新 SELECT 等待 → **讀寫雙重飢餓**

並發插入特性

MyISAM 滿足條件時，INSERT 與 SELECT 可並發：

• 表中間無空洞 (無 DELETE/UPDATE 變長行)
• 新行追加至檔案末尾
• 配合 READ LOCAL 或 concurrent_insert 系統變數控制

減少表鎖爭用的 12 條實戰建議

1. 加速 SELECT：建立覆蓋索引、摘要表、物化視圖，縮短鎖持有時間
2. 啟動參數 --low-priority-updates：降低寫優先級，讓讀優先
3. 會話級 SET LOW_PRIORITY_UPDATES = 1：指定連線所有寫操作低優先級
4. 語句級 LOW_PRIORITY：INSERT LOW_PRIORITY ...、UPDATE LOW_PRIORITY ...
5. 語句級 HIGH_PRIORITY：SELECT HIGH_PRIORITY ... 強制搶佔
6. max_write_lock_count：寫鎖達閾值後暫時提升讀優先級 (3.23.7+)
7. 改用 MyISAM 並發插入：滿足無空洞條件時自動生效
8. INSERT DELAYED：寫入緩衝隊列，客戶端立即返回 (高寫入突發緩衝)
9. DELETE ... LIMIT n：分批刪除，減少長鎖
10. SELECT ... SQL_BUFFER_RESULT：結果先存臨時表，釋放表鎖
11. 修改源碼 thr_lock.c：單一鎖佇列，讀寫同權 (極端方案)
12. 拆表/分區：將熱點表拆分，降低單表鎖範圍

InnoDB/BDB 環境下的 LOCK TABLES 注意事項

• InnoDB 自動行鎖，**不建議手動 LOCK TABLES** (會強制升級為表鎖，降低併發)
• 若必須用 (如 mysqldump --lock-tables)，需在事務外執行，並儘快 UNLOCK TABLES

何時考慮遷移至 InnoDB

• 頻繁 Table_locks_waited 飆升
• 讀寫混合導致嚴重延遲
• 需要事務/外鍵/崩潰恢復
• 高併發寫入場景

結語

表鎖雖簡單，優先級倒置、並發插入條件、爭用優化卻暗藏大學問。MyISAM 環境下，「加速查詢、低優先級寫入、並發插入利用、拆表分區」四大戰術組合拳，能解決 90% 鎖爭用問題。長期來看，**遷移至 InnoDB 行鎖 + MVCC** 才是架構層面的終極答案。