SpringBoot 多数据源无法并发执行的问题

╰+攻爆jí腚メ 2024-03-27 16:19 61阅读 0赞

#### SpringBoot 多数据源无法并发执行的问题 ####

*  1 问题描述
 *  2 原因
 *  3 解决方法
 *  4 相关排查指令
 *  4 相关代码

## 1 问题描述 ##

在配置多数据源后，如果配置了定时任务，同时调用两个数据源的Mapper进行查询，会出现卡死的现象。

## 2 原因 ##

猜测原因，是因为数据库源Bean并不是在创建完成后就连接数据库，而是在进行查询的时候才会连接，如果在某一时刻同时调用两个数据源进行查询，且两个数据源都没有进行首次连接，那么会造成卡死现象。

## 3 解决方法 ##

在两个数据源的Bean初始化完成后，手动连接数据库，确保在调用前已经完成连接，这样就可以确保不会卡死。

## 4 相关排查指令 ##

*  查询 Java 堆栈信息：`jstack -l 线程号 > 文件名`
    
     *  作用：在项目卡住不动时，检查到疑似数据库连接卡死，猜测有可能是由于两个数据库同时连接导致卡死，进而试验能否在 Bean 创建完成后就先连接数据库。

## 4 相关代码 ##

*  数据源1（MySQL）：
    
        package com.xxx.xxx.config;
        
        import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
        import lombok.extern.slf4j.Slf4j;
        import org.apache.ibatis.session.SqlSessionFactory;
        import org.mybatis.spring.SqlSessionTemplate;
        import org.mybatis.spring.annotation.MapperScan;
        import org.springframework.beans.factory.annotation.Qualifier;
        import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
        import org.springframework.boot.context.properties.ConfigurationProperties;
        import org.springframework.context.annotation.Bean;
        import org.springframework.context.annotation.Configuration;
        import org.springframework.context.annotation.PropertySource;
        import org.springframework.jdbc.core.JdbcTemplate;
        import org.springframework.jdbc.datasource.DataSourceTransactionManager;
        
        import javax.annotation.PostConstruct;
        import javax.annotation.Resource;
        import javax.sql.DataSource;
        
        /**
         * 初始化 MySQL 数据库源
         */
        @Configuration
        @MapperScan(basePackages = "com.xxx.xxx.mysql.dao", sqlSessionFactoryRef = "mysqlSqlSessionFactory")
        @PropertySource({
              "classpath:jdbc.properties"})
        @Slf4j
        public class MysqlDataSourceConfig {
              
            /**
             * 必须指定是那个数据源，否则可能会错误注入别的数据源
             */
            @Resource(name = "mysqlDataSource")
            private DataSource dataSource;
        
            /**
             * 在创建完 Bean 后需要先建立连接，否则在后面多线程执行任务会卡死
             */
            @PostConstruct
            public void init() throws Exception {
              
                dataSource.getConnection(); // 关键代码：手动进行连接
                log.info("[MySQL 数据库源] - 初始化完毕");
            }
        
            @Bean(name = "mysqlDataSource")
            @ConfigurationProperties(prefix = "spring.datasource.mysql")
            public DataSource mysqlDataSource() {
              
                return DataSourceBuilder.create().build();
            }
        
            // JdbcTemplate
            @Bean(name = "mysqlJdbcTemplate")
            public JdbcTemplate mysqlJdbcTemplate(@Qualifier("mysqlDataSource") DataSource dataSource) {
              
                return new JdbcTemplate(dataSource);
            }
        
            //事务管理
            @Bean(name = "mysqlTransactionManager")
            public DataSourceTransactionManager mysqlTransactionManager(@Qualifier("mysqlDataSource") DataSource dataSource) {
              
                return new DataSourceTransactionManager(dataSource);
            }
        
            @Bean(name = "mysqlSqlSessionFactory")
            public SqlSessionFactory sqlSessionFactory(@Qualifier("mysqlDataSource") DataSource dataSource) throws Exception {
              
                MybatisSqlSessionFactoryBean factoryBean = new MybatisSqlSessionFactoryBean();
                factoryBean.setDataSource(dataSource);
                factoryBean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true); // 添加驼峰命名转换
                return factoryBean.getObject();
            }
        
            @Bean(name = "mysqlSqlSessionTemplate")
            public SqlSessionTemplate mysqlSqlSessionTemplate(@Qualifier("mysqlSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
              
                return new SqlSessionTemplate(sqlSessionFactory);
            }
        }
 *  数据源2（Oracle）：
    
        package com.xxx.xxx.config;
        
        import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
        import lombok.extern.slf4j.Slf4j;
        import org.apache.ibatis.session.SqlSessionFactory;
        import org.mybatis.spring.SqlSessionTemplate;
        import org.mybatis.spring.annotation.MapperScan;
        import org.springframework.beans.factory.annotation.Qualifier;
        import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
        import org.springframework.boot.context.properties.ConfigurationProperties;
        import org.springframework.context.annotation.Bean;
        import org.springframework.context.annotation.Configuration;
        import org.springframework.context.annotation.Primary;
        import org.springframework.context.annotation.PropertySource;
        import org.springframework.jdbc.core.JdbcTemplate;
        import org.springframework.jdbc.datasource.DataSourceTransactionManager;
        
        import javax.annotation.PostConstruct;
        import javax.annotation.Resource;
        import javax.sql.DataSource;
        
        /**
         * 初始化 Oracle 数据库源
         */
        @Configuration
        @MapperScan(basePackages = {
              "com.xxx.xxx.oracle.dao"}, sqlSessionFactoryRef = "oracleSqlSessionFactory")
        @PropertySource({
              "classpath:jdbc.properties"})
        @Slf4j
        public class OracleDataSourceConfig {
              
            /**
             * 必须指定是那个数据源，否则可能会错误注入别的数据源
             */
            @Resource(name = "oracleDataSource")
            private DataSource dataSource;
        
            /**
             * 在创建完 Bean 后需要先建立连接，否则在后面多线程执行任务会卡死
             */
            @PostConstruct
            public void init() throws Exception {
              
                dataSource.getConnection(); // 关键代码：手动进行连接
                log.info("[Oracle 数据库源] - 初始化完毕");
            }
        
            @Bean(name = "oracleDataSource")
            @ConfigurationProperties(prefix = "spring.datasource.oracle")
            @Primary
            public DataSource oracleDataSource() {
              
                return DataSourceBuilder.create().build();
            }
        
            // JdbcTemplate
            @Bean(name = "oracleJdbcTemplate")
            public JdbcTemplate oracleJdbcTemplate(@Qualifier("oracleDataSource") DataSource dataSource) {
              
                return new JdbcTemplate(dataSource);
            }
        
            //事务管理
            @Bean(name = "oracleTransactionManager")
            @Primary
            public DataSourceTransactionManager oracleTransactionManager(@Qualifier("oracleDataSource") DataSource dataSource) {
              
                return new DataSourceTransactionManager(dataSource);
            }
        
            @Bean(name = "oracleSqlSessionFactory")
            MybatisSqlSessionFactoryBean masterSessionFactory(@Qualifier("oracleDataSource") DataSource dataSource) throws Exception {
              
                MybatisSqlSessionFactoryBean mybatisPlus = new MybatisSqlSessionFactoryBean();
                mybatisPlus.setDataSource(dataSource);
                mybatisPlus.getObject().getConfiguration().setMapUnderscoreToCamelCase(true); // 添加驼峰命名转换
                return mybatisPlus;
            }
        
            @Bean(name = "oracleSqlSessionTemplate")
            @Primary
            public SqlSessionTemplate oracleSqlSessionTemplate(@Qualifier("oracleSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
              
                return new SqlSessionTemplate(sqlSessionFactory);
            }
        }
 *  数据库配置文件：
    
        # Mysql 数据源
        spring.datasource.mysql.driver-class-name=com.mysql.cj.jdbc.Driver
        spring.datasource.mysql.url=jdbc:mysql://xxx.xxx.xxx.xxx:3306/database?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=GMT%2B8&useSSL=false
        spring.datasource.mysql.username=username
        spring.datasource.mysql.password=password
        # Oracle 数据源
        spring.datasource.oracle.driver-class-name=oracle.jdbc.driver.OracleDriver
        spring.datasource.oracle.url=jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1650))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVICE_NAME = xx))))
        spring.datasource.oracle.username=username
        spring.datasource.oracle.password=password
        
        spring.datasource.mysql.test-while-idle=true
        spring.datasource.mysql.test-on-Borrow=false
        spring.datasource.mysql.test-on-return=false
        spring.datasource.mysql.validation-query=SELECT 1 FROM DUAL
        spring.datasource.mysql.validation-query-timeout=1
        spring.datasource.mysql.time-between-eviction-runs-millis=600000
        spring.datasource.mysql.min-evictable-idle-time-millis=900000
        spring.datasource.mysql.num-tests-per-eviction-run=100
        
        spring.datasource.oracle.test-while-idle=true
        spring.datasource.oracle.test-on-Borrow=false
        spring.datasource.oracle.test-on-return=false
        spring.datasource.oracle.validation-query=SELECT 1 FROM DUAL 
        spring.datasource.oracle.validation-query-timeout=1
        spring.datasource.oracle.time-between-eviction-runs-millis=600000
        spring.datasource.oracle.min-evictable-idle-time-millis=900000
        spring.datasource.oracle.num-tests-per-eviction-run=100