title: What are the required C3P0 settings for hibernate in order to avoid Deadlocks
body: <p>I use Hibernate together with MySQL 5.1.30.</p>
  
  <p>I have the next libraries:  </p>
  
  <ul>
  <li><strong>c3p0-0.0.1.2.jar</strong> </li>
  <li><strong>mysql-connector-java-5.0.3-bin.jar</strong></li>
  <li><strong>hibernate3.jar</strong></li>
  </ul>
  
  <p>I use a hibernate.cfg.xml for configuration:  </p>
  
  <pre><code>&lt;!DOCTYPE hibernate-configuration PUBLIC
      "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
      "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"&gt;
  
  &lt;hibernate-configuration&gt;
      &lt;session-factory&gt;
          &lt;!-- Database connection settings --&gt;
          &lt;property name="connection.driver_class"&gt;org.gjt.mm.mysql.Driver&lt;/property&gt; 
  
          &lt;property name="connection.url"&gt;jdbc:mysql://localhost/fooDatatbase&lt;/property&gt;
      &lt;property name="connection.username"&gt;foo&lt;/property&gt;
      &lt;property name="connection.password"&gt;foo123&lt;/property&gt;
  
          &lt;!-- Use the C3P0 connection pool provider --&gt;
      &lt;property name="hibernate.c3p0.min_size"&gt;5&lt;/property&gt;
      &lt;property name="hibernate.c3p0.max_size"&gt;20&lt;/property&gt;
      &lt;property name="hibernate.c3p0.timeout"&gt;300&lt;/property&gt;
      &lt;property name="hibernate.c3p0.max_statements"&gt;50&lt;/property&gt;
      &lt;property name="hibernate.c3p0.idle_test_periods"&gt;3000&lt;/property&gt;       
  
          &lt;!-- SQL dialect --&gt;
          &lt;property name="dialect"&gt;org.hibernate.dialect.MySQLDialect&lt;/property&gt;
  
          &lt;!-- Enable Hibernate's automatic session context management --&gt;
          &lt;property name="current_session_context_class"&gt;thread&lt;/property&gt;
  
          &lt;!-- Disable the second-level cache  --&gt;
          &lt;property name="cache.provider_class"&gt;org.hibernate.cache.NoCacheProvider&lt;/property&gt;
  
          &lt;!-- Echo all executed SQL to stdout --&gt;
          &lt;property name="show_sql"&gt;true&lt;/property&gt;
  
          &lt;mapping resource="databaselayer/mail/Mail.hbm.xml"/&gt;
          &lt;mapping resource="databaselayer/courses/Course.hbm.xml"/&gt;
          &lt;mapping resource="databaselayer/price/Price.hbm.xml"/&gt;        
          &lt;mapping resource="databaselayer/contact/Contact.hbm.xml"/&gt;
          &lt;mapping resource="databaselayer/artists/Musician.hbm.xml"/&gt;
          &lt;mapping resource="databaselayer/concerts/Concert.hbm.xml"/&gt;     
          &lt;mapping resource="databaselayer/welcome/Welcome.hbm.xml"/&gt;
          &lt;mapping resource="databaselayer/information/Information.hbm.xml"/&gt;                             
      &lt;/session-factory&gt;
  &lt;/hibernate-configuration&gt;
  </code></pre>
  
  <p>In the <strong>JAVA persistance with hibernate</strong> book, c3p0 configuration options are explained: </p>
  
  <ul>
  <li><strong>hibernate.c3p0.min_size</strong> This is the minimum number of JDBC connections that C3P0 keeps ready at all times</li>
  <li><strong>hibernate.c3p0.max_size</strong> This is the maximum number of connections in the pool. An exception is thrown at runtime if this number is exhausted.</li>
  <li><strong>hibernate.c3p0.timeout</strong> You specify the timeout period (in this case, 300 seconds) after which an idle connection is removed from the pool).</li>
  <li><strong>hibernate.c3p0.max_statements</strong> Maximum Number of statements that will be cached. Caching of prepared statements is essential for best performance with Hibernate. </li>
  <li><strong>hibernate.c3p0.idle_test_periods</strong> This is the iddle time in seconds before a connection is automatically validated. </li>
  </ul>
  
  <p>I use Java 1.5.0_09 and <strong>tomcat 6.0</strong>. 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). </p>
  
  <p>Unfortunately with the above settings, after some hours running i get some nasty <strong>Deadlock errors</strong> which end killing tomcat. </p>
  
  <pre><code>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
  </code></pre>
  
  <p>This seems to be an error several people already got. I changed my settings trying to follow the workaround described here<a href="http://forum.hibernate.org/viewtopic.php?p=2386237" rel="noreferrer">http://forum.hibernate.org/viewtopic.php?p=2386237</a> to:</p>
  
  <pre><code>&lt;property name="hibernate.c3p0.acquire_increment"&gt;1&lt;/property&gt;
  &lt;property name="hibernate.c3p0.min_size"&gt;0&lt;/property&gt;
  &lt;property name="hibernate.c3p0.max_size"&gt;48&lt;/property&gt;
  &lt;property name="hibernate.c3p0.timeout"&gt;0&lt;/property&gt;
  &lt;property name="hibernate.c3p0.max_statements"&gt;0&lt;/property&gt;
  </code></pre>
  
  <p>With the new settings, I do not get Deadlocks, but I get: </p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Does anyone knows what I am doing wrong, and how I can setup c3p0 correctly?</p>