Getting "Deadlock found when trying to get lock; try restarting transaction"

Question

My Application(java spring-core) has several threads running concurrently and accessing db, I am getting exception in some peaktime

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

My code looks

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

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

                });

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

Answer 1

Here is an example with plain Spring and no extra frameworks.

    TransactionTemplate transactionTemplate = new TransactionTemplate(transactionManager); // autowired
    transactionTemplate.setIsolationLevel(TransactionDefinition.ISOLATION_SERIALIZABLE); // this increases deadlocks, but prevents multiple simultaneous similar requests from inserting multiple rows
    Object o = transactionTemplate.execute(txStatus -> {
            for (int i=0; i<3; i++) {
                try {
                    return findExistingOrCreate(...);
                } catch (DeadlockLoserDataAccessException e) {
                    Logger.info(TAG, "create()", "Deadlock exception when trying to find or create. Retrying "+(2-i)+" more times...");
                    try { Thread.sleep(2^i*1000); } catch (InterruptedException e2) {}
                }
            }
            return null;
    });
    if (o == null) throw new ApiException(HttpStatus.SERVICE_UNAVAILABLE, "Possible deadlock or busy database, please try again later.");

Using serializable transaction isolation level is specific to my situation because it converts SELECT to SELECT ... IN SHARE MODE / SELECT ... FOR UPDATE and locks those rows. The findExistingOrCreate() is doing a lot of complicated searching for existing rows and auto-generating names and checking for bad words, etc. When many of the same request came in at the same time, it would create multiple rows. With the serializable transaction isolation level, it is now idempotent; it now locks the rows, creates a single row, and all subsequent requests return the new existing row.

Answer 2

This can happen on none-concurrent applications with one thread inserting records consecutively, too. In case a table has a unique constraint MySQL "builds" that constraint after the commit. That locks the table and might disturb the next insert leading to the above mentioned deadlock. Although I only noticed that error on Windows.

Like on all other answers, repeating the insert solved the issue.

With other databases - PostgreSQL, Oracle or H2 - it works without this workaround.

Answer 3

MySQL's InnoDB engine sports row-level locking, which can lead to deadlocks even when your code is inserting or updating a single row (specially if there are several indexes on the table being updated). Your best bet is to design the code around this in order to retry a transaction if it fails due to a deadlock. Some useful info about MySQL deadlock diagnose and possible workarounds is available here.

An interesting implementation of deadlock retry via AOP in Spring is available here. This way you just need to add the annotation to the method you want to retry in case of deadlock.

Answer 4

If you are using JPA/Hibernate then simple just follow below steps to avoid dead lock. Once you have acquired the lock, don't made any call on db with same id anywhere in the transaction (I mean to say you should not get entity again on sameid), on locking object you modify and save no issues.

service level:-

employee=empDao.getForUpdate(id);

Dao level:-

public employee getForUpdate(String id)
return mySqlRepository.getForUpdate(id)

Repository(interface):-

@Lock(LockModeType.PESSIMITSIC_WRITE)
@Query("select e from employee e where id=?1")
public employee getForUpdate(String id)

Answer 5

Emir's answer is great and it describes the problem that you are getting. However I suggest you to try spring-retry.

It's a brilliant framework that implements the retry pattern via annotation(s).

Example:

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

            });
 } 

In case of exception, it will retry (call) up to 4 times the method doSomethingWithMysql() with a backoff policy of 500ms

Answer 6

When you face this kind of error "deadlock detected". You should inspect your queries execution and verify if two or more concurrent transactions can cause a deadlock.

These transactions should acquire database locks in the same order in order to avoid deadlock.