在java se应用程序上使用C3P0进行DEADLOCK

我在Java SE应用程序中使用的c3p0库(版本0.9.5.2)遇到了一个严重问题.

我的应用程序使用线程池通过执行作业来并行化任务.

每个作业使用数据库读取,更新或删除数据至少一次(在非常罕见的情况下,但可能发生)超过10,000次.

因此,我在我的项目c3p0库中包含了一个到数据库的连接池,以便我的线程池中的所有工作者可以同时与它进行交互.

在我的开发环境(OSX 10.11)上运行我的应用程序时没有任何问题,但是当我在生产中运行它(Linux Debian 8)时,我遇到了一个大问题!确实冻结了....

起初它是一个死锁,具有以下跟踪堆栈:

[WARNING] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@479d237b -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
[WARNING] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@479d237b -- APPARENT DEADLOCK!!! Complete Status: 
    Managed Threads: 3
    Active Threads: 3
    Active Tasks: 
        com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@264fb34f
            on thread: C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1adv4kd1qtfdi6|659f3099]-HelperThread-#2
        com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@39a5576b
            on thread: C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1adv4kd1qtfdi6|659f3099]-HelperThread-#1
        com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@5e676544
            on thread: C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1adv4kd1qtfdi6|659f3099]-HelperThread-#0
    Pending Tasks: 
        com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@6848208c
Pool thread stack traces:
    Thread[C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1adv4kd1qtfdi6|659f3099]-HelperThread-#2,5,main]
        sun.nio.ch.EPollArrayWrapper.epollWait(Native Method)
        sun.nio.ch.EPollArrayWrapper.poll(EPollArrayWrapper.java:269)
        sun.nio.ch.EPollSelectorImpl.doSelect(EPollSelectorImpl.java:93)
        sun.nio.ch.SelectorImpl.lockAndDoSelect(SelectorImpl.java:86)
        sun.nio.ch.SelectorImpl.select(SelectorImpl.java:97)
        com.microsoft.sqlserver.jdbc.SocketFinder.findSocketUsingJavaNIO(IOBuffer.java:2438)
        com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2290)
        com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:551)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1962)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1627)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1458)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:772)
        com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
        com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
        com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
        com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
        com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
        com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
        com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
        com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
        com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
        com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
    Thread[C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1adv4kd1qtfdi6|659f3099]-HelperThread-#1,5,main]
        sun.nio.ch.EPollArrayWrapper.epollWait(Native Method)
        sun.nio.ch.EPollArrayWrapper.poll(EPollArrayWrapper.java:269)
        sun.nio.ch.EPollSelectorImpl.doSelect(EPollSelectorImpl.java:93)
        sun.nio.ch.SelectorImpl.lockAndDoSelect(SelectorImpl.java:86)
        sun.nio.ch.SelectorImpl.select(SelectorImpl.java:97)
        com.microsoft.sqlserver.jdbc.SocketFinder.findSocketUsingJavaNIO(IOBuffer.java:2438)
        com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2290)
        com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:551)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1962)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1627)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1458)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:772)
        com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
        com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
        com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
        com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
        com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
        com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
        com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
        com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
        com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
        com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
    Thread[C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1adv4kd1qtfdi6|659f3099]-HelperThread-#0,5,main]
        sun.nio.ch.EPollArrayWrapper.epollWait(Native Method)
        sun.nio.ch.EPollArrayWrapper.poll(EPollArrayWrapper.java:269)
        sun.nio.ch.EPollSelectorImpl.doSelect(EPollSelectorImpl.java:93)
        sun.nio.ch.SelectorImpl.lockAndDoSelect(SelectorImpl.java:86)
        sun.nio.ch.SelectorImpl.select(SelectorImpl.java:97)
        com.microsoft.sqlserver.jdbc.SocketFinder.findSocketUsingJavaNIO(IOBuffer.java:2438)
        com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2290)
        com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:551)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1962)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:1627)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:1458)
        com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:772)
        com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1168)
        com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
        com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
        com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
        com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
        com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
        com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
        com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
        com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
        com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)

随后我根据不同网站上的建议做了一些更改:

System.setProperty("com.mchange.v2.log.MLog", "com.mchange.v2.log.FallbackMLog");
System.setProperty("com.mchange.v2.log.FallbackMLog.DEFAULT_CUTOFF_LEVEL", "WARNING");

// Create db pool
final ComboPooledDataSource cpds = new ComboPooledDataSource() ;

