获取“尝试获取锁时发现死锁；尝试重新启动事务”

Question

我的应用程序（java spring-core）有几个线程同时运行并访问数据库，我在某个高峰时间遇到异常

07:43:33,400 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 1213, SQLState: 40001
07:43:33,808 ERROR [org.hibernate.util.JDBCExceptionReporter] Deadlock found when trying to get lock; try restarting transaction
07:43:33,808 ERROR [org.hibernate.event.def.AbstractFlushingEventListener] Could not synchronize database state with session
org.hibernate.exception.LockAcquisitionException: could not insert: [com.xminds.bestfriend.frontend.model.Question]
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:107)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2436)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2856)
    at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
    at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265)
    at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184)
    at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
    at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
    at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216)
    at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:383)
    at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:133)
    at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:656)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:754)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:723)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:147)
    at com.xminds.bestfriend.consumers.Base.onMessage(Base.java:96)
    at org.springframework.jms.listener.adapter.MessageListenerAdapter.onMessage(MessageListenerAdapter.java:339)
    at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:535)
    at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:495)
    at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:467)
    at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325)
    at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:263)
    at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1058)
    at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1050)
    at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:947)
    at java.lang.Thread.run(Thread.java:662)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1065)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2450)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2371)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2355)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
    at org.hibernate.jdbc.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:46)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2416)
    ... 25 more

我的代码看起来

try
{
       this.consumerTransactionTemplate.execute(new TransactionCallbackWithoutResult(){

                    @Override
                    protected void doInTransactionWithoutResult(
                            TransactionStatus status)
                    {
                        process();
                    }

                });

  }
  catch(Exception e){
     logger.error("Exception occured " , e);
      //TODO: Exception handling
  }

原文由 Arun Mohan 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

MySQL 的 InnoDB 引擎支持行级锁定，即使您的代码正在插入或更新单个行（特别是如果正在更新的表上有多个索引），这也可能导致死锁。最好的办法是围绕此设计代码，以便在事务因死锁而失败时重试事务。 此处 提供了有关 MySQL 死锁诊断和可能的解决方法的一些有用信息。

此处 提供了一个有趣的通过 Spring 中的 AOP 进行死锁重试的实现。这样你只需要将注解添加到你想在死锁的情况下重试的方法。

原文由 EmirCalabuch 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

Emir 的回答很好，它描述了您遇到的问题。但是我建议你试试 spring-retry 。

这是一个通过注释实现重试模式的出色框架。

例子：

  @Retryable(maxAttempts = 4, backoff = @Backoff(delay = 500))
 public void doSomethingWithMysql() {
   consumerTransactionTemplate.execute(
             new TransactionCallbackWithoutResult(){
                @Override
                protected void doInTransactionWithoutResult(
                      TransactionStatus status)
                {
                    process();
                }

            });
 }

如果出现异常，它将重试（调用）方法 doSomethingWithMysql( ) 最多 4 次，退避策略为 500 毫秒

原文由 db80 发布，翻译遵循 CC BY-SA 4.0 许可协议