最近开发测试环境频繁发生死锁现象,测试小妹给我提了不少次bug,经过反复分析,从代码中看出了一些端倪。
报错日志:
### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction\n### The error may exist in com/zdb/dao/mapper/v1/AccountUserV1Mapper.java \u0028best guess\u0029\n### The error may involve com.zdb.dao.mapper.v1.AccountUserV1Mapper.update-Inline\n### The error occurred while setting parameters\n### SQL: UPDATE account_user_v1 SET update_time=? WHERE \u0028user_id = ?\u0029\n### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction\n; Lock wait timeout exceeded; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
代码:
/**
* 更新用户余额
* @param consumeType 消费类型:in-入账(充值) out-出账(消费)
* @param userId 用户id
* @param amount 金额
* @param remark 备注
* @param label 消费类型标识
* @param orderId 订单id
* @return
*/
@Transactional(rollbackFor = Exception.class)
@Override
public void updateBalance(String consumeType, String userId, BigDecimal amount, String remark, String label,String orderId) throws ResultInfo {
if (amount.compareTo(BigDecimal.ZERO)<=0){
throw new ResultInfo(ResultUtils.error("金额必须大于0","balance <= 0 !"));
}
BigDecimal calculateAmount = amount;
if (ConsumeType.out.equals(consumeType)){
//出账,改为负数
calculateAmount = calculateAmount.multiply(new BigDecimal("-1")).setScale(2,BigDecimal.ROUND_DOWN);
}
boolean update = lambdaUpdate()
.eq(AccountUserV1::getUserId, userId)
.setSql("balance = balance +" + calculateAmount)
.update();
if (!update){
//更新失败,账号不存在,先创建账号再重新更新
getOrCreate(userId);
lambdaUpdate()
.eq(AccountUserV1::getUserId, userId)
.setSql("balance = balance +" + calculateAmount)
.update();
}
AccountUserV1 accountUser = getOrCreate(userId);
if (accountUser.getBalance().compareTo(BigDecimal.ZERO)==-1){
throw new ResultInfo(ResultUtils.error("余额不足","balance is not enough !"));
}
//保存消费记录
saveAccountConsumeRecord(consumeType,userId,amount,remark,label,orderId);
//更新多账户记录
accountUserMultiV1Service.updateAccountUserMulti(consumeType,userId,amount,label);
//保存神策埋点: 上报当前余额
shenceService.saveProfileSet(userId,"star_balance",accountUser.getBalance().doubleValue());
}
报错一直显示锁超时未被释放,所以导致其他事务进不来。
shenceService.saveProfileSet() 这个神策埋点方法是第三方服务,会受到网络的原因导致事务提交的时间过久。
解决思路:细化事务粒度,减少事务方法所占用的时间(减少锁占用的时间)。
解决方案:1,将耗时的操作,比如调用第三方接口等操作改成异步操作的。2,优化查询,给耗时的sql字段增加索引。