What are the required C3P0 settings for hibernate in order to avoid Deadlocks

Question

I use Hibernate together with MySQL 5.1.30.

I have the next libraries:

• c3p0-0.0.1.2.jar
• mysql-connector-java-5.0.3-bin.jar
• hibernate3.jar

I use a hibernate.cfg.xml for configuration:

<!DOCTYPE hibernate-configuration PUBLIC     "-//Hibernate/Hibernate Configuration DTD 3.0//EN"     "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">  <hibernate-configuration>     <session-factory>         <!-- Database connection settings -->         <property name="connection.driver_class">org.gjt.mm.mysql.Driver</property>           <property name="connection.url">jdbc:mysql://localhost/fooDatatbase</property>     <property name="connection.username">foo</property>     <property name="connection.password">foo123</property>          <!-- Use the C3P0 connection pool provider -->     <property name="hibernate.c3p0.min_size">5</property>     <property name="hibernate.c3p0.max_size">20</property>     <property name="hibernate.c3p0.timeout">300</property>     <property name="hibernate.c3p0.max_statements">50</property>     <property name="hibernate.c3p0.idle_test_periods">3000</property>                 <!-- SQL dialect -->         <property name="dialect">org.hibernate.dialect.MySQLDialect</property>          <!-- Enable Hibernate's automatic session context management -->         <property name="current_session_context_class">thread</property>          <!-- Disable the second-level cache  -->         <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>          <!-- Echo all executed SQL to stdout -->         <property name="show_sql">true</property>          <mapping resource="databaselayer/mail/Mail.hbm.xml"/>         <mapping resource="databaselayer/courses/Course.hbm.xml"/>         <mapping resource="databaselayer/price/Price.hbm.xml"/>                 <mapping resource="databaselayer/contact/Contact.hbm.xml"/>         <mapping resource="databaselayer/artists/Musician.hbm.xml"/>         <mapping resource="databaselayer/concerts/Concert.hbm.xml"/>              <mapping resource="databaselayer/welcome/Welcome.hbm.xml"/>         <mapping resource="databaselayer/information/Information.hbm.xml"/>                                  </session-factory> </hibernate-configuration> 

In the JAVA persistance with hibernate book, c3p0 configuration options are explained:

• hibernate.c3p0.min_size This is the minimum number of JDBC connections that C3P0 keeps ready at all times
• hibernate.c3p0.max_size This is the maximum number of connections in the pool. An exception is thrown at runtime if this number is exhausted.
• hibernate.c3p0.timeout You specify the timeout period (in this case, 300 seconds) after which an idle connection is removed from the pool).
• hibernate.c3p0.max_statements Maximum Number of statements that will be cached. Caching of prepared statements is essential for best performance with Hibernate.
• hibernate.c3p0.idle_test_periods This is the iddle time in seconds before a connection is automatically validated.

I use Java 1.5.0_09 and tomcat 6.0. I have three applications deployed in tomcat. Each of them uses hibernate with a configuration file almost equivalent the shown above (only username, databasename, password and the mapping resoruces change).

Unfortunately with the above settings, after some hours running i get some nasty Deadlock errors which end killing tomcat.

Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@2437d -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@1dc5cb7 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@9cd2ef -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@4af355 -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:07 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run WARNING: com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@1275fcb -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks! Jan 22, 2009 3:29:35 PM com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run 

This seems to be an error several people already got. I changed my settings trying to follow the workaround described herehttp://forum.hibernate.org/viewtopic.php?p=2386237 to:

<property name="hibernate.c3p0.acquire_increment">1</property> <property name="hibernate.c3p0.min_size">0</property> <property name="hibernate.c3p0.max_size">48</property> <property name="hibernate.c3p0.timeout">0</property> <property name="hibernate.c3p0.max_statements">0</property> 

With the new settings, I do not get Deadlocks, but I get:

WARNING: SQL Error: 0, SQLState: 08S01 Jan 24, 2009 5:53:37 AM org.hibernate.util.JDBCExceptionReporter logExceptions SEVERE: Communications link failure due to underlying exception:   ** BEGIN NESTED EXCEPTION **   java.io.EOFException  STACKTRACE:  java.io.EOFException     at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1913) 

Does anyone knows what I am doing wrong, and how I can setup c3p0 correctly?

Answer 1

Actually this is probably too late, but the problem is quite simple: hibernate.c3p0.idle_test_periods must not be higher than hibernate.c3p0.timeout or connections closed by the database will not be properly detected.

Moreover, the deadlock detection warnings look like some part of your code is not properly returning the connections to the pool (i.e. session.close())

The MysqlIO exceptions occur when your application idles and MySQL closes the connection on the server. Now if C3P0 does not properly check whether a connection is still actually connected you get the EOFExceptions.

I hope this might be helpful.

Answer 2

There is no definitive answer to this question, as it changes from application to application depending on the usage & load pattern.

First point is refer the link https://www.hibernate.org/214.html, since it seems you've done that and gone ahead. here are a few tips;

• numHelperThreads : Helper threads that don't hold contended locks. Spreading these operations over multiple threads
• maxStatements : The size of c3p0's global PreparedStatement cache.
• maxStatementsPerConnection : The number of PreparedStatements c3p0 will cache for a single pooled Connection.
• maxAdministrativeTaskTime : Parameter that force a call to the task thread's interrupt() method if a task exceeds a set time limit

First three parameter can improve or reduce the performance based on the value set where as forth parameter can interrupt the thread after set limit and give a change to run to other thread.

Approximate values

• numHelperThreads = 6
• maxStatements =100
• maxStatementsPerConnection = 12
• maxAdministrativeTaskTime = need to sufficient time so that heavy query can run on production

maxStatements and maxStatementsPerConnection should be tested for few months as few posting point to dead lock because of these parameter.

Also referring to these links will be useful;

• http://www.mchange.com/projects/c3p0/index.html#configuration_properties
• http://www.mchange.com/projects/c3p0/index.html#hibernate-specific