index merge导致死锁问题

问题现象

前置条件： m_task 表 m_id和t_id是普通索引

这么一条简单的语句居然可能发生死锁 update m_task set `state`=1 where m_id=1 and t_id=1

org.springframework.dao.DeadlockLoserDataAccessException:
### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in file [F:\idea-prj\springboot-mybaits-demo\target\classes\mapper\MTaskInfoMapper.xml]
### The error may involve com.zsk.mybaits.demo.dao.MTaskInfoMapper.updateByPrimaryKeySelective-Inline
### The error occurred while setting parameters
### SQL: update m_task SET `state` = ? where m_id = ? and t_id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:271)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:88)

问题重现

建表Sql

CREATE TABLE `m_task` (
`id` bigint(11) NOT NULL AUTO_INCREMENT,
`state` int(4) NULL DEFAULT NULL,
`m_id` bigint(11) NULL DEFAULT NULL,
`t_id` bigint(11) NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `m_id`(`m_id`) USING BTREE,
INDEX `t_id`(`t_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 10008 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

插入10000条数据 每100条m_id对应相同的t_id

@GetMapping("/insertTestData")
public String insert() {
    int index = 1;
    for (int i = 1; i <= 10000; i++) {
        MTaskInfo info = new MTaskInfo();
        info.setMId((long) (i % 100));
        info.setTId((long) (index));
        if (i % 100 == 0) {
            index++;
        }
        taskDao.insert(info);
    }
    return "insert 10000 row data success";
}

测试，多点几次接口

private static ExecutorService pool = Executors.newCachedThreadPool();

@GetMapping("/testDeadLock")
public String getTask() {
    for (int i = 1; i <= 100; i++) {
        final int ii = i;
        pool.execute(() -> {
            int state = (int) (Math.random() * 10);
            System.out.println("update " + ii + "state:" + state);
            MTaskInfo info = new MTaskInfo();
            info.setMId((long) ii);
            info.setTId((long) ii);
            info.setState(state);
            taskDao.updateByPrimaryKeySelective(info);
        });
    }
    return "test success";
}

原因

该问题在多线程并发下，数据够大，概率性出现，一般在开发过程中不容易发现，原因是mysql index merge导致

查看这个语句的执行计划，索引走了index_merge(需要注意的是，如果没有足够满足条件的数据，执行计划的type是不会显示index_merge的)

 

index merge 技术如果简单的说就是 对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。

update会加行级排他锁，例如如下的语句

线程A update m_task set `state`=1 where m_id=1 and t_id=1  

线程B update m_task set `state`=1 where m_id=2 and t_id=2

m_id 和 t_id是多对多的关系，实际数据是互相包含的关系

线程A  锁定m_id对应的行，接着再锁定  t_id对应的行

线程B  锁定  t_id对应的行，接着再锁定m_id对应的行

此时出现线程A需要线程B锁定的行，而线程B又需要线程A锁定的行，完美达到死锁条件（两个线程同时需要对方的锁）

a->b | b->a 加锁顺序对称相反，死锁产生了

mysql bug report mysql bug report： https://bugs.mysql.com/bug.php?id=77209

关于index merge技术 参考：https://www.cnblogs.com/digdeep/p/4975977.html

官网关于index merge介绍：https://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html

参考博客：https://blog.csdn.net/shixiaojula/article/details/114004360

预防措施

方案1

将一条 SQL 拆分成条 SQL，在逻辑层做交集操作，阻止 MySQL 优化行为，比如这里可以先根据 m_id 查询到相应主键，再根据 t_id 查询相应主键，然后过滤处理。

方案2

建立联合索引，比如这里可以将 m_id 和 t_id 建立一个联合索引，MySQL 就不会走 Index Merge 了

方案3

强制走单个索引，在表名后添加 for index(m_id) 可以指定该语句仅走 m_id 索引

方案四

关闭 Index Merge 优化：

永久关闭：set [GLOBAL|SESSION] optimizer_switch='index_merge=off';
临时关闭：update /*+ no_index_merge(m_task) */ m_task set state=1  where m_id =1 and t_id=1