c3p0 hangs in awaitAvailable with hibernate

Question

I have console application that hangs during execution. Here is my configuration:

    cfg.setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver");
    cfg.setProperty("hibernate.connection.url", "jdbc:mysql://localhost:3306/db?user=db&password=db");
    cfg.setProperty("hibernate.connection.username", "db");
    cfg.setProperty("hibernate.connection.password", "db");
    cfg.setProperty("hibernate.connection.pool_size", "5");
    cfg.setProperty("hibernate.connection.autocommit", "false");
    cfg.setProperty("hibernate.c3p0.min_size", "5");
    cfg.setProperty("hibernate.c3p0.max_size", "20");
    cfg.setProperty("hibernate.c3p0.timeout", "300");
    cfg.setProperty("hibernate.c3p0.max_statements", "50");
    cfg.setProperty("hibernate.c3p0.idle_test_period", "3000");

Here is my stacktrace:

"main" prio=10 tid=0x000000000168f800 nid=0x1c37 in Object.wait() [0x00007fa60d0ad000]
   java.lang.Thread.State: WAITING (on object monitor)
        at java.lang.Object.wait(Native Method)
        - waiting on <0x00000007400f4c68> (a com.mchange.v2.resourcepool.BasicResourcePool)
        at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1315)
        at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
        - locked <0x00000007400f4c68> (a com.mchange.v2.resourcepool.BasicResourcePool)
        at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
        at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
        at org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:84)
        at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:281)
        at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:297)
        at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169)
        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67)
        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160)
        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1392)
        at org.kriyak.parser.IndexArchiveRapid.indexFile(IndexArchiveRapid.java:70)
        at org.kriyak.parser.IndexArchiveRapid.main(IndexArchiveRapid.java:53)

I open only one conencton and it doesn't seem that I leak them. And also I use one thread. I haven't adjusted any mysql settings except memory usage. Mysql works fine from console. Why can this happen? Is this c3p0 error?

Syncytium

Is that the only thread in your application? – Unbend

yes, it is the only thread – Syncytium

Answer 1

does this happen immediately, or after a while? that is, do checkouts initially succeed, but then hang like this? if so, it looks like a Connection leak. please try setting c3p0 params unreturnedConnectionTimeout and debugUnreturnedConnectionStackTraces to see if there is a leak. See http://www.mchange.com/projects/c3p0/#configuring_to_debug_and_workaround_broken_clients , http://www.mchange.com/projects/c3p0/#unreturnedConnectionTimeout , http://www.mchange.com/projects/c3p0/#debugUnreturnedConnectionStackTraces .

if this happens immediately, if no Connections are successfully checked out, the question is whether the pool ever succeeds at acquiring Connections. by default, if it never does succeed, after about 30 seconds your thread should break with a failure. (it doesn't look like you've done this, but if for example you'd set acquireRetryAttempts to zero, c3p0 might hang indefinitely waiting for Connections.)

to debug c3p0 issues, it's helpful to capture the version and config information that c3p0 dumps to logs at INFO level on pool initialization.

good luck!

Sophia

I wasn't closing the underlying connection, which was explicitly requested in my case. – Deb

Answer 2

Also, you haven't seem to initialize the checkoutTime parameter for c3p0, which specifies the amount of time a client should wait for acquiring a connection from the connection pool.

see http://www.mchange.com/projects/c3p0/#checkoutTimeout

Counteroffensive

Answer 3

You may need to increase c3p0.numHelperThreads. The helper threads are responsible for creating new database connections and adding them to the pool. If there aren't enough threads to keep up with demand then application threads will be stuck waiting in awaitAvailable(). To confirm this is the case look at all the HelperThreads and see if they are all in use and in the process of connecting to the database.

Hiro