srping boot多数据源事务管理

如果有两个数据源，比如oracle+sqlserver（或者两个mysql库），如何管理事务呢？
业务产生了两条记录，一条插入oracle，另一条插入sqlserver，如何保证事务的原子性？要么同时插入成功，要么同时失败。

maven引入依赖

<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-commons</artifactId>
    <version>2.2.6.RELEASE</version>
</dependency>

配置数据源

主数据源（默认的数据源）

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "oracleEntityManagerFactory", transactionManagerRef = "oracleTransactionManager", basePackages = { "com.sma.oracle.repository" })
public class OracleDatabaseConfiguration {
    @Primary
    @Bean
    @ConfigurationProperties(prefix = "oracle.datasource")
    public DataSourceProperties  oracleDataSourceProperties(@Qualifier("oracleDataSourceProperties() {
        return new DataSourceProperties();
    }
    @Primary
    @Bean
    public DataSource oracleDataSource(@Qualifier("oracleDataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }
    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean oracleEntityManagerFactory(@Qualifier("oracleDataSource") DataSource oracleDataSource, EntityManagerFactoryBuilder builder) {
        return builder.dataSource(oracleDataSource).packages("com.sma.oracle.entity").persistenceUnit("oracle").build();
    }
    @Primary
    @Bean
    public PlatformTransactionManager oracleTransactionManager(@Qualifier("oracleEntityManagerFactory") EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }
}

第二个数据源

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "sqlServerEntityManagerFactory", transactionManagerRef = "sqlServerTransactionManager", basePackages = "com.sma.aml.repository")
public class SqlServerDatabaseConfiguration {
 
    @Bean
    @ConfigurationProperties(prefix = "sqlServer.datasource")
    public DataSourceProperties sqlServerDataSourceProperties() {
        return new DataSourceProperties();
    }
    @Bean
    public DataSource sqlServerDataSource(@Qualifier("sqlServerDataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }


    @Bean
    public EntityManagerFactory  sqlServerEntityManagerFactory(@Qualifier("sqlServerDataSource") DataSource sqlServerDataSource, EntityManagerFactoryBuilder builder) {

        return builder.dataSource(sqlServerDataSource).packages("com.sma.aml.entity").persistenceUnit("sqlserver").build();

    }

    @Bean
    public PlatformTransactionManager sqlServerTransactionManager(@Qualifier("sqlServerEntityManagerFactory") EntityManagerFactory factory) {
        JpaTransactionManager jpa = new JpaTransactionManager(factory);
        jpa.setNestedTransactionAllowed(true);
        jpa.setRollbackOnCommitFailure(true);

        return jpa;
    }
}

application.properties

##application.properties
##SQL Server
sqlserver.datasource.url=jdbc:sqlserver://127.0.0.1;databaseName=TEST
sqlserver.datasource.username=sa
sqlserver.datasource.password=YourStrongPassword$123$db
##Oracle
oracle.datasource.url=jdbc:oracle:thin:@127.0.0.1:1521:test
oracle.datasource.username=root
oracle.datasource.password=YourStrongPassword$123$db

TransactionManagerConfig

使用ChainedTransactionManager解决问题

/**
 * @author Mak Sophea
 * @date : 1/15/2020
 **/
@Configuration
public class TransactionManagerConfig {

    @Bean(name = "chainedTransactionManager")
    public ChainedTransactionManager transactionManager (
            @Qualifier("sqlServerTransactionManager") PlatformTransactionManager sqlTransactionManager,
            @Qualifier("oracleTransactionManager") PlatformTransactionManager oracleTransactionManager) {
        return new ChainedTransactionManager(sqlTransactionManager, oracleTransactionManager);
    }
}

具体业务层使用

/**
 * @author Mak Sophea
 * @date : 1/15/2020
 **/
@Service
@Transactional
public class BackendService {

    //Category table is in SQL Server
    @Autowired
    private CategoryRepository categoryRepository;
 
    //Audit table is in Oracle Server
    @Autowired
    private AuditLogRepository auditLogRepository;

    @Transactional(value = "chainedTransactionManager")
    public void saveIntoDatabase() {
       
       //persist data Sql server
      categoryRepository.save(new record 1);
      //persist data Sql server
      categoryRepository.save(new record 2);
      //..more logic and then at somepoints we got Run time error...
      auditLogRepository.trackAuth(); //oracle database
    
     throws new Exception("sth went wrong, rollback transactions");
     ...
    }
}

如果是声明式事务

@Service
public class BackendService {

    //Category table is in SQL Server
    @Autowired
    private CategoryRepository categoryRepository;
 
    //Audit table is in Oracle Server
    @Autowired
    private AuditLogRepository auditLogRepository;
    @Resource(name="chainedTransactionManager")
    private ChainedTransactionManager chainedTransactionManager; 
  
    public void saveIntoDatabase() {
		DefaultTransactionDefinition def = new DefaultTransactionDefinition();
		def.setName("testStatus");
		def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
		TransactionStatus status = chainedTransactionManager.getTransaction(def);
		try{
			//persist data Sql server
			categoryRepository.save(new record 1);
			//persist data Sql server
			categoryRepository.save(new record 2);
			//..more logic and then at somepoints we got Run time error...
			auditLogRepository.trackAuth(); //oracle database
			//提交事务
			chainedTransactionManager.commit(status);
	    }catch(Exception e){
	    	//回滚事务
	     	chainedTransactionManager.rollback(status);
	    }
    }
}

---

参考文献
Springboot JPA Rollback distributed Transaction with multi databases: https://blog.usejournal.com/springboot-jpa-rollback-transaction-with-multi-databases-53e6f2f143d6