一、背景

隨著公司業(yè)務(wù)的發(fā)展，商品庫(kù)存從商品中心獨(dú)立出來成為一個(gè)獨(dú)立的系統(tǒng)，承接主站商品庫(kù)存校驗(yàn)、訂單庫(kù)存扣減、售后庫(kù)存釋放等業(yè)務(wù)。在上線之前我們對(duì)于核心接口進(jìn)行了壓測(cè)，壓測(cè)過程中出現(xiàn)了 MySQL 5.6.35 死鎖現(xiàn)象，通過日志發(fā)現(xiàn)引發(fā)死鎖的只是一條簡(jiǎn)單的sql，死鎖是怎么產(chǎn)生的？發(fā)揚(yáng)技術(shù)人員刨根問底的優(yōu)良傳統(tǒng)，對(duì)于這次死鎖原因進(jìn)行了細(xì)致的排查和總結(jié)。本文即是此次過程的一個(gè)記錄。

在深入探究問題之前，我們先了解一下 MySQL 的加鎖機(jī)制。

二、MySQL 加鎖機(jī)制

首先要明確的一點(diǎn)是 MySQL 加鎖實(shí)際上是給索引加鎖，而非給數(shù)據(jù)加鎖。我們先看下MySQL 索引的結(jié)構(gòu)。

MySQL?索引分為主鍵索引(或聚簇索引)和二級(jí)索引(或非主鍵索引、非聚簇索引、輔助索引，包括各種主鍵索引外的其他所有索引)。不同存儲(chǔ)引擎對(duì)于數(shù)據(jù)的組織方式略有不同。

對(duì)InnoDB而言，主鍵索引和數(shù)據(jù)是存放在一起的，構(gòu)成一顆B 樹(稱為索引組織表)，主鍵位于非葉子節(jié)點(diǎn)，數(shù)據(jù)存放于葉子節(jié)點(diǎn)。示意圖如下：

而MyISAM是堆組織表，主鍵索引和數(shù)據(jù)分開存放，葉子節(jié)點(diǎn)保存的只是數(shù)據(jù)的物理地址，示意圖如下：

二級(jí)索引的組織方式對(duì)于InnoDB和MyISAM是一樣的，保存了二級(jí)索引和主鍵索引的對(duì)應(yīng)關(guān)系，二級(jí)索引列位于非葉子節(jié)點(diǎn)，主鍵值位于葉子節(jié)點(diǎn)，示意圖如下：

那么在MySQL 的這種索引結(jié)構(gòu)下，我們?cè)趺凑业叫枰臄?shù)據(jù)呢？

以select * from t where name='aaa'為例，MySQL Server對(duì)sql進(jìn)行解析后發(fā)現(xiàn)name字段有索引可用，于是先在二級(jí)索引(圖2-2)上根據(jù)name='aaa'找到主鍵id=17，然后根據(jù)主鍵17到主鍵索引上(圖2-1)上找到需要的記錄。

了解 MySQL 利用索引對(duì)數(shù)據(jù)進(jìn)行組織和檢索的原理后，接下來看下MySQL 如何給索引枷鎖。

需要了解的是索引如何加鎖和索引類型(主鍵、唯一、非唯一、沒有索引)以及隔離級(jí)別(RC、RR等)有關(guān)。本例中限定隔離級(jí)別為RC，RR情況下和RC加鎖基本一致，不同的是RC為了防止幻讀會(huì)額外加上間隙鎖。

2.1 ?根據(jù)主鍵進(jìn)行更新

update t set name='xxx' where id=29；只需要將主鍵上id=29的記錄加上X鎖即可(X鎖稱為互斥鎖，加鎖后本事務(wù)可以讀和寫，其他事務(wù)讀和寫會(huì)被阻塞)。如下：

2.2??根據(jù)唯一索引進(jìn)行更新

