java - @Async 和 @Transactional 的 Spring 启动问题

Question

我有几百万条记录，我将其从一个 Oracle 数据库迁移到另一个。我们遇到性能问题，与同事讨论后我决定多线程处理数据。

我们有以下工件:

• Spring 启动 2.1.6.RELEASE
• HikariCP 3.2.0
• Hibernate 5.3.10.Final
• JDK 11.0.2
• OJDBC8 12.2.0.1

我有一个 Service 类，注释为@Service，类内部是方法

    @Async("threadPoolTaskExecutor")
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void processMigration(int from, int to) {

        int progressInterval = to - from;
        ProgressBar progressBar = new ProgressBar("Progress " + Thread.currentThread().getName(), progressInterval, ProgressBarStyle.UNICODE_BLOCK);
        try (Stream<SomeEntity> entityStream = someEntityRepository.streamAllInInterval(from, to)) {

            progressBar.start();
            entityStream.forEach(entity -> progressBar.step());
            progressBar.stop();
        } catch (Exception e) {
            progressBar.stop();
            throw e;
        }
    }

在foreach内部会有一些处理数据的逻辑，并且该服务被注入(inject)到另一个类中，称为Migrator(包含注入(inject)的ThreadPoolTask​​Executor)，方法如下:

public void migrate() { 

crimeService.processMigration(0, 500000);
crimeService.processMigration(500000, 1000000);
}

和我的 SpringBootApplication 类(主要配置):

@SpringBootApplication
@EnableAsync
@EnableTransactionManagement
public class MigrationApplication {

    public static void main(String[] args) {
        SpringApplication.run(MigrationApplication.class, args);
    }

    @Bean("threadPoolTaskExecutor")
    public TaskExecutor taskExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(10);
        executor.setMaxPoolSize(25);
        executor.setQueueCapacity(30);
        executor.afterPropertiesSet();
        return executor;
    }
}

application.yaml 如下所示:

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 100
          fetch_size: 400
      dialect: org.hibernate.dialect.Oracle12cDialect
      order_inserts: true
      order_updates: true
  datasource:
    url: jdbc:oracle:thin::1521:something
    username: username
    password: password
    hikari:
      maximum-pool-size: 10
      leak-detection-threshold: 30000
    driver-class-name: oracle.jdbc.OracleDriver

我认为当我运行上述代码时，Hikari 将创建 2 个连接，因为我已经调用了两次用 @Transactional 注释的 processMigration() 方法。我在日志中看到它只在开始时创建，但是当其中一个线程等待另一个线程时，只有一个连接处于 Activity 状态。我的计算机有可用的 4 个核心，因此我预计不存在硬件问题。我明白为什么只有一个 Activity 连接，因为只有一个线程在运行，但为什么第二个线程在等待我无法弄清楚。请提供帮助，如果有人有比我选择的更好的方法来迁移数据，我感谢您的建议。

更新

我发现 JpaTransactionManager 为两个线程创建事务，但一个事务在开始时立即提交。当第二个线程完成任务时，它还没有完成前一个任务。

2019-08-15 15:02:39.707 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Creating new transaction with name [org.springframework.data.jpa.repository.support.SimpleJpaRepository.count]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,readOnly
2019-08-15 15:02:39.707 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Opened new EntityManager [SessionImpl(1773290233<open>)] for JPA transaction
2019-08-15 15:02:39.717 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@698ef9d1]
2019-08-15 15:02:40.156 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Initiating transaction commit
2019-08-15 15:02:40.157 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Committing JPA transaction on EntityManager [SessionImpl(1773290233<open>)]
2019-08-15 15:02:40.163 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Closing JPA EntityManager [SessionImpl(1773290233<open>)] after transaction
Migrating 1799449 CRIMES

