英文:
Getting database deadlock with @Transactional in spring boot and hibernate
问题
为什么在这段代码中会出现死锁？
我尝试过进行调试，也阅读了很多关于死锁预防的文章，但仍然无法解决。我已经使用了同步机制，根据accountNumber来使代码块在多线程情况下保持线程安全。
我从一个API中获取这个Transaction对象，并且我想根据Transaction对象的内容来对代码进行加锁。Transaction对象包含有关借记/贷记账号号码、金额等信息。
如果两个线程之间存在任何共同的accountNumber，则不应同时执行executeTransaction方法。
在这里，lockedAccount存储了当前被锁定的所有账号，有两个方法用于锁定和解锁accountNumber。
DAO / Repository 层。
@Repository
public class TransactionDAOImpl implements TransactionDAO {
// 实体管理器字段
private EntityManager entityManager;
public TransactionDAOImpl() {}
// 设置构造器注入
@Autowired
public TransactionDAOImpl(EntityManager theEntityManager) {
entityManager = theEntityManager;
}
private static final Set<String> lockedAccounts = new HashSet<>();
private void LockAccount(String AccountNumber) throws InterruptedException {
int count = 0;
synchronized (lockedAccounts) {
while (!lockedAccounts.add(AccountNumber)) {
lockedAccounts.wait();
count++;
}
System.out.println(AccountNumber + " 等待了 " + count + " 次，现在我获得了锁");
}
}
private void unLockAccount(String AccountNumber) {
synchronized (lockedAccounts) {
lockedAccounts.remove(AccountNumber);
lockedAccounts.notifyAll();
System.out.println("解锁 " + AccountNumber);
}
}
@Override
public void executeTransaction(Transaction theTransaction) {
// 获取当前的 Hibernate 会话
Session currentSession = entityManager.unwrap(Session.class);
// 按升序锁定两个账号，以避免死锁
// 字典顺序较小的账号应先被锁定
String firstAccount = theTransaction.getDebitAccountNumber();
String secondAccount = theTransaction.getCreditAccountNumber();
// 检查 firstAccount 是否小于 secondAccount，如果不是，则交换值
if (firstAccount.compareTo(secondAccount) > 0) {
firstAccount = theTransaction.getCreditAccountNumber();
secondAccount = theTransaction.getDebitAccountNumber();
}
try {
LockAccount(firstAccount);
try {
LockAccount(secondAccount);
AccountDetail debitAccount = getAccountDetails(currentSession, theTransaction.getDebitAccountNumber());
AccountDetail creditAccount = getAccountDetails(currentSession, theTransaction.getCreditAccountNumber());
if (debitAccount == null || creditAccount == null) // 检查无效的账号号码
{
theTransaction.setStatus("失败，账号未找到");
} else if (debitAccount.getBalance() < theTransaction.getAmount()) // 检查账号余额不足
{
theTransaction.setStatus("失败，账号余额不足");
} else {
// 更新客户账号余额
debitAccount.setBalance(debitAccount.getBalance() - theTransaction.getAmount());
creditAccount.setBalance(creditAccount.getBalance() + theTransaction.getAmount());
// 保存到数据库
currentSession.saveOrUpdate(debitAccount);
currentSession.saveOrUpdate(creditAccount);
// 更新交易状态
theTransaction.setStatus("成功");
}
} catch (InterruptedException e) {
e.printStackTrace();
} finally {
unLockAccount(secondAccount);
}
} catch (InterruptedException e1) {
e1.printStackTrace();
} finally {
unLockAccount(firstAccount);
}
return;
}
private AccountDetail getAccountDetails(Session currentSession, String accountNumber) {
Query<?> query = currentSession.createQuery("from AccountDetail where accountNumber=:accountNumber");
query.setParameter("accountNumber", accountNumber);
AccountDetail accountDetails = (AccountDetail) query.uniqueResult();
return accountDetails;
}
}
关于更多信息，我的数据库中的accountDetails表有三个列：
id（int，主键）
accountNumber（String，唯一）
amount（double）
这是服务层，我在executeTransaction方法上使用了@Transactional注解。
public class TransactionServiceImpl implements TransactionService {
private TransactionDAO theTransactionDAO;
public TransactionServiceImpl() {}
// 构造器注入
@Autowired
public TransactionServiceImpl(TransactionDAO theTransactionDAO) {
this.theTransactionDAO = theTransactionDAO;
}
@Override
@Transactional
public void executeTransaction(Transaction theTransaction) {
theTransactionDAO.executeTransaction(theTransaction);
}
}
但是我在这段代码中遇到了数据库死锁。以下是我的错误信息。
2020-08-30 19:09:28.235 WARN 6948 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL 错误: 1213, SQL 状态: 40001
2020-08-30 19:09:28.236 ERROR 6948 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : 在尝试获取锁定时发现死锁; 请尝试重新启动事务
2020-08-30 19:09:28.384 ERROR 6948 --- [nio-8081-exec-4] o.a.c.c.C.[.[.[.[dispatcherServlet] : 在路径为 [/bank] 的上下文中为 servlet [dispatcherServlet] 提供的服务（Servlet.service()）抛出了异常 [请求处理失败; 嵌套异常是 org.springframework.dao.CannotAcquireLockException: 无法执行语句; SQL [n/a]; 嵌套异常是 org.hibernate.exception.LockAcquisitionException: 无法执行语句]，根本原因如下：
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: 在尝试获取锁定时发现死锁; 请尝试重新启动事务
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123) ~[mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql
<details>
<summary>英文:</summary>
Why I am getting deadlock in this code?
I tried to debug it and also read many article about deadlock prevention but could not get this. I have used synchronization, to make thread safe a block of code on the basis of `accountNumber`.
I am getting this `Transaction` object from an API and I want to lock my code on the basis of what the `Transaction` object contain. `Transaction` object contains info like debit/credit account number, amount etc.
Two threads should not be executed `executeTransaction` method simultaneously if there is any common `accountNumber` between them.
Here, `lockedAccount` is storing all accounts that are currently locked and two methods for locking and unlocking an `accountNumber`.
DAO / Repository layer.
@Repository
public class TransactionDAOImpl implements TransactionDAO {
// define field for entitymanager
private EntityManager entityManager;
public TransactionDAOImpl() {}
// set up constructor injection
@Autowired
public TransactionDAOImpl(EntityManager theEntityManager) {
entityManager = theEntityManager;
}
private static final Set<String> lockedAccounts = new HashSet<>();
private void LockAccount(String AccountNumber) throws InterruptedException {
int count = 0;
synchronized (lockedAccounts) {
while (!lockedAccounts.add(AccountNumber)) {
lockedAccounts.wait();
count++;
}
System.out.println(AccountNumber + " waited for " + count + " times" + " and now i am getting lock");
}
}
private void unLockAccount(String AccountNumber) {
synchronized (lockedAccounts) {
lockedAccounts.remove(AccountNumber);
lockedAccounts.notifyAll();
System.out.println("unlocking " + AccountNumber);
}
}
@Override
public void executeTransaction(Transaction theTransaction) {
// System.out.println(theTransaction);
// get the current hibernate session
Session currentSession = entityManager.unwrap(Session.class);
// lock both account in a increasing order to avoid deadlock
// lexicographically lesser account number should be lock first
String firstAccount = theTransaction.getDebitAccountNumber();
String secondAccount = theTransaction.getCreditAccountNumber();
// check firstAccount is lesser or greater then second account,if not then swap
// value
if (firstAccount.compareTo(secondAccount) > 0) {
firstAccount = theTransaction.getCreditAccountNumber();
secondAccount = theTransaction.getDebitAccountNumber();
}
try {
LockAccount(firstAccount);
try {
LockAccount(secondAccount);
AccountDetail debitAccount = getAccountDetails(currentSession, theTransaction.getDebitAccountNumber());
AccountDetail creditAccount = getAccountDetails(currentSession,
theTransaction.getCreditAccountNumber());
if (debitAccount == null || creditAccount == null) // check invalid accountNumber
{
theTransaction.setStatus("failed,account not found");
} else if (debitAccount.getBalance() < theTransaction.getAmount()) // check insufficient account balance
{
theTransaction.setStatus("failed,insufficient account balance");
} else {
// update custmer accout balance
debitAccount.setBalance(debitAccount.getBalance() - theTransaction.getAmount());
creditAccount.setBalance(creditAccount.getBalance() + theTransaction.getAmount());
// save to database
currentSession.saveOrUpdate(debitAccount);
currentSession.saveOrUpdate(creditAccount);
// update status of transacion
theTransaction.setStatus("successful");
}
} catch (InterruptedException e) {
e.printStackTrace();
} finally {
unLockAccount(secondAccount);
}
} catch (InterruptedException e1) {
e1.printStackTrace();
} finally {
unLockAccount(firstAccount);
}
return;
}
private AccountDetail getAccountDetails(Session currentSession, String accountNumber) {
Query<?> query = currentSession.createQuery("from AccountDetail where accountNumber=:accountNumber");
query.setParameter("accountNumber", accountNumber);
AccountDetail accountDetails = (AccountDetail) query.uniqueResult();
return accountDetails;
}
}
for more information ,
my **accountDetails** table in database have three columns,
**id(int,primary key)**
**AccountNumber(String,unique)**
**amount(double)**
this is Service layer
where i am using `@Transactional` annotation for `executeTransaction` method.
public class TransactionServiceImpl implements TransactionService {
private TransactionDAO theTransactionDAO;
public TransactionServiceImpl() {}
//constructor injection
@Autowired
public TransactionServiceImpl(TransactionDAO theTransactionDAO)
{
this.theTransactionDAO= theTransactionDAO;
}
@Override
@Transactional
public void executeTransaction(Transaction theTransaction) {
theTransactionDAO.executeTransaction(theTransaction);
}
}
but i am getting database deadlock in this code.
below is my error.
2020-08-30 19:09:28.235 WARN 6948 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1213, SQLState: 40001
2020-08-30 19:09:28.236 ERROR 6948 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : Deadlock found when trying to get lock; try restarting transaction
2020-08-30 19:09:28.384 ERROR 6948 --- [nio-8081-exec-4] o.a.c.c.C.[.[.[.[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [/bank] threw exception [Request processing failed; nested exception is org.springframework.dao.CannotAcquireLockException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute statement] with root cause
com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123) ~[mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.21.jar:8.0.21]
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
</details>
# 答案1
**得分**: 1
假设有两笔账务交易（`debitAccount`，`creditAccount`）：**AT1**（1,2）**AT2**（2,1）。我们有Java锁（**JL**）和数据库锁（**DBL**）。在以下情况下，将发生死锁。
+------+---------------------+---------------------+-----------------------------------------------------+
| 步骤 | AT1 状态 | AT2 状态 | 备注 |
+------+---------------------+---------------------+-----------------------------------------------------+
| 1 | 获取 JL | 等待 JL | |
+------+---------------------+---------------------+-----------------------------------------------------+
| 2 | 释放 JL | 获取 JL | AT1 的 saveOrUpdate 可能不会刷新到数据库， |
| | | | 因此数据库锁可能在此时尚未被获取 |
+------+---------------------+---------------------+-----------------------------------------------------+
| 3 | 刷新 debitAccount | 刷新 debitAccount | AT1 为账户1获取了 DB 锁， |
| | saveOrUpdate | saveOrUpdate | AT2 为账户2获取了 DB 锁 |
+------+---------------------+---------------------+-----------------------------------------------------+
| 4 | AT1 的 DBL 账户1 | AT2 的 DBL 账户2 | |
+------+---------------------+---------------------+-----------------------------------------------------+
| 5 | 刷新 creditAccount | 刷新 creditAccount | AT1 为账户2获取了 DBL， |
| | saveOrUpdate | saveOrUpdate | AT2 为账户1获取了 DBL，发生死锁 |
+------+---------------------+---------------------+-----------------------------------------------------+
还请注意：
1. 数据库锁在更新语句在刷新时获取。
2. 数据库锁在事务提交/回滚时释放。
<details>
<summary>英文:</summary>
Suppose there are two Account Transactions(`debitAccount`,`creditAccount`): **AT1**(1,2) **AT2**(2,1). And we have Java Lock (**JL**) and Database Lock (**DBL**). In following scenario, deadlock will occur.
+------+---------------------+---------------------+-----------------------------------------------------+
| Step | AT1 State | AT2 State | Remark |
+------+---------------------+---------------------+-----------------------------------------------------+
| 1 | get JL | wait JL | |
+------+---------------------+---------------------+-----------------------------------------------------+
| 2 | release JL | get JL | AT1 saveOrUpdate may not flush to database, |
| | | | hence database lock may not be acquired this moment |
+------+---------------------+---------------------+-----------------------------------------------------+
| 3 | flush debitAccount | flush debitAccout | AT1 acquire DB lock for account 1, |
| | saveOrUpdate | saveOrUpdate | AT2 acquire DB lock for account 2 |
+------+---------------------+---------------------+-----------------------------------------------------+
| 4 | AT1 DBL account 1 | AT2 DBL account 2 | |
+------+---------------------+---------------------+-----------------------------------------------------+
| 5 | flush creditAccount | flush creditAccount | AT1 acquire DBL for account 2, |
| | saveOrUpdate | saveOrUpdate | AT2 acquire DBL for account 1, Deadlock |
+------+---------------------+---------------------+-----------------------------------------------------+
Please also note that
1. Database lock is acquired in update statement when the statement is flushed.
2. Database lock is released when transaction commit/rollback.
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论