降低 MySQL 鎖競爭：四大實戰措施縮短用戶等待

MySQL 透過表鎖/行鎖實現併發控制，但鎖競爭會導致線程排隊、響應時間飆升。典型場景：用戶 A 查詢 → 用戶 B 更新（等 A 結束） → 用戶 C 查詢（等 B 結束，因更新優先級高於查詢）。本文給出四大針對性措施，從鎖粒度、優先級、SQL 改寫三個維度化解競爭。

措施一：LOCK TABLES 批量更新，減少鎖持有次數

高頻更新表（如收銀流水）可在事務中顯式鎖表：

LOCK TABLES orders WRITE;
UPDATE orders SET ... WHERE ...;
UPDATE orders SET ... WHERE ...;
...
UNLOCK TABLES;

一次鎖定完成多筆更新，避免重複加鎖/解鎖開銷，壓縮鎖持有窗口，間接降低與 SELECT 的衝突概率。

措施二：業務拆表，物理隔離熱點

將單張熱表按業務維度拆分：

• 收銀系統：每台 POS 機一張表（pos_1_sales, pos_2_sales…），寫入互不干擾。
• 統計分析時建立合併視圖（VIEW all_sales AS SELECT * FROM pos_1_sales UNION ALL ...）或定時匯總至倉庫表。

核心思想：把大鎖拆成多把小鎖，將併發衝突域最小化。

措施三：靈活調整語句優先級，業務優先級可控

MySQL 預設更新優先級 > 查詢優先級。四種調整手段，作用域由小到大：

決策原則： 以「用戶體驗優先」為準則，核心查詢路徑提升優先級，後台維護任務降低優先級，避免全局參數影響面過廣。

措施四：針對混合負載使用專用語法

• INSERT DELAYED：寫入緩存隊列立即返回，適用「寫入不需即時可見」場景（如日誌、統計計數）。
• DELETE LIMIT n：分批刪除大量數據，避免長事務持鎖阻塞查詢。
• SELECT ... LOCK IN SHARE MODE / FOR UPDATE：行級鎖精準控制，替代表鎖（需 InnoDB + 索引命中）。

總結

鎖競爭本質是併發度 × 鎖持有時間 × 鎖粒度的乘積。優化路徑：

1. 縮小粒度：InnoDB 行鎖替代表鎖、業務拆表。
2. 縮短持有：批量操作、分批提交、避免長事務。
3. 錯峰調度：優先級分級、DELAYED 寫入、讀寫分離。

併發低時無需過度優化；併發高時，先分析 SHOW ENGINE INNODB STATUS 鎖等待詳情，再針對性施策，才能事半功倍。