logo标签列表

PostgreSQL + Hibernate + C3P0 = FATAL: 抱歉，已达到最大客户端连接数。

javahibernatepostgresqlc3p0
3
我有以下代码。
    Configuration config = new Configuration().configure();
    config.buildMappings();
    serviceRegistry = new ServiceRegistryBuilder().applySettings(config.getProperties()).buildServiceRegistry(); 
    SessionFactory factory = config.buildSessionFactory(serviceRegistry);
    Session hibernateSession = factory.openSession();
    Transaction tx = hibernateSession.beginTransaction();
    ObjectType ot = (ObjectType)hibernateSession.merge(someObj);
    tx.commit();
    return ot;

hibernate.cfg.xml 包含：

<session-factory>

    <property name="connection.url">jdbc:postgresql://127.0.0.1:5432/dbase</property>
    <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
    <property name="connection.driver_class">org.postgresql.Driver</property>

    <property name="connection.username">username</property>
    <property name="connection.password">password</property>

    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>        
    <property name="hibernate.c3p0.acquire_increment">1</property>
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.max_statements">50</property>
    <property name="hibernate.c3p0.timeout">300</property>
    <property name="hibernate.c3p0.idle_test_period">3000</property>
    <property name="hibernate.c3p0.acquireRetryAttempts">1</property>
    <property name="hibernate.c3p0.acquireRetryDelay">250</property>

    <property name="hibernate.show_sql">true</property>
    <property name="hibernate.use_sql_comments">true</property>

    <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <property name="hibernate.current_session_context_class">thread</property>

    <mapping class="...." />

</session-factory>

几秒钟后进行了一些成功的插入操作，出现了以下异常：
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
    at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
    at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
    at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
    at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
    at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)
    at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
    at org.postgresql.Driver.makeConnection(Driver.java:393)
    at org.postgresql.Driver.connect(Driver.java:267)
    at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
    at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
    at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
    at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
    at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
12:24:19.151 [          Thread-160] WARN                   internal.JdbcServicesImpl - HHH000342: Could not obtain connection to query metadata : Connections could not be acquired from the underlying database!
12:24:19.151 [          Thread-160] INFO                             dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
12:24:19.151 [          Thread-160] INFO                  internal.LobCreatorBuilder - HHH000422: Disabling contextual LOB creation as connection was null
12:24:19.151 [          Thread-160] INFO        internal.TransactionFactoryInitiator - HHH000268: Transaction strategy: org.hibernate.engine.transaction.internal.jdbc.JdbcTransactionFactory
12:24:19.151 [          Thread-160] INFO               ast.ASTQueryTranslatorFactory - HHH000397: Using ASTQueryTranslatorFactory
12:24:19.151 [          Thread-160] INFO                        hbm2ddl.SchemaUpdate - HHH000228: Running hbm2ddl schema update
12:24:19.151 [          Thread-160] INFO                        hbm2ddl.SchemaUpdate - HHH000102: Fetching database metadata
12:24:19.211 [Runner$PoolThread-#0] WARN              resourcepool.BasicResourcePool - com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@ee4084 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (1). Last acquisition attempt exception: 
org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
    at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)

看起来Hibernate没有释放连接。但是hibernateSession.close()会引发异常Session is closed，因为调用了tx.commit()。

3个回答
1
我找到了c3p0表现出这种行为的原因。问题非常微不足道... 代码的这一部分：
Configuration config = new Configuration().configure();
config.buildMappings();
serviceRegistry = new ServiceRegistryBuilder().applySettings(config.getProperties()).buildServiceRegistry(); 
SessionFactory factory = config.buildSessionFactory(serviceRegistry);

被执行了多次。感谢 Steve 的提示。

1
我不太确定这里发生了什么，但我建议您不要将hibernate.c3p0.acquireRetryAttempts设置为1。首先，这使得您的下一个设置hibernate.c3p0.acquireRetryDelay无关--它设置重试尝试之间的时间长度，但如果只有一次尝试（好吧，参数名称有误导性，它设置总尝试次数），则没有重试。您的设置效果只是让池在客户端进入时尝试获取连接，然后立即向客户端抛出异常，如果失败的话。它根本不限制池将尝试获取的连接数（除非您将breakOnAcquireFailure设置为true，在这种情况下，使用您的设置，任何获取连接失败都会使整个池失效）。
我分享sola对您缺乏可靠资源清理的担忧。如果在您的设置下，commit()意味着close()（而且您不允许显式调用close？那似乎很糟糕），那么应该在finally块中放置commit（但在finally块中放置commit也似乎很糟糕，有时您不想提交）。无论close/commit存在什么问题，使用您的代码，openSession和commit之间偶尔出现异常将导致连接泄漏。

但这不应该是您打开连接过多的问题的原因。如果您泄漏了Connections，您将发现Connection池最终会冻结(因为由于泄漏而永久检出maxPoolSize Connections)。您只有25个打开的连接。还有其他问题。请尝试查看日志。是否某种方式初始化了多个连接池？（c3p0在池初始化时以INFO级别转储配置信息，因此如果正在打开多个池，则应看到多个消息。或者，您可以通过JMX检查运行中的c3p0池，以查看是否/为什么打开了超过25个连接。）

祝好运！

你是对的。c3p0初始化了41次池，并显示消息“Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@f3dc5b64 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@a959e809 [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl...”。这个版本太长了，可能是什么原因？ - LancerX
1
嗯，c3p0并不是自主完成的。我猜测你的设置中可能有一些原因导致了许多c3p0数据源被实例化，或者你正在使用许多不同的用户/密码对来访问池。(c3p0为每个请求的用户/密码对创建一个单独的池。) - Steve Waldman
我预料到会得到这样的答案 :) 我必须找出原因。给你加分，谢谢！ - LancerX
0

我建议您使用 try-catch-finally 代码块，

在 finally 中请关闭会话。

i.e 

try {           
        tx.commit();
    } catch (HibernateException e) {
        handleException(e);
    } finally {
        hibernateSession.close();
    }

而且，在 postgresql.conf 中，max_connections 属性默认为100。如果需要，可以增加它。
正如我在问题中所写的那样，调用hibernateSession.close()会导致Exception in thread "Thread-18" org.hibernate.SessionException: Session is closed!。tx.commit()已经关闭了会话。将max_connections增加到200（并重新启动）仍然没有改变:(我还将<property name="connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>从过时的org.hibernate.connection.C3P0ConnectionProvider更改为了新的。 - LancerX
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接