生产环境MySQL select for update 死锁问题

生产环境出现MySQL死锁异常，MySQL版本5.6，隔离级别 RC。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/18095.html

[CommandConsumer-pool-thread-1] Process error : 
org.springframework.dao.DeadlockLoserDataAccessException: 
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in class path resource [mybatis/mapper/sequence.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT current_seq FROM sequence WHERE type = ? AND `date` = ? FOR UPDATE
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
复制代码

代码分析

根据日志记录，导致死锁的关键代码如下文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/18095.html

    /**
     * 根据传入参数，生成一个序列号。
     *
     * @param type 序列号类型
     * @param date 时间
     * @return 一个新的序列号，第一次调用返回1，后续根据调用次数递增。
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.READ_COMMITTED)
    public int getSequence(String type, LocalDate date) {

        // select * from sequence where type = #{type} and date = #{date} for update
        Sequence seq = mapper.selectForUpdate(type, date);

        // seq 还未初始化，select for update 就没锁住
        if (seq == null) {
            // insert ignore into sequence(type, date, current_seq) values(#{type}, #{date}, #{currentSeq})
            if (mapper.insertIgnore(type, date, 1)) {
                return 1;
            }
            // insert ignore 竞争失败，重试
            return getSequence(type, date);
        }

        // update sequence set current_seq = current_seq + 1 where id = #{id}
        mapper.forwardSeq(seq.getId(), 1);

        return seq.getCurrentSeq() + 1;
    }

   CREATE TABLE `sequence` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
      `type` varchar(32) NOT NULL COMMENT '类型',
      `date` date NOT NULL COMMENT '时间',
      `current_seq` int(11) NOT NULL COMMENT '当前最大序号',
      PRIMARY KEY (`id`),
      UNIQUE KEY `uk_seq` (`date`,`type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='序列号'
复制代码

功能简述 这段代码主要实现了一个序列号的获取功能，这种功能常用于生成单据号。 举个栗子：我们需要给每个付款单生成一个付款单号，格式为：A-20200101，表示A公司在20200101这一天的付款单。 但是A公司每天不止一个付款单，为了保证付款单号的唯一性，我们还需要加一个自增的序列号。例如：A-20200101-1，表示A在2020-01-01这天的第一个付款单，以此类推，第二个、第三个付款单号即 A-20200101-2，A-20200101-3...文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/18095.html

代码实现 为了保证在并发环境下，序列号不会重复，代码里先通过 select 唯一索引 for update 锁住某一行数据，然后更新该行数据的current_seq = current_seq + 1，返回current_seq。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/18095.html

但有一个边界条件需要特殊处理，那就是第一次调用该函数时，数据还不存在， select 唯一索引 for update 返回 null ，需要 insert 一个序列号为1的初始数据，为了防止 for update 返回 null 没锁住导致多次 insert ，代码里用了 insert ignore，当 insert ignore 失败时重新调用（递归） getSequence 获取下一个序列号。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/18095.html

看完代码，并没有发现明显异常，我们尝试在本地复现一下死锁。文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/18095.html

本地复现死锁：

手动复现：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/18095.html

• 准备条件
  • MySQL 5.6
  • 事务隔离级别 RC
  • 准备两个数据库连接 A、B
• 通过观察SQL日志，并经过多次实验，发现以下两种操作可以复现死锁
• 操作步骤1
  • A begin; insert (ignore) xxx; 执行失败，因为xxx已存在。
  • B begin; select xxx for update; 阻塞，因为A insert 已持有锁
  • A select xxx for update; 成功
  • B 阻塞结束，提示死锁
• 操作步骤2
  • A begin; select xxx for update; 成功执行，持有排他锁
  • B begin; select xxx for update; 阻塞，等待A释放排他锁
  • A insert (ignore) xxx; 成功执行
  • B 阻塞结束，提示死锁
• 触发死锁操作的共性
  • 都是某数据已存在，某事务内通过 insert 拿到锁再去操作 select for update，或通过 select for update 拿到锁再去操作 insert，就会造成其他 for update 等待锁的事务提示死锁。
• 死锁原理
  • 尚不明确（路过的朋友有知道的还请赐教）

单元测试复现：文章源自菜鸟学院-https://www.cainiaoxueyuan.com/sjk/18095.html

    @Autowired
    private ISequenceService sequenceService;

    @Test
    public void test() throws InterruptedException {
        ExecutorService executorService = Executors.newFixedThreadPool(10);

        List<Runnable> runnableList = Lists.newLinkedList();

        for (int i = 0; i < 100; i++) {
            runnableList.add(() -> sequenceService.getSequence("TX", LocalDate.now()));
        }

        runnableList.forEach(executorService::execute);

        executorService.shutdown();
        executorService.awaitTermination(Long.MAX_VALUE, TimeUnit.NANOSECONDS);
    }
复制代码

解决方案

• 通过本地手动复现死锁，我们发现当在一个事务中 insert ignore 失败后 select for update ，才会出现死锁，那么避免两个操作在同一个事务出现即可。
• 更改后代码

    /**
     * 根据传入参数，生成一个序列号。
     *
     * @param type 序列号类型
     * @param date 时间
     * @return 一个新的序列号，第一次调用返回1，后续根据调用次数递增。
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.READ_COMMITTED)
    public int getSequence(String type, LocalDate date) {

        // select * from sequence where type = #{type} and date = #{date} for update
        Sequence seq = mapper.selectForUpdate(type, date);

        // seq 还未初始化，select for update 就没锁住
        if (seq == null) {
            // insert ignore into sequence(type, date, current_seq) values(#{type}, #{date}, #{currentSeq})
            if (mapper.insertIgnore(type, date, 1)) {
                return 1;
            }
            // insert ignore 竞争失败，在一个新事务中重试，从而避免死锁
            return applicationContext.getBean(ISequenceService.class).getSequence(type, date);
        }

        // update sequence set current_seq = current_seq + 1 where id = #{id}
        mapper.forwardSeq(seq.getId(), 1);

        return seq.getCurrentSeq() + 1;
    }
复制代码

• 经过单元测试验证，成功解决死锁问题。

总结

• 在带多个锁的方法中使用递归时，要特别注意，容易造成加多个锁顺序不一致的问题，从而引发死锁（跟本例无关，纯属感想）。
• 在一个事务中 select for update 锁住某行数据后，再 insert (ignore) 这行数据，出现死锁，这个可以理解，因为一般不会有逻辑 select 查出来数据了，还 insert 。但是我们无意中会写出 insert ignore 失败后 select for update 这种代码，而它跟 select for update 后 insert 在加锁的原理上是基本一致的，会造成死锁，所以日常写代码要注意这一点。