Spring batch-5 ORA-08177: can't serialize access for this transaction using Oracle database

Question

I am using spring batch 5 using oracle. I have more than 15 batches running using the database. But it shows below error:

            error code [8177]; ORA-08177: can't serialize access for this transaction

        org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION)
            VALUES (?, ?, ?, ?)
        ]; SQL state [72000]; error code [8177]; ORA-08177: can't serialize access for this transaction

            at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1573)
            at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
            at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:960)
            at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1015)
            at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:1020)
            at org.springframework.batch.core.repository.dao.JdbcJobInstanceDao.createJobInstance(JdbcJobInstanceDao.java:157)
            at org.springframework.batch.core.repository.support.SimpleJobRepository.createJobExecution(SimpleJobRepository.java:169)
            at jdk.internal.reflect.GeneratedMethodAccessor32.invoke(Unknown Source)
            at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
            at java.base/java.lang.reflect.Method.invoke(Method.java:568)
            at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
            at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
            at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391)
            at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
            at org.springframework.batch.core.repository.support.AbstractJobRepositoryFactoryBean.lambda$getObject$0(AbstractJobRepositoryFactoryBean.java:204)
            at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
            at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:244)
            at jdk.proxy2/jdk.proxy2.$Proxy124.createJobExecution(Unknown Source)
            at org.springframework.batch.core.launch.support.SimpleJobLauncher.run(SimpleJobLauncher.java:145)
            at org.springframework.batch.core.launch.support.TaskExecutorJobLauncher.run(TaskExecutorJobLauncher.java:59)
            at com.citylive.prepaidcard.config.ScheduledJobBean.perform(ScheduledJobBean.java:62)
            at jdk.internal.reflect.GeneratedMethodAccessor36.invoke(Unknown Source)
            at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
            at java.base/java.lang.reflect.Method.invoke(Method.java:568)
            at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
            at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
            at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:96)
            at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539)
            at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
            at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
            at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
            at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
            at java.base/java.lang.Thread.run(Thread.java:833)
        Caused by: java.sql.SQLException: ORA-08177: can't serialize access for this transaction

            at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
            at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
            at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1150)
            at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:770)
            at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
            at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:497)
            at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:151)
            at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1051)
            at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1530)
            at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1310)
            at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3745)
            at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3917)
            at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3896)
            at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:991)
            at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
            at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
            at org.springframework.jdbc.core.JdbcTemplate.lambda$update$2(JdbcTemplate.java:965)
            at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:648)
            ... 34 more
        Caused by: Error : 8177, Position : 0, Sql = INSERT INTO BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION)
            VALUES (:1 , :2 , :3 , :4 )
        , OriginalSql = INSERT INTO BATCH_JOB_INSTANCE(JOB_INSTANCE_ID, JOB_NAME, JOB_KEY, VERSION)
            VALUES (?, ?, ?, ?)
        , Error Msg = ORA-08177: can't serialize access for this transaction

            at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636)
            ... 51 more

Here is my code:

1. My application.properties:

       spring.datasource.url=jdbc:oracle:thin:@localhost:1521/mydb
       spring.datasource.username=test
       spring.datasource.password=test124
       db.name=test
   
       spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
       spring.datasource.hikari.maximumPoolSize=10
       spring.batch.jdbc.initialize-schema=always
   

2. My Schedule Run the job:

      @Component
       @Slf4j
       public class MyJobJob {
           @Autowired
           JobLauncher jobLauncher;
   
           @Autowired
           EftJob2 eftJob;
   
           @Autowired
           private  JobRepository jobRepository;
   
           @Scheduled(cron = "0 */2 * * * ?")
           public void perform() throws Exception
           {
   
               JobExecution execution = null;
               JobExecution lastJobExecutionStep1 = jobRepository.getLastJobExecution("EFT-STEP1", new JobParametersBuilder().toJobParameters());
   
   
               if (lastJobExecutionStep1 == null || lastJobExecutionStep1.getStatus().isLessThan(BatchStatus.STARTING)) {
                   log.info( "{}  batch step1 {} execution {} Started: ");
                   try {
   
                       JobParameters jobParameters = new JobParametersBuilder()
                               .addLong("startAtEftStep1", System.currentTimeMillis())
                               .toJobParameters();
   
                       execution = jobLauncher.run(eftJob.createEft(), jobParameters);
   
                       log.info(" batch {} Step1 existStatus: "+execution.getExitStatus().getExitCode());
   
                   } catch (JobExecutionAlreadyRunningException | JobRestartException |
                            JobInstanceAlreadyCompleteException |
                            JobParametersInvalidException ex) {
                       log.error("batch {} Step1 {} execution {} JobExecutionAlreadyRunningException | JobRestartException | JobInstanceAlreadyCompleteException | JobParametersInvalidException message: "+ex.getMessage());
                       ex.printStackTrace();
   
                   }catch (Exception ex) {
                       ex.printStackTrace();
   
   
                   }
               }else{
                   log.info(" batch step1 {} execution {} isRunning: ");
               }
   
           }
       }
   

