索引合并引起死锁分析

同组其他同学负责的系统线上服务报了一个死锁的异常阿里云后台的死锁信息如下

 

mysql的存储引擎是innodb隔离级别是READ COMMITED(这种隔离级别没有gap锁) crm_tag_relation表结构如下（条件中的target_id和tag_id都是普通索引）

CREATE TABLE `crm_tag_relation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一id',
  `life_cycle_id` bigint(20) NOT NULL COMMENT '用户生命周期id',
  `target_id` bigint(20) NOT NULL COMMENT '目标id',
  `tag_id` bigint(20) NOT NULL COMMENT '目标id',
  `tag_group_id` bigint(20) NOT NULL COMMENT '标签组id',
  `create_type` int(11) NOT NULL DEFAULT '1' COMMENT '创建类型 1：系统  2：人工',
  `state` int(11) NOT NULL DEFAULT '1' COMMENT '是否有效0否1是',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '修改时间',
  `creator_id` bigint(20) NOT NULL COMMENT '创建人id',
  `modifier_id` bigint(20) NOT NULL COMMENT '修改人id',
  PRIMARY KEY (`id`),
  KEY `idx_target_id` (`target_id`) USING BTREE,
  KEY `idx_gmtmodified` (`gmt_modified`) USING BTREE,
  KEY `idx_tag_id` (`tag_id`)
) ENGINE=InnoDB AUTO_INCREMENT=999585 DEFAULT CHARSET=utf8 

 最终在代码里定位到的代码是两个线程同时执行同一个方法 他们执行相同的sql 只是参数不一样

仔细看死锁信息的两条sql

 

sql 1	sql2
UPDATE crm_tag_relation SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.193',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 859985 and tag_id in ( 136 ) )	UPDATE crm_tag_relation SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.216',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 869975 and tag_id in ( 136 ) )

这两条sql的目标记录没有重合的数据，发生死锁了，不是那么容易理解，

desc UPDATE crm_tag_relation SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.193',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 859985 and tag_id in ( 136 ) )

输出如下

其中index merge和这次的事件有重大关联，索引合并是mysql优化的一个方法，官方文档 链接

简单就是查出其中一个索引匹配的数据，再查另一个索引匹配的数据，然后两个数据取交集合并成一组，我们这次的问题就是因为索引合并导致的，还原一下事件过程如下图所示

关于 Clustered and Secondary Indexes参考 链接

关于 innodb锁的类型参考 链接

两个事务 index merge情况下执行流程如下

事务1	事务2
UPDATE crm_tag_relation SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.193',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 859985 and tag_id in ( 136 ) )	UPDATE crm_tag_relation SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.216',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 869975 and tag_id in ( 136 ) )
idx_target_id加锁 （idx_target_id下的聚集索引也要上锁） 加锁顺序 【859985,1】、【859985,5】	idx_target_id加锁 （idx_target_id下的聚集索引也要上锁） 加锁顺序 【869975,3】、【869975,4】
idx_tag_id加锁 （idx_tag_id下的聚集索引也要上锁） 加锁顺序 【136,1】【136,3】【136,4】【136,5】	idx_tag_id尝试加锁
3，4的聚集索引被事务2占有（发生死锁）	idx_tag_id索引被事务1占有（发生死锁）
执行	回滚

索引合并引起的问题mysql bug库里面有记录参考 链接

 

我们换一个角度来分析下面这种情况innodb加锁和释放锁加深对类似事情的理解，下面两个事务，一个sql强制使用idx_target_id索引，另外一个sql强制使用idx_tag_id索引，一种情况其中一个事务会阻塞，另一种情况就不会阻塞

下面列两个事务sql

事务1

start TRANSACTION

UPDATE `sxc_yunke`.crm_tag_relation force index(idx_target_id) SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.216',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 869975 and tag_id in ( 136 ) )

rollback

事务2

start TRANSACTION

UPDATE `sxc_yunke`.crm_tag_relation force index(idx_tag_id) SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.193',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 859985 and tag_id in ( 136 ) )

rollback

会阻塞的情况

事务1执行

start TRANSACTION

UPDATE `sxc_yunke`.crm_tag_relation force index(idx_target_id) SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.216',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 869975 and tag_id in ( 136 ) )

 

事务2执行

start TRANSACTION

UPDATE `sxc_yunke`.crm_tag_relation force index(idx_tag_id) SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.193',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 859985 and tag_id in ( 136 ) )

 

 

我们把事务1rollback掉，事务2阻塞停止，把事务2rollback掉

 

不会阻塞的情况

事务2执行

start TRANSACTION

UPDATE `sxc_yunke`.crm_tag_relation force index(idx_tag_id) SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.193',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 859985 and tag_id in ( 136 ) )

 

事务1执行

start TRANSACTION

UPDATE `sxc_yunke`.crm_tag_relation force index(idx_target_id) SET id = id,state = 0,gmt_modified = '2020-09-03 12:30:19.216',modifier_id = 56 WHERE ( life_cycle_id = 3 and target_id = 869975 and tag_id in ( 136 ) )

 

为什么换一下顺序就不阻塞了，理解了这个就更能理解了为什么在这次事件中两条sql命中的数据不一样却发生了死锁