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

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

Solution 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

Solution 3:

The hibernate.c3p0.idle_test_periods have to be less than h*ibernate.c3p0.timeout* because the first is just only a time value where hibernate check for idle connections and try to close it.

Meanwhile the second is just how much time a connection need to be ejected.

If the idle_test_periods is bigger than timeout parameter than hibernate look for anything that is null or not exist in the system. At least I understood in this way.

Solution 4:

    <property name="hibernate.c3p0.timeout">300</property>     
    <property name="hibernate.c3p0.idle_test_periods">3000</property>       

idle_test_period value should be less than equal of time out value.