MYSQL报错-CannotCreateTransactionException: Could not open JDBC Connection for transaction 问题分析及解决方案

最近遇到一个关于数据库连接池的问题：

报错详情：org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLTransientConnectionException: HikariPool-3 - Connection is not available, request timed out after 30001ms.

大致意思就是创建事务异常，链接超过30001ms没有相应。

为什么会出现这个问题呢：

        经过查看日志发现，只有一个同事的代码在正常输出日志，其他人的接口都是报错：连接数据库超时。于是猜想同事的代码写法有严重的错误。经过排查他在循环里操作数据库（很多处）而且还添加了事务，导致代码运行慢，占用大量的数据库连接（数据库默认连接数是100个）。由于开启了数据库事务，只有提交了事务数据库连接才会释放。所以其他操作数据库都会发生错误。

注意：可能有的同学会说批量查询底不也是逐条查询的吗？其实查询是非常快的，关键是程序与数据库的连接时间比较耗时。经过代码测试得出一下结论：

1. 循环查询1000次，所用的时间：23521ms

2. 循环查询10次，所用的时间：201ms

3. 循环查询1次，所用的时间：21ms

4. 集合1000个条件批量查询1次，所用的时间：245ms

5. 集合10000个条件批量查询1次，所用的时间：523ms

什么是数据库连接数：

数据库连接数一般指数据库连接池。 数据库连接池负责分配、管理和释放数据库的连接。

解决方案：

1. 把在循环里面操作数据库的查询、修改...，放到循环外面做批量操作查询、修改。

2. 由于数据量大，拆分事务。把需要处理的数据分批查询，每一批数据添加一个事务，缩短事务的提交时间。

分析遇到这个错误还有哪些原因呢？

其实在我们开发中可能会存在数据库100个连接数确实不够的情况，这是要经过日志及分析后才能确定的。如果最终发现确实是项目需要更多的连接数，那就要修改数据库连接池相应的配置了，加大数据库的最大连接数。

springboot 2.0 默认连接池是Hikari，我就以这个为例，来修改相应的数据库连接池的配置：可以加大--连接池中维护的最小空闲连接数，解决问题。

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/user1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: root
    password: root
    # Hikari 连接池配置
    hikari:
      # 连接池中维护的最小空闲连接数
      minimum-idle: 100
      # 数据库连接超时时间,默认30秒，即30000
      connection-timeout: 30000
      # 空闲连接存活最大时间，默认600000（10分钟）
      idle-timeout: 1800000
      # 连接池最大连接数，默认是100
      maximum-pool-size: 200
      # 控制从池返回的连接的默认自动提交行为,默认值：true
      auto-commit: true
      # 控制连接池中连接的最长生命周期，值0表示无限生命周期，默认1800000即30分钟
      max-lifetime: 1800000
      #连接将被测试活动的最大时间量
      validation-timeout: 30000
      connection-test-query: SELECT 1
      connection-init-sql: SET NAMES utf8mb4

1.最大连接数：maximum-pool-size

是连接池能申请的最大连接数，如果数据库连接请求超过此数，后面的数据库连接请求将被加入到等待队列中，在队列中的时间大于配置的超时时间会报错。

2. 最小连接数：minimum-idle

是连接池一直保持的数据库连接，所以如果应用程序对数据库连接的使用量不大，将会有大量的数据库连接资源被浪费。

补充：

数据库的连接数是越大越好吗？

显然不是的，经过测试得出连接数加大，操作数据量的效率会降低。

原因：连接数增加，会使操作表的效率降低，锁表，CPU性能都是连接数的瓶颈。

想要了解更多的连接数设置，请看：数据库连接池性能优化，连接数到底应该设置多大？_柠檬不萌的技术博客_51CTO博客