背景

生產環(huán)境出現(xiàn)死鎖流水，通過查看死鎖日志，看到造成死鎖的是兩條一樣的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知識點較多，這里對很多名詞不進行過多介紹，有興趣的朋友，可以后續(xù)進行專項深入學習。

死鎖日志

*** (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)執(zhí)行的SQL語句，第7和第8行意思為事務(1)在等待 idx_status 索引上的X鎖；
• 2、第二塊內容（第11行到第19行）中，第16行為事務(2)執(zhí)行的SQL語句，第17和第18行意思為事務(2)持有 idx_status 索引上的X鎖；
• 3、第三塊內容（第21行到第23行）的意思為，事務(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引擎中有兩種索引：

• 聚簇索引： 將數(shù)據(jù)存儲與索引放到了一塊，索引結構的葉子節(jié)點保存了行數(shù)據(jù)。
• 輔助索引： 輔助索引葉子節(jié)點存儲的是主鍵值，也就是聚簇索引的鍵值。

主鍵索引 PRIMARY 就是聚簇索引，葉子節(jié)點中會保存行數(shù)據(jù)。uniq_trans_id 索引和idx_status 索引為輔助索引，葉子節(jié)點中保存的是主鍵值，也就是id列值。

當我們通過輔助索引查找行數(shù)據(jù)時，先通過輔助索引找到主鍵id，再通過主鍵索引進行二次查找（也叫回表），最終找到行數(shù)據(jù)。

執(zhí)行計劃

通過看執(zhí)行計劃，可以發(fā)現(xiàn)，update語句用到了索引合并，也就是這條語句既用到了 ??uniq_trans_id??? 索引，又用到了 ??idx_status??? 索引，??Using intersect(uniq_trans_id,idx_status)??的意思是通過兩個索引獲取交集。

為什么會用 index_merge（索引合并）

MySQL5.0之前，一個表一次只能使用一個索引，無法同時使用多個索引分別進行條件掃描。但是從5.1開始，引入了 ??index merge?? 優(yōu)化技術，對同一個表可以使用多個索引分別進行條件掃描。

如執(zhí)行計劃中的語句：

UPDATE test_table SET `status` = 1 WHERE `trans_id` = '38' AND `status` = 0 ;

MySQL會根據(jù) ??trans_id = ‘38’???這個條件，利用 ??uniq_trans_id??? 索引找到葉子節(jié)點中保存的id值；同時會根據(jù) ??status = 0???這個條件，利用 ??idx_status?? 索引找到葉子節(jié)點中保存的id值；然后將找到的兩組id值取交集，最終通過交集后的id回表，也就是通過 PRIMARY 索引找到葉子節(jié)點中保存的行數(shù)據(jù)。

這里可能很多人會有疑問了，??uniq_trans_id??? 已經是一個唯一索引了，通過這個索引最終只能找到最多一條數(shù)據(jù)，那MySQL優(yōu)化器為啥還要用兩個索引取交集，再回表進行查詢呢，這樣不是多了一次 ??idx_status?? 索引查找的過程么。我們來分析一下這兩種情況執(zhí)行過程。

第一種 只用uniq_trans_id索引 ：

• 根據(jù) ??trans_id = ‘38’??查詢條件，利用??uniq_trans_id?? 索引找到葉子節(jié)點中保存的id值；
• 通過找到的id值，利用PRIMARY索引找到葉子節(jié)點中保存的行數(shù)據(jù)；
• 再通過 ??status = 0?? 條件對找到的行數(shù)據(jù)進行過濾。

第二種 用到索引合并 ??Using intersect(uniq_trans_id,idx_status)??：

• 根據(jù) ??trans_id = ‘38’?? 查詢條件，利用 ??uniq_trans_id?? 索引找到葉子節(jié)點中保存的id值；
• 根據(jù) ??status = 0?? 查詢條件，利用 ??idx_status?? 索引找到葉子節(jié)點中保存的id值；
• 將1/2中找到的id值取交集，然后利用PRIMARY索引找到葉子節(jié)點中保存的行數(shù)據(jù)

上邊兩種情況，主要區(qū)別在于，第一種是先通過一個索引把數(shù)據(jù)找到后，再用其它查詢條件進行過濾；第二種是先通過兩個索引查出的id值取交集，如果取交集后還存在id值，則再去回表將數(shù)據(jù)取出來。

當優(yōu)化器認為第二種情況執(zhí)行成本比第一種要小時，就會出現(xiàn)索引合并。（生產環(huán)境流水表中 ??status = 0?? 的數(shù)據(jù)非常少，這也是優(yōu)化器考慮用第二種情況的原因之一）。

為什么用了 ??index_merge?? 就死鎖了

上面簡要畫了一下兩個update事務加鎖的過程，從圖中可以看到，在??idx_status?? 索引和 PRIMARY （聚簇索引） 上都存在重合交叉的部分，這樣就為死鎖造成了條件。

如，當遇到以下時序時，就會出現(xiàn)死鎖：

事務1等待事務2釋放鎖，事務2等待事務1釋放鎖，這樣就造成了死鎖。

MySQL檢測到死鎖后，會自動回滾代價更低的那個事務，如上邊的時序圖中，事務1持有的鎖比事務2少，則MySQL就將事務1進行了回滾。

解決方案

一、從代碼層面

• where 查詢條件中，只傳 ??trans_id?? ，將數(shù)據(jù)查詢出來后，在代碼層面判斷 status 狀態(tài)是否為0；
• 使用 ??force index(uniq_trans_id)?? 強制查詢語句使用 ??uniq_trans_id?? 索引；
• where 查詢條件后邊直接用 id 字段，通過主鍵去更新。

二、從MySQL層面

• 刪除 ??idx_status?? 索引或者建一個包含這倆列的聯(lián)合索引；
• 將MySQL優(yōu)化器的??index merge??優(yōu)化關閉。

到此這篇關于MySQL 優(yōu)化 index merge引起的死鎖分析的文章就介紹到這了,更多相關MySQL 優(yōu)化 index merge內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！