C3P0配置错误导致的连接关闭问题

错误日志：

java.sql.SQLException: Invalid state, the PreparedStatement object is closed.
	at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.checkOpen(JtdsPreparedStatement.java:186)
	at net.sourceforge.jtds.jdbc.JtdsStatement.getConnection(JtdsStatement.java:1204)
	at net.sourceforge.jtds.jdbc.JtdsResultSet.getConnection(JtdsResultSet.java:441)
	at net.sourceforge.jtds.jdbc.JtdsResultSet.close(JtdsResultSet.java:502)
	at org.hibernate.jdbc.AbstractBatcher.closeQueryStatement(AbstractBatcher.java:206)
	at org.hibernate.loader.Loader.doQuery(Loader.java:726)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
	at org.hibernate.loader.Loader.doList(Loader.java:2213)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
	at org.hibernate.loader.Loader.list(Loader.java:2099)
	at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
  ...
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:585)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
  ...
	at java.lang.Thread.run(Thread.java:595)
WARN Thread-9 org.hibernate.jdbc.AbstractBatcher - exception clearing maxRows/queryTimeout
java.sql.SQLException: Invalid state, the Connection object is closed.
	at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1659)
	at net.sourceforge.jtds.jdbc.ConnectionJDBC2.commit(ConnectionJDBC2.java:2037)
	at org.hibernate.transaction.JDBCTransaction.commitAndResetAutoCommit(JDBCTransaction.java:139)
	at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:115)
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:606)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:709)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:678)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:321)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:116)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	...
	at java.lang.Thread.run(Thread.java:595)

 

C3P0配置：

c3p0.unreturnedConnectionTimeout=180
c3p0.debugUnreturnedConnectionStackTraces=true

c3p0.maxConnectionAge=3600
c3p0.maxIdleTimeExcessConnections=60

 

放开C3P0的日志：

DEBUG C3P0PooledConnectionPoolManager[identityToken->2y6gb88w1p7nwbl1bqakad|8c5488]-AdminTaskTimer com.mchange.v2.resourcepool.BasicResourcePool - BEGIN check for expired resources.  [com.mchange.v2.resourcepool.BasicResourcePool@9ac272]
DEBUG C3P0PooledConnectionPoolManager[identityToken->2y6gb88w1p7nwbl1bqakad|8c5488]-AdminTaskTimer com.mchange.v2.resourcepool.BasicResourcePool - Removing expired resource: com.mchange.v2.c3p0.impl.NewPooledConnection@b7a60f [com.mchange.v2.resourcepool.BasicResourcePool@9ac272]
INFO C3P0PooledConnectionPoolManager[identityToken->2y6gb88w1p7nwbl1bqakad|8c5488]-AdminTaskTimer com.mchange.v2.resourcepool.BasicResourcePool - A checked-out resource is overdue, and will be destroyed: com.mchange.v2.c3p0.impl.NewPooledConnection@b7a60f
INFO C3P0PooledConnectionPoolManager[identityToken->2y6gb88w1p7nwbl1bqakad|8c5488]-AdminTaskTimer com.mchange.v2.resourcepool.BasicResourcePool - Logging the stack trace by which the overdue resource was checked-out.
java.lang.Exception: DEBUG STACK TRACE: Overdue resource check-out stack trace.
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:555)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:832)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:759)
	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
	at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:82)
	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)
	at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:144)
	at org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:50)
	at $Proxy59.getTransactionIsolation(Unknown Source)
	at org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:173)
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:464)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:377)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:263)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:101)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy37.deleteLogDataBeforeDate(Unknown Source)
	...
	at java.lang.Thread.run(Thread.java:595)

 

根据上面的log信息和C3P0的源代码最后发现导致上述问题的原因是c3p0.unreturnedConnectionTimeout=180这个配置。这个配置规定了一个连接需要释放到连接池中的时间限制，而不管它是否正在被使用。此处就是因为连接使用超过了3分钟，被强制关闭释放导致的。所以删除这个配置即可解决上述问题。

 

而根据C3P0官方文档的建议，当你不清楚操作会持续多久时，就不需要配置这个选项的，原文如下：

Seconds. If set, if an application checks out but then fails to check-in [i.e. close()] a Connection within the specified period of time, the pool will unceremoniously destroy() the Connection. This permits applications with occasional Connection leaks to survive, rather than eventually exhausting the Connection pool. And that's a shame. Zero means no timeout, applications are expected to close() their own Connections. Obviously, if a non-zero value is set, it should be to a value longer than any Connection should reasonably be checked-out. Otherwise, the pool will occasionally kill Connections in active use, which is bad. This is basically a bad idea, but it's a commonly requested feature. Fix your $%!@% applications so they don't leak Connections! Use this temporarily in combination with debugUnreturnedConnectionStackTraces to figure out where Connections are being checked-out that don't make it back into the pool!

详细参见 unreturnedConnectionTimeout