案例描述
在定時腳本運行過程中，發現當備份表格的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.應用程序中增加對死鎖的判斷，如果事務意外結束，重新運行該事務，減少對功能的影響。
