c3p0连接检出有时需要15分钟才能失败

Vladimir 发表于 Dev

弗拉基米尔

使用c3p0遇到问题。在大多数情况下，它工作正常，但在防火墙后的产品环境中，有时无法检出连接。问题是识别连接不可用需要15分钟。池未畅通，因为在15分钟的间隔内正在检查其他连接并愉快地使用它。

日志：

23 Apr 2015 09:08:16.426 [EventProcessor-1] DEBUG c.m.v.c.i.C3P0PooledConnectionPool - Testing PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@5a886282] on CHECKOUT.

15分钟后：

23 Apr 2015 09:23:43.073 [EventProcessor-1] DEBUG c.m.v.c.i.C3P0PooledConnectionPool - Test of PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@5a886282] on CHECKOUT has FAILED. 
java.sql.SQLException: Connection is invalid
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.testPooledConnection(C3P0PooledConnectionPool.java:572) [c3p0-0.9.5.jar:0.9.5]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.finerLoggingTestPooledConnection(C3P0PooledConnectionPool.java:451) [c3p0-0.9.5.jar:0.9.5]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.finerLoggingTestPooledConnection(C3P0PooledConnectionPool.java:443) [c3p0-0.9.5.jar:0.9.5]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.refurbishResourceOnCheckout(C3P0PooledConnectionPool.java:336) [c3p0-0.9.5.jar:0.9.5]
    at com.mchange.v2.resourcepool.BasicResourcePool.attemptRefurbishResourceOnCheckout(BasicResourcePool.java:1727) [c3p0-0.9.5.jar:0.9.5]
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:553) [c3p0-0.9.5.jar:0.9.5]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:756) [c3p0-0.9.5.jar:0.9.5]
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:683) [c3p0-0.9.5.jar:0.9.5]
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) [c3p0-0.9.5.jar:0.9.5]

然后是一些日志：

23 Apr 2015 09:23:43.073 [EventProcessor-1] DEBUG c.m.v.r.BasicResourcePool - A resource could not be refurbished for checkout. [com.mchange.v2.c3p0.impl.NewPooledConnection@5a886282] 
java.sql.SQLException: Connection is invalid
...
23 Apr 2015 09:23:43.074 [EventProcessor-1] DEBUG c.m.v.r.BasicResourcePool - Resource [com.mchange.v2.c3p0.impl.NewPooledConnection@5a886282] could not be refurbished in preparation for checkout. Will try to find a better resource. 
23 Apr 2015 09:23:43.074 [C3P0PooledConnectionPoolManager[identityToken->67oy4j981qzvkd716hgow4|4177fc5c]-HelperThread-#2] DEBUG c.m.v.r.BasicResourcePool - Preparing to destroy resource: com.mchange.v2.c3p0.impl.NewPooledConnection@5a886282 
23 Apr 2015 09:23:43.074 [EventProcessor-1] DEBUG c.m.v.c.i.C3P0PooledConnectionPool - Testing PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@41318736] on CHECKOUT. 
23 Apr 2015 09:23:43.074 [C3P0PooledConnectionPoolManager[identityToken->67oy4j981qzvkd716hgow4|4177fc5c]-HelperThread-#2] DEBUG c.m.v.c.i.C3P0PooledConnectionPool - Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@5a886282 
23 Apr 2015 09:23:43.076 [C3P0PooledConnectionPoolManager[identityToken->67oy4j981qzvkd716hgow4|4177fc5c]-HelperThread-#2] DEBUG c.m.v.c3p0.impl.NewPooledConnection - Failed to close physical Connection: oracle.jdbc.driver.T4CConnection@25145762 
java.sql.SQLRecoverableException: IO Error: Broken pipe
    at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:612) ~[ojdbc6_g-11.2.0.1.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:5094) ~[ojdbc6_g-11.2.0.1.0.jar:11.2.0.1.0]
    at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:642) [c3p0-0.9.5.jar:0.9.5]

c3p0配置：

        ComboPooledDataSource ods = new ComboPooledDataSource();
...
        ods.setInitialPoolSize(5);
        ods.setMinPoolSize(5);
        ods.setMaxPoolSize(10);
        ods.setMaxStatements(50);

        ods.setTestConnectionOnCheckout(true);

所以没有什么太奇异的了。我知道连接丢失是可能的，因此在结帐时测试连接。有什么想法为什么要花费这么长时间来验证/失败连接？我们正在使用Oracle数据库。谢谢。

弗拉基米尔

看起来好像是这样的情况：防火墙终止了连接，根本没有响应发回，即使没有数据的TCP ACK也是如此。在这种情况下，用于验证连接的查询将永远不会返回。这是在套接字/ jdbc驱动程序级别上。

解决方案：