案例描述
在定時(shí)腳本運(yùn)行過(guò)程中，發(fā)現(xiàn)當(dāng)備份表格的sql語(yǔ)句與刪除該表部分?jǐn)?shù)據(jù)的sql語(yǔ)句同時(shí)運(yùn)行時(shí)，mysql會(huì)檢測(cè)出死鎖，并打印出日志。
兩個(gè)sql語(yǔ)句如下：
（1）insert into backup_table select * from source_table
（2）DELETE FROM source_table WHERE Id>5 AND titleWeight<32768 AND joinTime<'$daysago_1week'
teamUser表的表結(jié)構(gòu)如下：
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB
兩語(yǔ)句對(duì)source_table表的使用情況如下：

死鎖日志打印出的時(shí)間點(diǎn)表明，語(yǔ)句（1）運(yùn)行過(guò)程中，當(dāng)語(yǔ)句（2）開始運(yùn)行時(shí)，發(fā)生了死鎖。
當(dāng)mysql檢測(cè)出死鎖時(shí)，除了查看mysql的日志，還可以通過(guò)show InnoDB STATUS \G語(yǔ)句在mysql客戶端中查看最近一次的死鎖記錄。由于打印出來(lái)的語(yǔ)句會(huì)很亂，所以，最好先使用pager less命令，通過(guò)文件內(nèi)容瀏覽方式查看結(jié)果，會(huì)更清晰。（以nopager結(jié)束）
得到的死鎖記錄如下：

根據(jù)死鎖記錄的結(jié)果，可以看出確實(shí)是這兩個(gè)語(yǔ)句發(fā)生了死鎖，且鎖沖突發(fā)生在主鍵索引上。那么，為什么兩個(gè)sql語(yǔ)句會(huì)存在鎖沖突呢？沖突為什么會(huì)在主鍵索引上呢？語(yǔ)句（2）得到了主鍵索引鎖，為什么還會(huì)再次申請(qǐng)鎖呢？
鎖沖突分析
2.1 innodb的事務(wù)與行鎖機(jī)制
MySQL的事務(wù)支持不是綁定在MySQL服務(wù)器本身，而是與存儲(chǔ)引擎相關(guān)，MyISAM不支持事務(wù)、采用的是表級(jí)鎖，而InnoDB支持ACID事務(wù)、 行級(jí)鎖、并發(fā)。MySQL默認(rèn)的行為是在每條SQL語(yǔ)句執(zhí)行后執(zhí)行一個(gè)COMMIT語(yǔ)句，從而有效的將每條語(yǔ)句作為一個(gè)單獨(dú)的事務(wù)來(lái)處理。
2.2 兩語(yǔ)句加鎖情況
在innodb默認(rèn)的事務(wù)隔離級(jí)別下，普通的SELECT是不需要加行鎖的，但LOCK IN SHARE MODE、FOR UPDATE及高串行化級(jí)別中的SELECT都要加鎖。有一個(gè)例外，此案例中，語(yǔ)句（1）insert into teamUser_20110121 select * from teamUser會(huì)對(duì)表teamUser_20110121（ENGINE= MyISAM）加表鎖，并對(duì)teamUser表所有行的主鍵索引（即聚簇索引）加共享鎖。默認(rèn)對(duì)其使用主鍵索引。
而語(yǔ)句（2）DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight<32768 AND joinTime<'$daysago_1week'為刪除操作，會(huì)對(duì)選中行的主鍵索引加排他鎖。由于此語(yǔ)句還使用了非聚簇索引KEY `k_teamid_titleWeight_score` (`teamId`,`titleWeight`,`score`)的前綴索引，于是，還會(huì)對(duì)相關(guān)行的此非聚簇索引加排他鎖。
2.3 鎖沖突的產(chǎn)生
由于共享鎖與排他鎖是互斥的，當(dāng)一方擁有了某行記錄的排他鎖后，另一方就不能其擁有共享鎖，同樣，一方擁有了其共享鎖后，另一方也無(wú)法得到其排他鎖。所 以，當(dāng)語(yǔ)句（1）、（2）同時(shí)運(yùn)行時(shí)，相當(dāng)于兩個(gè)事務(wù)會(huì)同時(shí)申請(qǐng)某相同記錄行的鎖資源，于是會(huì)產(chǎn)生鎖沖突。由于兩個(gè)事務(wù)都會(huì)申請(qǐng)主鍵索引，鎖沖突只會(huì)發(fā)生 在主鍵索引上。
常常看到一句話：在InnoDB中，除單個(gè)SQL組成的事務(wù)外，鎖是逐步獲得的。那就說(shuō)明，單個(gè)SQL組成的事務(wù)鎖是一次獲得的。而此案例中，語(yǔ)句（2） 已經(jīng)得到了主鍵索引的排他鎖，為什么還會(huì)申請(qǐng)主鍵索引的排他鎖呢？同理，語(yǔ)句（1）已經(jīng)獲得了主鍵索引的共享鎖，為什么還會(huì)申請(qǐng)主鍵索引的共享鎖呢？
死鎖記錄中，事務(wù)一等待鎖的page no與事務(wù)二持有鎖的page no相同，均為218436，這又代表什么呢？
我們的猜想是，innodb存儲(chǔ)引擎中獲得行鎖是逐行獲得的，并不是一次獲得的。下面來(lái)證明。
死鎖產(chǎn)生過(guò)程分析
要想知道innodb加鎖的過(guò)程，唯一的方式就是運(yùn)行mysql的debug版本，從gdb的輸出中找到結(jié)果。根據(jù)gdb的結(jié)果得到，單個(gè)SQL組成的事 務(wù)，從宏觀上來(lái)看，鎖是在這個(gè)語(yǔ)句上一次獲得的，但從底層實(shí)現(xiàn)上來(lái)看，是逐個(gè)記錄行查詢，得到符合條件的記錄即對(duì)該行記錄的索引加鎖。
Gdb結(jié)果演示如下：

