案例描述
在定時腳本運行過程中，發(fā)現(xiàn)當備份表格的sql語句與刪除該表部分數(shù)據(jù)的sql語句同時運行時，mysql會檢測出死鎖，并打印出日志。
兩個sql語句如下：
（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
兩語句對source_table表的使用情況如下：

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

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

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

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

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

條件：
A、 兩事務分別操作兩個表、相同表的同一行記錄
B、 申請的鎖互斥
C、 申請的順序不一致

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

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

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

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

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

條件：
A、 兩事務操作同一行記錄
B、 一事務對某一記錄先申請共享鎖，再升級為排他鎖
C、 另一事務在過程中申請這一記錄的排他鎖

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

最新評論