案例一：行锁和间隙锁冲突
数据库场景如下：MySQL 5.7 事务隔离级别为 RR
通过执行 show engine innodb status可以查看到最近一次死锁的日志。
案例二：并发 insert 唯一键冲突
日志分析如下:
- 事务 T2 insert into t7(id,a) values (26,10) 语句 insert 成功，持有 a=10 的 排他行锁( Xlocks rec but no gap )
- 事务 T1 insert into t7(id,a) values (30,10), 因为T2的第一条 insert 已经插入 a=10 的记录,事务 T1 insert a=10 则发生唯一键冲突,需要申请对冲突的唯一索引加上S Next-key Lock( 即 lock mode S waiting ) 这是一个间隙锁会申请锁住(,10],(10,20]之间的 gap 区域。
事务 T2 insert into t7(id,a) values (40，9)该语句插入的 a=9 的值在事务 T1 申请的 gap 锁4-10之间， 故需事务 T2 的第二条 insert 语句要等待事务 T1 的 S-Next-key Lock 锁释放,在日志中显示 lock_mode X locks gap before rec insert intention waiting 。
案例三：先 update 再 insert 的并发死锁问题
死锁分析:
可以看到两个事务 update 不存在的记录，先后获得间隙锁( gap 锁)，gap 锁之间是兼容的所以在update环节不会阻塞。两者都持有 gap 锁，然后去竞争插入意向锁。当存在其他会话持有 gap 锁的时候，当前会话申请不了插入意向锁，导致死锁。案例四：MySQL Index Merger 导致的死锁案例解析
MySQL 加锁实际上是给索引加锁，而非给数据加锁。
MySQL 如何给索引加锁呢？根据主键进行更新
update t set name=’xxx’ where id=29；只需要将主键上id=29的记录加上X锁即可(X锁称为互斥锁，加锁后本事务可以读和写，其他事务读和写会被阻塞)。
根据唯一索引进行更新
update t set name=’xxx’ where name=’ddd’;这里假设name是唯一的。InnoDB现在name索引上找到name=’ddd’的索引项(id=29)并加上加上X锁，然后根据id=29再到主键索引上找到对应的叶子节点并加上X锁。
一共两把锁，一把加在唯一索引上，一把加在主键索引上。说明：加锁是一步步加的，不会同时给唯一索引和主键索引加锁。这种分步加锁的机制实际上也是导致死锁的诱因之一根据非唯一索引进行更新
update t set name=’xxx’ where name=’ddd’;这里假设name不唯一，即根据name可以查到多条记录(id不同)。和上面唯一索引加锁类似，不同的是会给所有符合条件的索引项加锁。
InnoDB对于每个符合条件的记录是分步加锁的，即先加二级索引再加主键索引；其次是按记录逐条加锁的，即加完一条记录后，再加另外一条记录，直到所有符合条件的记录都加完锁。那么锁什么时候释放呢？答案是事务结束时会释放所有的锁。死锁分析
发生死锁的是库存扣减接口，该接口的主要逻辑是用户下单后，扣减订单商品在某个仓库的库存量。
``sql CREATE TABLEstore(idint(10) AUTO_INCREMENT COMMENT '主键',sku_codevarchar(45) COMMENT '商品编码',ws_codevarchar(32) COMMENT '仓库编码',store` int(10) COMMENT ‘库存量’,PRIMARY KEY (
id), KEYidx_skucode(sku_code), KEYidx_wscode(ws_code)
) ENGINE=InnoDB COMMENT=’商品库存表’
这里分别给sku_code和ws_code两个字段单独定义了索引：**idx_skucode, idx_wscode**。这样做的原因主要是业务上有根据单个字段查询的要求。库存扣减update语句：```sqlupdate storeset 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)上的锁
死锁的本质原因还是由加锁顺序不同所导致，本例中是由于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是一个道理。
4）最后推荐另外一种绕过index merge限制的方式。即去除死锁产生的条件，具体方法是先利用idx_skucode和idx_wscode查询到主键id，再拿主键id进行update操作。这种方式避免了由update引入X锁，由于最终更新的条件是唯一固定的，所以不存在加锁顺序的问题，避免了死锁的产生。
若有收获，就点个赞吧
0 人点赞