update t set name='xxx' where name='ddd';這里假設(shè)name是唯一的。InnoDB現(xiàn)在name索引上找到name='ddd'的索引項(xiàng)(id=29)并加上加上X鎖，然后根據(jù)id=29再到主鍵索引上找到對(duì)應(yīng)的葉子節(jié)點(diǎn)并加上X鎖。

一共兩把鎖，一把加在唯一索引上，一把加在主鍵索引上。這里需要說明的是加鎖是一步步加的，不會(huì)同時(shí)給唯一索引和主鍵索引加鎖。這種分步加鎖的機(jī)制實(shí)際上也是導(dǎo)致死鎖的誘因之一。示意如下：

2.3 根據(jù)非唯一索引進(jìn)行更新

update t set name='xxx' where name='ddd';這里假設(shè)name不唯一，即根據(jù)name可以查到多條記錄(id不同)。和上面唯一索引加鎖類似，不同的是會(huì)給所有符合條件的索引項(xiàng)加鎖。示意如下：

這里一共四把鎖，加鎖步驟如下：

1. 在非唯一索引(name)上找到(ddd,29)的索引項(xiàng)，加上X鎖；

2. 根據(jù)(ddd,29)找到主鍵索引的(29,ddd)記錄，加X鎖；

3. 在非唯一索引(name)上找到(ddd,37)的索引項(xiàng)，加上X鎖；

4. 根據(jù)(ddd,29)找到主鍵索引的(37,ddd)記錄，加X鎖；

從上面步驟可以看出，InnoDB對(duì)于每個(gè)符合條件的記錄是分步加鎖的，即先加二級(jí)索引再加主鍵索引；其次是按記錄逐條加鎖的，即加完一條記錄后，再加另外一條記錄，直到所有符合條件的記錄都加完鎖。那么鎖什么時(shí)候釋放呢？答案是事務(wù)結(jié)束時(shí)會(huì)釋放所有的鎖。

小結(jié)：MySQL 加鎖和索引類型有關(guān)，加鎖是按記錄逐條加，另外加鎖也和隔離級(jí)別有關(guān)。

三、死鎖現(xiàn)象及排查

了解MySQL 如何給索引加鎖后，下面步入正題，看看實(shí)際場(chǎng)景下的死鎖現(xiàn)象及其成因分析。

本次發(fā)生死鎖的是庫(kù)存扣減接口，該接口的主要邏輯是用戶下單后，扣減訂單商品在某個(gè)倉(cāng)庫(kù)的庫(kù)存量。比如用戶一個(gè)在vivo官網(wǎng)下單買了1臺(tái)X50手機(jī)和1臺(tái)X30耳機(jī)，那么下單后，首先根據(jù)用戶收貨地址確定發(fā)貨倉(cāng)庫(kù)，然后從該倉(cāng)庫(kù)里面分別減去一個(gè)X50庫(kù)存和一個(gè)X30庫(kù)存。分析死鎖sql之前，先看下商品庫(kù)存表的定義(為方便理解，只保留主要字段)：

CREATE TABLE `store` ( `id` int(10) AUTO_INCREMENT COMMENT '主鍵', `sku_code` varchar(45) COMMENT '商品編碼', `ws_code` varchar(32) COMMENT '倉(cāng)庫(kù)編碼', `store` int(10) COMMENT '庫(kù)存量', PRIMARY KEY (`id`), KEY `idx_skucode` (`sku_code`), KEY `idx_wscode` (`ws_code`) ) ENGINE=InnoDB COMMENT='商品庫(kù)存表'

注意這里分別給sku_code和ws_code兩個(gè)字段單獨(dú)定義了索引：idx_skucode,?idx_wscode。這樣做的原因主要是業(yè)務(wù)上有根據(jù)單個(gè)字段查詢的要求。

再看下庫(kù)存扣減update語(yǔ)句：

update storeset store = store-#{store}where sku_code=#{skuCode} and ws_code = #{wsCode} and (store-#{store}) >= 0

