业务上第一次遇到MySQL更新锁表超时(Lock wait timeout exceeded try restarting transaction)

前言：

最近，我在公司开发某个业务功能，需要使用钉钉审批流，在最后一个结点审批通过后，使用MQ监听回调信息，根据回调信息去更新申请单状态、触发系统还款、保存相关审批记录等。如果在完美情况下，这一套流程没有任何问题，但是因为历史数据等其他原因，触发系统还款可能会失败。此时，审批流程已经正常结束，不可能再发起一遍审批流程。为了解决这样的问题，我将申请单状态的“已还款”拆成“审批通过”和“已还款”。这两个状态在正常情况下，是同时完成的，但是如果系统还款有问题，只会更新项目状态和保存相关审批意见。从技术上来说我这里用到了嵌套型事务，处理最后一个节点的方法自身开启一个事务，该方法会调用系统还款的方法，系统还款的方法也会重新开启一个事务，这里系统还款方法的事务传播规则需要使用Propagation.REQUIRES_NEW（关于Spring事务，可以我看的《面试问你Spring事务怎么办，不会？点进来看一看》）。在此背景下，我遇到了数据库更新锁表超时的问题。

问题复现：

由于该系统的是内部核心系统，不能公布源码，我通过一个模拟例子，说明代码逻辑和演示出现数据库更新锁表超时的问题，模拟例子如下：

   /**
     * 模拟最后一个节点，需要的操作
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void lastNode() {
        
        //模拟更新申请单状态
        dictTwoDao.updateStatusById(21L,"已还款");
        try {
            //系统还款
            txService2.systemRepayment();
            //保存成功审批记录
            System.out.println("模拟成功审批记录");

        } catch (Exception e) {
            String errorMsg=e.getMessage();
            //保存失败审批记录
            System.out.println("模拟失败审批记录"+errorMsg);
        }

    }

   /**
     * 模拟系统还款操作
     */
    @Override
    @Transactional(rollbackFor = Exception.class,propagation = Propagation.REQUIRES_NEW)
    public void systemRepayment() {

        //系统还款
        System.out.println("模拟系统还款");

        //模拟系统还款异常
        //throw  new RuntimeException("系统还款异常");

        //更新申请单状态
        dictTwoDao.updateStatusById(21L,"已还款");

        //更新其他状态
        System.out.println("模拟更新其他状态");

    }

运行结果： 

### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may involve com.hanxiaozhang.common.DictTwoDao.updateStatusById-Inline
### The error occurred while setting parameters
### SQL: update sys_dict set type=? where id=?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

解决问题： 

我第一个想到是解决方法是，修改spring事务的隔离级别，让内层事务能获取到更新状态的变化，最后我运行了一下，没有解决我这个问题，我仔细想了一下，事务的隔离级别不能解决此问题，降低事务的隔离级别只能让其他事务的操作可以读取到还未提交的数据变更记录，不改变它的锁表超时问题。

然后，我开始对代码进行了分析，我发现，造成锁表超时的原因是：外层事务中进行了对表的更新操作锁住了表，外层事务还没有提交的时候，调用了内层事务，内层事务也要对表进行更新操作，此时，外层事务锁住该表，内层事务无法对事务进行提交，MySQL抛出了此异常。通过分析，我觉得改变我代码的操作顺序，把外层对表的更新操作放在调用内层事务方法的下面，结果就不会发生锁表超时，代码如下：

   /**
     * 模拟最后一个节点，需要的操作
     */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void lastNode() {
        
        try {
            //系统还款
            txService2.systemRepayment();
            //保存成功审批记录
            System.out.println("模拟成功审批记录");

        } catch (Exception e) {
            //模拟更新申请单状态
            dictTwoDao.updateStatusById(21L,"已还款");
            String errorMsg=e.getMessage();
            //保存失败审批记录
            System.out.println("模拟失败审批记录"+errorMsg);
        }

    }

后记： 

解决了锁表超时的问题，我再看代码，这种写法其实根本就不使用嵌套型事务，直接让所有的方法加入到当前事务，如果有异常，就捕获，然后处理异常消息就可以。

2020-05-05

学艺不精，出来害人喽，我这里使用嵌套事务的想法没有问题，但是内层事务的传播规则不应该使用REQUIRES_NEW，使用REQUIRES_NEW就会出现以上的问题，内层事务的传播规则应该使用NESTED。原因是NESTED与REQUIRES_NEW的嵌套事务本质区别是：NESTED还在一个事务中，它与主事务一块提交；而REQUIRES_NEW是新开启一个事务，独自提交。所以在一个事务中的NESTED可以修改同一条数据，不会出现上述的问题。