MySQL 5.6.35 索引优化导致的死锁案例解析

来源：这里教程网时间：2026-03-01 15:37:43 作者：

一、背景

随着公司业务的发展，商品库存从商品中心独立出来成为一个独立的系统，承接主站商品库存校验、订单库存扣减、售后库存释放等业务。在上线之前我们对于核心接口进行了压测，压测过程中出现了 MySQL 5.6.35 死锁现象，通过日志发现引发死锁的只是一条简单的 sql ，死锁是怎么产生的？发扬技术人员刨根问底的优良传统，对于这次死锁原因进行了细致的排查和总结。本文既是此次过程的一个记录。

在深入探究问题之前，我们先了解一下 MySQL 的加锁机制。

二、 MySQL 加锁机制

首先要明确的一点是 MySQL 加锁实际上是给索引加锁，而非给数据加锁。我们先看下 MySQL 索引的结构。

MySQL 索引分为 主键索引 ( 或聚簇索引 ) 和 二级索引 ( 或非主键索引、非聚簇索引、辅助索引，包括各种主键索引外的其他所有索引 ) 。不同存储引擎对于数据的组织方式略有不同。

对 InnoDB 而言，主键索引和数据是存放在一起的，构成一颗 B+ 树 ( 称为索引组织表 ) ，主键位于非叶子节点，数据存放于叶子节点。

而 MyISAM 是堆组织表，主键索引和数据分开存放，叶子节点保存的只是数据的物理地址。

二级索引的组织方式对于 InnoDB 和 MyISAM 是一样的，保存了二级索引和主键索引的对应关系，二级索引列位于非叶子节点，主键值位于叶子节点。

那么在 MySQL 的这种索引结构下，我们怎么找到需要的数据呢？

以 select * from t where name='aaa' 为例， MySQL Server 对 sql 进行解析后发现 name 字段有索引可用，于是先在二级索引 ( 图 2-2) 上根据 name='aaa' 找到主键 id=17 ，然后根据主键 17 到主键索引上 ( 图 2-1) 上找到需要的记录。

了解 MySQL 利用索引对数据进行组织和检索的原理后，接下来看下 MySQL 如何给索引枷锁。

需要了解的是索引如何加锁和索引类型 ( 主键、唯一、非唯一、没有索引 ) 以及隔离级别 (RC 、 RR 等 ) 有关。本例中限定隔离级别为 RC ， RR 情况下和 RC 加锁基本一致，不同的是 RC 为了防止幻读会额外加上间隙锁。

2.1 根据主键进行更新

update t set name='xxx' where id=29 ；只需要将主键上 id=29 的记录加上 X 锁即可 (X 锁称为互斥锁，加锁后本事务可以读和写，其他事务读和写会被阻塞 ) 。

2.2 根据唯一索引进行更新

update t set name='xxx' where name='ddd'; 这里假设 name 是唯一的。 InnoDB 现在 name 索引上找到 name='ddd' 的索引项 (id=29) 并加上加上 X 锁，然后根据 id=29 再到主键索引上找到对应的叶子节点并加上 X 锁。

一共两把锁，一把加在唯一索引上，一把加在主键索引上。这里需要说明的是加锁是一步步加的，不会同时给唯一索引和主键索引加锁。这种分步加锁的机制实际上也是导致死锁的诱因之一。

2.3 根据非唯一索引进行更新

update t set name='xxx' where name='ddd'; 这里假设 name 不唯一，即根据 name 可以查到多条记录 (id 不同 ) 。和上面唯一索引加锁类似，不同的是会给所有符合条件的索引项加锁。示意如下：

这里一共四把锁，加锁步骤如下：

在非唯一索引 (name) 上找到 (ddd,29) 的索引项，加上 X 锁；
根据 (ddd,29) 找到主键索引的 (29,ddd) 记录，加 X 锁；
在非唯一索引 (name) 上找到 (ddd,37) 的索引项，加上 X 锁；
根据 (ddd,29) 找到主键索引的 (37,ddd) 记录，加 X 锁；