這個(gè)sql的業(yè)務(wù)含義就是對(duì)某個(gè)商品(skuCode)從某個(gè)倉(cāng)庫(kù)(wsCode)中扣減store個(gè)庫(kù)存量，同時(shí)上面的where條件同時(shí)出現(xiàn)了sku_code和ws_code字段，壓測(cè)數(shù)據(jù)中 sku_code的選擇度要比ws_code高，理論上這條sql應(yīng)該會(huì)走idx_skucode索引，那么真實(shí)情況是怎樣的呢？

好，接下來對(duì)庫(kù)存扣減接口卡進(jìn)行壓測(cè)，50的并發(fā)，每個(gè)訂單5個(gè)商品，剛壓不到半分鐘就出現(xiàn)了死鎖，再壓，問題依舊，說明是必現(xiàn)的問題，必現(xiàn)解決后才能繼續(xù)。在MySQL 終端執(zhí)行?show engine innodb status?命令查看最后一次死鎖日志，主要關(guān)注日志中的?LATEST DETECTED DEADLOCK?部分：

------------------------LATEST DETECTED DEADLOCK------------------------2020-xx-xx 21:09:05 7f9b22008700

*** (1) TRANSACTION:TRANSACTION 4219870943, ACTIVE 0 sec fetching rowsmysql tables in use 3, locked 3LOCK WAIT 10 lock struct(s), heap size 2936, 3 row lock(s)MySQL thread id 301903552, OS thread handle 0x7f9b21a7b700, query id 5373393954 10.101.22.135 root updatingupdate storeset update_time = now(), store = store-1where sku_code='5468754' and ws_code = 'NO_001' and (store-1) >= 0
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3331 page no 16 n bits 904 index `idx_wscode` of table `store` trx id 4219870943 lock_mode X locks rec but not gap waitingRecord lock, heap no 415 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 5; hex 5730303735; asc NO_001;;1: len 8; hex 00000000000025a7; asc % ;;
*** (2) TRANSACTION:TRANSACTION 4219870941, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 1mysql tables in use 3, locked 39 lock struct(s), heap size 2936, 4 row lock(s)MySQL thread id 301939956, OS thread handle 0x7f9b22008700, query id 5373393941 10.101.22.135 root updatingupdate storeset update_time = now(), store = store-1where sku_code='5655620' and ws_code = 'NO_001' and (store-1) >= 0
*** (2) HOLDS THE LOCK(S):RECORD LOCKS space id 3331 page no 16 n bits 904 index `idx_wscode` of table `store` trx id 4219870941 lock_mode X locks rec but not gapRecord lock, heap no 415 PHYSICAL RECORD: n_fields 2; compact format; info bits 00: len 5; hex 5730303735; asc NO_001;;1: len 8; hex 00000000000025a7; asc % ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:RECORD LOCKS space id 3331 page no 7 n bits 328 index `PRIMARY` of table `store` trx id 4219870941 lock_mode X locks rec but not gap waitingRecord lock, heap no 72 PHYSICAL RECORD: n_fields 9; compact format; info bits 00: len 8; hex 00000000000025a7; asc % ;;1: len 6; hex 0000fb85fdf7; asc ;;2: len 7; hex 1a00001d3b21d4; asc ;! ;;3: len 7; hex 35343638373534; asc 5468754;;4: len 5; hex 5730303735; asc NO_001;;5: len 8; hex 8000000000018690; asc ;;6: len 5; hex 99a76b2b97; asc k ;;7: len 5; hex 99a7e35244; asc RD;;8: len 1; hex 01; asc ;;

從上面日志可以看出，存在兩個(gè)事務(wù)，分別在執(zhí)行這兩條sql時(shí)發(fā)生了死鎖：

update store set update_time = now(), store = store-1 where sku_code='5468754' and ws_code = 'NO_001' and (store-1) >= 0
update?store?set?update_time?=?now(),?store?=?store-1?where?sku_code='5655620'?and?ws_code?=?'NO_001'?and?(store-1)?>=?0?

