java.sql.SQLException：客户端检出连接的尝试已超时

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

提问by RanPaul

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

我有一个 java 客户端服务器，它应该在启动时建立连接池，但由于超时错误而失败。在同一问题上有很多线程，但没有一个解决方案对我有用

AM using jdk 7 and below is the mchange maven dependency

AM 使用 jdk 7 及以下是 mchange maven 依赖

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

jdbc.properties

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

这是我的 DAO 类，它在服务器启动时建立连接池

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.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.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?

有人可以帮我配置/代码有什么问题吗？

采纳答案by Lohith Ravi

The problem is Generally

问题是一般

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

Transaction.Begin() 不会直接或间接调用。所以这可能是会话、SessionFactory 或配置的问题

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

您始终可以通过对会话对象执行查询来调试它，如果该测试查询有效，则向其添加事务并适当地开始（），提交（）