从上面步骤可以看出， InnoDB 对于每个符合条件的记录是分步加锁的，即先加二级索引再加主键索引；其次是按记录逐条加锁的，即加完一条记录后，再加另外一条记录，直到所有符合条件的记录都加完锁。那么锁什么时候释放呢？答案是事务结束时会释放所有的锁。

小结： MySQL 加锁和索引类型有关，加锁是按记录逐条加，另外加锁也和隔离级别有关。

三、死锁现象及排查

了解 MySQL 如何给索引加锁后，下面步入正题，看看实际场景下的死锁现象及其成因分析。

本次发生死锁的是库存扣减接口，该接口的主要逻辑是用户下单后，扣减订单商品在某个仓库的库存量。比如用户一个在 vivo 官网下单买了 1 台 X50 手机和 1 台 X30 耳机，那么下单后，首先根据用户收货地址确定发货仓库，然后从该仓库里面分别减去一个 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 。这样做的原因主要是业务上有根据单个字段查询的要求。

再看下库存扣减 update 语句：

update store

set store = store -#{ store }

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

这个 sql 的业务含义就是对某个商品 (skuCode) 从某个仓库 (wsCode) 中扣减 store 个库存量，同时上面的 where 条件同时出现了 sku_code 和 ws_code 字段，压测数据中 sku_code 的选择度要比 ws_code 高，理论上这条 sql 应该会走 idx_skucode 索引，那么真实情况是怎样的呢？

好，接下来对库存扣减接口卡进行压测，50的并发，每个订单5个商品，刚压不到半分钟就出现了死锁，再压，问题依旧，说明是流量交易必现的问题，必现解决后才能继续。在 MySQL 终端执行 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 rows

mysql tables in use 3 , locked 3

LOCK 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 updating

update store

set update_time = now (), store = store -1

where 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 waiting

Record lock , heap no 415 PHYSICAL RECORD : n_fields 2 ; compact format; info bits 0

0: 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 1

mysql tables in use 3 , locked 3

9 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 updating

update store

set update_time = now (), store = store -1

where 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 gap

Record lock , heap no 415 PHYSICAL RECORD : n_fields 2 ; compact format; info bits 0

0: 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 waiting

Record lock , heap no 72 PHYSICAL RECORD : n_fields 9 ; compact format; info bits 0

0: 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 ;;

从上面日志可以看出，存在两个事务，分别在执行这两条 sql 时发生了死锁：

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

看一下实际数据：

就是说，这两个事务在更新同一张表的不同行时发生了死锁。在我们直观印象里， innodb 使用的是行锁，不同的行锁之间应该是互不干扰的？那这是怎么一回事呢？

我们再看一下 update 的执行计划：

和我们想象的不同， InnoDB 既没有使用 idx_skucode 索引，也没有使用 idx_wscode 索引，而是使用了 index_merge 。 index_merge 和这两个索引是什么关系呢？

查询资料得知 index_merge 是 MySQL 5.1 后引入的一项索引合并优化技术，它允许对同一个表同时使用多个索引进行查询，并对多个索引的查询结果进行合并 ( 取交集 (intersect) 、并集 (union) 等 ) 后返回。

回到上面的 update 语句： where sku_code='5468754' and ws_code = 'NO_001' ；如果没有 index_merge ，要么走 idx_skucode 索引，要么走 idx_wscode 索引，不会出现两个索引一起使用的情况。而在使用 index_merge 技术后，会同时执行两个索引，分别查到结果后再进行合并 (where 条件是 and ，所以会做交集运算 ) 。再结合第二部分对加锁机制 ( 分步按记录加锁 ) 的理解，是否隐约觉得两个索引的同时加锁是导致死锁的原因呢？

