Search code examples
sqlc3p0database-deadlocks

C3p0 APPARENT DEADLOCK exception even though statement caching is disabled

I am using C3p0 connection pool and I am observing following exception in my server. 

 com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@ed866f -- APPARENT DEADLOCK!!! Complete Status:
    Managed Threads: 1
    Active Threads: 1
    Active Tasks:
        com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@995ec2 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
    Pending Tasks: 
            com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@11c3ceb
            com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@e392ed
            com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@c33853
            com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@a6f77d
            com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1c25bfc
            com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@12534a9
            com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1a4564c
Pool thread stack traces:
        Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
                oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:1338)
                com.mchange.v2.c3p0.impl.NewPooledConnection.cleanupUncachedStatements(NewPooledConnection.java:651)
                com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:539)
                com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:234)
                com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:470)
                com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:964)
               com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

My configuration is

<property name="initialPoolSize" value="1" />
    <property name="minPoolSize" value="1" />
    <property name="maxPoolSize" value="20" />
    <property name="maxIdleTime" value="240" />
    <property name="checkoutTimeout" value="60000" />
    <property name="acquireRetryAttempts" value="0" />
    <property name="acquireRetryDelay" value="1000" />
    <property name="debugUnreturnedConnectionStackTraces" value="true" />
    <property name="unreturnedConnectionTimeout" value="300" />
    <property name="numHelperThreads" value="1" />
    <property name="preferredTestQuery" value="SELECT 1 FROM DUAL" />

After some googing I found following threads. https://forum.hibernate.org/viewtopic.php?t=947246

It is suggested to disable the statement caching to avoid this problem. But in my configuration I didn't enable the statement caching(maxStatements, maxStatementsPerConnection are 0 by default).

Please suggest any alternatives.

Solution

  • Don't set numHelperThreads to 1.

    If you set numHelperThreads to 1, any slow task will trigger an APPARENT DEADLOCK. c3p0 relies on an internal Thread pool. One thread does not make a pool. Let numHelperThreads have at least its default value of 3.

    Specifically what is going on in your stack trace is that an attempt by the Connection pool to close() a Connection is taking long time, for some reason. There could be a real problem here, if this happens a lot, but it could just be an occasional slow operation, which a reasonably sized Thread pool would just handle and shrug off.

    Good luck!