【解决】Quartz定时任务java.sql.SQLRecoverableException: 关闭的连接/IO错误/连接超时

问题描述：本人在做quartz的开发，已经在本地运行了多个程序，都正常。直到测试环境部署上，有一天发现任务阻塞，查看日志如下：2020-02-12 15:35:59.073 [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2] WARNcom.mchange.v1.db.sql.StatementU...

少年阿峣_从零单排

7316人浏览 · 2020-02-11 15:44:44

少年阿峣_从零单排 · 2020-02-11 15:44:44 发布

问题描述：

本人在做quartz的开发，已经在本地运行了多个程序，都正常。直到测试环境部署上，有一天发现任务阻塞，查看日志如下：

2020-02-12 15:35:59.073 [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2] WARN  com.mchange.v1.db.sql.StatementUtils -
                    Statement close FAILED.
java.sql.SQLRecoverableException: 关闭的连接
	at oracle.jdbc.driver.PhysicalConnection.needLine(PhysicalConnection.java:5416)
	at oracle.jdbc.driver.OracleStatement.closeOrCache(OracleStatement.java:1585)
	at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:1570)
	at oracle.jdbc.driver.OracleStatementWrapper.close(OracleStatementWrapper.java:94)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.close(OraclePreparedStatementWrapper.java:82)
	at com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:53)
	at com.mchange.v2.c3p0.stmt.GooGooStatementCache.synchronousDestroyStatement(GooGooStatementCache.java:413)
	at com.mchange.v2.c3p0.stmt.GooGooStatementCache.closeAll(GooGooStatementCache.java:351)
	at com.mchange.v2.c3p0.impl.NewPooledConnection.closeAllCachedStatements(NewPooledConnection.java:673)
	at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:543)
	at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:234)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470)
	at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964)
	at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
2020-02-12 15:35:59.072 [QuartzScheduler_DataPlatformQuartzScheduler-10.3.247.157-test_ClusterManager] ERROR org.quartz.impl.jdbcjobstore.JobStoreTX -
                    ClusterManager: Error managing cluster: Failure identifying failed instances when checking-in: IO 错误: 连接超时 (Read failed)
org.quartz.JobPersistenceException: Failure identifying failed instances when checking-in: IO 错误: 连接超时 (Read failed)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.findFailedInstances(JobStoreSupport.java:3405)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.clusterCheckIn(JobStoreSupport.java:3455)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.doCheckin(JobStoreSupport.java:3315)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.manage(JobStoreSupport.java:3920)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport$ClusterManager.run(JobStoreSupport.java:3957)
Caused by: java.sql.SQLRecoverableException: IO 错误: 连接超时 (Read failed)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1067)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1207)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
	at org.quartz.impl.jdbcjobstore.StdJDBCDelegate.selectSchedulerStateRecords(StdJDBCDelegate.java:2994)
	at org.quartz.impl.jdbcjobstore.JobStoreSupport.findFailedInstances(JobStoreSupport.java:3369)
	... 4 common frames omitted

错误发现，有关闭的连接，连接超时。

排查，首先查看业务逻辑，含有http请求，但是请求发送了，发送后会记录日志，此时是数据库连接超时导致。然而日志打印出来，并不是这里的问题，但是也要注意。之后c3p0日志多，怀疑是连接池问题，没有及时释放，导致资源浪费。

解决：

1、针对业务逻辑里有http，ftp，socket等，设置超时时间。

2、数据库连接池超时时间设置，可用性检测，完善连接池配置（核心）

这里quartz默认使用c3p0，查阅资料发现，可以不用c3p0连接池，quartz 2.2.0版本后，内部也可以用光速连接池 HikariPool

一、如果用c3p0的配置需要添加设置以下几个属性：

<property name="idleConnectionTestPeriod">60</property>
<property name="testConnectionOnCheckin">true</property>
<property name="testConnectionOnCheckout">true</property>

testConnectionOnCheckout ：true表示在每次从pool内checkout连接的时候测试其有效性，这是个同步操作，因此应用端的每次数据库调用，都会先通过测试sql测试其有效性，如果连接无效，会关闭此连接并剔除出pool，并尝试从pool内取其他连接，默认为false，此特性要慎用，会造成至少多一倍的数据库调用。