MySQL InnoDB 行級鎖實戰：共享鎖/排他鎖用法、死鎖成因與預防四大條件破壞法

兩類行鎖

業務場景：用戶提現 (扣款 + 記錄流水)

BEGIN;
SELECT coin FROM user WHERE uid = 123 FOR UPDATE;  -- 加排他鎖
UPDATE user SET coin = coin - 100 WHERE uid = 123;
INSERT INTO cash_log (uid, amount) VALUES (123, 100);
COMMIT;  -- 或 ROLLBACK

• 單表單行排他鎖無死鎖風險。
• 多表、多行、不同順序加鎖 → 死溫床。

死鎖四必要條件 (缺一不可)

1. 互斥：資源獨佔 (行鎖天然滿足)。
2. 請求保持：持有鎖等待新鎖。
3. 不可剝奪：鎖只能主動釋放 (Commit/Rollback)。
4. 環路等待：T1 等 T2 的鎖，T2 等 T1 的鎖。

破壞任一條件即可避免死鎖，最實用的是 破壞環路：統一加鎖順序。

經典死鎖案例與修復

-- 表結構
-- id PK, name IDX, age 普通列

-- Session A
UPDATE user SET name='x' WHERE name='y';  -- 走 name 索引 → 需回表鎖 PK

-- Session B
UPDATE user SET name='y' WHERE id=100;    -- 走 PK 索引 → 需鎖 name 索引

-- 並發執行 → A 持有 name 鎖等 PK，B 持有 PK 鎖等 name → 死鎖

修復：統一按主鍵順序加鎖

-- 改造 Session A
UPDATE user SET name='x' WHERE id = (SELECT id FROM user WHERE name='y');  -- 先走 PK

• 子查詢先定位 PK，主查詢直接按 PK 更新，雙方加鎖順序一致 (PK → name)，環路斷開。
• 原則：所有事務按相同索引順序訪問表/行 (通常 PK 順序)。

其他預防手段

1. 縮短事務：只在必須瞬間持有鎖，業務邏輯 (HTTP 呼叫、複雜計算) 移出事務。
2. 低隔離級別：READ COMMITTED + innodb_locks_unsafe_for_binlog=1 (5.7 棄用) 可減少間隙鎖，但可能導致幻讀，需業務容忍。
3. 重試機制：應用層捕獲 ER_LOCK_DEADLOCK (1213) 指數退避重試 2-3 次。
4. 監控告警：SHOW ENGINE INNODB STATUS 定期抓取 LATEST DETECTED DEADLOCK 入庫分析熱點表/索引。

死鎖 vs 鎖等待

• 鎖等待：單向阻塞，超時 (innodb_lock_wait_timeout 默認 50s) 報 ER_LOCK_WAIT_TIMEOUT (1205)。
• 死鎖：循環等待，InnoDB 死鎖檢測器 (每秒掃描) 主動選擇代價最小事務回滾，報 1213。

掌握加鎖順序統一、事務極小化、重試兜底三板斧，死鎖從「疑難雜症」變成「可控小概率事件」。