背景

生產(chǎn)環(huán)境出現(xiàn)死鎖流水，通過(guò)查看死鎖日志，看到造成死鎖的是兩條一樣的update語(yǔ)句（只有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;

一開(kāi)始比較費(fèi)解，通過(guò)大量查詢跟學(xué)習(xí)后，分析出了死鎖形成的具體原理，特分享給大家，希望能幫助到遇到同樣問(wèn)題的朋友。

因?yàn)?code>MySQL知識(shí)點(diǎn)較多，這里對(duì)很多名詞不進(jìn)行過(guò)多介紹，有興趣的朋友，可以后續(xù)進(jìn)行專項(xiàng)深入學(xué)習(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)

簡(jiǎn)要分析下上邊的死鎖日志：

• 1、第一塊內(nèi)容（第1行到第9行）中，第6行為事務(wù)(1)執(zhí)行的SQL語(yǔ)句，第7和第8行意思為事務(wù)(1)在等待 idx_status 索引上的X鎖；
• 2、第二塊內(nèi)容（第11行到第19行）中，第16行為事務(wù)(2)執(zhí)行的SQL語(yǔ)句，第17和第18行意思為事務(wù)(2)持有 idx_status 索引上的X鎖；
• 3、第三塊內(nèi)容（第21行到第23行）的意思為，事務(wù)(2)在等待 PRIMARY 索引上的X鎖。（but not gap指不是間隙鎖）
• 4、最后一句的意思即為，MySQL將事務(wù)(1)進(jìn)行了回滾操作。

表結(jié)構(gòu)

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

通過(guò)表結(jié)構(gòu)可以看出，trans_id 列上有一個(gè)唯一索引uniq_trans_id ，status 列上有一個(gè)普通索引idx_status ，id列為主鍵索引 PRIMARY 。

InnoDB引擎中有兩種索引：

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

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

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

執(zhí)行計(jì)劃

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

為什么會(huì)用 index_merge（索引合并）

MySQL5.0之前，一個(gè)表一次只能使用一個(gè)索引，無(wú)法同時(shí)使用多個(gè)索引分別進(jìn)行條件掃描。但是從5.1開(kāi)始，引入了 ??index merge?? 優(yōu)化技術(shù)，對(duì)同一個(gè)表可以使用多個(gè)索引分別進(jìn)行條件掃描。

如執(zhí)行計(jì)劃中的語(yǔ)句：

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

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

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

第一種 只用uniq_trans_id索引 ：

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

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

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

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

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

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

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

如，當(dāng)遇到以下時(shí)序時(shí)，就會(huì)出現(xiàn)死鎖：

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

MySQL檢測(cè)到死鎖后，會(huì)自動(dòng)回滾代價(jià)更低的那個(gè)事務(wù)，如上邊的時(shí)序圖中，事務(wù)1持有的鎖比事務(wù)2少，則MySQL就將事務(wù)1進(jìn)行了回滾。

解決方案

一、從代碼層面

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

二、從MySQL層面

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

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

最新評(píng)論