// Driver
cpds.setDriverClass( "com.microsoft.sqlserver.jdbc.SQLServerDriver" ); // loads the jdbc driver                     

// Url
cpds.setJdbcUrl( "jdbc:xxxx://xxxxx:xxxx;database=xxxxx;" );

// Username / Password
cpds.setUser( "xxxx" ) ;
cpds.setPassword( "xxxx" ) ;                                  

// Start size of db pool
cpds.setInitialPoolSize( 8 );

// Min and max db pool size
cpds.setMinPoolSize(  8 ) ;
cpds.setMaxPoolSize( 10 ) ;

// ????
cpds.setNumHelperThreads( 5 ) ;

// Max allowed time to execute statement for a connection
// @See http://stackoverflow.com/questions/14730379/apparent-deadlock-creating-emergency-threads-for-unassigned-pending-tasks
cpds.setMaxAdministrativeTaskTime( 60 ) ;

// ?????
cpds.setMaxStatements( 180 ) ;
cpds.setMaxStatementsPerConnection( 180 ) ;

// ?????
cpds.setUnreturnedConnectionTimeout( 60 ) ;

// ?????
cpds.setStatementCacheNumDeferredCloseThreads(1);

// We make a test : open and close opened connection
cpds.getConnection().close() ;

完成这些更改后,在执行某些作业后,应用程序会冻结几十秒钟,然后显示以下错误消息:

