Spring Batch Could not obtain last_insert_id(); nested exception is java.sql.SQLException: Lock wait timeout exceeded;

Question

I am using spring batch 3.0.5.RELEASE on MySQL database. I have a job which reads from multiple tables and process the records and mark their status after completion. The job data is designed in such a way that multiple instances of the same job can run without stepping on each others toes..(They have there own data set to work with)

I have following job repository configuration.

protected JobRepository createJobRepository() throws Exception {
    JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
    factory.setDataSource(dataSource);
    factory.setTransactionManager(transactionManager);
    factory.setTablePrefix(TABLE_PREFIX);
    factory.setIsolationLevelForCreate("ISOLATION_READ_COMMITTED");
    factory.afterPropertiesSet();
    return  factory.getObject();
}

When I run the multiple instances of this job I get following exception after some time. Some instances complete successfully however some instances fail due to this exception.

[12-04-2015 17:08:06,276] [Thread 39] ERROR org.springframework.batch.core.job.AbstractJob: Encountered fatal error executing job
org.springframework.batch.core.JobExecutionException: Flow execution ended unexpectedly
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:140)
    at org.springframework.batch.core.job.AbstractJob.execute(AbstractJob.java:306)
    at org.springframework.batch.core.launch.support.SimpleJobLauncher$1.run(SimpleJobLauncher.java:135)
    at org.springframework.core.task.SimpleAsyncTaskExecutor$ConcurrencyThrottlingRunnable.run(SimpleAsyncTaskExecutor.java:251)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.springframework.batch.core.job.flow.FlowExecutionException: Ended flow=SuspendAccountFlow at state=SuspendAccountFlow.partitionAccountListStep with exception
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:178)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.start(SimpleFlow.java:144)
    at org.springframework.batch.core.job.flow.FlowJob.doExecute(FlowJob.java:134)
    ... 4 more
Caused by: org.springframework.dao.DataAccessResourceFailureException: Could not obtain last_insert_id(); nested exception is java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer.getNextKey(MySQLMaxValueIncrementer.java:118)
    at org.springframework.jdbc.support.incrementer.AbstractDataFieldMaxValueIncrementer.nextLongValue(AbstractDataFieldMaxValueIncrementer.java:128)
    at org.springframework.batch.core.repository.dao.JdbcStepExecutionDao.buildStepExecutionParameters(JdbcStepExecutionDao.java:187)
    at org.springframework.batch.core.repository.dao.JdbcStepExecutionDao.saveStepExecution(JdbcStepExecutionDao.java:118)
    at org.springframework.batch.core.repository.support.SimpleJobRepository.add(SimpleJobRepository.java:170)
    at sun.reflect.GeneratedMethodAccessor81.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy25.add(Unknown Source)
    at sun.reflect.GeneratedMethodAccessor81.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.batch.core.configuration.annotation.SimpleBatchConfiguration$PassthruAdvice.invoke(SimpleBatchConfiguration.java:127)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy25.add(Unknown Source)
    at org.springframework.batch.core.job.SimpleStepHandler.handleStep(SimpleStepHandler.java:144)
    at org.springframework.batch.core.job.flow.JobFlowExecutor.executeStep(JobFlowExecutor.java:64)
    at org.springframework.batch.core.job.flow.support.state.StepState.handle(StepState.java:67)
    at org.springframework.batch.core.job.flow.support.SimpleFlow.resume(SimpleFlow.java:169)
    ... 6 more
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:998)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3847)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3783)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2447)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2594)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2541)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1604)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1535)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
    at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)

I tries following so far.

1. Increasing the lock wait time in DB.
2. Changed the sequence table's type from InnoDB to MyISAM. After changing the type to MyISAM all jobs started failing. However for different exception.
3. Set the transaction isolation level for job repository to ISOLATION_READ_COMMITTED

Nothing from above helped so far. Please share your thoughts on this.

Answer 1

Please check which DB you are using. If you are using MySQL, then in context.xml file, databaseType should be mysql. I'm using postgresql, so I have used postgres. Accepted databaseType formats are as follows

DERBY,DB2,DB2ZOS,HSQL,SQLSERVER,MYSQL,ORACLE,POSTGRES,SYBASE,H2

My JobRepository bean is as follows

<bean id="jobRepository"
    class="org.springframework.batch.core.repository.support.JobRepositoryFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="transactionManager" ref="transactionManager" />
    <property name="databaseType" value="postgres" />
</bean>