Spring+Hibernate连接MariaDb高并发删除触发死锁异常求助

这个死锁问题是MariaDB的InnoDB引擎在并发事务中锁顺序不一致导致的循环等待，仅在高并发场景下触发，下面是针对性的解决方案：

1. 给失败的事务添加重试逻辑 #

死锁发生时数据库会自动回滚其中一个事务，通过重试可以快速恢复。用Spring Retry实现很方便：

• 先引入依赖：

<dependency>
    <groupId>org.springframework.retry</groupId>
    <artifactId>spring-retry</artifactId>
    <version>1.3.4</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>

• 启动类添加@EnableRetry注解
• 在Service的transform方法上添加重试配置：

@Override
@Retryable(include = CannotAcquireLockException.class, maxAttempts = 3, backoff = @Backoff(delay = 100))
public void transform(.....) {
    // ...
    collectionRepository.deleteOldCollection(collectionId, botRef);
    // ...
}

这样当捕获到锁获取失败的异常时，会自动重试最多3次，每次间隔100毫秒。

2. 优化删除语句的锁范围 #

当前删除语句依赖collectionId和botRef过滤，必须给这两个字段加联合索引，减少数据库扫描的行数，从而降低锁的数量：

@Entity
@Table(indexes = @Index(name = "idx_collection_bot", columnList = "collection_id, bot_ref"))
public class Collection {
    // ... 其他字段
    @Column(name = "collection_id")
    private Long collectionId;
    
    @Column(name = "bot_ref")
    private Integer botRef;
    // ...
}

添加索引后，删除操作会直接通过索引定位目标行，避免全表扫描带来的大量锁竞争。

3. 统一事务的锁访问顺序 #

如果业务中还有其他涉及Collection表的操作，确保所有事务都按相同的顺序访问数据，比如按collectionId升序处理，避免不同事务锁的顺序交叉导致循环等待。同时尽量缩小事务范围，不要在一个事务中执行无关的操作。

4. 开启死锁日志定位根因 #

可以开启MariaDB的死锁日志，查看具体的死锁细节：
在数据库配置文件（my.cnf/my.ini）中添加：

innodb_print_all_deadlocks = 1

重启数据库后，死锁信息会输出到错误日志，通过日志可以看到具体是哪些事务和行导致的死锁，方便更精准地优化。

原问题详情 #

基于Spring+Hibernate的应用连接MariaDB，高并发执行删除操作时触发java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction异常，仅在大量请求同时执行时出现。

详细异常日志

org.springframework.dao.CannotAcquireLockException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.LockAcquisitionException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:267)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)

Caused by: org.hibernate.exception.LockAcquisitionException: could not execute statement
    at org.hibernate.dialect.MySQLDialect$3.convert(MySQLDialect.java:557)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
    at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:80)
    at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:50)
    at org.hibernate.hql.internal.ast.exec.DeleteExecutor.execute(DeleteExecutor.java:177)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:458)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:377)
    at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1438)
    at org.hibernate.query.internal.AbstractProducedQuery.doExecuteUpdate(AbstractProducedQuery.java:1690)
    at org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1672)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$ModifyingExecution.doExecute(JpaQueryExecution.java:238)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:88)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)
    at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
    at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:131)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
    ... 33 common frames omitted
Caused by: java.sql.SQLTransactionRollbackException: (conn=161649) Deadlock found when trying to get lock; try restarting transaction
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:76)
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:153)
    at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:274)
    at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:229)
    at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:149)
    at org.mariadb.jdbc.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:181)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
    ... 57 common frames omitted
Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Deadlock found when trying to get lock; try restarting transaction
    at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:194)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:177)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:321)
    at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:220)
    ... 62 common frames omitted
Caused by: java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1683)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1545)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1508)
    at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:318)
    ... 63 common frames omitted

代码片段

Service类

@Override
public void transform(.....) {

    // ... 其他业务逻辑

    collectionRepository.deleteOldCollection(collectionId, botRef); // 触发异常的行

    // ... 其他业务逻辑
}

Repository接口

@Repository
public interface CollectionRepository extends JpaRepository<Collection, Long> {

    @Transactional
    @Modifying
    @Query("DELETE FROM Collection c WHERE c.collectionId = ?1 AND c.botRef = ?2")
    void deleteOldCollection(Long collectionId, Integer botRef);

}

内容的提问来源于stack exchange，提问作者Swastik