MySQL 數據庫鎖衝突三大典型場景與解決方案：死鎖、鎖升級、樂觀悲觀鎖選型

兩大基礎鎖類型

• 共享鎖 (S Lock)：SELECT ... LOCK IN SHARE MODE，兼容 S，阻塞 X
• 排他鎖 (X Lock)：SELECT ... FOR UPDATE、UPDATE/DELETE/INSERT，阻塞 S/X

InnoDB 默認行鎖（索引命中時），無索引退化為表鎖。

死鎖場景一：多表更新順序反向

事務 A 鎖表 1 → 鎖表 2；事務 B 鎖表 2 → 鎖表 1。

解決

• **代碼層強制統一順序**（如按表名字母序）
• 存儲過程封裝多表操作
• 單條 SQL 多表更新（MySQL 支持 UPDATE t1 JOIN t2 ... SET ...）

死鎖場景二：查詢後更新的鎖升級衝突

-- 事務 A
SELECT * FROM t WHERE id=1 LOCK IN SHARE MODE;  -- 持有 S 鎖
UPDATE t SET c=2 WHERE id=1;  -- 嘗試升級為 X 鎖

-- 事務 B
UPDATE t SET c=3 WHERE id=1;  -- 持有 X 鎖，阻塞 A 升級
-- A 持有 S 阻塞 B 獲取 X → 死鎖

解決方案對比

**推薦**：核心業務「樂觀鎖 + 重試」為主，「前端防重」輔助；極少數強一致場景（如扣款）用「悲觀鎖 + 短事務」。

死鎖場景三：無索引導致全表掃描 → 鎖升級

• UPDATE/DELETE 無索引條件 → 掃全表 → 加表鎖（或大量行鎖）
• 多個此類事務並發 → 死鎖/嚴重阻塞

解決

1. EXPLAIN 分析 SQL，消除 type=ALL
2. 為 WHERE/JOIN/ORDER BY 列建立合適索引
3. 避免復雜多表關聯 UPDATE，拆分為單表操作

鎖問題根因：代碼質量

鎖衝突 90% 源於**應用代碼設計缺陷**（順序、索引、事務大小、併發控制），而非數據庫本身。質量內建於設計編碼階段，測試只能驗證，不能創造質量。

最佳實踐清單

1. 所有多表更新**統一順序**
2. 核心更新引入**樂觀鎖版本列**
3. 事務**最小化**：只包含必要寫操作，讀操作移出事務
4. 業務允許時用 **READ COMMITTED** 隔離級別
5. 定期 EXPLAIN 核心 SQL，消除全表掃描
6. 監控 Innodb_row_lock_waits、Innodb_row_lock_time_avg 指標

鎖是保護數據一致性的盾，用不好就是傷害性能的刀。懂原理、守規範、重設計，才能讓並發系統穩如磐石。