2019-08-15 15:02:40.186 DEBUG 3939 --- [lTaskExecutor-2] o.s.orm.jpa.JpaTransactionManager        : Creating new transaction with name [com.aliter.mvsmigration.dvs.service.CrimeServiceImpl.processMigration]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT
2019-08-15 15:02:40.186 DEBUG 3939 --- [lTaskExecutor-1] o.s.orm.jpa.JpaTransactionManager        : Creating new transaction with name [com.aliter.mvsmigration.dvs.service.CrimeServiceImpl.processMigration]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT
TASK duration: 830ms
2019-08-15 15:02:40.187 DEBUG 3939 --- [lTaskExecutor-2] o.s.orm.jpa.JpaTransactionManager        : Opened new EntityManager [SessionImpl(346995150<open>)] for JPA transaction
2019-08-15 15:02:40.187 DEBUG 3939 --- [lTaskExecutor-1] o.s.orm.jpa.JpaTransactionManager        : Opened new EntityManager [SessionImpl(1848267920<open>)] for JPA transaction
2019-08-15 15:02:40.187 DEBUG 3939 --- [lTaskExecutor-2] o.s.orm.jpa.JpaTransactionManager        : Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@1a69552b]
2019-08-15 15:02:40.188 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Creating new transaction with name [org.springframework.data.jpa.repository.support.SimpleJpaRepository.save]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
2019-08-15 15:02:40.189 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Opened new EntityManager [SessionImpl(282270616<open>)] for JPA transaction
2019-08-15 15:02:40.191 DEBUG 3939 --- [lTaskExecutor-1] o.s.orm.jpa.JpaTransactionManager        : Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@22c0dffa]
2019-08-15 15:02:40.192 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Exposing JPA transaction as JDBC [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@817e0aa]
2019-08-15 15:02:40.212 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Initiating transaction commit
2019-08-15 15:02:40.214 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Committing JPA transaction on EntityManager [SessionImpl(282270616<open>)]
2019-08-15 15:02:40.229 DEBUG 3939 --- [           main] o.s.orm.jpa.JpaTransactionManager        : Closing JPA EntityManager [SessionImpl(282270616<open>)] after transaction
PROGRAM duration: 1507ms
mvs-migration-shell:>2019-08-15 15:02:40.389  WARN 3939 --- [lTaskExecutor-2] org.jline                                : Unable to create a system terminal, creating a dumb terminal (enable debug logging for more information)
2019-08-15 15:02:40.389  WARN 3939 --- [lTaskExecutor-1] org.jline                                : Unable to create a system terminal, creating a dumb terminal (enable debug logging for more information)
Progress MVSThreadPoolTaskExecutor-2   0% │          │    0/1000 (0:00:00 / ?)
2019-08-15 15:02:41.820 DEBUG 3939 --- [lTaskExecutor-2] o.s.orm.jpa.JpaTransactionManager        : Initiating transaction commit
2019-08-15 15:02:41.821 DEBUG 3939 --- [lTaskExecutor-2] o.s.orm.jpa.JpaTransactionManager        : Committing JPA transaction on EntityManager [SessionImpl(346995150<open>)]
2019-08-15 15:02:41.825 DEBUG 3939 --- [lTaskExecutor-2] o.s.orm.jpa.JpaTransactionManager        : Closing JPA EntityManager [SessionImpl(346995150<open>)] after transaction
Progress MVSThreadPoolTaskExecutor-1 100% │██████████│ 1000/1000 (0:00:08 / 0:
2019-08-15 15:02:49.084 DEBUG 3939 --- [lTaskExecutor-1] o.s.orm.jpa.JpaTransactionManager        : Initiating transaction commit
2019-08-15 15:02:49.085 DEBUG 3939 --- [lTaskExecutor-1] o.s.orm.jpa.JpaTransactionManager        : Committing JPA transaction on EntityManager [SessionImpl(1848267920<open>)]
2019-08-15 15:02:49.585 DEBUG 3939 --- [lTaskExecutor-1] o.s.orm.jpa.JpaTransactionManager        : Closing JPA EntityManager [SessionImpl(1848267920<open>)] after transaction
2019-08-15 15:03:02.981 DEBUG 3939 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-08-15 15:03:32.984 DEBUG 3939 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-08-15 15:04:02.989 DEBUG 3939 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
2019-08-15 15:04:32.995 DEBUG 3939 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)

Answer 1

我发现问题出在哪里了，在提到的方法processMigration()中，有一个方法streamAllInInterval(from, to)，它返回指定时间间隔的数据。它包含 native SQL 查询:

Select f.* from someView f left join someTable em on em.id = f.utvar where em.id is null AND ROWNUM BETWEEN :from AND :to

当我设置从 0 到 500000 时，此查询返回数据，但不返回 500000 和 1000000 的数据。因此，我使用此处第一个提到的答案重写了查询 SQL ROWNUM how to return rows between a specific range现在一切正常。