Gdb結(jié)果顯示，語(yǔ)句（1）（2）加鎖的獲取記錄為多行，即逐行獲得鎖，這樣就解釋了語(yǔ)句（2）獲得了主鍵索引鎖還再次申請(qǐng)主鍵索引鎖的情況。
由于語(yǔ)句（1）使用了主鍵索引，而語(yǔ)句（2）使用了非聚簇索引，兩個(gè)事務(wù)獲得記錄行的順序不同，而加鎖的過(guò)程是邊查邊加、逐行獲得，于是，就會(huì)出現(xiàn)如下情況：

于是，兩個(gè)事務(wù)分別擁有部分鎖并等待被對(duì)方持有的鎖，出現(xiàn)這種資源循環(huán)等待的情況，即死鎖。此案例中被檢測(cè)時(shí)候的鎖沖突就發(fā)現(xiàn)在page no為218436和218103的鎖上。
InnoDB 會(huì)自動(dòng)檢測(cè)一個(gè)事務(wù)的死鎖并回滾一個(gè)或多個(gè)事務(wù)來(lái)防止死鎖。Innodb會(huì)選擇代價(jià)比較小的事務(wù)回滾，此次事務(wù)（1）解鎖并回滾，語(yǔ)句（2）繼續(xù)運(yùn)行直至事務(wù)結(jié)束。
innodb死鎖形式歸納
死鎖產(chǎn)生的四要素：互斥條件：一個(gè)資源每次只能被一個(gè)進(jìn)程使用；請(qǐng)求與保持條件：一個(gè)進(jìn)程因請(qǐng)求資源而阻塞時(shí)，對(duì)已獲得的資源保持不放；不剝奪條件:進(jìn)程 已獲得的資源，在末使用完之前，不能強(qiáng)行剝奪；循環(huán)等待條件:若干進(jìn)程之間形成一種頭尾相接的循環(huán)等待資源關(guān)系。
Innodb檢測(cè)死鎖有兩種情況，一種是滿足循環(huán)等待條件，還有另一種策略：鎖結(jié)構(gòu)超過(guò)mysql配置中設(shè)置的最大數(shù)量或鎖的遍歷深度超過(guò)設(shè)置的最大深度 時(shí)，innodb也會(huì)判斷為死鎖（這是提高性能方面的考慮，避免事務(wù)一次占用太多的資源）。這里，我們只考慮滿足死鎖四要素的情況。
死鎖的形式是多樣的，但分析到innodb加鎖情況的最底層，因循環(huán)等待條件而產(chǎn)生的死鎖只有可能是四種形式：兩張表兩行記錄交叉申請(qǐng)互斥鎖、同一張表則存在主鍵索引鎖沖突、主鍵索引鎖與非聚簇索引鎖沖突、鎖升級(jí)導(dǎo)致的鎖等待隊(duì)列阻塞。
以下首先介紹innodb聚簇索引與非聚簇索引的數(shù)據(jù)存儲(chǔ)形式，再以事例的方式解釋這四種死鎖情況。
4.1聚簇索引與非聚簇索引介紹
聚簇索引即主鍵索引，是一種對(duì)磁盤上實(shí)際數(shù)據(jù)重新組織以按指定的一個(gè)或多個(gè)列的值排序，聚簇索引的索引頁(yè)面指針指向數(shù)據(jù)頁(yè)面。非聚簇索引（即第二主鍵索 引）不重新組織表中的數(shù)據(jù)，索引順序與數(shù)據(jù)物理排列順序無(wú)關(guān)。索引通常是通過(guò)B-Tree數(shù)據(jù)結(jié)構(gòu)來(lái)描述，那么，聚簇索引的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，而非聚簇 索引的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，通常是一個(gè)指針指向?qū)?yīng)的數(shù)據(jù)塊。
而innodb在非聚簇索引葉子節(jié)點(diǎn)包含了主鍵值作為指針。（這樣是為了減少在移動(dòng)行或數(shù)據(jù)分頁(yè)時(shí)索引的維護(hù)工作。）其結(jié)構(gòu)圖如下：

