2020年9月2日 13:36:37go评论70阅读模式

英文:

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&lt;String&gt; lockedAccounts = new HashSet&lt;&gt;();
private void LockAccount(String AccountNumber) throws InterruptedException {
int count = 0;
synchronized (lockedAccounts) {
while (!lockedAccounts.add(AccountNumber)) {
lockedAccounts.wait();
count++;
}
System.out.println(AccountNumber + &quot; waited for &quot; + count + &quot; times&quot; + &quot; and now i am getting lock&quot;);
}
}
private void unLockAccount(String AccountNumber) {
synchronized (lockedAccounts) {
lockedAccounts.remove(AccountNumber);
lockedAccounts.notifyAll();
System.out.println(&quot;unlocking &quot; + 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) &gt; 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(&quot;failed,account not found&quot;);
} else if (debitAccount.getBalance() &lt; theTransaction.getAmount()) // check insufficient account balance
{
theTransaction.setStatus(&quot;failed,insufficient account balance&quot;);
} 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(&quot;successful&quot;);
}
} 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&lt;?&gt; query = currentSession.createQuery(&quot;from AccountDetail where accountNumber=:accountNumber&quot;);
query.setParameter(&quot;accountNumber&quot;, 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**）。在以下情况下，将发生死锁。


还请注意：
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.

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>

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

在Spring Boot和Hibernate中使用@Transactional出现数据库死锁问题。

问题

我如何知道通知中点击了哪个按钮？

如何阻止Maven将项目构建的JAR安装到本地仓库

如何使用org.springframework.jdbc.object来实现可重用的DDL语句？

在使用数组列表和添加值方面遇到了一些问题。Java

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论