APPARENT DEADLOCK c3p0 0.9.5.1 spring

Question

We are facing APPARENT DEADLOCK while using c3p0 0.9.5.1 ( which is latest version of c3p0). following is the connection pool config we are using.

      p:driverClass="${app.jdbc.driverClassReplica}"
      p:jdbcUrl="jdbc:mysql://database,database/dbname"
      p:acquireIncrement="5"
      p:idleConnectionTestPeriod="300"
      p:maxPoolSize="100"
      p:maxStatements="2000"
      p:minPoolSize="10" 
      p:maxIdleTime="1800"
      p:maxConnectionAge="3600"
      p:maxIdleTimeExcessConnections="20"
      p:numHelperThreads="15"
      p:preferredTestQuery="SELECT 1"/>

and Following are the logs

ThreadPoolAsynchronousRunner:743---- com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@70f6e5f5 -- A
PPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! #]
2015-06-20 11:36:15 WARN  ThreadPoolAsynchronousRunner:759---- com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@70f6e5f5 -- A
PPARENT DEADLOCK!!! Complete Status: 
        Managed Threads: 15
        Active Threads: 15
        Active Tasks: 

Every active task looks like

com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@4ec69595
                        on thread: C3P0PooledConnectionPoolManager[identityToken->z8kflt9a33udv812q4fqf|60dffe1d]-HelperThread-#6

Pending Tasks:

com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@2b131ea8
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@7441bdaf
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@80c67ca
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@667202e6
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@471c7e95
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1fba8cac
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@1069807a
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@7e71d200
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@62923eda
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@6f5c8cc4
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@251dd0fa
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@4882e01f
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@848386a
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@3d6fbb65
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@72780365
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@25271699
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@293ca9dd
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@4db40151
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@64c294b1
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@22b02425
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@5a150aed
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1b807bcf
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@10406124
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@72a98ad1
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@58d8da26
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@2a013697
com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask@35a7090c
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@69430e58
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@3162e965
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@54c8ff37
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@57eb9f5d

Pool thread stack traces: All 15 threads are looking like following

Thread[C3P0PooledConnectionPoolManager[identityToken->z8kflt9a33udv812q4fqf|60dffe1d]-HelperThread-#6,5,main]
                com.mysql.jdbc.StatementImpl.close(StatementImpl.java:575)
                com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:53)
                      com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask.run(GooGooStatementCache.java:938)

Whenever this happens, db becomes unresponsive for some time and number of connections increases to db. Is it a issue with c3p0 ?? Will switching to some other jdbc pool like hikaricp or boncecp help ?

Datary

maybe changing the pool implementation will help. you need to figure out the reason for the deadlock because the database may cause this as well. If you have concurrent writes that include table locking the reason could be inside the application itself. You also need to check the database side if locks are present - and then figure out how it came to that. – Neediness

Answer 1

The issue you have is with the Statement cache. Switching to other pools that don't cache Statements might help. But turning off statement caching within c3p0 by setting maxStatements to 0 would help in precisely the same way. If you don't cache statements, you don't have to worry about deadlocks in the Statement cache. But perhaps you enjoy a performance boost from Statement caching.

Fortunately, if you want, you can retain the performance benefit of statement caching, without migrating to a different pool.

The issue is that some DBMS/JDBC drivers can't deal with a Statement getting closed at the same time as its parent Connection is in use. Formally, that ought to be okay, but in practice it is not for some JDBC drivers. When the Statement cache tries to expire a Statement whose parent Connection happens to be in use, the call to close() deadlocks, eventually saturating and freezing the Thread pool.

c3p0 includes a workaround for these fragile drivers.

Set the c3p0 config parameter statementCacheNumDeferredCloseThreads to 1, and c3p0 will neurotically track whether the parent of an expiring Statement is in use, and defer the close() call until it is not. This setting should, hopefully, resolve your issue.

I guess in your Spring XML the config would look like

p:statementCacheNumDeferredCloseThreads="1"

I hope this helps!

Gora

Thanks Steve for the help. I will try it out and post the results. – Datary

It resolved the issue. Thanks for help, i checked documentation and it do talk about this setting. Ideally it should have been set as default. Is there any cons of this ? – Datary

There's the overhead of an extra dedicated Thread per DataSource, and a bit of extra complexity in managing the asynchronous Statement close operations. Those are really the only downsides. – Gora

@EmreTürkiş i'm glad it helped! – Gora

Hello @SteveWaldman, I thought that finally found solution here, but I've got same error even with statementCacheNumDeferredCloseThreads="1". I use Mysql 5.5 and the latest mysql-connector/j 5.1.42. Should note that error never happens with old connector 5.1.11. – Patriarchate

the same error meaning APPARENT DEADLOCK? do you also have the same active tasks? – Gora

Yes, that was APPARENT DEADLoCK. But it's strange there was only one task (Active Threads: 1, Active Task: 1). I can't reproduce it again for complete stack trace, but it was. Without statementCacheNumDeferredCloseThreads=1 topic's issue happens very often, with it I catch it only one time onder load on stage. – Patriarchate

If your pool has only one thread, a mere slow task could provoke detection of a deadlock. Please try setting hibernate.c3p0.numHelperThreads to maybe 8-to-10-ish, given what it looks like will be the scale of your pool. (c3p0's default thread pool size is three; i don't know why you are ending up with a single thread.) – Gora