當(dāng)使用非聚簇索引時(shí)，會(huì)根據(jù)得到的主鍵值遍歷聚簇索引，得到相應(yīng)的記錄。
4.2四種死鎖情況
在InnoDB中，使用行鎖機(jī)制，于是，鎖通常是逐步獲得的，這就決定了在InnoDB中發(fā)生死鎖是可能的。
即將分享的四種死鎖的鎖沖突分別是：不同表的相同記錄行索引鎖沖突、主鍵索引鎖沖突、主鍵索引鎖與非聚簇索引鎖沖突、鎖升級(jí)造成鎖隊(duì)列阻塞。
不同表的相同記錄行鎖沖突
案例：兩個(gè)表、兩行記錄，交叉獲得和申請(qǐng)互斥鎖

條件：
A、 兩事務(wù)分別操作兩個(gè)表、相同表的同一行記錄
B、 申請(qǐng)的鎖互斥
C、 申請(qǐng)的順序不一致

主鍵索引鎖沖突
案例：本文案例，產(chǎn)生沖突在主鍵索引鎖上
條件：
A、 兩sql語(yǔ)句即兩事務(wù)操作同一個(gè)表、使用不同索引
B、 申請(qǐng)的鎖互斥
C、 操作多行記錄
D、 查找到記錄的順序不一致

主鍵索引鎖與非聚簇索引鎖沖突
案例：同一行記錄，兩事務(wù)使用不同的索引進(jìn)行更新操作

此案例涉及TSK_TASK表，該表相關(guān)字段及索引如下：
ID：主鍵；
MON_TIME：監(jiān)測(cè)時(shí)間；
STATUS_ID：任務(wù)狀態(tài)；
索引：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

