Spring Batch ORA-08177: can't serialize access for this transaction for BATCH_JOB_INSTANCE

Question

In Spring batch v4.2.1 we are getting error - SQL [INSERT into BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION) values (?, ?, ?, ?)]; ORA-08177: can't serialize access for this transaction.

Tried all the workaround mentioned at the bottom of this link but it did not resolve. In lower environments ISOLATION_READ_COMMITTED fixed the issue but in Prod the error still occurs. Please help how to resolve this issue.

If it works in one environment but not the other with the same oracle version, then you need to check if there are other differences that might affect the result. Without a minimal example that reproduces the issue, it is hard to find the root cause. — Mahmoud Ben Hassine
I checked CP settings and other details but everything is same. Meanwhile came across this docs.oracle.com/cd/B28359_01/server.111/b28318/… and suggests if the error occurs, rollback the trx and restart again. Is this something that Spring batch v4.2.1 can provide a fix? — Prashant S
@PrashantS there is a Spring Batch issue open: github.com/spring-projects/spring-batch/issues/1127 — Federico

Prashant S Prashant S · Accepted Answer · 2021-03-22T19:51:21

Finally the issue got fixed by implementing BatchConfigurer interface and override getJobRepository() to set explicit isolation level.

We also tried other fixes (change initrans, extend DefaultBatchConfigurer, defaultTransactionIsolation="READ_COMMITTED" in DS context.xml) but none of them worked unfortunately.

Thanks @MahmoudBenHassine for your inputs

@Override
@Bean
public JobRepository getJobRepository() throws Exception {
    JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
    factory.setDataSource(getPrimaryDataSource());
    factory.setTransactionManager(getTransactionManager());

    //Explicit set isolation level in order to avoid ORA-08177 error that occurred when multiple jobs run at the same time default is Serializable
    factory.setIsolationLevelForCreate("ISOLATION_READ_COMMITTED");
    return factory.getObject();
}

Spring Batch ORA-08177: can't serialize access for this transaction for BATCH_JOB_INSTANCE

1 Answers