mysql - 如何确定 c3p0 max_statements

Question

我想知道如何正确确定用于 c3p0 max_statements 的值。我遇到了一些缓存死锁，根据我阅读的所有 SO Q&A，这似乎指向我的 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?

Answer 1

要解决与 Oracle/jTDS/mySQL 下的语句缓存相关的死锁，请确保您使用的是最新的 c3p0(0.9.5.1 是当前版本)，请参阅 statementCacheNumDeferredCloseThreads和 Configuring Statement Pooling .

TL;DR 设置配置参数

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

max_statements 的确切值只是偶然与此问题有关。如果 max_statements 太小，您将不必要地搅动语句，并且这个问题与 PreparedStatement.close() 的脆弱性相关，在某些驱动程序中会更频繁地出现。

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

请参阅maxStatements , maxStatementsPerConnection , 和 Configuring Statement Pooling .