记录一次排查、修复获取行锁超时失败导致佣金发放失败问题

背景：有一家租户反馈，有部分用户的佣金过了应入账时间，一直没入账。

据负责对接租户的同事的说法，自从她负责对接这个租户就有这个问题了，以前负责的开发一直没根治。每次租户反馈就调一次现有的微服务接口，全部入账一次。

翻了翻这笔佣金入账的日志，看到资产服务有个错误日志：

xxx-treasure catch DataAccessException , hashCode = 1532638683, exception = org.springframework.dao.CannotAcquireLockException:
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
### The error may exist in URL [jar:file:/xxx-treasure.jar!/BOOT-INF/classes!/mapper/TreasureInfoMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE treasure_info         SET amt_total   = amt_total + ?,             amt_vailable = amt_vailable + ?,             updated_at  = NOW()         WHERE user_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
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:262)
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)

导致下游分佣服务调用超时异常：

feign.RetryableException: 10.122.234.85:8080 failed to respond executing POST http://xxx-treasure/treasure/available/add
        at feign.FeignException.errorExecuting(FeignException.java:65) ~[feign-core-9.7.0.jar!/:na]
        at feign.SynchronousMethodHandler.executeAndDecode(SynchronousMethodHandler.java:105) ~[feign-core-9.7.0.jar!/:na]
        at feign.SynchronousMethodHandler.invoke(SynchronousMethodHandler.java:77) ~[feign-core-9.7.0.jar!/:na]
        at feign.ReflectiveFeign$FeignInvocationHandler.invoke(ReflectiveFeign.java:102) ~[feign-core-9.7.0.jar!/:na]

分佣服务的修复方案：

重试调用资产接口入账佣金。资产入账的接口做了幂等，所以可以随意重试。

1.利用feign的重试。

2.以及自己用定时任务，预期分佣1小时后重试分佣。并利用消息队列削峰，避免重试风暴。

在微服务架构下，可能有服务无法正常工作、调用失败等原因导致业务数据不一致。而分佣这种场景下，要求业务的成功率非常高，所以重试机制是必不可少的。

MySQL的锁机制不赘述了。列举一般出现锁获取失败的情况：

1.死锁。

2.长事务。

3.热点行。

4.事务中有远程调用，且远程调用时间长。

列举一下，我知道的查看锁占用SQL、事务等情况的排查方法：

1.阿里云sql洞察，界面就能看到慢sql、长事务。推荐，因为我所在的公司就是用的阿里云的polar db，很多功能非常实用。

2.infomation_schema库查询

• innodb_lock_waits，查出正在等待资源的事务、占用资源的事务以及锁id
• innodb_lock，查出目前数据库的加锁情况
• innodb_trx，查出目前数据库的事务情况

3死锁日志获取

• show engine innodb status；可以看到等待锁的语句，以及时间。非结构性的显示，不直观。

经过排查，是下单、分佣业务分别都调用了转账接口，形成了死锁。

以下是精简过的转账接口SQL：

begin;

UPDATE treasure_info         
SET amt_total   = amt_total - #{转账金额},             
amt_vailable = amt_vailable - #{转账金额},             
updated_at  = NOW()         
WHERE user_id = #{转出方user id};

UPDATE treasure_info         
SET amt_total   = amt_total + #{转账金额},             
amt_vailable = amt_vailable + #{转账金额},             
updated_at  = NOW()         
WHERE user_id = #{转入方user id};

commit;

如果A转账给B，事务隔离级别设置是READ COMMITED以上。从加锁的角度看，先锁A，再锁B，事务提交或回滚才释放锁。当B同时也转账给A时，就会形成死锁。

目前分佣的场景可以简单的理解为C下单，D、E、F可以获得佣金，此时是公司账户转账给D、E、F。

如果此时C、E、F任意一位下单，就可能会形成死锁，导致付款失败或佣金入账失败。

解决方案：

• 配置防御性处理
  • innodb设置死锁检测、回滚其中一个事务innodb_deadlock_detect = true
  • innodb锁等待超时时间，让其自动回滚。innodb_lock_wait_timeout，默认50秒，平时可以设置短一点，比如5秒。排查时可以设长一点
• 顺序加锁

  • 保证以相同的顺序加锁，比如按user id顺序加锁。

  • 伪代码如下：

  • begin;
    
    if (转出方user id < 转入方user id) {
    UPDATE treasure_info         
    SET amt_total   = amt_total - #{转账金额},             
    amt_vailable = amt_vailable - #{转账金额},             
    updated_at  = NOW()         
    WHERE user_id = #{转出方user id};
    
    UPDATE treasure_info         
    SET amt_total   = amt_total + #{转账金额},             
    amt_vailable = amt_vailable + #{转账金额},             
    updated_at  = NOW()         
    WHERE user_id = #{转入方user id};
    } else {
    UPDATE treasure_info         
    SET amt_total   = amt_total - #{转账金额},             
    amt_vailable = amt_vailable - #{转账金额},             
    updated_at  = NOW()         
    WHERE user_id = #{转出方user id};
    
    UPDATE treasure_info         
    SET amt_total   = amt_total + #{转账金额},             
    amt_vailable = amt_vailable + #{转账金额},             
    updated_at  = NOW()         
    WHERE user_id = #{转入方user id};
    }
    
    commit;