Spring-batch 3.0.3 Multiple jobs running simultaneously results in Primary Key violation [BATCH-2372]

[spring-projects-issues]

Ayushya Devmurari opened BATCH-2372 and commented

When trying to launch multiple jobs at the same time it results into the Primary key violation exception for the BATCH_JOB_INSTANCE table.

Here is the stack trace :
[ERROR] 2015-04-07 18:50:40.991 [org.springframework.scheduling.quartz.SchedulerFactoryBean#3_Worker-1] BatchJobScheduler executeInternal -
Violation of PRIMARY KEY constraint 'PK__BATCH_JO__4848154A7F60ED59'. Cannot insert duplicate key in object 'dbo.BATCH_JOB_INSTANCE'. The duplicate key value is (0).
org.hibernate.exception.ConstraintViolationException: Violation of PRIMARY KEY constraint 'PK__BATCH_JO__4848154A7F60ED59'. Cannot insert duplicate key in object 'dbo.BATCH_JOB_INSTANCE'. The duplicate key value is (0).
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:128) ~[hibernate-core-4.1.9.Final.jar:4.1.9.Final]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.1.9.Final.jar:4.1.9.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) ~[hibernate-core-4.1.9.Final.jar:4.1.9.Final]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) ~[hibernate-core-4.1.9.Final.jar:4.1.9.Final]
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) ~[hibernate-core-4.1.9.Final.jar:4.1.9.Final]
at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) ~[hibernate-core-4.1.9.Final.jar:4.1.9.Final]
at com.sun.proxy.$Proxy70.executeUpdate(Unknown Source) ~[?:?]
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:824) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:818) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:589) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:818) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:874) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:878) ~[spring-jdbc-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:115) ~[spring-batch-core-3.0.3.RELEASE.jar:3.0.3.RELEASE]
at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:135) ~[spring-batch-core-3.0.3.RELEASE.jar:3.0.3.RELEASE]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0_71]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[?:1.7.0_71]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.7.0_71]
at java.lang.reflect.Method.invoke(Method.java:606) ~[?:1.7.0_71]
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94) ~[spring-tx-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172) ~[spring-batch-core-3.0.3.RELEASE.jar:3.0.3.RELEASE]
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204) ~[spring-aop-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at com.sun.proxy.$Proxy95.createJobExecution(Unknown Source) ~[?:?]
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:125) ~[spring-batch-core-3.0.3.RELEASE.jar:3.0.3.RELEASE]
at com.abc.testme.batchjobs.util.BatchJobUtils.runJob(BatchJobUtils.java:154) ~[BatchJobUtils.class:?]
at com.abc.testme.batchjobs.util.BatchJobUtils.runJobWithCheckForRunningExecutions(BatchJobUtils.java:136) ~[BatchJobUtils.class:?]
at com.abc.testme.batchjobs.util.BatchJobUtils.runJob(BatchJobUtils.java:80) ~[BatchJobUtils.class:?]
at com.abc.testme.batchjobs.scheduler.BatchJobScheduler.executeInternal(BatchJobScheduler.java:52) [BatchJobScheduler.class:?]
at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113) [spring-context-support-3.2.3.RELEASE.jar:3.2.3.RELEASE]
at org.quartz.core.JobRunShell.run(JobRunShell.java:216) [quartz-1.8.5.jar:?]
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:549) [quartz-1.8.5.jar:?]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__BATCH_JO__4848154A7F60ED59'. Cannot insert duplicate key in object 'dbo.BATCH_JOB_INSTANCE'. The duplicate key value is (0).
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) ~[sqljdbc4-4.0.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) ~[sqljdbc4-4.0.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) ~[sqljdbc4-4.0.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) ~[sqljdbc4-4.0.jar:?]
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) ~[sqljdbc4-4.0.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) ~[sqljdbc4-4.0.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) ~[sqljdbc4-4.0.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) ~[sqljdbc4-4.0.jar:?]
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314) ~[sqljdbc4-4.0.jar:?]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.7.0_71]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) ~[?:1.7.0_71]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.7.0_71]
at java.lang.reflect.Method.invoke(Method.java:606) ~[?:1.7.0_71]
at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) ~[hibernate-core-4.1.9.Final.jar:4.1.9.Final]
... 33 more

I have posted my configuration on the link provided : http://stackoverflow.com/questions/29493484/primary-key-violation-in-spring-batchs-batch-job-instance-table-while-running-j?noredirect=1#comment47186282_29493484

---

Affects: 3.0.3

Reference URL: http://stackoverflow.com/questions/29493484/primary-key-violation-in-spring-batchs-batch-job-instance-table-while-running-j?noredirect=1#comment47186282_29493484

[spring-projects-issues]

Ayushya Devmurari commented

Changing this property seems to resolve this issue.
<property name="isolationLevelForCreate" value="ISOLATION_REPEATABLE_READ" />

So I don't think its a bug. Sorry for reporting.

But I would very much like to see it documented somewhere(Unless its already documented and I have missed it.) that if we are running multiple jobs parallel then this property needs to be changed to this.

[spring-projects-issues]

Michael Minella commented

This is discussed in section 4.3.1 of the documentation here: http://docs.spring.io/spring-batch/reference/html/configureJob.html. While we don't discuss this specific error, we do point out that this property does impact startup.

[spring-projects-issues]

Ayushya Devmurari commented

First of all thank you for looking into this issue :) :).
I agree that it's mentioned but what does it mean by REPEATABLE_READ ? Is it different than READ_UNCOMMITTED, a beginner user won't be able to figure this out. Kindly mention this in the documentation that if you want to start multiple job executions at the same time one should use "REPEATABLE_READ".