0 Replies Latest reply: Oct 20, 2011 1:43 PM by Michael Bauer RSS

java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction

Michael Bauer Newbie

I have been fighting this problem for sometime, and have read countless forum postings and the like, but with no answer.

 

Using Hibernate 3, I have a VO that I create then later update.  I am using Spring (2.5.6 if that matters) for the DAO code.  Basically, nothing fancy at all:

public WorkflowVO save(WorkflowVO vo) {
 this.getHibernateTemplate().save(vo);
 return vo;
}
public WorkflowVO update(WorkflowVO vo) {
 this.getHibernateTemplate().update(vo);
 return vo;
}

 

The code that calls this just fills in some info then calls save.  Then later on it fills in some more info and calls update.  At one point we did have transactions in our application, but we took those out due to other issues, so there is no transactions happening, at least from my code.

 

However, randomly we get the following nasty-gram in the logs:

2011-10-20 10:38:18,351 ERROR [com.xxx.yyy.zzz.core.ProcessorController] Could not update the Workflow 1319120571491.  Exception: org.springframework.dao.CannotAcquireLockException: Hibernate operation: Could not execute JDBC batch update; SQL [update SMT_zzz_WORKFLOWS set STATUS=?, INPUT=?, USER_ID=?, NAME=?, COMPLETION_DATE=?, BATCH_ID=?, EXECUTION_DATE=?, SCHEDULED_DATE=? where WORKFLOW_ID=?]; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
org.springframework.dao.CannotAcquireLockException: Hibernate operation: Could not execute JDBC batch update; SQL [update SMT_zzz_WORKFLOWS set STATUS=?, INPUT=?, USER_ID=?, NAME=?, COMPLETION_DATE=?, BATCH_ID=?, EXECUTION_DATE=?, SCHEDULED_DATE=? where WORKFLOW_ID=?]; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
 at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:260)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
 at org.springframework.orm.hibernate3.HibernateAccessor.convertJdbcAccessException(HibernateAccessor.java:424)
 at org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:410)
 at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:424)
 at org.springframework.orm.hibernate3.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:374)
 at org.springframework.orm.hibernate3.HibernateTemplate.update(HibernateTemplate.java:716)
 at org.springframework.orm.hibernate3.HibernateTemplate.update(HibernateTemplate.java:712)
 at com.xxx.esm.dataaccess.zzz_diag.dao.WorkflowDao.update(WorkflowDao.java:39)
 at com.xxx.yyy.zzz.core.ProcessorController$WorkflowProcessorThread.fireWorkflowScheduledEvent(ProcessorController.java:1481)
 at com.xxx.yyy.zzz.core.ProcessorController$WorkflowProcessorThread.fireWorkflowScheduledEvent(ProcessorController.java:1446)
 at com.xxx.yyy.zzz.core.ProcessorController$2.run(ProcessorController.java:436)
 at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
 at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
 at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedStatement.java:657)
 at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:142)
 at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
 at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
 at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
 at org.springframework.orm.hibernate3.HibernateAccessor.flushIfNecessary(HibernateAccessor.java:390)
 at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:420)
 ... 10 more

 

My limited understand of this is that there is a Transaction/Lock that has the table I need to update held, and it didn't release it in time.  But, like I said, we are not using Transactions at all, and the update shouldn't take that long.

 

I am not even sure where to look for this.  Is this a code-problem, a config problem...I am not sure.  I just know that we randomly get these, and when we do, the table is not updated.

 

Any help would be appreciated.