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.