[WARNING] A task has exceeded the maximum allowable task time. Will interrupt() thread [Thread[C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1ao3z0x88z7oi|4dd889bd]-HelperThread-#4,5,main]], with current task: com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@4128b402
[WARNING] Thread [Thread[C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1ao3z0x88z7oi|4dd889bd]-HelperThread-#4,5,main]] interrupted.
[WARNING] A task has exceeded the maximum allowable task time. Will interrupt() thread [Thread[C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1ao3z0x88z7oi|4dd889bd]-HelperThread-#3,5,main]], with current task: com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@5d6aab6d
[WARNING] Thread [Thread[C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1ao3z0x88z7oi|4dd889bd]-HelperThread-#3,5,main]] interrupted.
[WARNING] A task has exceeded the maximum allowable task time. Will interrupt() thread [Thread[C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1ao3z0x88z7oi|4dd889bd]-HelperThread-#0,5,main]], with current task: com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask@70a3328f
[WARNING] Thread [Thread[C3P0PooledConnectionPoolManager[identityToken->z8kfsx9l1ao3z0x88z7oi|4dd889bd]-HelperThread-#0,5,main]] interrupted.

我的问题是:

为什么应用程序在开发环境中完美运行并在生产过程中遇到这些问题？

最重要的是,如何补救它？

当连接达到使用setMaxStatements和setMaxStatementsPerConnection定义的最大语句数时,会发生什么？连接关闭然后另一个接管,而另一个创建？

我不太明白setStatementCacheNumDeferredCloseThreads函数对我的应用程序的影响.

非常感谢你 !祝你有美好的一天.

好.所以.你的基本问题很简单.在您的生产环境中,Connection获取尝试最终会冻结,即它们既没有成功也没有失败,它们只是悬挂.最终,这是您必须调试的内容:为什么当c3p0尝试连接到您的生产数据库时,有时这些调用会Driver.connect()挂起？无论是什么导致c3p0无法控制.你可能会在DBMS端的总连接数上达到限制(不是来自这个应用程序,你maxPoolSize是非常谦虚的,但也许你的生产服务器过度扩展).如果你在较旧的JVM上运行,那么挂起到SQLServer就会出现一个已知的问题,例如看到JDBC连接挂起而没有来自SQL Server 2008的响应r2 Driver.getConnection挂起使用SQLServer驱动程序和Java 1.6.0_29,但我怀疑你是否正在运行Java 6就此而言,我不知道最近的问题.在任何情况下,从您的日志中可以清楚地看到这是正在发生的事情:c3p0正在尝试从DBMS获取Connections,DBMS无限期地挂起,最终所有c3p0的帮助程序线程被挂起的任务所饱和,你看到了APPARENT DEADLOCK.要解决此问题,您必须调试JDBC驱动程序连接到DBMS的尝试有时会挂起的原因.

在搜索随机故障排除帖子后,您所做的大部分事情都与此问题无关.导致日志更改的原因是此设置

cpds.setMaxAdministrativeTaskTime( 60 );

这会以丑陋的方式解决问题.如果某个任务长时间挂起,该设置会使c3p0进入interrupt()正在运行的线程并放弃它.这可以防止死锁,但不能解决它们的原因.

但是,两个日志之间有一个令人惊讶的变化.APPARENT DEADLOCK用"任务超过最大允许任务时间"的报告替换喷射是可以预期的.但有趣的是,在第二个日志中,获取的任务interrupt()不是连接获取尝试,而是连接销毁尝试.我不知道为什么会发生这种变化,但核心问题是相同的:JDBC驱动程序与DBMS交互的尝试无限期冻结,nether成功,也没有及时出现异常.这就是你需要调试的东西.

如果您无法解决问题,您可以解决它.它非常难看,但是如果你减少maxAdministrativeTaskTime(说到30)并增加numHelperThreads(比如说20),你可以在很大程度上消除应用程序暂停,只要冻结很少.增加numHelperThreadsc3p0的线程池在被完全阻止之前可以容忍的冻结任务的数量.减少maxAdministrativeTaskTime会缩短堵塞的寿命.显然,正确的做法是调试JDBC驱动程序和DBMS之间的问题.但如果证明这是不可能的,有时候解决方法是你能做到的最好的.

我会消除(至少现在)这三个设置:

// ?????
cpds.setMaxStatements( 180 ) ;
cpds.setMaxStatementsPerConnection( 180 ) ;

// ?????
cpds.setStatementCacheNumDeferredCloseThreads(1);

前两个转为语句缓存,从应用程序的性能角度来看,这可能是也可能不是.但它们增加了c3p0与DBMS交互的复杂性.SQLServer(在几个数据库中)在多线程使用Connection方面非常脆弱(至少每个早期版本的JDBC规范都应该是合法的,但是太糟糕了).设置statementCacheNumDeferredCloseThreads以1确保Statement缓存不会在Connection正在使用时尝试关闭过期的Statement,从而防止冻结,APPARENT DEADLOCK通常显示为挂起的Statement关闭任务,而不是您的问题.如果打开Statement缓存,请务必将其statementCacheNumDeferredCloseThreads设置1为避免冻结.但最安全,最安全的事情是在调试主要问题之前避免Statement缓存的所有复杂性.您可以稍后恢复这些设置,以测试它们是否可以提高应用程序的性能.(如果您确实重新启用了Statement缓存,我的建议是您只需设置maxStatementsPerConnection,不设置全局maxStatements,或者如果同时设置两者,则将每个连接限制设置为远小于全局限制的值.但是,就目前而言,只需关闭所有这些东西.)

要了解您的具体问题:

为什么应用程序在开发环境中完美运行并在生产过程中遇到这些问题？

这是您想要在调试JDBC驱动程序和DBMS之间的挂起时使用的重要线索.有关生产服务器的信息会导致挂起,而这些挂起不会显示在开发服务器中.这可能只是开发服务器上相对较低的负载和生产服务器上的高负载问题.但是在设置中可能存在其他差异,这些差异提供了关于挂起的线索.

最重要的是,如何补救它？

调试挂起.如果您无法调试挂起,请尝试使用更短maxAdministrativeTaskTime和更大的问题来解决问题numHelperThreads.

当连接达到使用setMaxStatements和setMaxStatementsPerConnection定义的最大语句数时,会发生什么？连接关闭然后另一个接管,而另一个创建？

连接不会达到任何这些功能.这些是描述Statement缓存的参数.当缓存的语句总数达到命中时maxStatements,最近最少使用的缓存语句将被关闭(只是Statement,而不是它的Connection).当连接maxStatementsPerConnection被命中时,该连接最近最少使用的缓存语句将被关闭(但连接本身保持打开和活动状态).

我不太明白setStatementCacheNumDeferredCloseThreads函数对我的应用程序的影响.

如果您正在使用Statement缓存(同样,我建议您暂时将其关闭),此设置可确保close()在其父连接被其他某些线程使用时不会编辑过期语句(参见上文).该设置创建一个专用线程(或线程),其唯一目的是在不再使用Connections时等待,然后仅关闭它们(因此,语句缓存延迟关闭线程).

我希望这有帮助!

更新:您遇到的错误看起来非常像Java 6错误.如果您正在运行Java 6,那么很幸运,修复可能只是将您的生产JVM更新到最新版本的Java 6.