MySQL InnoDB 死鎖深度分析：INSERT SELECT 與 DELETE 並發觸發、鎖升級、四大死鎖形式、避免策略

InnoDB 死鎖實戰剖析：備份 INSERT SELECT 與刪除 DELETE 並發衝突完整還原

案例場景

• 表 teamUser：PK (uid, Id)、二級索引 k_id_titleWeight_score (Id, titleWeight, score)、InnoDB。
• 語句 1 (備份)：INSERT INTO backup_table SELECT * FROM teamUser (全表掃描)。
• 語句 2 (清理)：DELETE FROM teamUser WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week' (走二級索引前綴)。
• 並發執行 → 死鎖，InnoDB 檢測回滾語句 1 (代價較小)。

死鎖日誌關鍵資訊 (SHOW ENGINE INNODB STATUS)

• 兩事務**鎖衝突發生在主鍵索引 (聚簇索引) 頁面** (page no 218436)。
• 事務 1 (INSERT SELECT) 持有主鍵**共享鎖 (S)**，等待主鍵**排他鎖 (X)**。
  事務 2 (DELETE) 持有主鍵**排他鎖 (X)**，等待主鍵**排他鎖 (X)** (再次申請)。
• GDB 追蹤 lock_rec_lock 確認：InnoDB **逐行獲取鎖** (邊掃描邊加鎖)，非一次性獲取。

鎖衝突根因分析

1. 掃描順序不同：語句 1 按主鍵順序 (聚簇索引) 掃描；語句 2 按二級索引 (Id, titleWeight, score) 順序掃描 → **訪問行順序相反**。
2. 逐行加鎖：InnoDB 掃描到符合條件行即加鎖，非預先鎖定全表。
3. 形成循環等待：
   T1 鎖住主鍵頁 A (S) → 需主鍵頁 B (X)
   T2 鎖住主鍵頁 B (X) → 需主鍵頁 A (X)
   → 死鎖。

InnoDB 四大經典死鎖形式

聚簇索引 vs 二級索引結構差異 (鎖行為根本原因)

• 聚簇索引 (PK)：葉節點 = 完整行資料。掃描 PK 即掃描全表。
• 二級索引：葉節點 = 索引欄值 + PK 值 (指標)。較窄，掃描快。
• 二級索引查找流程：走二級索引 B+ 樹 → 取得 PK 值 → **回表** 走 PK B+ 樹取完整行 → 加鎖 PK 頁面。

避免死鎖的六大工程策略

1. 錯峰執行：大表備份 INSERT SELECT 安排低峰獨立時段，避免與寫入衝突；或改用 SELECT INTO OUTFILE + LOAD DATA INFILE (不鎖表、速度更快)。
2. 縮小鎖範圍：WHERE 條件盡量精確命中少量行 (走主鍵/唯一鍵)，避免大範圍掃描持有大量鎖。
3. 統一索引訪問路徑：更新/刪除語句 WHERE 條件**統一用主鍵或統一用同一二級索引**，避免 PK vs Secondary 交叉。
4. 拆分複雜 SQL：複雜 WHERE 先 SELECT pk INTO @tmp，再 DELETE ... WHERE pk IN (@tmp)，減少掃描範圍與鎖持有時間。
5. 減少嵌套/大事務：單條 SQL 隱式事務最安全；顯式事務盡量短、少表、早提交。
6. 應用層重試：捕獲 ER_LOCK_DEADLOCK (1213)，指數退避重試 (建議 3 次)，降低業務影響。

死鎖檢測與監控

• SHOW ENGINE INNODB STATUS\G 查看最近死鎖詳情 (配合 pager less 閱讀)。
• 啟用 innodb_print_all_deadlocks=ON (5.6.2+) 將所有死鎖寫入錯誤日誌。
• 監控 Innodb_deadlocks 計數器 (Prometheus/Grafana 告警)。
• 定期 pt-deadlock-logger 收集分析死鎖模式。