乐趣区

关于mysql:MySQL-5635-索引优化导致的死锁案例解析

jiezi

一、背景

随着公司业务的倒退，商品库存从商品核心独立进去成为一个独立的零碎，承接主站商品库存校验、订单库存扣减、售后库存开释等业务。在上线之前咱们对于外围接口进行了压测，压测过程中呈现了 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 之前，先看下商品库存表的定义(为不便了解，只保留次要字段)：

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

看一下理论数据：

图 3 -1 库存表数据

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

咱们再看一下 update 的执行打算：

图 3 -2 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，防止多个索引同时加锁的状况。

图 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 个性

从新登录终端后再看下执行打算：

图 4 -3  敞开 Index Merge 后索引状况

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 -4 利用联结索引来防止 Index Merge 优化

4）最初举荐另外一种绕过 index merge 限度的形式。即去除死锁产生的条件，具体方法是先利用 idx_skucode 和 idx_wscode 查问到主键 id，再拿主键 id 进行 update 操作。这种形式防止了由 update 引入 X 锁，因为最终更新的条件是惟一固定的，所以不存在加锁程序的问题，防止了死锁的产生。

五、小结

本文通过一个理论案例形容了因为 Index Merge 优化导致的死锁，详细描述了死锁产生的起因以及解决方案，并顺便介绍了 MySQL 索引构造及加锁机制。通过本文，大家能够把握死锁剖析的根本实践和个别办法，心愿能为大家工作中疾速解决理论呈现的死锁问题提供思路。

作者：vivo 官网商城开发团队

退出移动版