c3p0数据库连接池死锁问题. 解决方案

System.喵

[mw_shl_code=java,true]2016-09-01 11:04:11,165 (Timer-0) [ThreadPoolAsynchronousRunner.java:608:WARN ] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@511ec99a -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
2016-09-01 11:04:11,169 (Timer-0) [ThreadPoolAsynchronousRunner.java:624:WARN ] com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@511ec99a -- APPARENT DEADLOCK!!! Complete Status:
        Managed Threads: 3
        Active Threads: 3
        Active Tasks:
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@431a5401 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@75813011 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@747ae85b (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
        Pending Tasks:
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@312cea1b
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@7bbdabd5
                com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@615c6c6a
                com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@7ad1d515
                com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@65fc94b1
                com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask@298624c6
                com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@25d85876
                com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1e97be63
                com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@7c546545
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@8c4d4c8
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@326d9af7
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@39d8e7fe
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@4f66f25d
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@6fb1e064
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@702fe86
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@55941302
Pool thread stack traces:
        Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
                java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
                java.net.InetAddress$1.lookupAllHostAddr(InetAddress.java:838)
                java.net.InetAddress.getAddressFromNameService(InetAddress.java:1193)
                java.net.InetAddress.getLocalHost(InetAddress.java:1343)
                oracle.jdbc.driver.T4CTTIoauthenticate.setSessionFields(T4CTTIoauthenticate.java:884)
                oracle.jdbc.driver.T4CTTIoauthenticate.<init>(T4CTTIoauthenticate.java:183)
                oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:352)
                oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
                oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
                oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
                oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
                com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
                com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
                com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
                com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
                com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
                com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
                com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
        Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
                java.lang.Object.wait(Native Method)
                java.lang.Object.wait(Object.java:474)
                java.net.InetAddress.checkLookupTable(InetAddress.java:1264)
                java.net.InetAddress.getAddressFromNameService(InetAddress.java:1182)
                java.net.InetAddress.getLocalHost(InetAddress.java:1343)
                oracle.jdbc.driver.T4CTTIoauthenticate.setSessionFields(T4CTTIoauthenticate.java:884)
                oracle.jdbc.driver.T4CTTIoauthenticate.<init>(T4CTTIoauthenticate.java:183)
                oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:352)
                oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
                oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
                oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
                oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
                com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
                com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
                com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
                com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
                com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
                com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
                com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
        Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
                java.lang.Object.wait(Native Method)
                java.lang.Object.wait(Object.java:474)
                java.net.InetAddress.checkLookupTable(InetAddress.java:1264)
                java.net.InetAddress.getAddressFromNameService(InetAddress.java:1182)
                java.net.InetAddress.getLocalHost(InetAddress.java:1343)
                oracle.jdbc.driver.T4CTTIoauthenticate.setSessionFields(T4CTTIoauthenticate.java:884)
                oracle.jdbc.driver.T4CTTIoauthenticate.<init>(T4CTTIoauthenticate.java:183)
                oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:352)
                oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
                oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
                oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
                oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
                com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
                com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
                com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
                com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
                com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
                com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
                com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
                com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
[/mw_shl_code]

1·http://blog.csdn.net/ge_zhiqiang/article/details/8637684

http://blog.csdn.net/strivehard/article/details/6697857

这2篇文章说一些属性可能和死锁有关：maxStatements 和checkoutTimeout

有人说c3p0本身没有问题，导致问题是spring的原因，c3p0作为hibernate官方推荐的连接池，在性能和稳定性上都是中规中矩的。根据以前的使用经验，我判断也不是c3p0的问题。

根据这篇文章的方式修改添加了如下配置

1.  <property name="maxStatements" value="0"/>  

1.  <property name="checkoutTimeout" value="100"/>  

解决办法:

把max_statements设置为0。
c3p0在同时关闭statement和connection的时候，或者关闭他们之间的时间很短的时候，有时候connection并没有被关闭，因为有些preparedstatement还在被cached住。这是c3p0的作者自己说的。

【经测试加以上2个参数无效，还是会死锁】

2·http://www.2cto.com/database/201402/279569.html

这篇文章说是加<property name="preferredTestQuery"><value>SELECT 1</value></property>

大概就是说连接长期不使用时，连接池会自动从数据库中断开该连接，等再次使用时再尝试连接，这个语句就是在断开重连时做检测的

【经测试加上该参数依然无效】

3·http://blog.sina.com.cn/s/blog_53923f940100g6as.html

这篇文章 大概意思是说  导致死锁问题的是spring 的OpenSessionInView

总之，大家看到了，我都说了不要用Spring，如果有兄弟非要较真，我非要用Spring不可呢，没问题，我再给几个提示：

1、如果你用Spring来如上的粗放型控制事务，那么一定不敢用OpenSessionInView模式，如果你不用，然后在每个会导致数据库更新的方法上都标注Spring的@Transactional并且声明需要事务，问题也可能会不见。
2、如果你非要用OpenSessionInView模式，还要用Spring控制事务，那么对不起，无解。最好的结果是让人直接自杀，而不是等待吃饭一直等到饿死。

哑谜打到现在，有兄弟说了，我骂了Spring半天，不就是不能用OpenSessionInView模式吗？但，凡是个像样的牛牛写的web数据库访问指南，都说要用这个模式呢，和Spring有啥关系。

通过了解下面的文章，发现项目没有使用OpenSessionInView模式

http://www.cnblogs.com/liukemng/p/4146245.html

4·下面的文章 让添加配置 maxPerConnectionStatements=100

http://www.iteye.com/problems/41006

【但是经过测试 添加该配置也无效】

5·http://www.oschina.net/question/215595_137688

通过在解决死锁问题的时候发现，出现这个问题的人有很多

一些人说加以上的各种配置的方法 ，他们测试 不再出现死锁了 ，但经过我们测试还是会出现死锁  发送60万数据，最后还是没找到是什么原因导致的死锁。。。

游客，如果您要查看本帖隐藏内容请回复

上一篇：2016年9月9日签到记录贴
下一篇：2016年9月10日签到记录贴