报错信息

org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.

原因排查

配置了两个数据源，服务一段时间就会报hikari超时的错误，然后卡死。后面把application.yml的多数据源配置跟Configuration文件删掉之后就不复现了，所以确定是多数据源引起的问题。查了一些其他的配置方法

数据源1

@Configuration
@MapperScan(basePackages = "com.demo.mapper",
        sqlSessionTemplateRef = "primSqlSessionTemplate")
public class PrimDataSourceConfiguration {

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource.prim")
    public DataSource primDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @Primary
    public SqlSessionFactory primSqlSessionFactory(@Qualifier("primDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
        return bean.getObject();
    }

    @Bean
    @Primary
    public DataSourceTransactionManager primTransactionManager(@Qualifier("primDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    @Primary
    public SqlSessionTemplate primSqlSessionTemplate(@Qualifier("primSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

原application的配置

datasource:
    druid:
      initial-size: 1
      min-idle: 1
      max-active: 20
      test-on-borrow: true      
    prim:
      jdbcUrl: jdbc:mysql://10.11.11.111:3306/database?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
      username: username
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver

修改后的application配置

修改之后，原来接口5分钟不访问再访问就会超时，现在20分钟不访问都不会超时

datasource:
    prim:
      jdbcUrl: jdbc:mysql://10.11.11.111:3306/database?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
      username: username
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver
      type: com.zaxxer.hikari.HikariDataSource
      read-only: false
      #客户端等待连接池连接的最大毫秒数
      connection-timeout: 60000
      #允许连接在连接池中空闲的最长时间(以毫秒为单位)
      idle-timeout: 60000
      #连接将被测试活动的最大时间量
      validation-timeout: 3000
      #池中连接关闭后的最长生命周期
      max-lifetime: 60000
      #最大池大小
      maximum-pool-size:  60
      #连接池中维护的最小空闲连接数
      minimum-idle: 10
      #从池返回的连接的默认自动提交行为。默认值为true
      auto-commit: true
      #如果您的驱动程序支持JDBC4，我们强烈建议您不要设置此属性
      connection-test-query: SELECT 1
      #自定义连接池名称
      pool-name: MyHikariCP