一、背景

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

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

二、MySQL 加鎖機制

首先要明確的一點是 MySQL 加鎖實際上是給索引加鎖，而非給數(shù)據(jù)加鎖。我們先看下MySQL 索引的結構。

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

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

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

二級索引的組織方式對于InnoDB和MyISAM是一樣的，保存了二級索引和主鍵索引的對應關系，二級索引列位于非葉子節(jié)點，主鍵值位于葉子節(jié)點，示意圖如下：

那么在MySQL 的這種索引結構下，我們怎么找到需要的數(shù)據(jù)呢？

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

小結：MySQL 加鎖和索引類型有關，加鎖是按記錄逐條加，另外加鎖也和隔離級別有關。

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

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

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

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

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

再看下庫存扣減update語句：

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

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

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

從上面日志可以看出，存在兩個事務，分別在執(zhí)行這兩條sql時發(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ù)據(jù)：

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

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

我們再看一下update的執(zhí)行計劃：

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

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

查詢資料得知index_merge是MySQL 5.1后引入的一項索引合并優(yōu)化技術，它允許對同一個表同時使用多個索引進行查詢，并對多個索引的查詢結果進行合并(取交集(intersect)、并集(union)等)后返回。

回到上面的update語句：

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

我們再深入死鎖日志看一下，日志比較復雜，翻譯過來大意如下：

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

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

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

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

用一張圖來說明一下這個情況：

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

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

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

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

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

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

四、如何解決

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

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

圖4-1 使用Force Index強制指定索引

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

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

?圖4-2 關閉Index Merge特性

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

圖4-3? 關閉Index Merge后索引情況

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

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

alter table store add index?

idx_skucode_wscode(sku_code,ws_code);

再看下執(zhí)行計劃，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進行update操作。這種方式避免了由update引入X鎖，由于最終更新的條件是唯一固定的，所以不存在加鎖順序的問題，避免了死鎖的產(chǎn)生。

五、小結

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