【Mysql】Could not open JDBC Connection for transaction 异常分析和解决

1.问题:

异常：Could not open JDBC Connection for transaction; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 60000, active 0, maxActive 20, creating 0, createErrorCount 2493

原因：

maxActive/ maxWait: 最大活动连接数，即连接池中最大连接数。当程序超过此连接数再获取连接时，就处于排队状态， 当 排队时间超过maxWait，就会抛出异常。

2.MYSQL配置详解：

initialSize="10"  ：初始化连接，连接池启动时创建的初始化连接数量（默认值为0）

maxActive="80"  ：最大活动连接，连接池中可同时连接的最大的连接数（默认值为8）

minIdle="10"     ： 最小空闲连接，连接池中最小的空闲的连接数，低于这个数量会被创建新的连接（默认为0，该参数越接近maxIdle，性能越好，因为连接的创建和销毁，都是需要消耗资源的；但是不能太大，因为在机器很空闲的时候，也会创建低于minidle个数的连接，类似于jvm参数中的Xmn设置）

maxIdle="60"    ： 最大空闲连接，连接池中最大的空闲的连接数，超过的空闲连接将被释放，如果设置为负数表示不限制（默认为8个，maxIdle不能设置太小，因为假如在高负载的情况下，连接的打开时间比关闭的时间快，会引起连接池中idle的个数上升超过maxIdle，而造成频繁的连接销毁和创建，类似于jvm参数中的Xmx设置)

maxWait="3000"  ： 从池中取连接的最大等待时间，单位ms.当没有可用连接时，连接池等待连接释放的最大时间，超过该时间限制会抛出异常，如果设置-1表示无限等待（默认为无限）

validationQuery = "SELECT 1"     ： 验证使用的SQL语句

testWhileIdle = "true"   ：指明连接是否被空闲连接回收器(如果有)进行检验.如果检测失败,则连接将被从池中去除.

testOnBorrow = "false"  ：  借出连接时不要测试，否则很影响性能。一定要配置，因为它的默认值是true。false表示每次从连接池中取出连接时，不需要执行validationQuery = "SELECT 1" 中的SQL进行测试。若配置为true,对性能有非常大的影响，性能会下降7-10倍。

timeBetweenEvictionRunsMillis = "30000"   ： 每30秒运行一次空闲连接回收器，配置timeBetweenEvictionRunsMillis = "30000"后，每30秒运行一次空闲连接回收器（独立线程）。并每次检查3个连接，如果连接空闲时间超过30分钟就销毁。销毁连接后，连接数量就少了，如果小于minIdle数量，就新建连接，维护数量不少于minIdle，过行了新老更替。

minEvictableIdleTimeMillis = "1800000" ： 池中的连接空闲30分钟后被回收

numTestsPerEvictionRun="3" ： 在每次空闲连接回收器线程(如果有)运行时检查的连接数量

removeAbandoned="true" ：  连接泄漏回收参数，当可用连接数少于3个时才执行

removeAbandonedTimeout="180"  ： 连接泄漏回收参数，180秒，泄露的连接可以被删除的超时值

注意事项
maxIdle值与maxActive值应配置的接近
     当连接数超过maxIdle值后，刚刚使用完的连接（刚刚空闲下来）会立即被销毁。而不是想要的空闲M秒后再销毁起一个缓冲作用。若maxIdle与maxActive相差较大，在高负载的系统中会导致频繁的创建、销毁连接，连接数在maxIdle与maxActive间快速频繁波动，这不是想要的。高负载系统的maxIdle值可以设置为与maxActive相同或设置为-1(-1表示不限制)，让连接数量在minIdle与maxIdle间缓冲慢速波动。

timeBetweenEvictionRunsMillis建议设置值
minIdle要与timeBetweenEvictionRunsMillis配合使用才有用,单独使用minIdle不会起作用。

