druid连接池获取连接超时异常

Question

项目使用spring+springmvc+hibernate，数据库使用oracle11.2.0.1.0,允许的最大连接数为300，数据库服务器防火墙是关闭的。项目中有个定时任务（每天凌晨执行）使用jdbcTemplate同步数据。druid连接池异常信息如下：

[org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-3] WARN  com.alibaba.druid.pool.DruidDataSource - get connection timeout retry : 1
[2019-11-02 03:00:02.002] [org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-3] ERROR org.quartz.core.JobRunShell - Job DEFAULT.syncSyldDataDetail threw an unhandled Exception: 
org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 1000, active 0, maxActive 350, creating 1
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:371) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:578) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at com.aqkk.task.SyncSyldData.start(SyncSyldData.java:33) ~[classes/:?]
    at com.aqkk.task.SyncSyldDataJob.executeInternal(SyncSyldDataJob.java:17) ~[classes/:?]
    at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:75) ~[spring-context-support-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202) [quartz-2.3.0.jar:?]
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) [quartz-2.3.0.jar:?]
Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 1000, active 0, maxActive 350, creating 1
    at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1512) ~[druid-1.1.10.jar:1.1.10]
    at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:1255) ~[druid-1.1.10.jar:1.1.10]
    at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:5007) ~[druid-1.1.10.jar:1.1.10]
    at com.alibaba.druid.filter.stat.StatFilter.dataSource_getConnection(StatFilter.java:680) ~[druid-1.1.10.jar:1.1.10]
    at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:5003) ~[druid-1.1.10.jar:1.1.10]
    at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1233) ~[druid-1.1.10.jar:1.1.10]
    at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:1225) ~[druid-1.1.10.jar:1.1.10]
    at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:90) ~[druid-1.1.10.jar:1.1.10]
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:151) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:115) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:78) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    ... 7 more
[2019-11-02 03:00:02.002] [org.springframework.scheduling.quartz.SchedulerFactoryBean#0_Worker-3] ERROR org.quartz.core.ErrorLogger - Job (DEFAULT.syncSyldDataDetail threw an exception.
org.quartz.SchedulerException: Job threw an unhandled exception.
    at org.quartz.core.JobRunShell.run(JobRunShell.java:213) [quartz-2.3.0.jar:?]
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573) [quartz-2.3.0.jar:?]
Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Failed to obtain JDBC Connection; nested exception is com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 1000, active 0, maxActive 350, creating 1
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:81) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:371) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:578) ~[spring-jdbc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at com.aqkk.task.SyncSyldData.start(SyncSyldData.java:33) ~[classes/:?]
    at com.aqkk.task.SyncSyldDataJob.executeInternal(SyncSyldDataJob.java:17) ~[classes/:?]
    at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:75) ~[spring-context-support-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202) ~[quartz-2.3.0.jar:?]
    ... 1 more
Caused by: com.alibaba.druid.pool.GetConnectionTimeoutException: wait millis 1000, active 0, maxActive 350, creating 1
    at com.alibaba.druid.pool.DruidDataSource.getConnectionInternal(DruidDataSource.java:1512) ~[druid-1.1.10.jar:1.1.10]
    at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:1255) ~[druid-1.1.10.jar:1.1.10]

数据源配置信息如下：

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" 
  destroy-method="close">
    <property name="url" value="${db.jdbcUrl}"/>
    <property name="username" value="${db.user}"/>
    <property name="password" value="${db.password}"/>
    <property name="driverClassName" value="${db.driver}"/>
    <!-- 配置初始化大小、最小、最大 -->
    <property name="initialSize" value="1"/>
    <property name="minIdle" value="20"/>
    <property name="maxActive" value="350"/>
    <!-- 配置获取连接等待超时的时间 -->
    <property name="maxWait" value="1000"/>
    <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
    <property name="timeBetweenEvictionRunsMillis" value="10000"/>
    <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
    <property name="minEvictableIdleTimeMillis" value="30000"/>
    <property name="testWhileIdle" value="true"/>
    <property name="validationQuery" value="SELECT 1 FROM DUAL"/>
    <property name="testOnBorrow" value="false"/>
    <property name="testOnReturn" value="false"/>
    <!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->
    <property name="poolPreparedStatements" value="true"/>
    <property name="maxPoolPreparedStatementPerConnectionSize" value="20"/>
    <!-- 开启Druid的监控统计功能 -->
    <property name="filters" value="stat" />
</bean>

 

Answer 1

谢谢回复，修改了maxWait和timeBetweenEvictionRunsMillis参数，问题解决了

Answer 2

回复
只设置两个还是会出问题的，出现这个问题的原因是连接池里没有活的连接了，druid自己没有检测出来，然后自己认为还有活的，当获取连接的时候采取检查，最终所有的都是死连接，最后再去创建新的连接，maxWait就是这个过程的时间

Answer 3

回复
druid版本1.1.10，最近四天定时任务都正常执行，先这样跑着吧。后面如果异常再配置keepAlive参数试试

Answer 4

不知道你druid的版本是什么，需要用到后面的版本配置keepAlive参数，前期的druid是不保证连接的存活的，记得是1.0.18以后的才行，建议直接用最新版本

 

Answer 5

谢谢回复，修改了maxWait和timeBetweenEvictionRunsMillis参数，确实问题解决了

Answer 6

maxwait 设长一点试试