MySQL 資料庫鎖產生原因與死鎖三大典型場景解決方案

鎖是資料庫並發控制核心。本文梳理 MySQL 兩大基礎鎖類型（排它鎖 X、共享鎖 S），深度剖析三類常見死鎖成因與針對性解決方案，並介紹樂觀鎖/悲觀鎖應用模式。

基礎鎖類型

• 排它鎖 (X Lock)：寫操作持有，阻塞其他讀寫。
• 共享鎖 (S Lock)：讀操作持有，允許並發讀，阻塞寫。

死鎖場景一：多表操作順序不一致

現象：事務 A 鎖表 1 → 鎖表 2；事務 B 鎖表 2 → 鎖表 1。雙方互相等待。

解決：統一所有事務的多表操作順序（如按表名字母序），從代碼邏輯層面消除循環等待。

死鎖場景二：查詢後更新同一行（鎖升級衝突）

現象：用戶 A 查詢行（持有 S 鎖）→ 更新（需升級 X 鎖）；用戶 B 直接更新（持有 X 鎖）。A 等 B 釋放 X，B 等 A 釋放 S → 死鎖。常見於前端重複點擊提交按鈕。

解決：

1. 前端防重：點擊即禁用按鈕。
2. 樂觀鎖：表加 version 字段，更新時 WHERE version = old_version，失敗則重試。避免長事務鎖開銷，提升高併發吞吐。**注意**：外部系統繞過應用層可能造成髒寫。
3. 悲觀鎖：SELECT ... FOR UPDATE 獨佔讀取。保證強一致性，但長事務嚴重拖垮效能，慎用。

死鎖場景三：全表掃描導致行鎖升級為表鎖

現象：UPDATE 無索引走全表掃描 → 行鎖升級為表鎖，多事務並發極易死鎖/阻塞。大表缺索引同理。

解決：

• 避免複雜多表關聯查詢；
• EXPLAIN 分析 SQL，針對全表掃描建立適當索引；
• 定期檢視 SHOW ENGINE INNODB STATUS 死鎖日誌。

總結

鎖問題本質是**代碼品質問題**。如同製造業質量在生產過程決定而非檢測環節，**設計與編碼階段**即應考慮並發控制，測試只能驗證無法窮盡所有場景。