MySQL LOAD DATA 引發死鎖深度剖析與兩種解決方案

線上數據分析專案，兩條並發 LOAD DATA 向同一 InnoDB 表（含自增主鍵）導入資料，觸發死鎖。本文從 AUTO_INC 鎖機制、Binlog 主從一致性、鎖獲取時機三個維度完整復現並給出方案。

場景與現象

CREATE TABLE tb (id INT PRIMARY KEY AUTO_INCREMENT, c INT NOT NULL) ENGINE=InnoDB;
LOAD DATA INFILE 'data1.csv' INTO TABLE tb;  -- 含 id=1,2,3
LOAD DATA INFILE 'data2.csv' INTO TABLE tb;  -- 含 id=10,11,12

SHOW ENGINE INNODB STATUS 顯示：事務 1 等待行鎖，事務 2 持有該行鎖但等待 AUTO_INC 表鎖 → 死鎖，事務 2 回滾。

核心背景知識

1. AUTO_INC 鎖與 innodb_autoinc_lock_mode

• Mode 0 (傳統)：語句開始獲取表鎖，語句結束釋放。插入過程持有鎖，無法並行。
• Mode 1 (預設)：簡單 INSERT 分配 ID 即釋放鎖；但 LOAD DATA、INSERT ... SELECT 等「批量插入」退化為 Mode 0，為了主從 ID 連續性。
• Mode 2 (高並發)：完全不使用表級 AUTO_INC 鎖，採用輕量級互斥量，**前提是 binlog_format=ROW**。

2. 主從一致性考量

Statement 格式下，主庫 Binlog 記錄 SET INSERT_ID 指定首行自增值，從庫重放前執行此指令，**要求主庫批量插入產生的 ID 連續**，故退化為 Mode 0。

3. 指定自增值時的鎖獲取順序

若 INSERT 語句**顯式指定**自增列值，InnoDB 流程為：
1. 插入資料行（需行鎖）
2. 成功後才申請 AUTO_INC 鎖更新計數器
3. 語句結束釋放 AUTO_INC 鎖

因此 LOAD DATA 實際流程：插入第一行 → 獲取 AUTO_INC 鎖 → 插入剩餘所有行 → 釋放 AUTO_INC 鎖。行鎖（事務級）比 AUTO_INC 鎖（語句級）更早獲得、更晚釋放。

死鎖復現時序

1. Session 1：插入第一行（獲取行鎖 A）→ 獲取 AUTO_INC 鎖。
2. Session 2：插入第一行（獲取行鎖 B）→ 嘗試獲取 AUTO_INC 鎖（被 Session 1 阻塞）。
3. Session 1：插入第二行 → 需要行鎖 B（被 Session 2 阻塞）。
4. 形成循環等待 → 死鎖。

解決方案

方案一：移除不必要的 AUTO_INCREMENT（推薦）

業務已在 CSV 中指定完整 ID，表定義改為 id INT PRIMARY KEY 移除 AUTO_INCREMENT，根源消除 AUTO_INC 鎖。

方案二：強制 Mode 2（需 Row 格式 Binlog）

SET GLOBAL innodb_autoinc_lock_mode = 2;
-- 前提：binlog_format = ROW

優點：無需變更表結構與資料；缺點：需確保主從均為 Row 格式，且允許批量插入 ID 不連續。

總結

死鎖觸發條件苛刻（需並發批量插入、指定 ID、行鎖交叉），但一旦發生影響寫入可用性。**方案一最徹底，方案二最輕量**，視架構約束選擇。