線上項目遭遇 LOAD DATA 並發導入引發死鎖。本文從 InnoDB 自增鎖原理、innodb_autoinc_lock_mode 三種模式、binlog 格式對主從一致性的要求、死鎖形成的四個必要條件、兩套徹底解決方案等維度深度剖析。
CREATE TABLE tb (
id INT PRIMARY KEY AUTO_INCREMENT,
c INT NOT NULL
) ENGINE=InnoDB;
-- 會話 1
LOAD DATA INFILE 'data1.csv' INTO TABLE tb;
-- data1.csv: 1,100 / 2,100 / 3,100
-- 會話 2
LOAD DATA INFILE 'data2.csv' INTO TABLE tb;
-- data2.csv: 10,100 / 11,100 / 12,100
------------------------
LATEST DETECTED DEADLOCK
------------------------
TRANSACTION 1:
WAITING FOR LOCK: RECORD LOCKS (索引記錄鎖)
TRANSACTION 2:
HOLDING LOCK: RECORD LOCKS (同一記錄)
WAITING FOR LOCK: AUTO_INC TABLE LOCK
*** WE ROLL BACK TRANSACTION 2 ***
InnoDB 為自增列維護表級鎖（AUTO_INC），每次插入需獲取：
AUTO_INC 鎖AUTO_INCREMENT 值 n，遞增nAUTO_INC 鎖（特殊：非事務結束釋放）| 模式 | 值 | 行為 | 適用場景 |
|---|---|---|---|
| 傳統 | 0 | 語句級鎖，全程持有 AUTO_INC 至語句結束 | 兼容性、LOAD DATA/INSERT...SELECT 強制退化 |
| 連續 | 1 (默認) | 「簡單插入」語句結束前釋放；「批量插入」退化為 0 | 均衡性能與一致性 |
| 交錯 | 2 | 所有插入輕量級互斥量，無表鎖，自增值可能不連續 | 高並發，**需 binlog_format=ROW** |
關鍵點：LOAD DATA 屬「批量插入」，**即使模式=1 也退化為模式 0**，持有 AUTO_INC 表鎖至語句結束。
STATEMENT：記錄 SQL 文本。LOAD DATA 需保證主從自增值**連續**，依賴 SET INSERT_ID 事件同步起始 IDROW：記錄行變更。無需連續自增值，主從自然一致→ 模式 0/1 為滿足 STATEMENT 格式主從一致，強制批量插入持有 AUTO_INC 鎖保證連續性。
若 LOAD DATA 文件中**已包含 id 值** (如本例)：
AUTO_INC 鎖，調用 set_max 更新 AUTO_INCREMENT 計數器AUTO_INC 鎖→ **行鎖（記錄鎖）先於 AUTO_INC 鎖獲取，行鎖事務結束釋放，AUTO_INC 語句結束釋放**。
時間 →
會話 1 (LOAD data1) 會話 2 (LOAD data2)
│ │
├─ 插入 id=1 (X 鎖) ──────────►│ (阻塞：等待 id=1 X 鎖)
│ │
├─ 申請 AUTO_INC 鎖 (成功) ◄───┤ (持有 id=10 X 鎖，申請 AUTO_INC)
│ │
├─ 插入 id=2,3... │
│ │
▼ (語句結束，釋放 AUTO_INC) ▼ (死鎖檢測，回滾會話 2)
四個必要條件滿足：
AUTO_INC 表鎖 + 記錄鎖AUTO_INC；會話 2 持有記錄鎖等 AUTO_INCAUTO_INC 鎖ALTER TABLE tb MODIFY id INT PRIMARY KEY; -- 移除 AUTO_INCREMENT
-- 或建表時直接不加
CREATE TABLE tb (id INT PRIMARY KEY, c INT NOT NULL) ENGINE=InnoDB;
適用場景：業務層已生成全局唯一 ID（雪花算法、UUID、分段號段），數據導入文件含完整主鍵。
效果：無自增列 → 無 AUTO_INC 鎖 → 死鎖根因消失。並發導入僅涉及行鎖，自然串行化無死鎖。
[mysqld]
innodb_autoinc_lock_mode = 2
binlog_format = ROW
原理：模式 2 使用輕量級互斥量替代表鎖，自增值分配無序但唯一；ROW 格式 binlog 記錄實際行變更，主從無需連續自增值即可保證一致性。
前提：業務接受自增值不連續（如中間有空洞）；主從均為 5.1.22+ 支持 ROW 格式。
優勢：無需變更表結構、應用代碼，僅配置調整，風險最小。
| 誤區 | 後果 | 正確姿勢 |
|---|---|---|
| 以為模式 1 能解決 LOAD DATA 並發 | 批量插入退化為模式 0，死鎖依然發生 | 模式 1 僅優化「簡單插入」；批量插入需模式 2 |
| 模式 2 配合 STATEMENT binlog | 主從自增值不一致，數據漂移 | 模式 2 **強制要求** binlog_format=ROW |
| 未指定 id 就導入，依賴自增 | 並發導入自增值交錯、主從不一致 | 業務層生成 ID，文件含主鍵，移除 AUTO_INCREMENT |
| 死鎖後只加索引不改鎖模式 | 治標不治本，高並發下必復發 | 從鎖機制入手：移除自增或模式 2+ROW |
LOAD DATA 死鎖本質是 **批量插入強制持有 AUTO_INC 表鎖 + 行鎖先行獲取 + 循環等待** 的經典組合。根治路徑只有二：業務層接管 ID 生成、移除 AUTO_INCREMENT（架構級治本）或 innodb_autoinc_lock_mode=2 + binlog_format=ROW（配置級治標）。選型原則：新建表強制無自增、舊表評估兼容性後切模式 2。深入理解鎖機制、binlog 語義、參數交互，是 DBA 從「處理死鎖」進化到「設計無鎖架構」的分水嶺。
