java - 数据库宕机时C3p0重连数据库失败

Question

我在配置 c3p0 设置时遇到问题。情况是:我有一个java项目，每天凌晨3:00启动，然后连接到数据库做一些事情。有时，数据库可能当时就宕机了，可能会在2、3小时后恢复。
所以，我需要让程序在特定的时间间隔内尝试重新连接数据库，看看数据库是否ok，直到能够成功连接数据库。我尝试配置 c3p0 以无限地重新连接数据库直到成功，但似乎陷入了僵局。以下是我的 c3p0 设置。我在Spring框架中使用c3p0 v0.9.1和hibernate，数据库是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>

当我运行该程序时，它失败了，错误日志如下所示:

[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)

如果设置有问题，您能帮忙纠正一下吗？预先感谢您!

Answer 1

首先，您不应该使用 c3p0-0.9.1。那是古老的。当前版本是0.9.5.2。

这里的问题是，当数据库关闭时，连接尝试不会因异常而失败，而是会挂起。因此，c3p0 的线程池充满了获取连接的尝试，这些连接既不成功也不失败，而是无限期挂起。一旦线程池完全饱和并阻塞一段时间，c3p0 就会声明明显的死锁，您就会看到您所看到的。

最好的办法是修复网络或服务器中的任何错误，这些错误会导致连接到数据库的尝试挂起而不是成功或失败。如果您能解决这个问题，您的问题可能就会消失。

如果您无法解决此问题，您也许可以使用 c3p0 设置 maxAdministrativeTaskTime 来解决该问题。 。如果您设置了此项，则在您定义的秒数之后，c3p0 将认为任何挂起的任务(例如您的连接获取尝试)被破坏，并尝试通过调用 interrupt() 来强制其失败任务挂起的线程。如果幸运的话，您卡住的获取任务将因 InterruptedException 失败，并且生活将继续。

如果您确实使用maxAdministrativeTaskTime，您将需要设置一个比数据库连接合理要求的时间长得多的值(当 DBMS 已启动且可用时)。您可能还希望增加 numHelperThreads 的默认值 3，以便那些不会无休止地挂起但仍然缓慢的获取任务在它们使整个线程池饱和并引发一个线程之前有更多的线程可以使用。陷入僵局。