背景
在DBS-集群列表-更多-連接查詢-死鎖中，看到9月22日有數(shù)據(jù)庫(kù)死鎖日志，后排查發(fā)現(xiàn)是因?yàn)閙ysql的優(yōu)化-index merge（索引合并）導(dǎo)致數(shù)據(jù)庫(kù)死鎖。
定義
index merge(索引合并)：該數(shù)據(jù)庫(kù)查詢優(yōu)化的一種技術(shù)，在mysql 5.1之后進(jìn)行引入，它可以在多個(gè)索引上進(jìn)行查詢，并將結(jié)果合并返回。
mysql數(shù)據(jù)庫(kù)的鎖機(jī)制
在排查問(wèn)題之前，首先講一下mysql數(shù)據(jù)庫(kù)的鎖機(jī)制：
1 加鎖的基本單位是 next-key lock（記錄鎖+間隙鎖），當(dāng)記錄鎖或者間隙鎖能夠解決幻讀的問(wèn)題，就會(huì)退化為記錄鎖（行鎖），間隙鎖。
2 加鎖是將鎖加在了索引之上，而不是數(shù)據(jù)之上。
3 對(duì)于當(dāng)前讀，索引進(jìn)行加鎖，當(dāng)前讀語(yǔ)句包括了（select ... from. ... for update,select...from ..... lock in share mode,update...,delete....）。
4 加鎖根據(jù)唯一性索引、非唯一性索引進(jìn)行了區(qū)分，根據(jù)查詢條件分為了等值查詢、范圍查詢，根據(jù)是否能夠查到數(shù)據(jù)又分為了記錄存在和不存在的情況。
本次死鎖問(wèn)題使用的索引是非唯一性索引的等值查詢中記錄存在的情況，因此本文僅僅詳細(xì)介紹這種情況，其它情況可以查看最下面的參考文檔1：
加鎖情況是：會(huì)依次掃描，首先掃描到條件匹配的數(shù)據(jù)，加一個(gè)next-key lock，然后接下來(lái)掃描到第一個(gè)記錄不匹配的數(shù)據(jù)，增加一個(gè)間隙鎖，最后對(duì)查到記錄的主鍵增加一個(gè)記錄鎖，
針對(duì)以上情況加了三種鎖，加鎖的目的是為了防止幻讀的發(fā)生。
針對(duì)二級(jí)索引的鎖進(jìn)行分析：
表結(jié)構(gòu)：
CREATE TABLE `jdi_roster_apply_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`apply_id` varchar(100) NOT NULL COMMENT '申請(qǐng)單號(hào)',
`status` tinyint(10) NOT NULL COMMENT '狀態(tài)',
PRIMARY KEY (`id`),
KEY `idx_status` (`status`),
KEY `idx_apply_id` (`apply_id`)
) ENGINE=InnoDB AUTO_INCREMENT=984483 DEFAULT CHARSET=utf8 COMMENT='黑白名單申請(qǐng)單明細(xì)'
表數(shù)據(jù)：
| id | apply_id | status |
|---|---|---|
| 959651 | 1695369220522068998 | 1 |
| 960738 | 1695369227576173690 | 1 |
| 961319 | 1695373047673903326 | 1 |
| 961365 | 1695373122447865228 | 1 |
通過(guò) idx_apply_id建立的b+樹(shù)：
因?yàn)樗饕嵌?jí)索引，所以葉子節(jié)點(diǎn)存儲(chǔ)的數(shù)據(jù)是主鍵值。
執(zhí)行sql:
select * from jdi_roster_apply_detail where apply_id='1695369227576173690' for update
執(zhí)行數(shù)據(jù)掃描過(guò)程
1 查到符合條件的記錄，增加next-key 鎖，因此鎖是(1695369220522068998,1695369227576173690]
2 找到第一個(gè)不符合記錄的數(shù)據(jù)增加間隙鎖，因此鎖是 （1695369227576173690，1695373047673903326）
3 對(duì)符合條件的主鍵索引增加記錄鎖，因此對(duì) id=960738，增加記錄鎖。
針對(duì)三種鎖解決的幻讀：
1 如果沒(méi)有第一條的next-key鎖， 另一個(gè)事務(wù)增加一個(gè)apply_id=1695369227576173690， id<960738 時(shí)，該事務(wù)在進(jìn)行查詢時(shí)，會(huì)多一條記錄，因此會(huì)造成幻讀。
2 如果沒(méi)有第二條的 間隙鎖，另一個(gè)事務(wù)增加一個(gè)apply_id=1695369227576173690， id>960738是，該事務(wù)在進(jìn)行查詢時(shí)，會(huì)多一條記錄，因此會(huì)造成幻讀。
3 如果沒(méi)有第三條的記錄鎖，另一條事務(wù)刪除一條 id=960738的記錄，該事務(wù)進(jìn)行查詢時(shí)，會(huì)少一條數(shù)據(jù)，因此會(huì)造成幻讀。
實(shí)際問(wèn)題分析
數(shù)據(jù)庫(kù)死鎖日志
以上日志兩個(gè)事務(wù)分別執(zhí)行了update語(yǔ)句：
#事務(wù)1
update jdi_roster_apply_detail set `status` = 10 where `status` = 1 and apply_id = '1695369220522068998'
#事務(wù)2
update jdi_roster_apply_detail set `status` = 10 where `status` = 1 and apply_id = '1695369227576173690'
這個(gè)sql是用于將某個(gè)申請(qǐng)單id待審批的數(shù)據(jù)改為已審批。
因?yàn)樵谔┥嚼锊荒軋?zhí)行update語(yǔ)句 ，因此執(zhí)行了select語(yǔ)句查看用的索引情況：
explain select * from jdi_roster_apply_detail where `status` = 1 and apply_id = '1695369220522068998'
執(zhí)行的結(jié)果：
通過(guò)結(jié)果可以看出兩個(gè)update語(yǔ)句都使用了兩個(gè)索引，分別是idx_status，idx_apply_id，然后將查到的結(jié)果進(jìn)行合并，因此在模擬的過(guò)程中，可以將其拆成兩個(gè)查詢語(yǔ)句。
死鎖模擬
| 事務(wù)1 | 事務(wù)2 | 鎖的范圍 |
|---|---|---|
| begin | begin | |
| select * from jdi_roster_apply_detail where apply_id = '1695369220522068998' for update | idx_apply_id所以鎖住了（-∞，1695369220522068998]，(1695369220522068998,1695369227576173690) 主鍵id索引鎖住了 id=959651 | |
| select * from jdi_roster_apply_detail where apply_id = '1695369227576173690' for update | idx_apply_id所以鎖住了（1695369220522068998，1695369227576173690]，(1695369227576173690,1695373047673903326) 主鍵id索引鎖住了 id=960738 | |
| select * from jdi_roster_apply_detail where status = 1 for update | 會(huì)對(duì)idx_status上加next-key鎖和間隙鎖，但是在對(duì)主鍵959651，960738，961319，961365進(jìn)行加記錄鎖時(shí)，其中事務(wù)2 對(duì)960738已經(jīng)加了記錄鎖，所以該事務(wù)1進(jìn)行了阻塞。 | |
| select * from jdi_roster_apply_detail where status = 1 for update | 會(huì)對(duì)idx_status上加next-key鎖和間隙鎖，但是在對(duì)主鍵959651，960738，961319，961365進(jìn)行加記錄鎖時(shí)，其中事務(wù)1對(duì)959651已經(jīng)加了記錄鎖，所以該事務(wù)2進(jìn)行了阻塞。 | |
| deadlock |
兩個(gè)事務(wù)分別想要兩個(gè)主鍵id的記錄鎖，造成相互等待，形成了死鎖。
以上是先執(zhí)行idx_apply_id的索引查詢?cè)賵?zhí)行idx_status索引查詢，如果先執(zhí)行idx_status索引查詢，再執(zhí)行idx_apply_id的索引查詢，也會(huì)因?yàn)橹麈I的記錄鎖造成死鎖。
解決方案
1 利用force index(idx_apply_id)強(qiáng)制走某個(gè)索引，這樣InnoDB就會(huì)忽略index merge，避免多個(gè)索引同時(shí)加鎖的情況。
2 禁用Index Merge，用命令禁用Index Merge：SET GLOBAL optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off';
3 Index Merge同時(shí)使用了2個(gè)獨(dú)立索引，因此新建一個(gè)包含這兩個(gè)索引所有字段的聯(lián)合索引，這樣InnoDB就只會(huì)走這個(gè)單獨(dú)的聯(lián)合索引。
第三種方案相較于第一種查詢性能更好，相對(duì)于第二種僅僅作用于該表，影響范圍小，因此本次也是采用了該方案。
總結(jié)
該死鎖問(wèn)題是因?yàn)閮?yōu)化器使用了合并索引問(wèn)題導(dǎo)致的，最終通過(guò)新建一個(gè)聯(lián)合索引來(lái)解決這個(gè)問(wèn)題。
參考文檔：
1 https://www.xiaolincoding.com/mysql/lock/how_to_lock.html
作者：京東工業(yè) 李小輝文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-710666.html
來(lái)源：京東云開(kāi)發(fā)者社區(qū) 轉(zhuǎn)載請(qǐng)注明來(lái)源文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-710666.html
到了這里，關(guān)于MySQL的index merge(索引合并）導(dǎo)致數(shù)據(jù)庫(kù)死鎖分析與解決方案的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
