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

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