Showing posts with label JDBC. Show all posts
Showing posts with label JDBC. Show all posts

Wednesday, 20 February 2013

APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!

My c3p0 version is
compile 'com.mchange:c3p0:0.9.1.2'

[1] Error 
WARN   run, com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@c3d2e5 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
WARN   run, com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@c3d2e5 -- APPARENT DEADLOCK!!! Complete Status: 
 Managed Threads: 3
 Active Threads: 3
 Active Tasks: 
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1556819 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@254f54 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@17b15ec (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
 Pending Tasks: 
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1dc5047
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@11a9ca3
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@21b8e1
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1f0c306
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@193e3d0
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@5174ad
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1c325b7
Pool thread stack traces:
 Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
  java.lang.Thread.sleep(Native Method)
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
  com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
 Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
  java.lang.Thread.sleep(Native Method)
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
  com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
 Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
  java.lang.Thread.sleep(Native Method)
  com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
  com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)




[2] Pooled DataSource / jpa-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:mvc="http://www.springframework.org/schema/mvc"
 xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
  http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd 
  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd"
 default-autowire="byName">


  <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
  <property name="driverClass" value="${db.driver}"/>
  <property name="jdbcUrl" value="${db.url}"/>
  <property name="user" value="${db.username}"/>
  <property name="password" value="${db.password}"/>
  <property name="minPoolSize" value="${db.poolsize.min}"/>
  <property name="maxPoolSize" value="${db.poolsize.max}"/>
  <property name="maxStatementsPerConnection" value="15"/>
  <property name="idleConnectionTestPeriod" value="3000"/>
 </bean>

</beans>


hibernate.cfg.xml is : 

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
 <session-factory>
  <property name="hibernate.default_batch_fetch_size">16</property>
  <property name="hibernate.max_fetch_depth">5</property>

   <property name="hibernate.cache.use_query_cache">false</property>
  <property name="hibernate.cache.use_second_level_cache">false</property>
  <property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property> 
  
  <!-- property name="show_sql">true</property-->
        <!--property name="format_sql">true</property-->
        <!--property name="use_sql_comments">true</property-->

  </session-factory>
</hibernate-configuration>



db.properties
db.username=root
db.password=mysql55
db.url=jdbc:mysql://localhost/eccount
db.dialect=org.hibernate.dialect.MySQL5Dialect
db.driver=com.mysql.jdbc.Driver
db.generateDdl=false
db.showSql=false

db.persistenceUnit=eccount

db.generateDdl=true
db.poolsize.max=100
db.poolsize.min=10
# turn on statement pooling


[3] PROPOSED SOLUTION

For version 0.9.2 of c3p0 (compile 'com.mchange:c3p0:0.9.2'), 

[1] Configure Statement Pooling
1.1 statementCacheNumDeferredCloseThreads="1"

If statementCacheNumDeferredCloseThreads is > zero, 
the Statement pool will defer physically close()ing cached Statements until its parent Connection is not   in use by any client or internally (in e.g. a test) by the pool itself.
For some JDBC drivers (especially Oracle), attempts to close a Statement freeze if the parent Connection is in use.
This parameter defaults to 0.

Set it to a positive value if you observe "APPARENT DEADLOCKS" realted to Connection close tasks.
Almost always, that value should be one: if you need more than one Thread dedicated solely to Statement destruction, you probably should set maxStatements and/or maxStatementsPerConnection to higher values so you don't churn through cached Statements so quickly.



SOLUTION IN MY CASE
Restart mysql server.

REFERENCES
Configuring Statement Pooling
http://www.mchange.com/projects/c3p0/#configuring_statement_pooling

c3p0 - JDBC3 Connection and Statement Pooling,  http://www.mchange.com/projects/c3p0/#numHelperThreads

What are the required C3P0 settings for hibernate in order to avoid Deadlocks, http://stackoverflow.com/a/3731978/432903

c3p0 apparent deadlock / creating emergency threads, https://forum.hibernate.org/viewtopic.php?t=947246&highlight=apparent+deadlock+c3p0

java+hibernate webservice - apperant deadlock,
http://stackoverflow.com/a/11669290/432903

c3p0 - JDBC3 Connection and Statement Pooling, http://www.mchange.com/projects/c3p0/#configuration_files

Best way of database pooling in spring for production system,
http://stackoverflow.com/a/14548122/432903

http://stackoverflow.com/a/12088523/432903