死锁现场产生

  • 本次发生死锁的是库存扣减接口，该接口的主要逻辑是用户下单后，扣减订单商品在某个仓库的库存量。比如用户一个在vivo官网下单买了1台X50手机和1台X30耳机，那么下单后，首先根据用户收货地址确定发货仓库，然后从该仓库里面分别减去一个X50库存和一个X30库存

    表定义

    ``java CREATE TABLEstore(idint(10) AUTO_INCREMENT COMMENT '主键',sku_codevarchar(45) COMMENT '商品编码',ws_codevarchar(32) COMMENT '仓库编码',store` int(10) COMMENT ‘库存量’,

    PRIMARY KEY (id), KEY idx_skucode (sku_code), KEY idx_wscode (ws_code)

) ENGINE=InnoDB COMMENT=’商品库存表’

  1. <a name="GWmVO"></a>
  2. #### Update Sql
  3. ```java
  4. update store
  5. set store = store-#{store}
  6. where sku_code=#{skuCode} and ws_code = #{wsCode} and (store-#{store}) >= 0

死锁日志

  • 在MySQL 终端执行 show engine innodb status 命令查看最后一次死锁日志，主要关注日志中的 LATEST DETECTED DEADLOCK 部分： ```java

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

<a name="o5it8"></a>
#### 死锁SQL
```java
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

死锁数据.jpg

问题分析

执行计划分析

死锁sql执行计划.jpg

index_merge

  • InnoDB既没有使用idx_skucode索引，也没有使用idx_wscode索引，而是使用了index_merge
  • index_merge是MySQL 5.1后引入的一项索引合并优化技术，它允许对同一个表同时使用多个索引进行查询，并对多个索引的查询结果进行合并(取交集(intersect)、并集(union)等)后返回

    死锁日志翻译

  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)上的锁，大家互不相让，只能僵在那里死锁喽^_^

    死锁现象解释

    死锁.jpg
  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带来的多个索引同时执行的情况
  1. 利用force index(idx_skucode)强制走某个索引，这样InnoDB就会忽略index merge，避免多个索引同时加锁的情况。

force index.jpg

  1. 禁用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’;禁用命令.jpg

  1. 既然Index Merge同时使用了2个独立索引，我们不妨新建一个包含这两个索引所有字段的联合索引，这样InnoDB就只会走这个单独的联合索引，这其实和禁用index merge是一个道理。
    • 新增联合索引：
    • alter table store add index idx_skucode_wscode(sku_code,ws_code);
  2. 最后推荐另外一种绕过index merge限制的方式。即去除死锁产生的条件，具体方法是先利用idx_skucode和idx_wscode查询到主键id，再拿主键id进行update操作。这种方式避免了由update引入X锁，由于最终更新的条件是唯一固定的，所以不存在加锁顺序的问题，避免了死锁的产生。