Can’t serialize access for this transaction with isolation to ISOLATION_READ_UNCOMMITTED

Question

Using spring-batch, I have 2 datasources : one for the spring batch called centralDataSource and one for the business called localDataSource.

I've set the isolation level to ISOLATION_READ_UNCOMMITTED to avoid problems with concurrent transactions as below.

@Bean
public Job myJob(Step firstStep,
                     Step secondStep,
                     JobCompletionNotificationListener listener,
                     JobRepository customJobRepository) {
    return jobBuilderFactory.get("my-job")
                           .repository(customJobRepository)
                           .listener(listener)
                           .incrementer(new RunIdIncrementer())
                           .start(firstStep)
                           .next(secondStep)
                           .build();
}

@Bean
public JobRepository customJobRepository(
        @Qualifier("centralDataSource") DataSource centralDataSource,
        @Qualifier("centralTransactionManager") PlatformTransactionManager centralTransactionManager)
            throws Exception {
    JobRepositoryFactoryBean factoryBean = new JobRepositoryFactoryBean();
    factoryBean.setDatabaseType("ORACLE");
    factoryBean.setDataSource(centralDataSource);
    factoryBean.setTransactionManager(centralTransactionManager);
    factoryBean.setIsolationLevelForCreate("ISOLATION_READ_UNCOMMITTED");
    return factoryBean.getObject();
}

I still have Application run failed with SQLException: ORA-08177: can’t serialize access for this transaction.

I don't understand why. There are not even 2 concurrent spring batch jobs running at the same time. Jobs are run sequentially so why does this happen and how can I solve it ?

Can anyone help me ?

Have you tried to set the isolation level to `ISOLATION_REPEATABLE_READ`? See https://stackoverflow.com/questions/26774833/ — Mahmoud Ben Hassine, Jun 02 '20 at 08:21

score 1 · Accepted Answer · answered Jun 02 '20 at 07:48

I don't know Spring and how it can change transaction isolation level but:

First, Oracle database does not support READ_UNCOMMITTED isolation level:

alter session set isolation_level=read_uncommitted
                                  *
ERROR at line 1:
ORA-02183: valid options: ISOLATION_LEVEL { SERIALIZABLE | READ COMMITTED }

Second if you have ORA-8177 it means you are using SERIALIZABLE isolation level.

$ oerr ora 8177
08177, 00000, "can't serialize access for this transaction"
// *Cause:   Encountered data changed by an operation that occurred after
//           the start of this serializable transaction.
// *Action:  In read/write transactions, retry the intended operation or
//           transaction.

You need to check what ALTER SESSION statements to change isolation level are run by Spring.

Spring batch default isolation level is SERIALIZABLE. It looks like the isolation level change failed. I'll set it too READ COMMITTED and see if problem is still present. It could take a few days. — Degravef, Jun 02 '20 at 08:18
No more error after a month. I'd say it is solved. Thank you. — Degravef, Jul 07 '20 at 14:47

Can’t serialize access for this transaction with isolation to ISOLATION_READ_UNCOMMITTED

1 Answers1