【springboot 多数据源下，开启事务后，数据源切换失败】

爱被打了一巴掌 2023-09-26 20:49 130阅读 0赞

### 问题： ###

使用 @Transactional 注解后，多数据源切换失败

### 原因： ###

当我们配置了事物管理器和拦截Service中的方法后，每次执行Service中方法前会开启一个事务，并且同时会缓存一些东西：DataSource、SqlSessionFactory、Connection等，所以，我们在外面再怎么设置要求切换数据源也没用，因为Conneciton都是从缓存中拿的，所以我们要想能够顺利的切换数据源，实际就是能够动态的根据DatabaseType获取不同的Connection，并且要求不能影响整个事物的特性。也就是说springboot的声明式事务需要重写Transaction。

### 解决：新建两个类 ###

**MultiDataSourceTransaction.java类：**

package com.ylz.bjyf.common.multipledatasource;
    
    import groovy.util.logging.Slf4j;
    import org.apache.ibatis.transaction.Transaction;
    
    @Slf4j
    public class MultiDataSourceTransaction implements Transaction {
        
    
        private final DataSource dataSource;
    
        private Connection mainConnection;
    
        private String mainDatabaseIdentification;
    
        private ConcurrentMap<String, Connection> otherConnectionMap;
    
    
        private boolean isConnectionTransactional;
    
        private boolean autoCommit;
    
    
        public MultiDataSourceTransaction(DataSource dataSource) {
        
            notNull(dataSource, "No DataSource specified");
            this.dataSource = dataSource;
            otherConnectionMap = new ConcurrentHashMap<>();
            mainDatabaseIdentification = DynamicDataSourceContextHolder.getMainDateSource();
        }
    
    
        /**
         * {@inheritDoc}
         */
        @Override
        public Connection getConnection() throws SQLException {
        
            String databaseIdentification = DynamicDataSourceContextHolder.getDataSourceType();
            if (null == databaseIdentification || databaseIdentification.equals(mainDatabaseIdentification)) {
        
                if (mainConnection != null) {
        
                    return mainConnection;
                } else {
        
                    openMainConnection();
                    mainDatabaseIdentification = databaseIdentification;
                    return mainConnection;
                }
            } else {
        
                if (!otherConnectionMap.containsKey(databaseIdentification)) {
        
                    try {
        
                        Connection conn = dataSource.getConnection();
                        otherConnectionMap.put(databaseIdentification, conn);
                    } catch (SQLException ex) {
        
                        throw new CannotGetJdbcConnectionException("Could not get JDBC Connection", ex);
                    }
                }
                return otherConnectionMap.get(databaseIdentification);
            }
    
        }
    
    
        private void openMainConnection() throws SQLException {
        
            this.mainConnection = DataSourceUtils.getConnection(this.dataSource);
            this.autoCommit = this.mainConnection.getAutoCommit();
            this.isConnectionTransactional = DataSourceUtils.isConnectionTransactional(this.mainConnection, this.dataSource);
    
            if (log.isDebugEnabled()) {
        
                log.debug(
                        "JDBC Connection ["
                                + this.mainConnection
                                + "] will"
                                + (this.isConnectionTransactional ? " " : " not ")
                                + "be managed by Spring");
            }
        }
    
        /**
         * {@inheritDoc}
         */
        @Override
        public void commit() throws SQLException {
        
            if (this.mainConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
        
                if (log.isDebugEnabled()) {
        
                    log.debug("Committing JDBC Connection [" + this.mainConnection + "]");
                }
                this.mainConnection.commit();
                for (Connection connection : otherConnectionMap.values()) {
        
                    connection.commit();
                }
            }
        }
    
        /**
         * {@inheritDoc}
         */
        @Override
        public void rollback() throws SQLException {
        
            if (this.mainConnection != null && !this.isConnectionTransactional && !this.autoCommit) {
        
                if (log.isDebugEnabled()) {
        
                    log.debug("Rolling back JDBC Connection [" + this.mainConnection + "]");
                }
                this.mainConnection.rollback();
                for (Connection connection : otherConnectionMap.values()) {
        
                    connection.rollback();
                }
            }
        }
    
        /**
         * {@inheritDoc}
         */
        @Override
        public void close() throws SQLException {
        
            DataSourceUtils.releaseConnection(this.mainConnection, this.dataSource);
            for (Connection connection : otherConnectionMap.values()) {
        
                DataSourceUtils.releaseConnection(connection, this.dataSource);
            }
        }
    
        @Override
        public Integer getTimeout() throws SQLException {
        
            return null;
        }
    }

**MultiDataSourceTransactionFactory类**

public class MultiDataSourceTransactionFactory extends SpringManagedTransactionFactory {
        
    
        @Override
        public Transaction newTransaction(DataSource dataSource, TransactionIsolationLevel level,
                                          boolean autoCommit) {
        
            return new MultiDataSourceTransaction(dataSource);
        }
    }

**在mybatis-plus配置中添加配置：**

@Configuration
    public class MybatisPlusConfig {
        
    
        private String mapperLocations = "classpath*:mapper/**/*.xml";
    
        @Bean("sqlSessionFactory")
        @DependsOn({
        "dynamicDataSource"})
        @Primary
        public MybatisSqlSessionFactoryBean sqlSessionFactory(DataSource dynamicDataSource) {
        
            MybatisSqlSessionFactoryBean sessionFactoryBean = new MybatisSqlSessionFactoryBean();
            sessionFactoryBean.setDataSource(dynamicDataSource);
            sessionFactoryBean.setConfigLocation(new ClassPathResource("/mybatis/mybatis-config.xml"));
            sessionFactoryBean.setMapperLocations(ResourceUtil.getPathMatchingResource(mapperLocations));
            //事务配置
            sessionFactoryBean.setTransactionFactory(new MultiDataSourceTransactionFactory());
            return sessionFactoryBean;
        }
    }

**最后代码中引入：**

@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Throwable.class)

### 需要注意的是： ###

在spring的文档中说道，spring声明式事务管理默认对非检查型异常和运行时异常进行事务回滚，而对检查型异常（try catch）则不进行回滚操作：  
故而如果异常被try｛｝catch｛｝了，事务就不回滚了，如果想让事务回滚必须再往外抛try｛｝catch｛throw Exception｝。  
**启用事务后，主数据源 可以正常回滚，而 副数据源 无法回滚。原因是，项目的默认数据源是主数据源，在开启事务时，使用的是默认数据源，无论后面切换到哪个数据源，事务只有在主数据源上是开启的，事务回滚与提交也只操作主数据源。**

参考链接：[https://blog.csdn.net/gaoshili001/article/details/79378902][https_blog.csdn.net_gaoshili001_article_details_79378902]  
参考链接微信：[https://mp.weixin.qq.com/s/PaLxmgH7R-aUOy2lOzZyFw][https_mp.weixin.qq.com_s_PaLxmgH7R-aUOy2lOzZyFw]

[https_blog.csdn.net_gaoshili001_article_details_79378902]: https://blog.csdn.net/gaoshili001/article/details/79378902
[https_mp.weixin.qq.com_s_PaLxmgH7R-aUOy2lOzZyFw]: https://mp.weixin.qq.com/s/PaLxmgH7R-aUOy2lOzZyFw