看一下實(shí)際數(shù)據(jù)：

圖3-1 庫(kù)存表數(shù)據(jù)

就是說，這兩個(gè)事務(wù)在更新同一張表的不同行時(shí)發(fā)生了死鎖。在我們直觀印象里，innodb使用的是行鎖，不同的行鎖之間應(yīng)該是互不干擾的？那這是怎么一回事呢？

我們?cè)倏匆幌聈pdate的執(zhí)行計(jì)劃：

?圖3-2 update語(yǔ)句執(zhí)行計(jì)劃

和我們想象的不同，InnoDB既沒有使用idx_skucode索引，也沒有使用idx_wscode索引，而是使用了index_merge。index_merge和這兩個(gè)索引是什么關(guān)系呢？

查詢資料得知index_merge是MySQL 5.1后引入的一項(xiàng)索引合并優(yōu)化技術(shù)，它允許對(duì)同一個(gè)表同時(shí)使用多個(gè)索引進(jìn)行查詢，并對(duì)多個(gè)索引的查詢結(jié)果進(jìn)行合并(取交集(intersect)、并集(union)等)后返回。

回到上面的update語(yǔ)句：

where sku_code='5468754' and ws_code = 'NO_001'??；如果沒有index_merge，要么走idx_skucode索引，要么走idx_wscode索引，不會(huì)出現(xiàn)兩個(gè)索引一起使用的情況。而在使用index_merge技術(shù)后，會(huì)同時(shí)執(zhí)行兩個(gè)索引，分別查到結(jié)果后再進(jìn)行合并(where條件是and，所以會(huì)做交集運(yùn)算)。再結(jié)合第二部分對(duì)加鎖機(jī)制(分步按記錄加鎖)的理解，是否隱約覺得兩個(gè)索引的同時(shí)加鎖是導(dǎo)致死鎖的原因呢？

我們?cè)偕钊胨梨i日志看一下，日志比較復(fù)雜，翻譯過來大意如下：

1）事務(wù)一 4219870943 在執(zhí)行update語(yǔ)句時(shí)，在等待索引idx_wscode上的行鎖(編號(hào)space id 3331 page no 16 n bits 904 )。

2）事務(wù)二 4219870941 在執(zhí)行update語(yǔ)句時(shí)，已經(jīng)持有idx_wscode上的行鎖(編號(hào)space id 3331 page no 16 n bits 904 )，從鎖編號(hào)來看，就是事務(wù)一需要的鎖。

3）事務(wù)二 4219870941 同時(shí)也在等待主鍵索引上的一把鎖，這把鎖誰(shuí)在持有呢？從這行日志（3: len 7; hex 35343638373534; asc 5468754;;）可以看出，正是事務(wù)一要更新的那行記錄，說明這把鎖被事務(wù)一霸占著。

好了，死鎖條件已經(jīng)很清楚了：事務(wù)一在等待事務(wù)二持有的索引 idx_wscode上的行鎖(編號(hào)space id 3331 page no 16 n bits 904 )，而事務(wù)二同時(shí)也在等待事務(wù)一持有的主鍵索引(5468754)上的鎖，大家互不相讓，只能僵在那里死鎖嘍^_^

用一張圖來說明一下這個(gè)情況：

上圖描述的只是發(fā)生死鎖的一條可能路徑，實(shí)際上仔細(xì)梳理的話還有其他路徑也會(huì)導(dǎo)致死鎖，大家感興趣可以自己探索。上圖解釋如下：

1）事務(wù)一(where sku_code='5468754' and ws_code = 'NO_001'?)首先走idx_skucode索引，分別對(duì)二級(jí)索引和主鍵索引加鎖成功(1-1和1-2)。

2）此時(shí)事務(wù)二開始執(zhí)行( where sku_code='5655620' and ws_code = 'NO_001'?)，首先也是走idx_skucode(左上)索引，因?yàn)楹褪聞?wù)一所加鎖的記錄不沖突，所以也順利加鎖成功(2-1和2-2)。

