mysql-如何确定c3p0 max_statements

我正在使用mysql,当我在有4个活动线程的情况下执行多线程时,似乎会发生死锁.

我的配置

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

例外

[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)

所以我的问题是如何确定这些值.我敢肯定,一定要有办法做到这一点.

我读过的文章

should i activate c3p0 statement pooling?

How to trace and prevent the deadlock appeared in c3po which is running in seperate processes?

TL; DR设置配置参数

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

max_statements的确切值仅与此问题相关.如果max_statements太小,您将不必要地遍历所有语句,并且此问题与PreparedStatement.close()的脆弱性相关,在某些驱动程序中会更频繁地出现.

但是,对于maxPoolSize 50池来说,hibernate.c3p0.max_statements的值太小.即使在解决了死锁问题之后,通过语句进行搅动也将减少或消除语句高速缓存的任何性能优势.要为hibernate.c3p0.max_statements(映射到c3p0.maxStatements)计算一个好的值,计算应用程序中经常使用的不同PreparedStatement的数量,然后将其乘以maxPoolSize(或者在您的情况下为hibernate.c3p0.max_size).或者,只需将hibernate.c3p0.maxStatementsPerConnection设置为应用程序经常使用的不同PreparedStatements的数量.

请参阅maxStatements、maxStatementsPerConnection和Configuring Statement Pooling.