MySQL 鎖機制深度解析：三級鎖特性、MyISAM 表鎖爭用、並發插入、調度優化與主從分離策略

理解 MySQL 鎖機制是高併發優化的前提。本文系統講解三級鎖（行級、頁級、表級）特性對比、MyISAM 表鎖模式與爭用分析、並發插入控制、鎖調度優先級調整，並提出讀寫分離作為根治高併發鎖爭用的架構方案。

一、三級鎖特性對比

二、MyISAM 表鎖機制

1. 兩種鎖模式

• 表共享讀鎖 (Table Read Lock)：SELECT 前自動加。不阻塞其他讀，阻塞寫。
• 表獨占寫鎖 (Table Write Lock)：INSERT/UPDATE/DELETE 前自動加。阻塞所有其他讀寫。

2. 爭用情況監控

SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 76939364 |
| Table_locks_waited    | 305089  |
+-----------------------+---------+

Table_locks_waited 較高（此例 30 萬+）說明存在嚴重表鎖爭用。

3. 實測驗證鎖行為

-- 客戶端 1：長時間讀操作（3 分 15 秒）
SELECT COUNT(*) FROM gz_phone GROUP BY ua;

-- 客戶端 2：並發讀 → 立即返回（0.01s）
SELECT id, phone FROM gz_phone LIMIT 1000, 10;

-- 客戶端 2：並發寫 → 等待 2 分 57 秒（讀鎖釋放後執行）
UPDATE gz_phone SET phone='11111111111' WHERE id=1001;

-- 客戶端 1：長時間寫操作（3 分 4 秒）
UPDATE gz_phone SET phone='11111111111';

-- 客戶端 2：並發讀 → 等待 2 分 58 秒
SELECT * FROM gz_phone LIMIT 10;

-- 客戶端 3：並發寫 → 等待 3 分 50 秒
UPDATE gz_phone SET phone='55555' WHERE id=1;

結論：寫鎖獨佔，讀寫互斥，長查詢/長事務會嚴重阻塞併發。

三、MyISAM 並發插入

系統變量 concurrent_insert 控制：

建議：純追加寫入場景（日誌、埋點）設 2 可提升併發；有刪除更新的表謹慎使用。

四、MyISAM 鎖調度優先級

默認寫優先：寫請求插隊到讀請求前面。大量更新導致查詢「餓死」。

調整手段

1. 啟動參數：--low-priority-updates → 讀優先
2. 會話級：SET LOW_PRIORITY_UPDATES=1
3. 語句級：INSERT/UPDATE/DELETE LOW_PRIORITY ...

缺點：長查詢會導致寫入餓死。無法同時滿足讀寫低延遲。

五、根治方案：主從讀寫分離

架構層面解決鎖爭用：

• 主庫：承擔所有寫入（INSERT/UPDATE/DELETE）、DDL、關鍵實時讀
• 從庫：承擔所有報表、分析、非實時讀查詢
• 同步：主庫 Binlog → 從庫 Relay Log → SQL Thread 重放
• 優勢：讀寫物理隔離，無鎖爭用；可水平擴展多從庫分攤讀壓力

六、InnoDB 行級鎖補充

• 基於索引加鎖：無索引條件 → 全表掃描 → 退化為表鎖
• 間隙鎖：防止幻讀，可鎖定不存在的記錄區間
• 死鎖檢測：自動回滾代價較小的事務
• 鎖升級：不支援，始終行級

七、鎖優化實戰清單

1. ☐ 核心業務表**強制使用 InnoDB**
2. ☐ 所有 WHERE/JOIN/ORDER BY 條件**建立索引**，避免全表掃描退化表鎖
3. ☐ 拆分長事務，縮短鎖持有時間
4. ☐ 避免 SELECT ... LOCK IN SHARE MODE / FOR UPDATE 濫用
5. ☐ 監控 Table_locks_waited、Innodb_row_lock_waits、Innodb_row_lock_time_avg
6. ☐ 讀寫分離架構落地，報表查詢導流至從庫
7. ☐ 定期 OPTIMIZE TABLE 減少碎片，提升並發插入效率

鎖是資料庫併發控制的核心。MyISAM 表鎖在高併發寫入場景下是性能瓶頸，InnoDB 行級鎖配合合理索引設計是主流選擇。架構層面，讀寫分離是解決讀寫鎖爭用的終極方案。