I have problem understanding how the JDBC connections go back to the C3P0 pool. From what I understood of the documentation, closing the Hibernate session should release the JDBC connection. But ... my app is running under tomcat. When I call a servlet several times in a row, I get a "
connection limit exceeded for non-superusers" (see stack trace below).
I tried to have a closer look at DB traffic with ethereal : it looks like quite a lot of connections are opened, but closed only after some time. As i have set c3p0.max_size to 5, I expected only 5 connections at most to be opened. I was also expecting the connections to be reused as much as possible ...
I can also see in the c3p0 logs lines like this one :
Code:
92675 [http-8080-Processor4] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - trace com.mchange.v2.resourcepool.BasicResourcePool@14f223f [[b]managed: 2[/b], unused: 1, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@4d273a)
It seems that the number of managed connections never goes over 2.
Obviously, I am doing something wrong, but I dont understand in which direction I should be looking. Any help ?
Thanks a lot !
Guillaume
Hibernate version: 2.1.8
hibernate.cfg.xml:Code:
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="connection.username">siems</property>
<property name="connection.url">jdbc:postgresql://192.168.1.11/siems</property>
<property name="jta.UserTransaction">UserTransaction</property>
<property name="transaction.factory_class">net.sf.hibernate.transaction.JDBCTransactionFactory</property>
<property name="show_sql">true</property>
<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property>
<property name="c3p0.min_size">0</property>
<property name="c3p0.max_size">5</property>
<property name="c3p0.timeout">100</property>
<property name="c3p0.max_statements">0</property>
<property name="dialect">net.sf.hibernate.dialect.PostgreSQLDialect</property>
<property name="jdbc.batch_size">0</property>
<property name="hibernate.cache.use_query_cache">false</property>
<property name="hibernate.cache.provider_class">net.sf.hibernate.cache.EhCacheProvider</property>
<property name="hibernate.cache.query_cache_factory">net.sf.hibernate.cache.StandardQueryCacheFactory</property>
Quite a few indirections ... but the session is implemented using the ThreadLocal design pattern :
Code:
public static Session currentSession() throws HibernateException {
Session s = (Session) session.get();
if (s == null) {
SessionFactory sf = new Configuration().configure().buildSessionFactory();
s = sf.openSession();
session.set(s);
}
return s;
}
Code:
public static void closeSession() throws HibernateException, SQLException {
Session s = (Session) session.get();
session.set(null);
if (s != null) {
Connection con = s.connection();
s.close();
}
}
I have a base servlet handling the hibernate stuff. All child servlets implement doHibernate() :
Code:
protected void doGet(HttpServletRequest request,
HttpServletResponse response) {
try {
Session session = HibernateSession.currentSession();
doHibernate(request, response, session);
} finally {
try {
HibernateSession.closeSession();
} catch (...) {...}
}
}
Full stack trace of any exception that occurs:
93710 [http-8080-Processor4] INFO com.mchange.v2.c3p0.PoolBackedDataSource - Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@180bfe1 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@1f4d7c0 [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 1f4d7c0, idleConnectionTestPeriod -> 100, initialPoolSize -> 0, maxIdleTime -> 100, maxPoolSize -> 5, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 0, nestedDataSource ->com.mchange.v2.c3p0.DriverManagerDataSource@1105002 [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 1105002, jdbcUrl-> jdbc:postgresql://192.168.1.11/siems, properties -> {user=******, password=******} ], preferredTestQuery -> null, propertyCycle -> 300, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ], factoryClassLocation -> null, identityToken -> 180bfe1, numHelperThreads -> 3 ]
93710 [http-8080-Processor4] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - awaitAvailable(): [unknown]
93710 [http-8080-Processor4] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - trace com.mchange.v2.resourcepool.BasicResourcePool@7b954b [managed: 0, unused: 0, excluded: 0]
93720 [com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1] DEBUG com.mchange.v2.resourcepool.BasicResourcePool - An exception occurred while acquiring a resource.
org.postgresql.util.PSQLException: Backend start-up failed: org.postgresql.util.PSQLException: FATAL: connection limit exceeded for non-superusers
at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV3(AbstractJdbc1Connection.java:445)
at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJdbc1Connection.java:213)
at org.postgresql.Driver.connect(Driver.java:139)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:81)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:95)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1.acquireResource(C3P0PooledConnectionPool.java:89)
at com.mchange.v2.resourcepool.BasicResourcePool.assimilateResource(BasicResourcePool.java:938)
at com.mchange.v2.resourcepool.BasicResourcePool.acquireUntil(BasicResourcePool.java:643)
at com.mchange.v2.resourcepool.BasicResourcePool.access$500(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1159)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:366)
Name and version of the database you are using:
Postgresql 7.4.7
[b]Others[/]
All that running under Tomcat 5.5.7
Login
FAQ
Search