Spring Boot多数据源更新报错及事务一致性实现问询

我基于Spring Boot配置了两个数据源，参考了多数据源配置与@Modifying注解教程，目前从两个数据源读取数据正常，但执行更新操作时抛出org.springframework.dao.InvalidDataAccessApiUsageException: Executing an update/delete query错误。现请教：

1. 错误的原因是什么？
2. 如何在同一事务中更新所有数据源？

项目代码详情 #

数据源1

com/example/jpa/db1/entity/Db1Table1Entity.java

@Entity
@Table(name = "table1", catalog = "db1")
@ToString
@RequiredArgsConstructor
@Accessors(fluent = true)
@Getter
@Setter
public class Db1Table1Entity implements Serializable {

  @Id
  @Column(name = "a")
  private String a;

  @Column(name = "b")
  private String b;
}

com/example/jpa/db1/repo/Db1Table1Repository.java

@Repository
public interface Db1Table1Repository extends JpaRepository<Db1Table1Entity, String> {

  Optional<Db1Table1Entity> findByA(final String a);

  @Modifying
  @Query("UPDATE Db1Table1Entity e SET e.b = :b WHERE e.a = :a")
  void updateByA(@Param("a") String a, @Param("b") String b);
}

com/example/jpa/db1/Db1JpaConfiguration.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = {"com.example.jpa.db1.repo"},
    entityManagerFactoryRef = "db1EntityManagerFactory",
    transactionManagerRef = "db1TransactionManager")
public class Db1JpaConfiguration {

  @Primary
  @Bean
  public LocalContainerEntityManagerFactoryBean db1EntityManagerFactory(
      final EntityManagerFactoryBuilder builder, final DataSource db1DataSource) {

    return builder
        .dataSource(db1DataSource)
        .packages("com.example.jpa.db1")
        .persistenceUnit("db1")
        .build();
  }

  @Bean
  @ConfigurationProperties("spring.datasource.db1")
  public DataSourceProperties db1DataSourceProperties() {
    return new DataSourceProperties();
  }

  @Primary
  @Bean
  @ConfigurationProperties("spring.datasource.db1.hikari")
  public DataSource db1xDataSource() {
    return db1DataSourceProperties().initializeDataSourceBuilder().build();
  }

  @Primary
  @Bean
  public PlatformTransactionManager db1TransactionManager(
      final EntityManagerFactory db1EntityManagerFactory) {
    return new JpaTransactionManager(db1EntityManagerFactory);
  }
}

数据源2

com/example/jpa/db2/entity/Db2Table1Entity.java

@Entity
@Table(name = "table1", catalog = "db2")
@ToString
@RequiredArgsConstructor
@Accessors(fluent = true)
@Getter
@Setter
public class Db2Table1Entity implements Serializable {

  @Id
  @Column(name = "a")
  private String a;

  @Column(name = "b")
  private String b;
}

com/example/jpa/db2/repo/Db2Table1Repository.java

@Repository
public interface Db2Table1Repository extends JpaRepository<Db2Table1Entity, String> {

  Optional<Db2Table1Entity> findByA(final String a);

  @Modifying
  @Query("UPDATE Db2Table1Entity e SET e.b = :b WHERE e.a = :a")
  void updateByA(@Param("a") String a, @Param("b") String b);
}

com/example/jpa/db2/Db2JpaConfiguration.java

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
    basePackages = {"com.example.jpa.db2.repo"},
    entityManagerFactoryRef = "db2EntityManagerFactory",
    transactionManagerRef = "db2TransactionManager")
public class Db2JpaConfiguration {

  @Bean
  public LocalContainerEntityManagerFactoryBean db2EntityManagerFactory(
      final EntityManagerFactoryBuilder builder, final DataSource db2DataSource) {

    return builder
        .dataSource(db2DataSource)
        .packages("com.example.jpa.db2")
        .persistenceUnit("db2")
        .build();
  }

  @Bean
  @ConfigurationProperties("spring.datasource.db2")
  public DataSourceProperties db2DataSourceProperties() {
    return new DataSourceProperties();
  }

  @Bean
  @ConfigurationProperties("spring.datasource.db2.hikari")
  public DataSource db2DataSource() {
    return db2DataSourceProperties().initializeDataSourceBuilder().build();
  }

  @Bean
  public PlatformTransactionManager db2TransactionManager(
      final EntityManagerFactory db2EntityManagerFactory) {
    return new JpaTransactionManager(db2EntityManagerFactory);
  }
}

服务类

com/example/jpa/DbService.java

import org.springframework.transaction.annotation.Transactional;

@Service
public class DbService {
  private final Db1Table1Repository repo1;
  private final Db2Table1Repository repo2;

  public DbService(Db1Table1Repository repo1, Db2Table1Repository repo2) {
    this.repo1 = repo1;
    this.repo2 = repo2;
  }

