srping boot多数据源事务管理

川长思鸟来 2022-10-23 12:52 123阅读 0赞

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTIzODYzMTE_size_16_color_FFFFFF_t_70_pic_center]: /images/20221023/8325b3b7c7694a79baea8f4fd3de82e4.png