MySQL Bugs: #61577: APPARENT DEADLOCK!!!

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!!!

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.

<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" />

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.

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.

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.

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.

Read http://dev.mysql.com/doc/refman/5.1/en/error-log.html about the error log. Check your my.cnf settings.

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?

I am using version c3p0-0.9.1.2

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...