生產環境出現死鎖流水，透過查看死鎖日誌，看到造成死鎖的是兩個相同的update語句（只有where條件中的值不同），
如下：
UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0; UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0;
一開始比較費解，透過大量查詢跟學習後，分析出了死鎖形成的具體原理，特分享給大家，希望能幫助到遇到同樣問題的朋友。
因為MySQL知識點較多，這裡對很多名詞不進行過多介紹，有興趣的朋友，可以後續進行專項深入學習。
*** (1) TRANSACTION: TRANSACTION 791913819, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999 mysql tables in use 3, locked 3 LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s) MySQL thread id 462005230, OS thread handle 0x7f55d5da3700, query id 2621313306 x.x.x.x test_user Searching rows for update UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx1' AND `status` = 0; *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913819 lock_mode X waiting Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 *** (2) TRANSACTION: TRANSACTION 791913818, ACTIVE 0 sec starting index read, thread declared inside InnoDB 4999 mysql tables in use 3, locked 3 5 lock struct(s), heap size 1184, 4 row lock(s) MySQL thread id 462005231, OS thread handle 0x7f55cee63700, query id 2621313305 x.x.x.x test_user Searching rows for update UPDATE test_table SET `status` = 1 WHERE `trans_id` = 'xxx2' AND `status` = 0; *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 110 page no 39167 n bits 1056 index `idx_status` of table `test`.`test_table` trx id 791913818 lock_mode X Record lock, heap no 495 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 110 page no 41569 n bits 88 index `PRIMARY` of table `test`.`test_table` trx id 791913818 lock_mode X locks rec but not gap waiting Record lock, heap no 14 PHYSICAL RECORD: n_fields 30; compact format; info bits 0 *** WE ROLL BACK TRANSACTION (1)
簡單分析下上邊的死鎖日誌：
1、第一塊內容（第1行到第9行）中，第6行為事務(1)執行的SQL語句，第7和第8行意思為事務(1)在等待idx_status 索引上的X鎖定；
2、第二塊內容（第11行到第19行）中，第16行為事務(2)執行的SQL語句，第17和第18行意思為事務(2)持有idx_status 索引上的X鎖；
意思為：交易(2)正在等待在PRIMARY 索引上取得X 鎖定。 （but not gap指不是間隙鎖）
4、最後一句的意思即為，MySQL將事務(1)進行了回滾操作。
CREATE TABLE `test_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `trans_id` varchar(21) NOT NULL, `status` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniq_trans_id` (`trans_id`) USING BTREE, KEY `idx_status` (`status`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
透過表格結構可以看出，trans_id 列上有一個唯一索引uniq_trans_id ，status 欄位上有一個普通索引idx_status ，id列為主鍵索引PRIMARY 。
InnoDB引擎中有兩個索引：
#叢集索引： 將資料儲存與索引放到了一塊，索引結構的葉子節點保存了行資料。
輔助索引： 輔助索引葉子節點儲存的是主鍵值，也就是叢集索引的鍵值。
主鍵索引 PRIMARY 是叢集索引，葉子節點中會儲存行資料。 uniq_trans_id 索引和idx_status 索引為輔助索引，葉子節點中保存的是主鍵值，也就是id列值。
當我們透過輔助索引尋找行資料時，先透過輔助索引找到主鍵id，再透過主鍵索引進行二次查找（也叫回表），最終找到行資料。
透過看執行計劃，可以發現，update語句用到了索引合併，也就是這條語句既用到了 uniq_trans_id 索引，又用到了 idx_status 索引，Using intersect(uniq_trans_id,idx_status)的意思是透過兩個索引來取得交集。
MySQL5.0之前，一個表一次只能使用一個索引，無法同時使用多個索引分別進行條件掃描。但從5.1開始，引進了 index merge 最佳化技術，對同一個表格可以使用多個索引分別進行條件掃描。
如執行計畫中的語句：
UPDATE test_table SET `status` = 1 WHERE `trans_id` = '38' AND `status` = 0 ;
MySQL會根據 trans_id = ‘38’這個條件，利用 uniq_trans_id 索引找到葉子節點中保存的id值；同時會根據 status = 0這個條件，利用 idx_status 索引找到葉子節點中保存的id值；然後將找到的兩組id值取交集，最後透過交集後的id回表，也就是透過PRIMARY 索引找到葉子節點中儲存的行資料。
這裡可能很多人會有疑問了，uniq_trans_id 已經是一個唯一索引了，透過這個索引最終只能找到最多一條數據，那MySQL優化器為啥還要用兩個索引取交集，再回表查詢呢，這樣就不是多了一次 idx_status 索引查找的過程麼。我們來分析一下這兩種情況執行過程。
第一種只用uniq_trans_id索引：
根據 trans_id = ‘38’查詢條件，利用uniq_trans_id 索引找到葉節點中已儲存的id值；
透過找到的id值，利用PRIMARY索引找到葉子節點中已儲存的行資料；
再透過 status = 0 條件對找到的行資料進行篩選。
第二個用到索引合併 Using intersect(uniq_trans_id,idx_status)：
根據 trans_id = ‘38’ 查詢條件，利用 uniq_trans_id 索引找出葉子節點中保存的id值;
根據 status = 0 查詢條件，利用 idx_status 索引找出葉子節點中保存的id值；
將1/2中找到的id值交集，然後利用PRIMARY索引找到葉子節點中儲存的行資料
status = 0 的資料非常少，這也是最佳化器考慮用第二種情況的原因之一）。
index_merge 就死鎖了
idx_status 索引和PRIMARY （叢集索引） 上都存在重疊交叉的部分，這樣就為死鎖造成了條件。
如，當遇到以下時序時，就會出現死鎖：
trans_id ，將資料查詢出來後來，在程式碼層面判斷status 狀態是否為0;
強制查詢語句使用 uniq_trans_id 索引；
索引或建立一個包含這兩個欄位的共同索引；
最佳化關閉。
以上是MySQL優化index merge造成的死鎖怎麼解決的詳細內容。更多資訊請關注PHP中文網其他相關文章！