我们再深入死锁日志看一下，日志比较复杂，翻译过来大意如下：

1 ）事务一 4219870943 在执行 update 语句时，在等待索引 idx_wscode 上的行锁 ( 编号 space id 3331 page no 16 n bits 904 ) 。

2 ）事务二 4219870941 在执行 update 语句时，已经持有 idx_wscode 上的行锁 ( 编号 space id 3331 page no 16 n bits 904 ) ，从锁编号来看，就是事务一需要的锁。

3 ）事务二 4219870941 同时也在等待主键索引上的一把锁，这把锁谁在持有呢？从这行日志（ 3: len 7; hex 35343638373534; asc 5468754;; ）可以看出，正是事务一要更新的那行记录，说明这把锁被事务一霸占着。

好了，死锁条件已经很清楚了：事务一在等待事务二持有的索引 idx_wscode 上的行锁 ( 编号 space id 3331 page no 16 n bits 904 ) ，而事务二同时也在等待事务一持有的主键索引 ( 5468754) 上的锁，大家互不相让，只能僵在那里死锁喽 ^_^

上图描述的只是发生死锁的一条可能路径，实际上仔细梳理的话还有其他路径也会导致死锁，大家感兴趣可以自己探索。上图解释如下：

1 ）事务一 ( where sku_code='5468754' and ws_code = 'NO_001' ) 首先走 idx_skucode 索引，分别对二级索引和主键索引加锁成功 (1-1 和 1-2) 。

2 ）此时事务二开始执行 ( where sku_code='5655620' and ws_code = 'NO_001' ) ，首先也是走 idx_skucode( 左上 ) 索引，因为和事务一所加锁的记录不冲突，所以也顺利加锁成功 (2-1 和 2-2) 。

3 ）事务二继续执行，这时走的是 idx_wscode( 右上 ) 索引，先对二级索引加锁成功 (2-3 ，此时事务一还没有开始在 idx_wscode 上加锁 ) ，但是在对主键索引加索引时，发现 id=9639 的主键索引已经被事务一上锁，因此只能等待 (2-4) ，同时在 2-4 完成加锁前，对其他记录的加锁也会暂停 (2-5 和 2-6 ，因为 InnoDB 是逐条记录加锁的，前一条未完成则后面的不会执行 ) 。

4 ）此时事务一继续执行，这时走的是 idx_wscode 索引，但是加锁的时候发现 (NO_001,9639) 这条索引项已经被事务二上锁，所以也只能等待。同理，后面的 1-4 也无法执行。

到此就出现了 “ 两个事务，反向加锁 " 导致的死锁现象。

四、如何解决

死锁的本质原因还是由加锁顺序不同所导致，本例中是由于 Index Merge 同时使用 2 个索引方向加锁所导致，解决方法也比较简单，就是消除因 index merge 带来的多个索引同时执行的情况。

1 ）利用 force index(idx_skucode) 强制走某个索引，这样 InnoDB 就会忽略 index merge ，避免多个索引同时加锁的情况。

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';

重新登录终端后再看下执行计划：

3 ）既然 Index Merge 同时使用了 2 个独立索引，我们不妨新建一个包含这两个索引所有字段的联合索引，这样 InnoDB 就只会走这个单独的联合索引，这其实和禁用 index merge 是一个道理。

新增联合索引：

alter table store add index

idx_skucode_wscode(sku_code,ws_code);

再看下执行计划， type=range 说明没有使用 index merge ，另外 key=idx_skucode_wscode 说明走的是刚刚创建的联合索引：

4 ）最后推荐另外一种绕过 index merge 限制的方式。即去除死锁产生的条件，具体方法是先利用 idx_skucode 和 idx_wscode 查询到主键 id ，再拿主键 id 进行 update 操作。这种方式避免了由 update 引入 X 锁，由于最终更新的条件是唯一固定的，所以不存在加锁顺序的问题，避免了死锁的产生。