一個(gè)線上項(xiàng)目報(bào)的死鎖，簡(jiǎn)要說(shuō)明一下產(chǎn)生原因、處理方案和相關(guān)的一些點(diǎn).

1、背景

這是一個(gè)類似數(shù)據(jù)分析的項(xiàng)目，數(shù)據(jù)完全通過(guò)LOAD DATA語(yǔ)句導(dǎo)入一個(gè)InnoDB表中。為方便描述，表結(jié)構(gòu)簡(jiǎn)化為如下：

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

導(dǎo)入數(shù)據(jù)的語(yǔ)句對(duì)應(yīng)為

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

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

cat Data1.csv

1 100

2 100

3 100


Cat data2.csv

10 100

11 100

12 100

產(chǎn)生死鎖的證據(jù)是在show engine innodb status的LATEST DETECTED DEADLOCK段中看到死鎖信息，簡(jiǎn)化為如下：

說(shuō)明

從上面表格中看出，事務(wù)1在等待某一行的鎖。而事務(wù)2持有這行的鎖，但等待表的自增鎖（AUTO_INC），判斷為死鎖，事務(wù)回滾。
這里事務(wù)1沒(méi)有寫(xiě)出來(lái)，但是可以推斷，事務(wù)1持有這個(gè)表的自增鎖（否則就不是死鎖了）。

2、背景知識(shí)1：AUTO_INC lock 及其選項(xiàng)

在InnoDB表中，若存在自增字段，則會(huì)維護(hù)一個(gè)表級(jí)別的鎖，這里稱為自增鎖。每次插入新數(shù)據(jù)，或者update語(yǔ)句修改了此字段，都會(huì)需要獲取這個(gè)鎖

由于一個(gè)事務(wù)可能包含多個(gè)語(yǔ)句，而并非所有的語(yǔ)句都與自增字段有關(guān)，因此InnoDB作了一個(gè)特殊的處理，自增鎖在一個(gè)語(yǔ)句結(jié)束后馬上被釋放。之所以說(shuō)是特殊處理，是因?yàn)槠胀ǖ逆i，都是在事務(wù)結(jié)束后釋放。

若一個(gè)表有自增字段，一個(gè)insert語(yǔ)句不指定該字段的值，或指定為NULL時(shí)，InnoDB會(huì)給它賦值為當(dāng)前的AUTO_INCREMENT的值，然后AUTO_INCREMENT加1。

與這個(gè)自增鎖相關(guān)的一個(gè)參數(shù)是innodb_autoinc_lock_mode. 默認(rèn)值為1，可選為0,1,2。

我們先來(lái)看當(dāng)這個(gè)值設(shè)置為0時(shí)，一個(gè)有自增字段的表，插入一行數(shù)據(jù)時(shí)的行為：

1) 申請(qǐng)AUTO_INC鎖

2) 得到當(dāng)前AUTO_INCREMNT值n，給AUTO_INCREMENT 加1

3) 執(zhí)行插入操作，并將n填入新增的行對(duì)應(yīng)字段中

4) 釋放AUTO_INC鎖

我們看到這個(gè)過(guò)程中，雖然InnoDB為了減少鎖粒度，在語(yǔ)句執(zhí)行完成就馬上釋放，但這鎖還是太大了――它包括了插入操作的時(shí)間。這就導(dǎo)致了兩個(gè)insert語(yǔ)句，實(shí)際上沒(méi)辦法并行。

沒(méi)有這個(gè)參數(shù)之前，行為就是與設(shè)置為0相同，0這個(gè)選項(xiàng)就是留著兼容的。

很容易想到設(shè)置為1的時(shí)候，應(yīng)該是將3) 和 4)對(duì)調(diào)。但是本文還是要討論為0的情況，因?yàn)槲覀兊那疤崾荓OAD語(yǔ)句，而LOAD語(yǔ)句這類插入多行的語(yǔ)句中（包括insert …select …），即使設(shè)置為1也沒(méi)用，會(huì)退化為0的模式。

3、背景知識(shí)2：LOAD DATA語(yǔ)句的主從行為

為什么插入多行的語(yǔ)句要即使將innodb_autoinc_lock_mode設(shè)置為1，也會(huì)用0的模式呢？

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

1) 將load data用到的文件發(fā)給slave，slave將文件保存在臨時(shí)目錄。

2) 在slave也執(zhí)行一次LOAD DATA語(yǔ)句。

其間有一個(gè)問(wèn)題：slave怎么保證load data語(yǔ)句的自增id字段與master相同？

為了解決這個(gè)問(wèn)題，主庫(kù)的binlog中還有一個(gè)set SET INSERT_ID命令，表明這個(gè)LOAD DATA語(yǔ)句插入的第一行的自增ID值。這樣slave在執(zhí)行l(wèi)oad data之前，先執(zhí)行了這個(gè)set SET INSERT_ID語(yǔ)句，用于保證執(zhí)行結(jié)果與主庫(kù)一模一樣。

上述的機(jī)制能保證主從數(shù)據(jù)一致的前提是：主從庫(kù)上LOAD DATA語(yǔ)句生成的自增ID值必須是連續(xù)的。

4、背景知識(shí)1＋2：分析

回到前面說(shuō)的模式0和1的區(qū)別，我們看到，如果AUTO_INC鎖在整個(gè)語(yǔ)句開(kāi)始之前就獲取，在語(yǔ)句結(jié)束之后才釋放，這樣就能保證整個(gè)語(yǔ)句生成的id連續(xù)――模式0的保證。

