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

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.