MySQL Innodb表導致死結日誌情況分析與歸納

案例描述
在定時指令碼運行過程中，發現當備份表格的sql語句與刪除該表部分資料的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表的表結構如下：
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB
兩語句對source_table表的使用方式如下：

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

根據死結記錄的結果，可以看出確實是這兩個語句發生了死結，且鎖衝突發生在主鍵索引上。那麼，為什麼兩個sql語句會存在鎖衝突呢？衝突為什麼會在主鍵索引上呢？語句（2）得到了主鍵索引鎖，為什麼還會再次申請鎖呢？
鎖衝突分析
2.1 innodb的事務與行鎖機制
MySQL的事務支援不是綁定在MySQL伺服器本身，而是與儲存引擎相關，MyISAM不支援事務、採用的是表級鎖，而InnoDB支援ACID事務、 行級鎖、並發。MySQL預設的行為是在每條SQL語句執行後執行一個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 鎖衝突的產生
由於共用鎖定與獨佔鎖定是互斥的，當一方擁有了某行記錄的獨佔鎖定後，另一方就不能其擁有共用鎖定，同樣，一方擁有了其共用鎖定後，另一方也無法得到其獨佔鎖定。所 以，當語句（1）、（2）同時運行時，相當於兩個事務會同時申請某相同記錄行的鎖資源，於是會產生鎖衝突。由於兩個事務都會申請主鍵索引，鎖衝突只會發生 在主鍵索引上。
常常看到一句話：在InnoDB中，除單個SQL組成的事務外，鎖是逐步獲得的。那就說明，單個SQL組成的事務鎖是一次獲得的。而此案例中，語句（2） 已經得到了主鍵索引的獨佔鎖定，為什麼還會申請主鍵索引的獨佔鎖定呢？同理，語句（1）已經獲得了主鍵索引的共用鎖定，為什麼還會申請主鍵索引的共用鎖定呢？
死結記錄中，事務一等待鎖的page no與事務二持有鎖的page no相同，均為218436，這又代表什麼呢？
我們的猜想是，innodb儲存引擎中獲得行鎖是逐行獲得的，並不是一次獲得的。下面來證明。
死結產生過程分析
要想知道innodb加鎖的過程，唯一的方式就是運行mysql的debug版本，從gdb的輸出中找到結果。根據gdb的結果得到，單個SQL組成的事 務，從宏觀上來看，鎖是在這個語句上一次獲得的，但從底層實現上來看，是逐個記錄行查詢，得到合格記錄即對該行記錄的索引加鎖。
Gdb結果示範如下：

複製代碼 代碼如下:(gdb) b lock_rec_lock
Breakpoint 1 at 0×867120: file lock/lock0lock.c, line 2070.
(gdb) c
Continuing.
[Switching to Thread 1168550240 (LWP 5540)]
Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01c1 “789\200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
2070 {
Current language: auto; currently c
(gdb) c
Continuing.
Breakpoint 1, lock_rec_lock (impl=0, mode=1029, rec=0x2aedbc80ba “\200″, index=0x2aada730b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
2070 {
(gdb) c
Continuing.
Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01cf “789\200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
2070 {
(gdb) c
Continuing.

（說明：”789\200″為非聚簇索引，”\200″為主鍵索引）

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

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

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

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

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

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

此案例涉及TSK_TASK表，該表相關欄位及索引如下：
ID：主鍵；
MON_TIME：監測時間；
STATUS_ID：任務狀態；
索引：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

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

當執行update、delete操作時，會修改表中的資料資訊。由於innodb儲存引擎中索引的資料存放區結構，會根據修改語句使用的索引以及修改資訊 的不同執行不同的加鎖順序。當使用索引進行尋找並修改記錄時，會首先加使用的索引鎖，然後，如果修改了主鍵資訊，會加主鍵索引鎖和所有非聚簇索引鎖，修改 了非聚簇索引列值會加該種非聚簇索引鎖。
此案例中，事務一使用非聚簇索引尋找並修改主索引值，事務二使用主鍵索引尋找並修改主索引值，加鎖順序不同，導致同時運行時產生資源迴圈等待。
鎖定擴大造成鎖隊列阻塞
案例：同一行記錄，事務內進行鎖定擴大，與另一等待鎖發送鎖隊列阻塞，導致死結

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

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