3）事務(wù)二繼續(xù)執(zhí)行，這時(shí)走的是idx_wscode(右上)索引，先對(duì)二級(jí)索引加鎖成功(2-3，此時(shí)事務(wù)一還沒有開始在idx_wscode上加鎖)，但是在對(duì)主鍵索引加索引時(shí)，發(fā)現(xiàn)id=9639的主鍵索引已經(jīng)被事務(wù)一上鎖，因此只能等待(2-4)，同時(shí)在2-4完成加鎖前，對(duì)其他記錄的加鎖也會(huì)暫停(2-5和2-6，因?yàn)镮nnoDB是逐條記錄加鎖的，前一條未完成則后面的不會(huì)執(zhí)行)。

4）此時(shí)事務(wù)一繼續(xù)執(zhí)行，這時(shí)走的是idx_wscode索引，但是加鎖的時(shí)候發(fā)現(xiàn)(NO_001,9639)這條索引項(xiàng)已經(jīng)被事務(wù)二上鎖，所以也只能等待。同理，后面的1-4也無法執(zhí)行。

到此就出現(xiàn)了“兩個(gè)事務(wù)，反向加鎖"導(dǎo)致的死鎖現(xiàn)象。

四、如何解決

死鎖的本質(zhì)原因還是由加鎖順序不同所導(dǎo)致，本例中是由于Index Merge同時(shí)使用2個(gè)索引方向加鎖所導(dǎo)致，解決方法也比較簡(jiǎn)單，就是消除因index merge帶來的多個(gè)索引同時(shí)執(zhí)行的情況。

1）利用force index(idx_skucode)強(qiáng)制走某個(gè)索引，這樣InnoDB就會(huì)忽略index merge，避免多個(gè)索引同時(shí)加鎖的情況。

圖4-1 使用Force Index強(qiáng)制指定索引

2）禁用Index Merge，這樣InnoDB只會(huì)使用idx_skucode和idx_wscode中的一個(gè)，所有事物加鎖順序都一樣，不會(huì)造成死鎖。

用命令禁用Index Merge：SET GLOBAL optimizer_switch='index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off';

?圖4-2 關(guān)閉Index Merge特性

重新登錄終端后再看下執(zhí)行計(jì)劃：

圖4-3? 關(guān)閉Index Merge后索引情況

3）既然Index Merge同時(shí)使用了2個(gè)獨(dú)立索引，我們不妨新建一個(gè)包含這兩個(gè)索引所有字段的聯(lián)合索引，這樣InnoDB就只會(huì)走這個(gè)單獨(dú)的聯(lián)合索引，這其實(shí)和禁用index merge是一個(gè)道理。

新增聯(lián)合索引：

alter table store add index?

idx_skucode_wscode(sku_code,ws_code);

再看下執(zhí)行計(jì)劃，type=range說明沒有使用index merge，另外key=idx_skucode_wscode說明走的是剛剛創(chuàng)建的聯(lián)合索引：

圖4-4 利用聯(lián)合索引來避免Index Merge優(yōu)化

4）最后推薦另外一種繞過index merge限制的方式。即去除死鎖產(chǎn)生的條件，具體方法是先利用idx_skucode和idx_wscode查詢到主鍵id，再拿主鍵id進(jìn)行update操作。這種方式避免了由update引入X鎖，由于最終更新的條件是唯一固定的，所以不存在加鎖順序的問題，避免了死鎖的產(chǎn)生。

五、小結(jié)

本文通過一個(gè)實(shí)際案例描述了由于Index Merge優(yōu)化導(dǎo)致的死鎖，詳細(xì)描述了死鎖產(chǎn)生的原因以及解決方案，并順便介紹了 MySQL 索引結(jié)構(gòu)及加鎖機(jī)制。通過本文，大家可以掌握死鎖分析的基本理論和一般方法，希望能為大家工作中快速解決實(shí)際出現(xiàn)的死鎖問題提供思路。