initialSize="5"，会在tomcat一启动时，创建5条连接，效果很理想。但同时我们还配置了minIdle="10"，也就是说，最少要保持10条连接，那现在只有5条连接，哪什么时候再创建少的5条连接呢？
     1、等业务压力上来了， DBCP就会创建新的连接。
     2、配置timeBetweenEvictionRunsMillis=“时间”,DBCP会启用独立的工作线程定时检查，补上少的5条连接。销毁多余的连接也是同理。

连接销毁的逻辑

DBCP的连接数会在initialSize - minIdle - maxIdle - maxActive  之间变化。变化的逻辑描述如下：
    默认未配置initialSize(默认值是0)和timeBetweenEvictionRunsMillis参数时，刚启动tomcat时，连接数是0。当应用有一个并发访问数据库时DBCP创建一个连接。目前连接数量还未达到minIdle，但DBCP也不自动创建新连接已使数量达到minIdle数量（没有一个独立的工作线程来检查和创建）。随着应用并发访问数据库的增多，连接数也增多，但都与minIdle值无关，很快minIdle被超越，minIdle值一点用都没有。直到连接的数量达到maxIdle值，这时的连接都是只增不减的。 再继续发展，连接数再增多并超过maxIdle时，使用完的连接（刚刚空闲下来的）会立即关闭，总体连接的数量稳定在maxIdle但不会超过maxIdle。
    但活动连接（在使用中的连接）可能数量上瞬间超过maxIdle，但永远不会超过maxActive。这时如果应用业务压力小了，访问数据库的并发少了，连接数也不会减少（没有一个独立的线程来检查和销毁），将保持在maxIdle的数量。

    默认未配置initialSize(默认值是0)，但配置了timeBetweenEvictionRunsMillis=“30000”（30秒）参数时，刚启动tomcat时，连接数是0。马上应用有一个并发访问数据库时DBCP创建一个连接。目前连接数量还未达到minIdle，每30秒DBCP的工作线程检查连接数是否少于minIdle数量，若少于就创建新连接直到达到minIdle数量。
    随着应用并发访问数据库的增多，连接数也增多，直到达到maxIdle值。这期间每30秒DBCP的工作线程检查连接是否空闲了30分钟，若是就销毁。但此时是业务的高峰期，是不会有长达30分钟的空闲连接的，工作线程查了也是白查，但它在工作。到这里连接数量一直是呈现增长的趋势。
    当连接数再增多超过maxIdle时，使用完的连接(刚刚空闲下来)会立即关闭，总体连接的数量稳定在maxIdle。停止了增长的趋势。但活动连接（在使用中的连接）可能数量上瞬间超过maxIdle，但永远不会超过maxActive。
     这时如果应用业务压力小了，访问数据库的并发少了，每30秒DBCP的工作线程检查连接(默认每次查3条)是否空闲达到30分钟(这是默认值)，若连接空闲达到30分钟，就销毁连接。这时连接数减少了，呈下降趋势，将从maxIdle走向minIdle。当小于minIdle值时，则DBCP创建新连接已使数量稳定在minIdle，并进行着新老更替。

3.MYSQL常用配置参考：

DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUsername(username);
        druidDataSource.setUrl(url);
        druidDataSource.setPassword(password);
        druidDataSource.setDriverClassName(className);
        druidDataSource.setInitialSize(20);
        druidDataSource.setMaxActive(80);
        druidDataSource.setMinIdle(20);
        druidDataSource.setMaxWait(60000);
        druidDataSource.setTestOnBorrow(false);
        druidDataSource.setTestWhileIdle(true);
        druidDataSource.setTimeBetweenEvictionRunsMillis(60000);
        druidDataSource.setValidationQuery("SELECT 1");
        druidDataSource.setMinEvictableIdleTimeMillis(1800000);
        druidDataSource.setRemoveAbandoned(true);
        druidDataSource.setRemoveAbandonedTimeout(1800);
        druidDataSource.setFilters("stat,wall,slf4j,config");
        druidDataSource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000");
        druidDataSource.setUseGlobalDataSourceStat(true);