MySQL 5.0 官方手冊深度解讀：鎖機制與表鎖優化策略 (第五篇)

鎖機制總覽：不同引擎、不同粒度

核心原則：沒有絕對最好的鎖，只有最適合應用存取模式的鎖。Web 應用多讀少寫、索引更新、追加寫入 → MyISAM 極佳；高併發寫入、事務需求 → InnoDB 必選。

表鎖機制深度解析 (MyISAM 等)

寫鎖 (WRITE LOCK) 獲取邏輯

1. 表無鎖 → 直接加寫鎖
2. 表有鎖 → 請求進入寫鎖佇列等待

讀鎖 (READ LOCK) 獲取邏輯

1. 表無寫鎖 → 直接加讀鎖 (可共存多個讀鎖)
2. 表有寫鎖 → 請求進入讀鎖佇列等待

關鍵優先級規則：寫鎖 > 讀鎖

鎖釋放時，寫鎖佇列優先獲得鎖，讀鎖佇列必須等所有寫鎖完成。意味著：大量更新操作會導致 SELECT 長時間等待。

監控鎖爭用

SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |  -- 立即獲得
| Table_locks_waited    | 15324   |  -- 等待過
+-----------------------+---------+

Table_locks_waited / Table_locks_immediate > 1% 即需關注優化。

MyISAM 並發插入 (Concurrent Insert) 特性

• 條件：資料檔中間無空洞 (無刪除/更新產生碎片)
• 新記錄追加至檔案末尾，同時允許 SELECT 讀取
• 3.23.7+ (Windows 3.23.25+) 支援
• 空洞被填補後自動恢復並發插入

大量 INSERT + SELECT 衝突的緩解方案

-- 使用暫存表批次轉移
LOCK TABLES real_table WRITE, insert_table WRITE;
INSERT INTO real_table SELECT * FROM insert_table;
TRUNCATE TABLE insert_table;
UNLOCK TABLES;

行鎖 vs 表鎖：優劣勢權衡

行鎖優點 (InnoDB)

• 不同行併發衝突極低
• 回滾開銷小 (僅影響該行)
• 支援長時間單行鎖定

行鎖缺點

• 記憶體開銷大 (每鎖一行需鎖結構)
• 全表掃描/大範圍 GROUP BY 效能差 (需鎖大量行)
• 鎖升級風險 (某些條件下升級為表鎖)

表鎖優於行鎖/頁鎖的場景

1. 絕大多數操作為讀取
2. 嚴格索引條件下的單行更新/刪除 (WHERE pk=val)
3. SELECT + INSERT 並發、極少 UPDATE/DELETE
4. 大量全表掃描 + GROUP BY、無寫入

多版本並發控制 (MVCC) - InnoDB 核心優勢

「按需複製」/「寫時複製」：讀取不加鎖，讀到一致性快照；寫入建立新版本。優點：讀寫不阻塞。缺點：最壞情況記憶體佔用更高。

應用層鎖替代方案

SELECT GET_LOCK('my_lock', 10);  -- 獲取命名鎖，最多等 10 秒
-- 業務邏輯
SELECT RELEASE_LOCK('my_lock');  -- 釋放

僅適用於可信任應用程式 (勸告鎖，不強制)，適合分散式協調、單機序列化。

表鎖優化實戰策略

1. 縮短鎖持有時間

• 優化慢查詢 (加索引、避免全表掃描)
• 拆分大表 (熱/冷資料分離)
• 使用 SQL_BUFFER_RESULT 將結果集寫入暫存表，快速釋放表鎖

2. 調整優先級

• 啟動參數 --low-priority-updates：降低寫入優先級，SELECT 優先
• Session 級：SET LOW_PRIORITY_UPDATES=1
• 語句級：INSERT LOW_PRIORITY ...、SELECT HIGH_PRIORITY ...
• max_write_lock_count：寫鎖達閾值後暫時提升讀優先級

3. 選擇適合的引擎

• 並發 INSERT + SELECT 衝突 → MyISAM (支援並發插入)
• 大量 INSERT + DELETE 衝突 → INSERT DELAYED (MyISAM) 或批次處理
• SELECT + DELETE 衝突 → DELETE ... LIMIT n 分批刪除

4. 程式碼層面優化 (進階)

修改 mysys/thr_lock.c 合併讀寫鎖佇列，讓優先級平等。僅建議極度熟悉 MySQL 內核的團隊嘗試。

鎖問題診斷清單

總結：鎖優化的決策樹

1. 識別存取模式：讀多寫少 → MyISAM/表鎖；寫多/事務 → InnoDB/行鎖
2. 監控爭用指標：Table_locks_waited、InnoDB 行鎖等待
3. 優化查詢與索引：縮短鎖持有時間 (根治)
4. 調整優先級：LOW_PRIORITY_UPDATES 等參數 (緩解)
5. 架構層面：拆表、分庫、讀寫分離、引擎選擇 (徹治)

鎖不是調校參數，而是架構決策的體現。理解業務模式，選對引擎，優化查詢，才是解決鎖爭用的正道。