Failing to get connections from C3P0

[Castillo, Bryan]

Some parts of this message have been removed. Learn more about Nabble's security policy.

 

I have an application running which is failing to get connections from C3P0.  This is the stack trace I am seeing.

 

Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.

        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)

        at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)

        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:488)

        at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)

        at org.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:56)

        at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:423)

        ... 24 more

Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.re

sourcepool.BasicResourcePool@16d542a -- timeout at awaitAvailable()

        at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1281)

        at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:521)

        at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:441)

        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:486)

        ... 27 more

 

I went through my logs and was able to see that we had 35 open transactions at that point.  My application is configured to have < 40 active threads.  The max database pool size is configured to 50.  After the error I did see that the pool had 50 connections in it.  Although they were not busy when I looked at the stats through jmx.  In my logs I see that 5 seconds transpired before the exception was seen and the time at which the given thread started processing.  This corresponds to the 5 second timeout I have configured for the checkoutTimeout.  From what I have read and seen in C3P0’s source code, check in of a connection is done asynchronously, so it is possible to have more connections in the pool than the number of threads I have in my applications thread pool  (executor service).  I don’t see how it is possible though for more than 15 check-in events queued up for more than 5 seconds.  I know I only have 3 threads configured for the numHelperThreads and I could bump that, but something still doesn’t seem right.  Is it possible that the idle connection testing could have had an effect?

 

 

These are my current settings.  Any suggestions on what I should change?

 

 

      2   "acquireIncrement" = 3;

      3   "acquireRetryAttempts" = 2;

      4   "acquireRetryDelay" = 1000;

      6   "autoCommitOnClose" = false;

      7   "automaticTestTable" = null;

      8   "breakAfterAcquireFailure" = false;

      9   "checkoutTimeout" = 5000;

     10   "connectionCustomizerClassName" = null;

     11   "connectionTesterClassName" = "com.mchange.v2.c3p0.impl.DefaultConnectionTester";

     18   "idleConnectionTestPeriod" = 100;

     19   "initialPoolSize" = 10;

     20   "maxAdministrativeTaskTime" = 0;

     21   "maxIdleTime" = 1800;

     22   "maxIdleTimeExcessConnections" = 0;

     23   "maxPoolSize" = 50;

     24   "maxStatements" = 0;

     25   "maxStatementsPerConnection" = 0;

     26   "minPoolSize" = 10;

     27   "numBusyConnections" = 0;

     28   "numBusyConnectionsDefaultUser" = 0;

     29   "numConnections" = 26;

     30   "numConnectionsAllUsers" = 26;

     31   "numConnectionsDefaultUser" = 26;

     32   "numFailedCheckinsDefaultUser" = 0;

     33   "numFailedCheckoutsDefaultUser" = 0;

     34   "numFailedIdleTestsDefaultUser" = 0;

     35   "numIdleConnectionsDefaultUser" = 26;

     36   "numThreadsAwaitingCheckoutDefaultUser" = 0;

     37   "numUnclosedOrphanedConnections" = 0;

     38   "numUnclosedOrphanedConnectionsAllUsers" = 0;

     39   "numUnclosedOrphanedConnectionsDefaultUser" = 0;

     40   "numUserPools" = 1;

     41   "overrideDefaultPassword" = null;

     42   "overrideDefaultUser" = null;

     43   "startTimeMillisDefaultUser" = 1245267073398;

     44   "statementCacheNumCheckedOutDefaultUser" = 0;

     45   "statementCacheNumCheckedOutStatementsAllUsers" = 0;

     46   "statementCacheNumConnectionsWithCachedStatementsAllUsers" = 0;

     47   "statementCacheNumConnectionsWithCachedStatementsDefaultUser" = 0;

     48   "statementCacheNumStatementsAllUsers" = 0;

     49   "statementCacheNumStatementsDefaultUser" = 0;

     50   "testConnectionOnCheckin" = false;

     51   "testConnectionOnCheckout" = false;

     52   "threadPoolNumActiveThreads" = 0;

     53   "threadPoolNumIdleThreads" = 3;

     54   "threadPoolNumTasksPending" = 0;

     55   "threadPoolSize" = 3;

     56   "unreturnedConnectionTimeout" = 0;

     57   "upTimeMillisDefaultUser" = 83671359;

     61   "usesTraditionalReflectiveProxies" = false;

 

Thanks.

 

------------------------------------------------------------------------------
Crystal Reports - New Free Runtime and 30 Day Trial
Check out the new simplified licensing option that enables unlimited
royalty-free distribution of the report engine for externally facing
server and web deployment.
http://p.sf.net/sfu/businessobjects
_______________________________________________
c3p0-users mailing list
c3p0-users@...
https://lists.sourceforge.net/lists/listinfo/c3p0-users