Bug #61577 APPARENT DEADLOCK!!!
Submitted: 21 Jun 2011 11:04 Modified: 30 Jun 2011 14:09
Reporter: sube singh Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1 OS:Solaris
Assigned to: CPU Architecture:Any
Tags: APPARENT DEADLOCK!!!

[21 Jun 2011 11:04] sube singh
Description:
2011-06-15 07:32:32,487  WARN [Timer-0] (ThreadPoolAsynchronousRunner.java:608) - com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@1cea2bf -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
2011-06-15 07:32:32,662  WARN [Timer-0] (ThreadPoolAsynchronousRunner.java:624) - com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@1cea2bf -- APPARENT DEADLOCK!!! Complete Status: 
	Managed Threads: 10
	Active Threads: 0
	Active Tasks: 
	Pending Tasks: 
		com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1b8da6c
Pool thread stack traces:
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,]
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,]
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#5,5,]
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#4,5,]
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#7,5,]
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#8,5,]
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#6,5,]
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#3,5,]
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,]
	Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#9,5,]

How to repeat:
I do not the exact steps but my application give extra load on database server.

There are lot of insertion, deletion and select operation going on.
[21 Jun 2011 11:06] sube singh
<Resource auth="Container"
	driverClass="com.mysql.jdbc.Driver"
	maxPoolSize="50"
	minPoolSize="10"
	acquireIncrement="5"
	numHelperThreads="10"
	name="jdbc/DB"
	user="root"
	password=""
	factory="org.apache.naming.factory.BeanFactory"
	type="com.mchange.v2.c3p0.ComboPooledDataSource"
	maxIdleTime="2700"
	idleConnectionTestPeriod="600"
	maxIdleTimeExcessConnections="300"
	jdbcUrl="jdbc:mysql://localhost/mdsdb?autoReconnect=true" />
[21 Jun 2011 11:07] Valeriy Kravchuk
Why do you think this is a deadlock in MySQL server, not your connection pool? Please, upload the entire error log of your MySQL server.
[21 Jun 2011 11:38] sube singh
Could you please guide me how to write correct connection pool? Earlier this problem was coming very frequently. 
But after adding parameter acquireIncrement="5" and numHelperThreads="10" it get resolved for few days.

Not possible to share the application log. 

Please provide the steps for getting the MySQL server log.
[21 Jun 2011 11:49] Valeriy Kravchuk
Error log of MySQL server is a file usually named <hostname>.err (where <hostname> is a hostname of the machine where MySQL server runs) and located in the data directory of MySQL server (datadir). On Solaris you can try to find it using the following command:

find / -name "*.err" -print 2>/dev/null.
[21 Jun 2011 14:15] sube singh
Unfortunately we do not find any error log on MySQL server machine. 
Is MySQL Server error logging be activated somehow? Please provide the steps for enabling error log.
[21 Jun 2011 14:36] Valeriy Kravchuk
Read http://dev.mysql.com/doc/refman/5.1/en/error-log.html about the error log. Check your my.cnf settings.
[21 Jun 2011 14:44] Mark Matthews
Various versions of the C3P0 connection pool have had deadlock issues in the past. Is it possible for you to try swapping in DBCP instead and see if the issue goes away? Are you sure you're using the latest version of C3P0?
[21 Jun 2011 15:51] sube singh
I am using version c3p0-0.9.1.2
[21 Jun 2011 16:08] Valeriy Kravchuk
OK, your c3p0-0.9.1.2 is latest and greatest, but I still see no evidence that this problem has anything to do with MySQL software, especially server, while the bug is reported in MySQL Server category...