Spring Batch多批处理共用Oracle连接报ORA-08177序列化访问错误

问题场景与错误信息 #

10多个Spring Batch批处理进程共用同一个数据库连接，通过Spring调度定时执行时触发以下错误：

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:1539)
        ........
Caused by: java.sql.SQLException: ORA-08177: can't serialize access for this transaction
        .....
 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)
        ... 53 more

现有配置代码 #

1. application.properties #

spring.datasource.url=myurl
spring.datasource.username=mydbName
spring.datasource.password=myPassword
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver

spring.batch.job.enabled=true
spring.batch.job.chunk.size=10

# 0 */2 * * * ? --> 每2分钟执行一次
batch-schduled-time=0 */2 * * * ?

2. MyBatch Job 定义 #

@Component
@RequiredArgsConstructor
@Slf4j
public class MyJob {

   private final MyTrxSummaryRepo approvedTrxSummaryRepo;
   private final MyApprovedTransactionRepo corpApprovedTransactionRepo;
   private final MyTransactionViewRepo allApprovedTransactionViewRepo;

   private final ProcessorStep1 batchProcessorStep1;
   private final WriterStep1 batchWriterStep1;

   private final PlatformTransactionManager transactionManager;
   private final JobRepository jobRepository;

   @Value("${spring.batch.job.chunk.size}")
   private Integer chunkSize;

   public Job createEft(){
       String id = UUID.randomUUID().toString();
       return new JobBuilder(id, jobRepository)
               .incrementer(new RunIdIncrementer())
               .start(step1())
               .build();
   }

   private Step step1(){
       return new StepBuilder("step1", jobRepository)
               .<MyTrxSummery,MyTrxSummery> chunk(chunkSize, transactionManager)
               .reader(new ReaderStep1(approvedTrxSummaryRepo,corpApprovedTransactionRepo))
               .processor(batchProcessorStep1)
               .writer(batchWriterStep1)
               .build();
   }
}

3. BatchBean 配置 #

@Configuration
public class BatchBean {
    @Bean
    public ProcessorStep1 batchProcessorStep1() {
        return new ProcessorStep1();
    }

    @Bean
    public WriterStep1 batchWriterStep1() {
        return new WriterStep1();
    }
}

4. 实体与仓库示例（MyTrxSummery） #

@Entity
@Table(name = "mySummeryTb")
@Data
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class MyTrxSummery {
    @Id
    @Column(name = "TRANSACTION_REFERENCE")
    private String transactionReference;
    // 其他字段省略
}

@Repository
public interface MyTrxSummaryRepo extends JpaRepository<MyTrxSummery, Long> {
    @Query("myquery.....")
    List<MyTrxSummery> getAllTransaction();

    MyTrxSummery findByTransactionReference(String transactionReference);

    @Modifying
    @Query(" update.....")
    void updateApprovedTrxSummeriesByTransactionReference(String transactionReference);

    @Modifying
    @Query(" update......")
    void updateSegmentedTransaction(String transactionReference);
}

5. 调度配置（ScheduledJobBean） #

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

   @Autowired
   MyJob eftJob;

   @Scheduled(cron = "${batch-schduled-time}")
   public void perform() throws Exception
   {
      try {
           JobExecution execution = jobLauncher.run(eftJob.createEft(), new JobParameters());
       }catch (Exception ex) {
           ex.printStackTrace();
       }
    }
 }

问题根源 #

ORA-08177是Oracle数据库在序列化事务隔离级别下，并发事务修改同一数据导致的冲突。结合代码，核心问题包括：

1. 共用单一数据库连接：多个批处理复用同一连接，所有事务在同一连接下执行，触发元数据表（如BATCH_JOB_INSTANCE）的并发插入冲突。
2. Job创建逻辑不合理：每次生成随机UUID作为Job名称，搭配RunIdIncrementer导致元数据管理混乱，加剧锁竞争。
3. Reader加载时机不当：构造方法中一次性查询全量数据，事务持有时间过长，增加锁冲突概率。
4. 事务配置冲突：JPA仓库的@Transactional注解与Spring Batch事务管理器形成嵌套事务，进一步加剧并发问题。

解决方案 #

1. 配置数据库连接池 #

放弃单一连接，使用HikariCP连接池分配独立连接，修改application.properties：

# 新增HikariCP配置
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

2. 自定义JobRepository调整事务隔离级别 #

将Spring Batch元数据操作的隔离级别改为READ_COMMITTED，减少冲突：

@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");
        factory.setTablePrefix("BATCH_");
        factory.afterPropertiesSet();
        return factory.getObject();
    }
}

3. 修正Job创建逻辑 #

固定Job名称，让RunIdIncrementer负责生成唯一实例ID：

public Job createEft(){
    return new JobBuilder("eft-job", jobRepository)
            .incrementer(new RunIdIncrementer())
            .start(step1())
            .build();
}

4. 优化Reader数据加载逻辑 #

改为分页查询按需加载数据，缩短事务持有时间：

@Slf4j
public class ReaderStep1 implements ItemReader<MyTrxSummery> {
    private final MyTrxSummaryRepo myTrxSummaryRepo;
    private final MyTransactionRepo corpApprovedTransactionRepo;
    private int currentPage = 0;
    private static final int PAGE_SIZE = 100;
    private Iterator<MyTrxSummery> iterator;

    public ReaderStep1(MyTrxSummaryRepo myTrxSummaryRepo, MyTransactionRepo corpApprovedTransactionRepo) {
        this.myTrxSummaryRepo = myTrxSummaryRepo;
        this.corpApprovedTransactionRepo = corpApprovedTransactionRepo;
    }

    @Override
    public MyTrxSummery read() {
        if (iterator == null || !iterator.hasNext()) {
            List<MyTrxSummery> list = myTrxSummaryRepo.getAllTransactionByPage(currentPage++, PAGE_SIZE);
            if (list.isEmpty()) {
                return null;
            }
            iterator = list.iterator();
        }
        return iterator.hasNext() ? iterator.next() : null;
    }
}

同时在仓库添加分页查询方法：

@Query("myquery.....")
List<MyTrxSummery> getAllTransactionByPage(@Param("page") int page, @Param("size") int size);

5. 统一事务管理 #

移除仓库上的@Transactional注解，由Spring Batch事务管理器统一管理批处理事务，避免嵌套事务冲突。

6. 避免批处理同时触发 #

调整调度时间错开批处理执行；若必须同时执行，添加分布式锁（如数据库悲观锁、Redis锁），确保同一时间只有一个Job实例操作元数据或业务数据。

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