Spring пакет-5 ORA-08177: невозможно сериализовать доступ для этой транзакции с использованием базы данных Oracle ⇐ JAVA

[Anonymous]

Я использую весенний пакет 5 с помощью oracle. У меня в базе данных работает более 15 пакетов. Но ниже отображается ошибка:

Код: Выделить всё

            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

Вот мой код:

• Мое приложение.свойства:

  Код: Выделить всё

      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
  

• Мое расписание Выполнить задание:

  Код: Выделить всё

     @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: ");
  }
  
  }
  }
  

• Работа:

  Код: Выделить всё

      @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)
  . chunk(10, transactionManager)
  .reader(new EftReaderStep1(approvedTrxSummaryRepo,corpApprovedTransactionRepo, transactionSPcall))
  .processor(batchProcessorStep1)
  .writer(batchWriterStep1)
  .build();
  }
  
  }
  

• Пакетная конфигурация:

  Код: Выделить всё

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

• Репозиторий:

  Код: Выделить всё

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

• Пакетное чтение:
  Чтение данных из репозитория с помощью ItemReader

  Код: Выделить всё

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

• Пакетный процессор:

  Код: Выделить всё

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

• Создание пакетов:

  Код: Выделить всё

      @Slf4j
  public class WriterStep1 implements ItemWriter {
  
  @Override
  public void write(Chunk
  
  Подробнее здесь: [url]https://stackoverflow.com/questions/78290960/spring-batch-5-ora-08177-cant-serialize-access-for-this-transaction-using-orac[/url]