Mysql死锁及简单解决方案

项目场景：

最近在进行类似转账的订单业务时，遇见了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的讲解，网上的文章很多，就不再赘述。