對(duì)于1，每次拿到下一個(gè)值就釋放，插入數(shù)據(jù)后，若需要再申請(qǐng)，則不連續(xù)。

這就是為什么，即使設(shè)置為1，對(duì)于多行操作，會(huì)退化成0。

至此我們知道這個(gè)死鎖出現(xiàn)的原因，是這兩個(gè)LOAD DATA語(yǔ)句不僅會(huì)訪問(wèn)相同的記錄，還會(huì)訪問(wèn)同一個(gè)AUTO_INC鎖，造成互相等待。

到此沒(méi)完，因?yàn)槲覀冎离m然兩個(gè)線程訪問(wèn)兩個(gè)鎖可能造成死鎖，但是死鎖還有另外一個(gè)條件，與申請(qǐng)順序有關(guān)。既然AUTO_INC是一個(gè)表鎖，不論誰(shuí)先拿到，會(huì)阻塞其他同表的LOAD DATA的執(zhí)行，又為什么會(huì)在某個(gè)記錄上出現(xiàn)鎖等待？

5、背景知識(shí)3：AUTO_INC的加鎖時(shí)機(jī)

前面我們說(shuō)到每次涉及到插入新數(shù)據(jù)，就會(huì)要求對(duì)AUTO_INC加鎖，并列出了流程。但這個(gè)流程是對(duì)于需要從InnoDB中得到自增值來(lái)設(shè)置列值的情況。另一種情況是在語(yǔ)句中已經(jīng)指定了該列的值。

比如對(duì)于這個(gè)表，執(zhí)行 insert into tb values(9,100). 此時(shí)id的值已經(jīng)明確是9，雖然不需要取值來(lái)填，但是插入這行后有可能需要改變AUTO_INCREMENT的值（若原來(lái)是<10,則應(yīng)該改為10），所以這個(gè)鎖還是省不了。流程變成：

1） 插入數(shù)據(jù)

2） 若失敗則流程結(jié)束

3） 若成功，申請(qǐng)AUTO_INC鎖

4） 調(diào)用set_max….函數(shù)，如有必要?jiǎng)t修改AUTO_INCREMENT

5） 語(yǔ)句結(jié)束時(shí)釋放AUTO_INC鎖。

6、為什么修改AUTO_INC順序

這么調(diào)整的好處是什么？ 主要是為了減少不必要的鎖訪問(wèn)。若在插入數(shù)據(jù)期間發(fā)生錯(cuò)誤，比如其他字段造成DUPLICATE KEY error，這樣就不用訪問(wèn)AUTO_INC鎖。

7、死鎖過(guò)程復(fù)現(xiàn)

必須強(qiáng)調(diào)是“語(yǔ)句結(jié)束時(shí)”。這樣我們來(lái)看一個(gè)每行都已經(jīng)指定了自增列值的LOAD DATA語(yǔ)句的流程（也就是本文例子的情況）：

1) 插入第一條數(shù)據(jù)

2) 申請(qǐng)AUTO_INC鎖

3) 插入第二條

4) 申請(qǐng)AUTO_INC 鎖（因?yàn)橐呀?jīng)是自己的，直接成功）

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

6) 釋放AUTO_INC鎖。

所以這個(gè)流程就簡(jiǎn)單描述為：插入第一行，申請(qǐng)AUTO_INC鎖，然后插入剩下的所有行后再釋放。

我們前面提到過(guò)，插入第一條數(shù)據(jù)時(shí)可能需要訪問(wèn)的記錄鎖，是要等到整個(gè)事務(wù)結(jié)束后才釋放的.

有了上面的這些背景知識(shí)，我們來(lái)復(fù)現(xiàn)一下死鎖出現(xiàn)的過(guò)程

可以看到觸發(fā)條件還是比較苛刻的，尤其是session2要?jiǎng)偤靡玫絪ession1鎖住的那個(gè)記錄鎖。需要說(shuō)明，由于InnoDB內(nèi)部對(duì)記錄的表示，同一個(gè)記錄鎖并不表示主鍵值一定相同。

8、解決方案1：去掉不必要的AUTO_INCREMENT字段

在這個(gè)業(yè)務(wù)中，由于所有的數(shù)據(jù)都是通過(guò)LOAD DATA進(jìn)去，而且都已經(jīng)指定了自增字段的值，因此這個(gè)AUTO)INCREMENT屬性是不需要的。

少了一個(gè)，就死鎖不了了。

9、解決方案2：強(qiáng)制模式1

前面我們說(shuō)到innodb_autoinc_lock_mode這個(gè)參數(shù)的可選值有0、1、2。當(dāng)設(shè)置為1的時(shí)候，在LOAD DATA語(yǔ)句會(huì)退化為模式0。但若設(shè)置為2，則無(wú)論如何都會(huì)使用模式1。

我們前面說(shuō)到使用模式1會(huì)導(dǎo)致LOAD DATA生成的自增id值不連續(xù)，這樣會(huì)導(dǎo)致在binlog_format是1時(shí)主從不一致，因此設(shè)置為2的前提，是binlog_format 是row.

在binlog_format='row'時(shí)，設(shè)置innodb_autoinc_lock_mode為2是安全的。

若允許，方案2比方案1更輕量些，不需要修改數(shù)據(jù)和表結(jié)構(gòu)。

最新評(píng)論