前言:最近碰见了一个非常稀奇古怪的问题,查遍google和baidu,只有2个人碰见的类似,但都还有差异,就此对c3p0产生
APPARENT DEADLOCK 的现象分析一遍
现象:8月10日晚18:20分左右,当日无发布的情况下,突然抛出一个这个异常,导致系统响应变慢,之后的3天内,均产生大量的异常,系统应用oracle数据库。
异常如下:
- // 片段1:
- Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
- at <span style="color: #0000ff;">org.springframework.orm.ibatis.SqlMapClientTemplate.execute</span>
- (SqlMapClientTemplate.java:204)
- // 片段2:
- Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
- at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
- at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
- at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:527)
- at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
- at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:113)
- at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:190)
- ... 81 more
- Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@5db533b0 -- timeout at awaitAvailable()
- <span style="color: #800080;">at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable</span>
- (BasicResourcePool.java:1317)
- at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
- at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
- at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
- ... 84 more
- // 片段3:
- 2012-08-16 09:11:41,346:WARN Timer-1 com.mchange.v2.async.ThreadPoolAsynchronousRunner - Task com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@430e820d (in deadlocked PoolThread) failed to complete in maximum time 60000ms. Trying interrupt().
- 2012-08-16 09:11:41,346:WARN Timer-1 com.mchange.v2.async.ThreadPoolAsynchronousRunner - Task com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@6d7c45df (in deadlocked PoolThread) failed to complete in maximum time 60000ms. Trying interrupt().
- 2012-08-16 09:11:41,346:WARN Timer-1 com.mchange.v2.async.ThreadPoolAsynchronousRunner - Task com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@79cf6ba0 (in deadlocked PoolThread) failed to complete in maximum time 60000ms. Trying interrupt().
- 2012-08-16 09:11:41,354:WARN Timer-1 com.mchange.v2.async.ThreadPoolAsynchronousRunner - com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@6f853a49 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
- 2012-08-16 09:11:41,356:WARN Timer-1 com.mchange.v2.async.ThreadPoolAsynchronousRunner - com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@6f853a49 -- APPARENT DEADLOCK!!! Complete Status:
- Managed Threads: 3
- Active Threads: 3
- Active Tasks:
- com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@264e25ab (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
- com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@548b1132 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
- com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask@761614fa (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
- Pending Tasks:
- com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@23bb65a7
- com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@640ec944
- com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@6bfc2ae
- com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask@178630f8
- com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask@6af8f5d8
- Pool thread stack traces:
- Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
- <span style="color: #ff0000;">oracle.jdbc.driver.OracleStatement.close</span>
- (OracleStatement.java:1340)
- com.alibaba.china.jdbc.proxy.simple.StatementProxy.close(StatementProxy.java:61)
- com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:41)
- com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask.run(GooGooStatementCache.java:404)
- com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
- Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
- <span style="color: #ff0000;">oracle.jdbc.driver.OracleStatement.close</span>
- (OracleStatement.java:1340)
- com.alibaba.china.jdbc.proxy.simple.StatementProxy.close(StatementProxy.java:61)
- com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:41)
- com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask.run(GooGooStatementCache.java:404)
- com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
- Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
- <span style="color: #ff0000;">oracle.jdbc.driver.OracleStatement.close</span>
- (OracleStatement.java:1340)
- com.alibaba.china.jdbc.proxy.simple.StatementProxy.close(StatementProxy.java:61)
- com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:41)
- com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StatementCloseTask.run(GooGooStatementCache.java:404)
- com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
日志里已经很明确了,无法获取JDBC连接,向SA和DBA询问后,机器网络和数据库都良好。
晕了。。。当日无发布的情况下,运行1年的系统突然抛这个错误,尼玛坑爹啊!
继续分析,翻遍google和百度,发现出现c3p0的DEADLOCK的原因有很多种,很大一部分是网络问题。但我这个,请看“红色”的异常,均在oracle驱动close的时候抛错,如果是网络问题,应该能看见net或socket什么的,不是网络……
这里有3篇文章。
1.c3p0开发者描述(第5楼):https://forum.hibernate.org/viewtopic.php?t=947246&highlight=apparent+deadlock+c3p0
2. c3p0防死锁配置(跟我异常一样):https://forum.hibernate.org/viewtopic.php?p=2390809
其中介绍了maxAdministrativeTaskTime和 numHelperThreads,仔细看这两个配置,第一个会中断你的程序,可能不安全。
3. 这位仁兄分析,他帖子最后 : http://www.iteye.com/topic/1117330
看一下当时出错的系统线程情况:
可以看到,block的线程数很多。
在放大数据库连接池无效,排除jms消息对数据库压力无效的情况下。
换c3p0为dbcp,得以解决(其实是一种绕过问题的方法,从一个坑跳到另一个,但暂时解决了问题)。
附上c3p0配置:
- <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
- <property name="validationQuery">
- <value>select 1 from dual</value>
- </property>
- <property name="driverClassName">
- <value>数据库驱动</value>
- </property>
- <property name="url">
- <value>数据库url</value>
- </property>
- <property name="username">
- <value>用户名</value>
- </property>
- <property name="password">
- <value>密码</value>
- </property>
- <property name="initialSize">
- <value>3</value>
- </property>
- <property name="maxActive">
- <value>20</value>
- </property>
- <property name="maxIdle">
- <value>20</value>
- </property>
- <property name="maxWait">
- <value>1000</value>
- </property>
- <property name="minIdle">
- <value>3</value>
- </property>
- <property name="testWhileIdle"><value>true</value></property>
- <property name="testOnBorrow"><value>false</value></property>
- <property name="timeBetweenEvictionRunsMillis"><value>10000</value></property>
- </bean>
我实验了一下,放大当数据库连接池耗尽时一次获取数据库连接的个数,无效。
而这个参数 c3p0.maxStatements=0 没有设置,因为在生产环境,机会不多。希望有实验成功正在处理解决方法的
人能回复一下解决方案,大家多多交流。
我对此问题的看法:估计是c3p0在很多跟数据库交互很频繁并短促的情况下,c3p0 cache住了准备语句,造成了close时
死锁,死锁的是c3p0内部线程池,并不是数据库,就如第三个帖子中的描述一样。后面分析一下c3p0的源码,再仔细分析。