Spring Batch 5+Oracle数据库遭遇ORA-08177序列化访问错误求助

错误原因分析 #

ORA-08177 是 Oracle 数据库的序列化访问失败错误，结合你运行15+ Spring Batch任务的场景，核心诱因包括：

• 任务启动时的竞态条件：多线程同时判断任务状态并启动，导致元数据表并发插入冲突
• 业务代码的锁竞争：SELECT ... FOR UPDATE 锁定大量行，加剧事务阻塞
• 连接池资源不足：并发任务数超过连接池上限，引发事务超时与锁等待
• Spring Batch元数据操作的并发冲突：多任务同时写入BATCH_JOB_INSTANCE等元数据表时的锁冲突

代码中的具体问题点 #

1. 任务启动的竞态条件 #

MyJobJob.perform()方法中，jobRepository.getLastJobExecution()的查询未在事务中执行，多线程可能同时读到任务未运行的状态，进而并发启动任务，导致元数据表插入冲突。

2. SELECT ... FOR UPDATE 的不合理使用 #

Repository中的getbKashTransaction()使用FOR UPDATE锁定所有符合条件的行，且Reader在构造函数中一次性加载所有数据并持有锁，直到任务完成，严重加剧锁竞争。

3. 连接池配置不足 #

maximumPoolSize=10无法支撑15+并发任务的连接需求，导致连接等待，进而引发事务超时与锁冲突。

4. Job实例重复创建 #

EftJob2.createEft()每次调用都新建Job实例，增加不必要的元数据操作压力。

解决方案 #

1. 解决任务启动的竞态条件 #

将任务状态判断与启动逻辑放入事务中，确保原子性：

@Component
@Slf4j
public class MyJobJob {
    @Autowired
    JobLauncher jobLauncher;

    @Autowired
    Job eftJob; // 改为注入Bean

    @Autowired
    private JobRepository jobRepository;

    @Scheduled(cron = "0 */2 * * * ?")
    @Transactional // 添加事务注解，保证查询与启动的原子性
    public void perform() throws Exception {
        JobExecution execution = null;
        JobParameters jobParameters = new JobParametersBuilder()
                .addLong("startAtEftStep1", System.currentTimeMillis())
                .toJobParameters();
        JobExecution lastJobExecutionStep1 = jobRepository.getLastJobExecution("EFT-STEP1", jobParameters);

        if (lastJobExecutionStep1 == null || lastJobExecutionStep1.getStatus().isLessThan(BatchStatus.STARTING)) {
            log.info("batch step1 execution Started: ");
            try {
                execution = jobLauncher.run(eftJob, jobParameters);
                log.info("batch Step1 existStatus: " + execution.getExitStatus().getExitCode());
            } catch (JobExecutionAlreadyRunningException | JobRestartException |
                     JobInstanceAlreadyCompleteException | JobParametersInvalidException ex) {
                log.error("batch Step1 execution error: " + ex.getMessage());
            } catch (Exception ex) {
                log.error("batch Step1 execution unexpected error: ", ex);
            }
        } else {
            log.info("batch step1 execution isRunning: ");
        }
    }
}

2. 优化SELECT ... FOR UPDATE的锁策略 #

使用FOR UPDATE SKIP LOCKED跳过已锁定的行，减少锁竞争（Oracle 11g+支持）：

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

同时建议将Reader改为分页读取，缩短锁持有时间。

3. 调整连接池配置 #

根据并发任务数调整Hikari连接池参数：

spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.minimumIdle=10
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=1800000

4. 将Job与Step定义为Bean #

避免重复创建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;

    @Bean
    public Job createEft(){
        return new JobBuilder("EFT-STEP1", jobRepository)
                .incrementer(new RunIdIncrementer())
                .start(step1())
                .build();
    }

    @Bean
    private Step step1(){
        return new StepBuilder("step1", jobRepository)
                .<ApprovedTrxSummery, ApprovedTrxSummery> chunk(10, transactionManager)
                .reader(eftReaderStep1())
                .processor(batchProcessorStep1)
                .writer(batchWriterStep1)
                .build();
    }

    @Bean
    public EftReaderStep1 eftReaderStep1() {
        return new EftReaderStep1(approvedTrxSummaryRepo, corpApprovedTransactionRepo, transactionSPcall);
    }
}

5. 调整Spring Batch事务隔离级别 #

确保JobRepository使用合适的隔离级别：

@Configuration
public class BatchConfig {
    @Bean
    public JobRepository jobRepository(DataSource dataSource, PlatformTransactionManager transactionManager) throws Exception {
        JobRepositoryFactoryBean factory = new JobRepositoryFactoryBean();
        factory.setDataSource(dataSource);
        factory.setTransactionManager(transactionManager);
        factory.setIsolationLevelForCreate("ISOLATION_READ_COMMITTED"); // 匹配Oracle默认隔离级别
        factory.setTablePrefix("BATCH_");
        return factory.getObject();
    }
}

6. 控制任务并发数 #

配置TaskExecutorJobLauncher的线程池，避免过度并发：

@Bean
public JobLauncher jobLauncher(JobRepository jobRepository) {
    TaskExecutorJobLauncher jobLauncher = new TaskExecutorJobLauncher();
    jobLauncher.setJobRepository(jobRepository);
    ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
    taskExecutor.setCorePoolSize(10);
    taskExecutor.setMaxPoolSize(15);
    taskExecutor.setQueueCapacity(20);
    taskExecutor.setThreadNamePrefix("batch-task-");
    taskExecutor.initialize();
    jobLauncher.setTaskExecutor(taskExecutor);
    return jobLauncher;
}

内容的提问来源于stack exchange，提问作者Enamul Haque