项目场景:
最近在进行类似转账的订单业务时,遇见了Mysql死锁问题,报错日志如下:
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 involve defaultParameterMap
### The error occurred while setting parameters
### SQL: update test set version = version + 1, balance = balance+? where id = ? and version = ?
### 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:266)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy88.update(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy94.addBalance(Unknown Source)
......
问题描述:
上述报错是在进行类似转账的业务时出现的,可以总结为转账死锁。test表可以表示用户的钱包账户,balance是余额,version是乐观锁版本号。举个栗子,用户A在test表的id是1,用户B在test表的id是2。
A和B同时互相转账就会引发Mysql死锁。转账操作是在同一个事务里面。
转账操作代码大致如下:
UserWallet walletA = userWalletService.getByUserId(userIdA);// A账户
UserWallet walletB = userWalletService.getByUserId(userIdB);// B账户
BigDecimal amount = new BigDecimal("100");// 转账金额,假设是100
/* 使用乐观锁扣除A账户余额 */
walletA.setHandleBalance(amount);
if(userWalletService.subtractBalance(walletA) <= 0){
throw new RuntimeException("乐观锁扣除A账户金额失败!");
}
/* 使用乐观锁添加B账户余额 */
walletB.setHandleBalance(amount);
if(userWalletService.addBalance(walletB) <= 0){
throw new RuntimeException("乐观锁添加B账户金额失败!");
}
原因分析:
请求1:A转账给B,先从A账户扣钱,执行update test set version = version + 1, balance = balance-100 where id = 1 and version = ?,再给B账户价钱,执行update test set version = version + 1, balance = balance+100 where id = 2 and version = ?
先获取id为1的锁,再获取id为2的锁。
请求2:B转账给A时先获取id为2的锁,再获取id为1的锁。
请求1,要获取id为2的锁时,获取不到,因为id为2的锁被请求2持有,请求2要获取id为1的锁,也获取不到,因为id为1的锁被请求1持有。
两个事务互相等待对方释放锁,形成了死锁。
解决方案:
先运行
SHOW ENGINE INNODB STATUS;
来查看死锁日志。
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-09-02 13:40:25 0x7f520229b700
*** (1) TRANSACTION:
TRANSACTION 1271804263, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 2967294, OS thread handle 139990035568384, query id 213702535 124.70.67.234 aaa updating
update test set
version = version + 1,
balance = balance+100
where id = '1' and version = 17
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37773 page no 871 n bits 176 index id of table `test` trx id 1271804263 lock_mode X locks rec but not gap waiting
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 30; hex 633630313636343136363535346231326138383639363964313061623061; asc c601664166554b12a886969d10ab0a; (total 33 bytes);
1: len 30; hex 633630313636343136363535346231326138383639363964313061623061; asc c601664166554b12a886969d10ab0a; (total 32 bytes);
2: len 1; hex 33; asc 3;;
*** (2) TRANSACTION:
TRANSACTION 1271804264, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 2967293, OS thread handle 139990200334080, query id 213702536 124.70.67.234 aaa updating
update test set
version = version + 1,
balance = balance+100
where id = '2' and version = 103
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 37773 page no 871 n bits 176 index id of table `test` trx id 1271804264 lock_mode X locks rec but not gap
Record lock, heap no 12 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 30; hex 633630313636343136363535346231326138383639363964313061623061; asc c601664166554b12a886969d10ab0a; (total 33 bytes);
1: len 30; hex 633630313636343136363535346231326138383639363964313061623061; asc c601664166554b12a886969d10ab0a; (total 32 bytes);
2: len 1; hex 33; asc 3;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37773 page no 653 n bits 288 index id of table `test` trx id 1271804264 lock_mode X locks rec but not gap waiting
Record lock, heap no 100 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 30; hex 383739363964386464313736346133386235653335663964623064333965; asc 87969d8dd1764a38b5e35f9db0d39e; (total 33 bytes);
1: len 30; hex 383739363964386464313736346133386235653335663964623064333965; asc 87969d8dd1764a38b5e35f9db0d39e; (total 32 bytes);
2: len 1; hex 33; asc 3;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
============================
可以看到事务1在等待176 index id的锁,事务2持有176 index id的锁,等待288 index id的锁。事务1和事务2互相等待,形成死锁。
简单的解决方案:
使用jdk1.5提供的ReentrantLock可重入锁,基础用法在javadoc里面已经写好
class X {
private final ReentrantLock lock = new ReentrantLock();
// ...
public void m() {
lock.lock(); // block until condition holds
try {
// ... method body
} finally {
lock.unlock()
}
}
}}
把业务代码放在try中即可。关于ReentrantLock的讲解,网上的文章很多,就不再赘述。