在DBS-集群列表-更多-連接查詢-死鎖中，看到9月22日有資料庫死鎖日誌，後排查發現是因為mysql的優化-index merge（索引合併）導致資料庫死鎖。 ...
背景
在DBS-集群列表-更多-連接查詢-死鎖中，看到9月22日有資料庫死鎖日誌，後排查發現是因為mysql的優化-index merge（索引合併）導致資料庫死鎖。
定義
index merge(索引合併)：該資料庫查詢優化的一種技術，在mysql 5.1之後進行引入，它可以在多個索引上進行查詢，並將結果合併返回。
mysql資料庫的鎖機制
在排查問題之前，首先講一下mysql資料庫的鎖機制：
1 加鎖的基本單位是 next-key lock（記錄鎖+間隙鎖），當記錄鎖或者間隙鎖能夠解決幻讀的問題，就會退化為記錄鎖（行鎖），間隙鎖。
2 加鎖是將鎖加在了索引之上，而不是數據之上。
3 對於當前讀，索引進行加鎖，當前讀語句包括了（select ... from. ... for update,select...from ..... lock in share mode,update...,delete....）。
4 加鎖根據唯一性索引、非唯一性索引進行了區分，根據查詢條件分為了等值查詢、範圍查詢，根據是否能夠查到數據又分為了記錄存在和不存在的情況。
本次死鎖問題使用的索引是非唯一性索引的等值查詢中記錄存在的情況，因此本文僅僅詳細介紹這種情況，其它情況可以查看最下麵的參考文檔1：
加鎖情況是：會依次掃描，首先掃描到條件匹配的數據，加一個next-key lock，然後接下來掃描到第一個記錄不匹配的數據，增加一個間隙鎖，最後對查到記錄的主鍵增加一個記錄鎖，
針對以上情況加了三種鎖，加鎖的目的是為了防止幻讀的發生。
針對二級索引的鎖進行分析：
表結構：
CREATE TABLE `jdi_roster_apply_detail` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`apply_id` varchar(100) NOT NULL COMMENT '申請單號',
`status` tinyint(10) NOT NULL COMMENT '狀態',
PRIMARY KEY (`id`),
KEY `idx_status` (`status`),
KEY `idx_apply_id` (`apply_id`)
) ENGINE=InnoDB AUTO_INCREMENT=984483 DEFAULT CHARSET=utf8 COMMENT='黑白名單申請單明細'
表數據：
| id | apply_id | status |
|---|---|---|
| 959651 | 1695369220522068998 | 1 |
| 960738 | 1695369227576173690 | 1 |
| 961319 | 1695373047673903326 | 1 |
| 961365 | 1695373122447865228 | 1 |
通過 idx_apply_id建立的b+樹：
因為索引是二級索引，所以葉子節點存儲的數據是主鍵值。
執行sql:
select * from jdi_roster_apply_detail where apply_id='1695369227576173690' for update
執行數據掃描過程
1 查到符合條件的記錄，增加next-key 鎖，因此鎖是(1695369220522068998,1695369227576173690]
2 找到第一個不符合記錄的數據增加間隙鎖，因此鎖是 （1695369227576173690，1695373047673903326）
3 對符合條件的主鍵索引增加記錄鎖，因此對 id=960738，增加記錄鎖。
針對三種鎖解決的幻讀：
1 如果沒有第一條的next-key鎖， 另一個事務增加一個apply_id=1695369227576173690， id<960738 時，該事務在進行查詢時，會多一條記錄，因此會造成幻讀。
2 如果沒有第二條的 間隙鎖，另一個事務增加一個apply_id=1695369227576173690， id>960738是，該事務在進行查詢時，會多一條記錄，因此會造成幻讀。
3 如果沒有第三條的記錄鎖，另一條事務刪除一條 id=960738的記錄，該事務進行查詢時，會少一條數據，因此會造成幻讀。
實際問題分析
資料庫死鎖日誌
以上日誌兩個事務分別執行了update語句：
#事務1
update jdi_roster_apply_detail set `status` = 10 where `status` = 1 and apply_id = '1695369220522068998'
#事務2
update jdi_roster_apply_detail set `status` = 10 where `status` = 1 and apply_id = '1695369227576173690'
這個sql是用於將某個申請單id待審批的數據改為已審批。
因為在泰山裡不能執行update語句 ，因此執行了select語句查看用的索引情況：
explain select * from jdi_roster_apply_detail where `status` = 1 and apply_id = '1695369220522068998'
執行的結果：
通過結果可以看出兩個update語句都使用了兩個索引，分別是idx_status，idx_apply_id，然後將查到的結果進行合併，因此在模擬的過程中，可以將其拆成兩個查詢語句。
死鎖模擬
| 事務1 | 事務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 | 會對idx_status上加next-key鎖和間隙鎖，但是在對主鍵959651，960738，961319，961365進行加記錄鎖時，其中事務2 對960738已經加了記錄鎖，所以該事務1進行了阻塞。 | |
| select * from jdi_roster_apply_detail where status = 1 for update | 會對idx_status上加next-key鎖和間隙鎖，但是在對主鍵959651，960738，961319，961365進行加記錄鎖時，其中事務1對959651已經加了記錄鎖，所以該事務2進行了阻塞。 | |
| deadlock |
兩個事務分別想要兩個主鍵id的記錄鎖，造成相互等待，形成了死鎖。
以上是先執行idx_apply_id的索引查詢再執行idx_status索引查詢，如果先執行idx_status索引查詢，再執行idx_apply_id的索引查詢，也會因為主鍵的記錄鎖造成死鎖。
解決方案
1 利用force index(idx_apply_id)強制走某個索引，這樣InnoDB就會忽略index merge，避免多個索引同時加鎖的情況。
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同時使用了2個獨立索引，因此新建一個包含這兩個索引所有欄位的聯合索引，這樣InnoDB就只會走這個單獨的聯合索引。
第三種方案相較於第一種查詢性能更好，相對於第二種僅僅作用於該表，影響範圍小，因此本次也是採用了該方案。
總結
該死鎖問題是因為優化器使用了合併索引問題導致的，最終通過新建一個聯合索引來解決這個問題。
參考文檔：
1 https://www.xiaolincoding.com/mysql/lock/how_to_lock.html
作者：京東工業 李小輝
來源：京東雲開發者社區 轉載請註明來源
