MySQL中由load data語句引起死結的解決案例_Mysql

一個線上項目報的死結，簡要說明一下產生原因、處理方案和相關的一些點.

1、背景

這是一個類似資料分析的項目，資料完全通過LOAD DATA語句匯入一個InnoDB表中。為方便描述，表結構簡化為如下：

Create table tb(id int primary key auto_increment, c int not null) engine=innodb;

匯入資料的語句對應為

Load data infile ‘data1.csv' into table tb;Load data infile ‘data2.csv' into table tb;

cat Data1.csv1 1002 1003 100Cat data2.csv10 10011 10012 100

產生死結的證據是在show engine innodb status的LATEST DETECTED DEADLOCK段中看到死結資訊，簡化為如下：

說明

從上面表格中看出，事務1在等待某一行的鎖。而事務2持有這行的鎖，但等待表的自增鎖（AUTO_INC），判斷為死結，交易回復。
這裡事務1沒有寫出來，但是可以推斷，事務1持有這個表的自增鎖（否則就不是死結了）。

2、背景知識1：AUTO_INC lock 及其選項

在InnoDB表中，若存在自增欄位，則會維護一個表層級的鎖，這裡稱為自增鎖。每次插入新資料，或者update語句修改了此欄位，都會需要擷取這個鎖

由於一個事務可能包含多個語句，而並非所有的語句都與自增欄位有關，因此InnoDB作了一個特殊的處理，自增鎖在一個語句結束後馬上被釋放。之所以說是特殊處理，是因為普通的鎖，都是在事務結束後釋放。

若一個表有自增欄位，一個insert語句不指定該欄位的值，或指定為NULL時，InnoDB會給它賦值為當前的AUTO_INCREMENT的值，然後AUTO_INCREMENT加1。

與這個自增鎖相關的一個參數是innodb_autoinc_lock_mode. 預設值為1，可選為0,1,2。

我們先來看當這個值設定為0時，一個有自增欄位的表，插入一行資料時的行為：

1) 申請AUTO_INC鎖

2) 得到當前AUTO_INCREMNT值n，給AUTO_INCREMENT 加1

3) 執行插入操作，並將n填入新增的行對應欄位中

4) 釋放AUTO_INC鎖

我們看到這個過程中，雖然InnoDB為了減少鎖粒度，在語句執行完成就馬上釋放，但這鎖還是太大了――它包括了插入操作的時間。這就導致了兩個insert語句，實際上沒辦法並行。

沒有這個參數之前，行為就是與設定為0相同，0這個選項就是留著相容的。

很容易想到設定為1的時候，應該是將3) 和 4)對調。但是本文還是要討論為0的情況，因為我們的前提是LOAD語句，而LOAD語句這類插入多行的語句中（包括insert …select …），即使設定為1也沒用，會退化為0的模式。

3、背景知識2：LOAD DATA語句的主從行為

為什麼插入多行的語句要即使將innodb_autoinc_lock_mode設定為1，也會用0的模式呢？

主要原因還是為了主從一致性。設想binlog_format='statement'，一個LOAD DATA語句在主庫的binlog直接記錄為語句本身，那從庫如何重放：

1) 將load data用到的檔案發給slave，slave將檔案儲存在臨時目錄。

2) 在slave也執行一次LOAD DATA語句。

其間有一個問題：slave怎麼保證load data語句的自增id欄位與master相同？

為瞭解決這個問題，主庫的binlog中還有一個set SET INSERT_ID命令，表明這個LOAD DATA語句插入的第一行的自增ID值。這樣slave在執行load data之前，先執行了這個set SET INSERT_ID語句，用於保證執行結果與主庫一模一樣。

上述的機制能保證主從資料一致的前提是：主從庫上LOAD DATA語句產生的自增ID值必須是連續的。

4、背景知識1＋2：分析

回到前面說的模式0和1的區別，我們看到，如果AUTO_INC鎖在整個語句開始之前就擷取，在語句結束之後才釋放，這樣就能保證整個語句產生的id連續――模式0的保證。

對於1，每次拿到下一個值就釋放，插入資料後，若需要再申請，則不連續。

這就是為什麼，即使設定為1，對於多行操作，會退化成0。

至此我們知道這個死結出現的原因，是這兩個LOAD DATA語句不僅會訪問相同的記錄，還會訪問同一個AUTO_INC鎖，造成互相等待。

到此沒完，因為我們知道雖然兩個線程訪問兩個鎖可能造成死結，但是死結還有另外一個條件，與申請順序有關。既然AUTO_INC是一個表鎖，不論誰先拿到，會阻塞其他同表的LOAD DATA的執行，又為什麼會在某個記錄上出現鎖等待？

5、背景知識3：AUTO_INC的加鎖時機

前面我們說到每次涉及到插入新資料，就會要求對AUTO_INC加鎖，並列出了流程。但這個流程是對於需要從InnoDB中得到自增值來設定列值的情況。另一種情況是在語句中已經指定了該列的值。

比如對於這個表，執行 insert into tb values(9,100). 此時id的值已經明確是9，雖然不需要取值來填，但是插入這行後有可能需要改變AUTO_INCREMENT的值（若原來是<10,則應該改為10），所以這個鎖還是省不了。流程變成：

1） 插入資料

2） 若失敗則流程結束

3） 若成功，申請AUTO_INC鎖

4） 調用set_max….函數，如有必要則修改AUTO_INCREMENT

5） 語句結束時釋放AUTO_INC鎖。

6、為什麼修改AUTO_INC順序

這麼調整的好處是什嗎？ 主要是為了減少不必要的鎖訪問。若在插入資料期間發生錯誤，比如其他欄位造成DUPLICATE KEY error，這樣就不用訪問AUTO_INC鎖。

7、死結過程複現

必須強調是“語句結束時”。這樣我們來看一個每行都已經指定了自增列值的LOAD DATA語句的流程（也就是本文例子的情況）：

1) 插入第一條資料

2) 申請AUTO_INC鎖

3) 插入第二條

4) 申請AUTO_INC 鎖（因為已經是自己的，直接成功）

5) 。。。。。。插入剩餘所有行

6) 釋放AUTO_INC鎖。

所以這個流程就簡單描述為：插入第一行，申請AUTO_INC鎖，然後插入剩下的所有行後再釋放。

我們前面提到過，插入第一條資料時可能需要訪問的記錄鎖，是要等到整個事務結束後才釋放的.

有了上面的這些背景知識，我們來複現一下死結出現的過程

可以看到觸發條件還是比較苛刻的，尤其是session2要剛好要用到session1鎖住的那個記錄鎖。需要說明，由於InnoDB內部對記錄的表示，同一個記錄鎖並不表示主索引值一定相同。

8、解決方案1：去掉不必要的AUTO_INCREMENT欄位

在這個業務中，由於所有的資料都是通過LOAD DATA進去，而且都已經指定了自增欄位的值，因此這個AUTO)INCREMENT屬性是不需要的。

少了一個，就死結不了了。

9、解決方案2：強制模式1

前面我們說到innodb_autoinc_lock_mode這個參數的可選值有0、1、2。當設定為1的時候，在LOAD DATA語句會退化為模式0。但若設定為2，則無論如何都會使用模式1。

我們前面說到使用模式1會導致LOAD DATA產生的自增id值不連續，這樣會導致在binlog_format是1時主從不一致，因此設定為2的前提，是binlog_format 是row.

在binlog_format='row'時，設定innodb_autoinc_lock_mode為2是安全的。

若允許，方案2比方案1更輕量些，不需要修改資料和表結構。