3. Job:

       @Component
       @RequiredArgsConstructor
       @Slf4j
       public class EftJob2 {
           private final MySummaryRepo approvedTrxSummaryRepo;
           private final MyTransactionRepo corpApprovedTransactionRepo;
   
           private final EftProcessorStep1 batchProcessorStep1;
           private final EftWriterStep1 batchWriterStep1;
   
   
           private final PlatformTransactionManager transactionManager;
           private final JobRepository jobRepository;
   
   
           @Autowired
           private JobExecutionListenerStep1 jobExecutionListenerStep1;
   
   
           @Autowired
           private MySPcall transactionSPcall;
   
   
           public Job createEft(){
   
               return new JobBuilder("EFT-STEP1", jobRepository)
                       .incrementer(new RunIdIncrementer())
                       .start(step1())
                       .build();
   
           }
   
   
           private Step step1(){
   
               return new StepBuilder("step1", jobRepository)
                       .<ApprovedTrxSummery, ApprovedTrxSummery> chunk(10, transactionManager)
                       .reader(new EftReaderStep1(approvedTrxSummaryRepo,corpApprovedTransactionRepo, transactionSPcall))
                       .processor(batchProcessorStep1)
                       .writer(batchWriterStep1)
                       .build();
           }
   
   
       }
   

4. Batch config:

         @Configuration
         public class BatchBean {
           @Bean
           public EftProcessorStep1 batchProcessorStep1() {
               return new EftProcessorStep1();
           }
   
           @Bean
           public EftWriterStep1 batchWriterStep1() {
               return new EftWriterStep1();
           }
   
       }  
   

5. Repository:

       @Transactional
       @Repository
       public interface TransactionRepo extends JpaRepository<TransactionEntity, Long> {
           @Query(value = "SELECT * FROM TRANSACTION WHERE STATUS =1 FOR UPDATE ", nativeQuery = true)
           List<TransactionEntity> getbKashTransaction();
       }
   

6. Batch Reader: Read data from repository using ItemReader

       @Slf4j
       public class ReaderStep1 implements ItemReader<TransactionEntity> {
           private Iterator<TransactionEntity> iterator;
           private TransactionSPcall transactionSPcall;
           public ReaderStep1(TransactionRepo transactionRepo, TransactionSPcall transactionSPcall) {
               this.transactionSPcall = transactionSPcall;
              List<TransactionEntity> list = transactionRepo.getbKashTransaction();
   
               if(!list.isEmpty()){
   
                   this.iterator = list.iterator();
   
               }
           }
   
           @Override
           public TransactionEntity read() {
   
               return (iterator != null && iterator.hasNext()) ? iterator.next() : null;
           }
       }
   

7. Batch Processor:

       @Slf4j
       public class ProcessorStep1 implements ItemProcessor<TransactionEntity, TransactionEntity> {
           @Override
           public TransactionEntity process(TransactionEntity item) throws Exception {
                  // Logic calling api
   
               return item;
           }
   

8. Batch writer:

       @Slf4j
       public class WriterStep1 implements ItemWriter<TransactionEntity> {
   
   
           @Override
           public void write(Chunk<? extends TransactionEntity> chunk) {
   
               chunk.forEach(item -> {
                 // update database 
   
               });      
           }
       }
       }
   

It often occur the error.

What is the wrong of my code?

Please help

Answer 1

Problem is about

    @Transactional
    @Repository
    public interface TransactionRepo extends JpaRepository<TransactionEntity, Long> {
        @Query(value = "SELECT * FROM TRANSACTION WHERE STATUS =1 FOR UPDATE ", nativeQuery = true)
        List<TransactionEntity> getbKashTransaction();
    }

You already have @Transactional on top which makes everything transactional and below using FOR UPDATE is SQL level transactions, instead of mixing java and sql level transactions try to keep them in one layer. I would suggest keep all in java. Otherwise this code may break somewhere else.

And define isolation level like below or like this

    batch:
      repository:
        isolationlevelforcreate: ISOLATION_READ_COMMITTED

Also the exception may come from multiple batch jobs starting at the same time. Try to separate their start times and make sure they do not overlap.

Reference:

• https://github.com/spring-projects/spring-boot/issues/25373
• https://stackoverflow.com/a/48211928/175554
• https://stackoverflow.com/a/64429710/175554