C3p0 failed to reconnect the database when the database is down

I've encountered an issue when configuring the c3p0 settings. The senario is: I have one java project which will be started at 3:00 am everyday and then connect to the database to do something. Sometimes, the database may be down at that time and may be recovered after 2 or 3 hours.
so, I need to make the program try to reconnect the database in a specific time interval to see whether the database is ok, until it can successfully connect to the database. I've tried to configure the c3p0 to reconnect the database infinitely until success, but seems it stuck into some deadlock. Below is my c3p0 settings. I use c3p0 v0.9.1 with hibernate in Spring framework, the database is DB2.

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
    <property name="driverClass" value="${jdbc.driverClassName}"/>
    <property name="jdbcUrl" value="${jdbc.url}"/>
    <property name="user" value="root"/>
    <property name="password" value="root"/>

    <property name="acquireRetryAttempts" value="0"/>

    <property name="acquireRetryDelay" value="10000"/>

    <property name="maxIdleTime"value="60"/>
    <property name="minPoolSize" value="5" />
    <property name="maxPoolSize" value="200"/>
    <property name="idleConnectionTestPeriod" value="30" />
    <property name="preferredTestQuery" value="values(1)" />
</bean>

When I run the program, it failed and the error log is shown below:

[WARN]: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@21eb3f -- APPARENT DEADLOCK!!! 
Creating emergency threads for unassigned pending tasks!
[WARN]: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@21eb3f -- APPARENT DEADLOCK!!! 
Complete Status:
 Managed Threads: 3
 Active Threads: 3
 Active Tasks: 
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@15e796d (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@176150c (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@15fc793 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
 Pending Tasks: 
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@127bd04
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1ea8fc0
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@83969e
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1159092
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@c69203
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@9c035a
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@a0416a
Pool thread stack traces:
  ...
[WARN] [2017-09-04  ThreadPoolAsynchronousRunner.processReplacedThreads() ] Task com.mchange.v2.resourcepol.BasicResourcePool$AcquireTask@15e796d 
(in deadlocked PoolThread) failed to complete in maximum time 60000ms. Trying interrupt().
[WARN] [2017-09-04  ThreadPoolAsynchronousRunner.processReplacedThreads() ] Task com.mchange.v2.resourcepol.BasicResourcePool$AcquireTask@176150c 
(in deadlocked PoolThread) failed to complete in maximum time 60000ms. Trying interrupt().
[WARN] [2017-09-04  ThreadPoolAsynchronousRunner.processReplacedThreads() ] Task com.mchange.v2.resourcepol.BasicResourcePool$AcquireTask@15fc793 
(in deadlocked PoolThread) failed to complete in maximum time 60000ms. Trying interrupt().

[WARN] [2017-09-04 BasicResourcePool$AcquireTask.run()] com.mchange.v2.resourcepool.BasicResourcePool@53b9e444 -- Thread unexpectedly interrupted
while performing an acquisition attempt.
java.lang.InterruptedException: sleep interrupted
   at java.lang.Thread.sleep(Native Method)
   at com.mchange.v2.resourcepol.BasicResourcePool$AcquireTask.run (BasicResourcePool.java:1805)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
[WARN] [2017-09-04 BasicResourcePool$AcquireTask.run()] com.mchange.v2.resourcepool.BasicResourcePool@53b9e444 -- Thread unexpectedly interrupted
while performing an acquisition attempt.
java.lang.InterruptedException: sleep interrupted
   at java.lang.Thread.sleep(Native Method)
   at com.mchange.v2.resourcepol.BasicResourcePool$AcquireTask.run (BasicResourcePool.java:1805)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
[WARN] [2017-09-04 BasicResourcePool$AcquireTask.run()] com.mchange.v2.resourcepool.BasicResourcePool@53b9e444 -- Thread unexpectedly interrupted
while performing an acquisition attempt.
java.lang.InterruptedException: sleep interrupted
   at java.lang.Thread.sleep(Native Method)
   at com.mchange.v2.resourcepol.BasicResourcePool$AcquireTask.run (BasicResourcePool.java:1805)
   at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

Could you please help to correct my if the settings has anything wrong? Thank you in advance!

Solution

First, you shouldn't be using c3p0-0.9.1. That's ancient. The current version is 0.9.5.2.

The issue here is that, when your database is down, attempts to connect are not failing with an Exception, they are hanging. So c3p0's Thread pool is filling with attempts to acquire Connections that neither succeed nor fail but hang indefinitely. Once the Thread pool is completely saturated and blocked for a while, c3p0 declares an APPARENT DEADLOCK and you see what you see.

The best thing to do is to fix whatever is amiss in your network or server that causes attempts to connect to the database to hang rather than succeed or fail. If you can resolve this, your problem will probably go away.

If you can't resolve this, you may be able to ickily work around the problem with a c3p0 setting, maxAdministrativeTaskTime. If you set this, after the number of seconds you define, c3p0 will consider any hanging tasks (like your Connection acquisition attempts) to be broken, and will try to force their failure by calling interrupt() on the Threads the tasks have hung. If you are lucky, your frozen acquire tasks will fail with an InterruptedException and life will go on.

If you do use maxAdministrativeTaskTime, you'll want to set a value significantly longer than Connection to your database could reasonably require (while the DBMS is up and available). You'll probably also want to increase numHelperThreads from its default value of 3, so that the not endlessly hanging but still sluggish acquire tasks have more Threads to play with before they saturate the whole Thread pool and provoke a deadlock.