MySQL的index merge(索引合併）導致資料庫死鎖分析與解決方案

京東雲技術團隊發表於2023-10-13
MySqlIndex索引資料庫

背景

在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

作者：京東工業 李小輝

來源：京東雲開發者社群 轉載請註明來源

相關文章