Spring Batch admin gui with Oracle 11G

[deyindra]

I am running Spring Batch admin application with Oracle 11G repository and JDK 1.5. We are using ojdbc14.jar for Oracle Database connectivity and using JNDI databa source configured in the TC server.

While lunching any job through Spring Batch Admin GUI, I am getting following error. I am wondering, if any one encounter this similar issue before.

org.springframework.web.util.NestedServletExceptio n: Request processing failed; nested exception is org.springframework.dao.CannotSerializeTransaction Exception: PreparedStatementCallback; SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ORA-08177: can't serialize access for this transaction
; nested exception is java.sql.SQLException: ORA-08177: can't serialize access for this transaction

org.springframework.web.servlet.FrameworkServlet.p rocessRequest(FrameworkServlet.java:656)
org.springframework.web.servlet.FrameworkServlet.d oPost(FrameworkServlet.java:560)
javax.servlet.http.HttpServlet.service(HttpServlet .java:637)
javax.servlet.http.HttpServlet.service(HttpServlet .java:717)
org.springframework.web.filter.HiddenHttpMethodFil ter.doFilterInternal(HiddenHttpMethodFilter.java:7 7)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)
org.springframework.web.filter.ShallowEtagHeaderFi lter.doFilterInternal(ShallowEtagHeaderFilter.java :57)
org.springframework.web.filter.OncePerRequestFilte r.doFilter(OncePerRequestFilter.java:76)

[Dave Syer]

You probably need to tweak the INITRANS setting on the BATCH_JOB_INSTANCE table and/or modify the tx isolation setting in the JobRepositiory configuration. There's nothing special about Spring Batch Admin here: you should be able to reproduce the issue in a unit test by launching two jobs near simultaneously.

[amit.kapps]

What's the risk associated with setting isolationLevelForCreate to read_committed instead of serialized? What concurrency issue are we talking about with 2 simultaneous inserts into jobExecution/JobInstance etc.

[Dave Syer]

If you allow simultaneous inserts you run the risk of two job executions running with the same JobParameters (so you might process the same business data twice). If you can accept the risk, or detect the clash in your business code and fail one of the executions, then READ_COMMITTED is fine.

[amit.kapps]

I tried setting the isolation level to read commited to avoid the transaction could not be serialized exception. For my implementation of jobs, I'm ok with a job having duplicate executions with the same jobParams.
However I get a Unique Constratint violation for the defined unique index - CAS_SPR_JOB_INSTANCE (JOB_NAME, JOB_KEY).

org.springframework.dao.DuplicateKeyException: PreparedStatementCallback; SQL [INSERT into CAS_SPR_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ORA-00001: unique constraint (CAS_SPR_JOB_INSTANCE_IX01) violated
; nested exception is java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (CAS_SPR_JOB_INSTANCE_IX01) violated

	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:241)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:602)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:811)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:867)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:871)
	at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:117)
	at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:127)
	at sun.reflect.GeneratedMethodAccessor497.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy132.createJobExecution(Unknown Source)
	at com.matson.cas.batch.core.launch.JobLauncherDetails.executeInternal(JobLauncherDetails.java:264)
	at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:86)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:216)
	at org.springframework.scheduling.commonj.DelegatingWork.run(DelegatingWork.java:61)
	at weblogic.work.j2ee.J2EEWorkManager$WorkWithListener.run(J2EEWorkManager.java:169)
	at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
	at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (AKAPOOR.CAS_SPR_JOB_INSTANCE_IX01) violated

	at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:85)
	at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1030)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:947)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3462)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1349)
	at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:159)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:817)
	at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)
	... 22 more

It doesn't look like I can remove the uniqueness on the job_name, job_key index, If I do the JdbcJobInstanceDao.getJobInstance will throw IllegalStateException if it gets duplicate JobInstance rows.

Any Ideas?

[Dave Syer]

Right, I guess you can't avoid that because JobINstances obviously have to be unique. Howver this exception should be rare (because you wouldn't intentionally submit two jobs with the same parameters simultaneously) and quick to detect.

You can catch and retry with a new set of JobParameters, or just report the failure to your user. I guess the framework could translate it into a JobInstanceAlreadyExistsException (nice to have feature if you want to open a JIRA), but you would still have to deal with it.