條件：
A、 兩事務(wù)使用不同索引
B、 申請(qǐng)的鎖互斥
C、 操作同一行記錄

當(dāng)執(zhí)行update、delete操作時(shí)，會(huì)修改表中的數(shù)據(jù)信息。由于innodb存儲(chǔ)引擎中索引的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)，會(huì)根據(jù)修改語(yǔ)句使用的索引以及修改信息 的不同執(zhí)行不同的加鎖順序。當(dāng)使用索引進(jìn)行查找并修改記錄時(shí)，會(huì)首先加使用的索引鎖，然后，如果修改了主鍵信息，會(huì)加主鍵索引鎖和所有非聚簇索引鎖，修改 了非聚簇索引列值會(huì)加該種非聚簇索引鎖。
此案例中，事務(wù)一使用非聚簇索引查找并修改主鍵值，事務(wù)二使用主鍵索引查找并修改主鍵值，加鎖順序不同，導(dǎo)致同時(shí)運(yùn)行時(shí)產(chǎn)生資源循環(huán)等待。
鎖升級(jí)造成鎖隊(duì)列阻塞
案例：同一行記錄，事務(wù)內(nèi)進(jìn)行鎖升級(jí)，與另一等待鎖發(fā)送鎖隊(duì)列阻塞，導(dǎo)致死鎖

條件：
A、 兩事務(wù)操作同一行記錄
B、 一事務(wù)對(duì)某一記錄先申請(qǐng)共享鎖，再升級(jí)為排他鎖
C、 另一事務(wù)在過(guò)程中申請(qǐng)這一記錄的排他鎖

避免死鎖的方法
InnoDB給MySQL提供了具有提交，回滾和崩潰恢復(fù)能力的事務(wù)安全（ACID兼容）存儲(chǔ)引擎。InnoDB鎖定在行級(jí)并且也在SELECT語(yǔ)句提供非鎖定讀。這些特色增加了多用戶部署和性能。
但其行鎖的機(jī)制也帶來(lái)了產(chǎn)生死鎖的風(fēng)險(xiǎn)，這就需要在應(yīng)用程序設(shè)計(jì)時(shí)避免死鎖的發(fā)生。以單個(gè)SQL語(yǔ)句組成的隱式事務(wù)來(lái)說(shuō)，建議的避免死鎖的方法如下：
1.如果使用insert…select語(yǔ)句備份表格且數(shù)據(jù)量較大，在單獨(dú)的時(shí)間點(diǎn)操作，避免與其他sql語(yǔ)句爭(zhēng)奪資源，或使用select into outfile加上load data infile代替 insert…select，這樣不僅快，而且不會(huì)要求鎖定
2. 一個(gè)鎖定記錄集的事務(wù)，其操作結(jié)果集應(yīng)盡量簡(jiǎn)短，以免一次占用太多資源，與其他事務(wù)處理的記錄沖突。
3.更新或者刪除表格數(shù)據(jù)，sql語(yǔ)句的where條件都是主鍵或都是索引，避免兩種情況交叉，造成死鎖。對(duì)于where子句較復(fù)雜的情況，將其單獨(dú)通過(guò)sql得到后，再在更新語(yǔ)句中使用。
4. sql語(yǔ)句的嵌套表格不要太多，能拆分就拆分，避免占有資源同時(shí)等待資源，導(dǎo)致與其他事務(wù)沖突。
5. 對(duì)定點(diǎn)運(yùn)行腳本的情況，避免在同一時(shí)間點(diǎn)運(yùn)行多個(gè)對(duì)同一表進(jìn)行讀寫的腳本，特別注意加鎖且操作數(shù)據(jù)量比較大的語(yǔ)句。
6.應(yīng)用程序中增加對(duì)死鎖的判斷，如果事務(wù)意外結(jié)束，重新運(yùn)行該事務(wù)，減少對(duì)功能的影響。

最新評(píng)論