问题描述
使用c3p0 0.9.5.1(c3p0的最新版本)时，我们面临着APPARENT DEADLOCK.以下是我们正在使用的连接池配置.
We are facing APPARENT DEADLOCK while using c3p0 0.9.5.1 ( which is latest version of c3p0). following is the connection pool config we are using.
p:driverClass="${app.jdbc.driverClassReplica}"
p:jdbcUrl="jdbc:mysql://database,database/dbname"
p:acquireIncrement="5"
p:idleConnectionTestPeriod="300"
p:maxPoolSize="100"
p:maxStatements="2000"
p:minPoolSize="10"
p:maxIdleTime="1800"
p:maxConnectionAge="3600"
p:maxIdleTimeExcessConnections="20"
p:numHelperThreads="15"
p:preferredTestQuery="SELECT 1"/>
和以下是日志
ThreadPoolAsynchronousRunner:743---- com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@70f6e5f5 -- A
PPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! #]
2015-06-20 11:36:15 WARN ThreadPoolAsynchronousRunner:759---- com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@70f6e5f5 -- A
PPARENT DEADLOCK!!! Complete Status:
Managed Threads: 15
Active Threads: 15
Active Tasks:
每个活动任务看起来像
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@4ec69595
on thread: C3P0PooledConnectionPoolManager[identityToken->z8kflt9a33udv812q4fqf|60dffe1d]-HelperThread-#6
待处理任务:
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@2b131ea8
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@7441bdaf
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@80c67ca
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@667202e6
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@471c7e95
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1fba8cac
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@1069807a
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@7e71d200
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@62923eda
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@6f5c8cc4
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@251dd0fa
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@4882e01f
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@848386a
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@3d6fbb65
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@72780365
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@25271699
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@293ca9dd
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@4db40151
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@64c294b1
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@22b02425
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@5a150aed
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@1b807bcf
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@10406124
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@72a98ad1
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@58d8da26
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@2a013697
com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask@35a7090c
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@69430e58
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@3162e965
com.mchange.v2.resourcepool.BasicResourcePool$1RefurbishCheckinResourceTask@54c8ff37
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@57eb9f5d
池线程堆栈跟踪:所有15个线程都像在关注
Pool thread stack traces:All 15 threads are looking like following
Thread[C3P0PooledConnectionPoolManager[identityToken->z8kflt9a33udv812q4fqf|60dffe1d]-HelperThread-#6,5,main]
com.mysql.jdbc.StatementImpl.close(StatementImpl.java:575)
com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:53)
com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask.run(GooGooStatementCache.java:938)
无论何时发生这种情况，db都会在一段时间内无响应，并且与db的连接数会增加. c3p0是否有问题?切换到hikaricp或boncecp之类的其他jdbc池会有所帮助吗?
Whenever this happens, db becomes unresponsive for some time and number of connections increases to db. Is it a issue with c3p0 ?? Will switching to some other jdbc pool like hikaricp or boncecp help ?
推荐答案
语句缓存是您遇到的问题.切换到不缓存语句的其他池可能会有所帮助.但是通过将maxStatements设置为0来关闭c3p0中的语句缓存将以完全相同的方式提供帮助.如果不缓存语句，则不必担心语句缓存中的死锁.但是，也许您可以享受语句缓存带来的性能提升.
The issue you have is with the Statement cache. Switching to other pools that don't cache Statements might help. But turning off statement caching within c3p0 by setting maxStatements to 0 would help in precisely the same way. If you don't cache statements, you don't have to worry about deadlocks in the Statement cache. But perhaps you enjoy a performance boost from Statement caching.
幸运的是，如果需要，您可以保留语句缓存的性能优势，而无需迁移到其他池.
Fortunately, if you want, you can retain the performance benefit of statement caching, without migrating to a different pool.
问题是某些DBMS/JDBC驱动程序无法处理在使用其父Connection的同时被关闭的Statement.从形式上来说，这应该没问题，但实际上，对于某些JDBC驱动程序而言，这不是必须的.当Statement缓存尝试使父连接正巧在使用中的Statement过期时，对close()的调用将死锁，最终使线程池饱和并冻结.
The issue is that some DBMS/JDBC drivers can't deal with a Statement getting closed at the same time as its parent Connection is in use. Formally, that ought to be okay, but in practice it is not for some JDBC drivers. When the Statement cache tries to expire a Statement whose parent Connection happens to be in use, the call to close() deadlocks, eventually saturating and freezing the Thread pool.
c3p0包括针对这些易碎驱动程序的解决方法.
c3p0 includes a workaround for these fragile drivers.
将c3p0配置参数 statementCacheNumDeferredCloseThreads 设置为1，然后c3p0会从神经上追踪即将使用的到期语句的父级，并延迟close()调用，直到没有使用它为止.希望该设置可以解决您的问题.
Set the c3p0 config parameter statementCacheNumDeferredCloseThreads to 1, and c3p0 will neurotically track whether the parent of an expiring Statement is in use, and defer the close() call until it is not. This setting should, hopefully, resolve your issue.
我猜在您的Spring XML中，配置看起来像
I guess in your Spring XML the config would look like
p:statementCacheNumDeferredCloseThreads="1"
我希望这会有所帮助！
这篇关于明显的死锁c3p0 0.9.5.1弹簧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！