I have a strange issue that has cropped up recently in my project that I can't seem to nail down the source for, and wondered if those more versed in Spring and Oracle could shed some light on this stubborn bug.
In a nutshell, when I start up the job, it gets to executing the first step in my job configuration, and then the following is outputted every time.
2017-06-06 12:22:38.341 INFO 10776 --- [ask-scheduler-3] o.s.b.f.xml.XmlBeanDefinitionReader : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2017-06-06 12:22:38.360 INFO 10776 --- [ask-scheduler-3] o.s.jdbc.support.SQLErrorCodesFactory : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
2017-06-06 12:22:38.412 ERROR 10776 --- [ask-scheduler-3] o.s.integration.handler.LoggingHandler : org.springframework.dao.CannotSerializeTransactionException: PreparedStatementCallback; SQL [INSERT INTO BATCH_JOB_EXECUTION_CONTEXT (SHORT_CONTEXT, SERIALIZED_CONTEXT, JOB_EXECUTION_ID) 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
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:649)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:870)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:931)
at org.springframework.batch.core.repository.dao.JdbcExecutionContextDao.persistSerializedContext(JdbcExecutionContextDao.java:233)
at org.springframework.batch.core.repository.dao.JdbcExecutionContextDao.saveExecutionContext(JdbcExecutionContextDao.java:175)
at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:146)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
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:282)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean$1.invoke(AbstractJobRepositoryFactoryBean.java:172)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy78.createJobExecution(Unknown Source)
at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:125)
at org.autodatacorp.dpt.importer.costimport.etl.scheduling.CostImportJobRunnable.launchCostImportJob(CostImportJobRunnable.java:64)
at org.autodatacorp.dpt.importer.costimport.etl.scheduling.CostImportJobRunnable.run(CostImportJobRunnable.java:48)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:81)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: ORA-08177: can't serialize access for this transaction
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:4875)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:877)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:870)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
... 32 more
the job will continue but will keep pumping out that error over and over again.
I've attempted to enable logback.xml to try and get more information as to what is going on, but it seems to hang the job process on execution. Enabling debug mode for the application provides the same error with no additional insight. Ive tried this with both a local instance of Oracle and a remote with no difference in error message or frequency. Setting batch.isolationlevel in oracle-batch.properties to READ_COMMITED and ISOLATION_SERIALIZABLE provides no relief to the issue. I’ve verified that I have been the only user of our amazon instance today to negate the possibility of multiple users having collisions when accessing the DB somehow, and have verified that the code I’m using does build without errors. I've also verified that @EnableTransaction annotation has been set in the DatabaseConfiguration class.
The latest version of Spring Batch and Boot are being used, and the Oracle instance are all using Oracle 12c. Other applications can connect fine using the same login credentials (they are not connecting to the database when I have tested this application). And of course, the spring batch tables do exist in the DB and are accessable to the user being used to login to the database
So in a nutshell, I've ran out of possibilities as to what is the root cause of this and how I can make it happy. Anyone ran into this issue, and have any advice?
EDIT: As requested, the job configuration
@Bean
public Job sampleJob(@Qualifier("stepOne") Step stepOne,
@Qualifier("stepTwo") Step stepTwo,
@Qualifier("stepThree") Step stepThree,
@Qualifier("stepFour") Step stepFour,
@Qualifier("stepFive") Step stepFive,
@Qualifier("stepSix") Step stepSix,
@Qualifier("listener") CustomJobListener listener
) {
LOGGER.info("Starting Job");
return jobBuilderFactory.get("Importing Data")
.incrementer(new RunIdIncrementer())
.listener(listener)
.flow(stepOne)
.next(stepTwo)
.from(stepTwo).on(Constants.STEP_EXIT_STATUS_CONTINUE)
.to(stepThree)
.from(stepTwo).on(Constants.STEP_EXIT_STATUS_COMPLETED).end()
.from(stepThree)
.next(stepFour)
.from(stepFour).on(Constants.STEP_EXIT_STATUS_CONTINUE)
.to(transformingCostImportData)
.from(stepFour).on(Constants.STEP_EXIT_STATUS_COMPLETED).end()
.from(transformingCostImportData)
.next(stepFive)
.next(stepSix)
.next(stepTwo)
.build()
.build();
}
