Spring Batch - ORA-08177

Question

org.springframework.dao.CannotSerializeTransactionException: 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

        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:660)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:909)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:970)
        at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:975)
        at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:115)
        at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:135)
        at sun.reflect.GeneratedMethodAccessor147.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:497)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
        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:281)
        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:207)
        at com.sun.proxy.$Proxy54.createJobExecution(Unknown Source)

I have tried the following:

1) Changing isolation level to ISOLATION_READ_COMMITTED

  @Bean(name="jobRepository")
public JobRepository getJobRepository() {
    JobRepositoryFactoryBean factoryBean = new JobRepositoryFactoryBean();
    factoryBean.setDataSource(configDataSource);
    factoryBean.setTransactionManager(transactionManager);
    factoryBean.setIsolationLevelForCreate("ISOLATION_READ_COMMITTED");
    try {
        factoryBean.afterPropertiesSet();
        return factoryBean.getObject();
    } catch (Exception e) {
        log.error("JobRepository bean could not be initialized");
        throw new BatchConfigurationException(e);
    }
}

2) Altered initrans for all spring batch core tables

Update: I have already tried possible solutions provided in the other questions. However, I am still facing this problem at least once in a week in prod.

Possible duplicate of [Spring Batch ORA-08177: can't serialize access for this transaction when running single job, SERIALIZED isolation level](http://stackoverflow.com/questions/22364432/spring-batch-ora-08177-cant-serialize-access-for-this-transaction-when-running) — Luca Basso Ricci, Nov 28 '16 at 11:22
I have already tried all the possible solutions provided in the above link. still I'm facing this issue. — Arul, Nov 28 '16 at 11:49
Maybe you should check the transaction setting of `createJobExecution` method/class. — Beck Yang, Nov 28 '16 at 15:27

score 3 · Answer 1 · edited Oct 19 '20 at 14:17

Finally, I was able to get rid of this long hauling issue.

I hadn't configured the job launcher properly using job repository bean. After I did that, I never got this issue back and the code is successfully running in production environment without any issues.

//Overriding default configurer, since by default spring batch configuration allows only one datasource
    @Bean
    public BatchConfigurer configurer(){
      return new DefaultBatchConfigurer(configDataSource);
    }
    
    @Bean(name="jobRepository")
    public JobRepository getJobRepository() {
        JobRepositoryFactoryBean factoryBean = new JobRepositoryFactoryBean();
        factoryBean.setDataSource(configDataSource);
        factoryBean.setTransactionManager(configTxManager);
        factoryBean.setIsolationLevelForCreate("ISOLATION_READ_COMMITTED");
        factoryBean.setTablePrefix("BATCH_");
        try {
            factoryBean.afterPropertiesSet();
            return factoryBean.getObject();
        } catch (Exception e) {
            log.error("JobRepository bean could not be initialized");
            throw new BatchConfigurationException(e);
        }
    }
    
    @Bean
    public JobRegistryBeanPostProcessor jobRegistryBeanPostProcessor(JobRegistry jobRegistry) {
        JobRegistryBeanPostProcessor jobRegistryBeanPostProcessor = new JobRegistryBeanPostProcessor();
        jobRegistryBeanPostProcessor.setJobRegistry(jobRegistry);
        return jobRegistryBeanPostProcessor;
    }
    
    @Bean
    public JobLauncher jobLauncher(){
        SimpleJobLauncher jobLauncher = new SimpleJobLauncher();
        jobLauncher.setJobRepository(jobRepository);
        return jobLauncher;
    }
    
    @Bean
    public JobOperator jobOperator() {
        SimpleJobOperator jobOperator = new SimpleJobOperator();
        jobOperator.setJobExplorer(jobExplorer);
        jobOperator.setJobLauncher(jobLauncher);
        jobOperator.setJobRegistry(jobRegistry);
        jobOperator.setJobRepository(jobRepository);
        return jobOperator;
    }

score 1 · Answer 2 · answered Jul 27 '21 at 20:58

I had that same problem configuring in one of our environment, the funny thing is that we have alredy used the same job running in production. So I believed that this was cause because of the DDL that I used to create our tables insted of any problem in our job configuration.

We solved the problem doing a dummy insert on BATCH_JOB_INSTANCE and it worked.

Spring Batch - ORA-08177

2 Answers2