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

一、背景

随着公司业务的发展，商品库存从商品中心独立出来成为一个独立的系统，承接主站商品库存校验、订单库存扣减、售后库存释放等业务。在上线之前我们对于核心接口进行了压测，压测过程中出现了 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不同)。和上面唯一索引加锁类似，不同的是会给所有符合条件的索引项加锁。示意如下：

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

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

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

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

三、死锁现象及排查

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

本次发生死锁的是库存扣减接口，该接口的主要逻辑是用户下单后，扣减订单商品在某个仓库的库存量。比如用户一个在vivo官网下单买了1台X50手机和1台X30耳机，那么下单后，首先根据用户收货地址确定发货仓库，然后从该仓库里面分别减去一个X50库存和一个X30库存。分析死锁sql之前，先看下商品库存表的定义(为方便理解，只保留主要字段)：

1 2 3 4 5 6 7 8 9 10 11

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语句：

1 2 3

update store set