  public List<String> find(final String a) {
    return List.of(
        repo1.findByA(a).map(Db1Table1Entity::b).orElse(""),
        repo2.findByA(a).map(Db2Table1Entity::b).orElse(""));
  }

  @Transactional
  public void update(final String a, final String b) {
    repo1.updateByA(a, b);
    repo2.updateByA(a, b);
  }
}

测试数据

INSERT
IGNORE INTO
        db1.table1(
            a,
            b
        )
    VALUES(
        "info-a1",
        "info-b1"
    ),
    (
        "info-a2",
        "info-b2"
    );
INSERT
IGNORE INTO
        db2.table1(
            a,
            b
        )
    VALUES(
        "info-a1",
        "info-b1"
    ),
    (
        "info-a2",
        "info-b2"
    );

测试类

@DataJpaTest(properties = "spring.test.database.replace=NONE")
@Isolated
@Import(value = {DbService.class, Db1JpaConfiguration.class, Db2JpaConfiguration.class})
class DbServiceIT {
  @Autowired private DbService sut;

  public static Stream<Arguments> data() {
    return Stream.of(
        Arguments.of("info-a1", List.of("info-b1", "info-b1")),
        Arguments.of("info-a2", List.of("info-b2", "info-b2")),
        Arguments.of("xxx-yyy", List.of("", "")));
  }

  @ParameterizedTest
  @MethodSource("data")
  void find(final String a, final List<String> expected) {
    final var result = sut.find(a);
    Assertions.assertThat(result).containsExactlyInAnyOrderElementsOf(expected);
  }

  @ParameterizedTest
  @MethodSource("data")
  void update(final String a, final List<String> expected) {
    final var newValue = "new info";
    sut.update(a, newValue);

    final var result = sut.find(a);
    Assertions.assertThat(result).hasSameSizeAs(expected).allMatch(r -> r.equals(newValue));
  }
}

问题解答 #

1. 错误原因 #

核心问题出在事务上下文不匹配和@Modifying注解的缺失配置：

• 你的@Modifying方法没有绑定对应的事务管理器：DbService的@Transactional默认只会关联主数据源（db1）的事务管理器，调用repo2.updateByA时，db2的EntityManager未处于有效事务中，导致执行更新操作时抛出异常。
• @Modifying注解未指定缓存清理：执行JPQL更新后，JPA一级缓存中的数据不会自动刷新，若后续读取会出现数据不一致，同时也会触发JPA对更新操作的合法性校验失败。

2. 同一事务更新多数据源方案 #

方案一：使用JTA分布式事务（保证原子性，推荐）

该方案能实现跨数据源的事务原子性，一个数据源更新失败时所有操作都会回滚。

1. 添加JTA依赖（以Atomikos为例）

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jta-atomikos</artifactId>
</dependency>

2. 修改数据源配置为XA类型
   以db1为例，修改Db1JpaConfiguration中的数据源Bean：

@Bean
public DataSource db1DataSource() {
    HikariXADataSource xaDataSource = db1DataSourceProperties()
            .initializeDataSourceBuilder()
            .type(HikariXADataSource.class)
            .build();
    AtomikosDataSourceBean atomikosDataSource = new AtomikosDataSourceBean();
    atomikosDataSource.setXaDataSource(xaDataSource);
    atomikosDataSource.setUniqueResourceName("db1");
    return atomikosDataSource;
}

db2配置做相同修改。
3. 配置全局JTA事务管理器

@Configuration
@EnableTransactionManagement
public class JtaTransactionConfig {
    @Bean
    public PlatformTransactionManager transactionManager() {
        return new JtaTransactionManager();
    }
}

4. 服务类指定使用JTA事务

@Transactional(transactionManager = "transactionManager")
public void update(final String a, final String b) {
    repo1.updateByA(a, b);
    repo2.updateByA(a, b);
}

方案二：手动绑定事务管理器（简单场景，无原子性保证）

适合不需要严格原子性的场景，每个数据源操作绑定自己的事务管理器：

1. 在Repository的@Modifying方法上指定事务管理器
   修改Db1Table1Repository：

@Modifying(clearAutomatically = true)
@Transactional(transactionManager = "db1TransactionManager")
@Query("UPDATE Db1Table1Entity e SET e.b = :b WHERE e.a = :a")
void updateByA(@Param("a") String a, @Param("b") String b);

修改Db2Table1Repository：

@Modifying(clearAutomatically = true)
@Transactional(transactionManager = "db2TransactionManager")
@Query("UPDATE Db2Table1Entity e SET e.b = :b WHERE e.a = :a")
void updateByA(@Param("a") String a, @Param("b") String b);

2. 服务类保留@Transactional即可（注意：此方式无法保证分布式事务原子性，一个更新失败另一个不会回滚）

通用修复：补充@Modifying配置 #

无论哪种方案，都需要给@Modifying添加clearAutomatically = true，避免一级缓存导致的数据不一致：

@Modifying(clearAutomatically = true)

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