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

Question

I have a java client server which is supposed to establish connection pool on startup but its failing on timeout error. There are so many threads on this same issue but none of the solutions worked for me

AM using jdk 7 and below is the mchange maven dependency

<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.2.1</version>
</dependency>

jdbc.properties

url=jdbc\:sqlserver\://server\\instance;databaseName\=db
driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
user=admin
password=admin
maxPoolSize=20
minPoolSize=5
acquireIncrement=5

Here is my DAO class which establishes connection-pool on server startup

public class ShareDocDAO {
//..........
 private static Connection getConnection() throws SQLException {
        LOG.info("Getting DB connection");
        ComboPooledDataSource cpds = getPool();

        return cpds.getConnection(); //Line 36:
    }
 private static ComboPooledDataSource getPool() {
        if (pool!=null) {
            return pool;
        }

        ComboPooledDataSource cpds = new ComboPooledDataSource();
        try {
            Properties dbProperties = getDbProperties();

            //loads the jdbc driver
            cpds.setDriverClass(dbProperties.getProperty("driver"));             
            cpds.setJdbcUrl(dbProperties.getProperty("url"));
            cpds.setUser(dbProperties.getProperty("user"));
            cpds.setPassword(dbProperties.getProperty("password"));

            // the settings below are optional -- c3p0 can work with defaults
            cpds.setMinPoolSize(Integer.valueOf(dbProperties.getProperty("minPoolSize")));
            cpds.setAcquireIncrement(Integer.valueOf(dbProperties.getProperty("acquireIncrement")));
            cpds.setMaxPoolSize(Integer.valueOf(dbProperties.getProperty("maxPoolSize")));
            cpds.setCheckoutTimeout(30000);
            cpds.setIdleConnectionTestPeriod(10800);
            cpds.setMaxIdleTime(21600);
            LOG.info("cpds driver "+cpds.getDriverClass()+ " JDBC URL = "+cpds.getJdbcUrl() +" User = "+cpds.getUser()+ " Pwd = "+cpds.getPassword()+ " MinPoolSize "+cpds.getMinPoolSize() +" AcquireIncrement "+cpds.getAcquireIncrement() +" MaxPoolSize "+cpds.getMaxPoolSize());
        } catch (Exception ex) {
            LOG.error("failed to create pool", ex);
        }
        pool = cpds;
        return pool;
    }
}

Here is the stacktrace

[Oct 29 11:58:22] SSLHandshake-3 | ERROR | com.dc.ssltunnel.server.ShareDocDAO | Error querying for User Information
java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:77)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:687)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
    at com.dc.ssltunnel.server.ShareDocDAO.getConnection(ShareDocDAO.java:36)
    at com.dc.ssltunnel.server.ShareDocDAO.checkSerialNumber(ShareDocDAO.java:75)
    at com.dc.ssltunnel.server.MainServer.retrieveOrCreateClient(MainServer.java:95)
    at com.dc.ssltunnel.server.MainServerHandshakeThread.handshake(MainServerHandshakeThread.java:58)
    at com.dc.ssltunnel.server.MainServerHandshakeThread.run(MainServerHandshakeThread.java:71)
    at com.dc.ssltunnel.utilities.threading.ShutdownThreadPoolExecutor$1.run(ShutdownThreadPoolExecutor.java:36)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@3414b7f8 -- timeout at awaitAvailable()
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1416)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:606)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:526)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:755)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:682)
    ... 12 more
[Oct 29 11:58:22] SSLHandshake-3 | DEBUG | com.dc.ssltunnel.server.MainServerHandshakeThread | Handshake thread is done
[Oct 29 11:58:22] SSLHandshake-3 | DEBUG | com.dc.ssltunnel.server.MainServerHandshakeThread | Handshake thread is done
[Oct 29 11:58:22] SSLHandshake-1 | INFO  | com.dc.ssltunnel.server.ShareDocDAO | Getting DB connection
[Oct 29 11:58:22] SSLHandshake-1 | INFO  | com.dc.ssltunnel.server.ShareDocDAO | Getting DB connection
[Oct 29 11:58:22] SSLHandshake-3 | DEBUG | com.dc.ssltunnel.utilities.threading.ShutdownThreadPoolExecutor | RUNNABLE:removing total:2
[Oct 29 11:58:22] SSLHandshake-3 | DEBUG | com.dc.ssltunnel.utilities.threading.ShutdownThreadPoolExecutor | RUNNABLE:removing total:2
[Oct 29 11:58:22] SSLHandshake-1 | ERROR | com.dc.ssltunnel.server.ShareDocDAO | Error querying for User Information
java.sql.SQLException: Connections could not be acquired from the underlying database!
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:689)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
    at com.dc.ssltunnel.server.ShareDocDAO.getConnection(ShareDocDAO.java:36)
    at com.dc.ssltunnel.server.ShareDocDAO.checkSerialNumber(ShareDocDAO.java:75)
    at com.dc.ssltunnel.server.MainServer.retrieveOrCreateClient(MainServer.java:95)
    at com.dc.ssltunnel.server.MainServerHandshakeThread.handshake(MainServerHandshakeThread.java:58)
    at com.dc.ssltunnel.server.MainServerHandshakeThread.run(MainServerHandshakeThread.java:71)
    at com.dc.ssltunnel.utilities.threading.ShutdownThreadPoolExecutor$1.run(ShutdownThreadPoolExecutor.java:36)
    at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
    at java.util.concurrent.FutureTask.run(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: com.mchange.v2.resourcepool.CannotAcquireResourceException: A ResourcePool could not acquire a resource from its primary factory or source.
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1418)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:606)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:526)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:755)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:682)
    ... 12 more

Can someone please help me whats wrong with my config/code?

Answer 1

The problem is Generally

Transaction.Begin() is not called directly or indirectly. so this can be a problem with a session, SessionFactory or configurations

You can always debug this by, doing a Query with your session object, if that test query works then add a transaction to it and begin(), commit() appropriately