How to determine c3p0 max_statements

Question

How to determine c3p0 max_statements

I am wondering how to correctly determine which value to use for c3p0 max_statements. I have experienced some caching deadlocks that seem to point to my max_statements configuration based on all SO Q&A I have read.

I am using mysql and there seems to be a deadlock when I am executing multiple threads where I have 4 active threads.

My config

<property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.max_size">50</property>
<property name="hibernate.c3p0.idle_test_period">1800</property>
<property name="hibernate.c3p0.timeout">3600</property>

An exception

[WARN] async.ThreadPoolAsynchronousRunner com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@72df1587 -- APPARENT DEADLOCK!!! Complete Status:
    Managed Threads: 3
    Active Threads: 3
    Active Tasks:
        com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@e877a61
            on thread: C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#2
        com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@109b1150
            on thread: C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#0
        com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask@3eb42946
            on thread: C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#1
    Pending Tasks:
        com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask@52729f95
Pool thread stack traces:
    Thread[C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#0,5,main]
        com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:2765)
        com.mysql.jdbc.StatementImpl.close(StatementImpl.java:541)
        com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:53)
        com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask.run(GooGooStatementCache.java:938)
        com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
    Thread[C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#1,5,main]
        com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:2765)
        com.mysql.jdbc.StatementImpl.close(StatementImpl.java:541)
        com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:53)
        com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask.run(GooGooStatementCache.java:938)
        com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)
    Thread[C3P0PooledConnectionPoolManager[identityToken->1hge0wd9a1o1iea71i8u346|1a799bb]-HelperThread-#2,5,main]
        com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:2765)
        com.mysql.jdbc.StatementImpl.close(StatementImpl.java:541)
        com.mchange.v1.db.sql.StatementUtils.attemptClose(StatementUtils.java:53)
        com.mchange.v2.c3p0.stmt.GooGooStatementCache$StatementDestructionManager$1UncheckedStatementCloseTask.run(GooGooStatementCache.java:938)
        com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)

So my question is how to determine what these values should be. I'm sure this should be done without guessing.

Articles I read

should i activate build c3p0?

How to track and prevent deadlock introduced in c3po running in separate processes?

+3

mysql hibernate c3p0

Code junkie Jul 29. 15 at 15:25

source to share

1 answer

Steve waldman · Answer 1 · 2015-07-29T16:38:00+0000

To resolve deadlocks related to Statement caching in Oracle / jTDS / mySQL, make sure you are using recent c3p0 (0.9.5.1 is the current version) and see statementCacheNumDeferredCloseThreads and Report Pool Configuration .

TL; DR set config param

<property name="hibernate.c3p0.statementCacheNumDeferredCloseThreads">1</property>

The exact meaning max_statements

is only related to this problem. If it is max_statements

too small, you will unnecessarily reject assertions, and this fragility issue PreparedStatement.close()

will appear more frequently in some drivers.

However, your value for is hibernate.c3p0.max_statements

too low for a pool of maxPoolSize

50. Even after you fix the deadlock issue, disabling statements will reduce or remove any performance benefit from the statement cache. To compute a good value for hibernate.c3p0.max_statements

(which maps to c3p0.maxStatements

), count the number of individual PreparedStatements that are used frequently in your application and multiply by maxPoolSize

(or in your case hibernate.c3p0.max_size

). Or alternatively, just set the hibernate.c3p0.maxStatementsPerConnection

number of different PreparedStatements your application uses frequently.

See maxStatements , maxStatementsPerConnection , and Customizing Reporting .

How to determine c3p0 max_statements

More articles: