Hi,
I got the following exception from my app. It looks like the c3p0 pool is exhausted. While I can increase the pool size (currently our pool size is very small) to workaround the problem, does anyone know if c3p0 has any timeout mechanism to wait for available connection? Is it available in 0.8.5? (The timtout configuration parameter in hibernate.properties only control the max idle time, not time to wait for available connection in the pool.)

Thanks,
--Jiunjiun

Read the rules before posting!
http://www.hibernate.org/ForumMailingli ... AskForHelp

[b]Hibernate version:[/b]
2.1.7c

[b]Mapping documents:[/b]
N/A

[b]Code between sessionFactory.openSession() and session.close():[/b]

[b]Full stack trace of any exception that occurs:[/b]
java.sql.SQLException: An SQLException was provoked by the following failure: com.mchange.v2.resourcepool.ResourcePoolException: A ResourcePool could not acquire a resource from its primary factory or source.
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:68)
at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:57)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:213)
at com.mchange.v2.c3p0.PoolBackedDataSource.getConnection(PoolBackedDataSource.java:64)
at net.sf.hibernate.connection.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:33)
at net.sf.hibernate.impl.BatcherImpl.openConnection(BatcherImpl.java:289)
at net.sf.hibernate.impl.SessionImpl.connect(SessionImpl.java:3361)
at net.sf.hibernate.impl.SessionImpl.connection(SessionImpl.java:3321)
at net.sf.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:40)
at net.sf.hibernate.transaction.JDBCTransactionFactory.beginTransaction(JDBCTransactionFactory.java:19)


[b]Name and version of the database you are using:[/b]
postgres 7.x

[b]The generated SQL (show_sql=true):[/b]
N/A
[b]Debug level Hibernate log excerpt:[/b]
N/A

Jiunjiun,

Is this the first Exception that you see under these conditions? The Exception that you are seeing is one c3p0 emits after it has determined that the database from which it is trying to pool Exceptions is down or unavailable. Normally, you would see a whole series of messages indicating that attempted Connection acquisitions failed, prior to seeing this message. This method does not indicate pool exhaustion per se (although there is a relationship).

If the pool were merely exhausted, your thread would simply wait until a Connection was returned by the application or acquired from the databse, and then move merrily on (unless you had set a checkoutTimeout, in which case it might break with an exception when its wait time was up).

Your message is a more serious condition -- the pool wants more Connections to give to the application, but it has determined it cannot get any from the database. Any threads waiting for Connections break with this exception, and, depending on your configuration, c3p0 will try again later (when it receives new Connection requests) in hopes that the database has recovered.

Anyway, when reporting potential c3p0 issues, please include c3p0's configuration dump, which should be emitted in your logs when the pool is first initialized. In this case, the config information could be very helpful in understanding your problem. Also, can you be more specofic about the version of postgres you are using? 7.x was a pretty broad and long-running series...

smiles,
Steve (c3p0 guy)

1. No, this is not the first Exception we saw. The app was running fine for a while, then hit this error. It was intermittent.

2. Will c3p0 try to acquire new connection(s) even when the connection count has reached the max pool size? Or will it wait until there is any connection released by the app? I am just wondering why c3p0 is trying to acquiring new connection in this case.

3. Here is our c3p0 configuration in the hibernate.properties. We didn't set any extra properties:
hibernate.c3p0.max_size 2
hibernate.c3p0.min_size 0
hibernate.c3p0.timeout 5000
hibernate.c3p0.max_statements 100
hibernate.c3p0.idle_test_period 3000
hibernate.c3p0.acquire_increment 1
hibernate.c3p0.validate false

4. Is it possible that this problem happened because postgres db's max connection count had been reached? (Our app is sharing the same db with some other app.) In this case, can I minimize the problem by increasing the initial connection pool size and timeout?

> 1. No, this is not the first Exception we saw. The app
> was running fine for a while, then hit this error. It was
> intermittent.

If there were other Exceptions, can you post them? Really, you should only see the Exception you are seeing after seeing failures to acquire new Connections, which should send messages and stack traces to wherever you log System.err. (If getting to System.err is hard, try upgrading to c3p0-0.9.0-preX, which will log to jdk14 or log4j logging apis instead.)

> 2. Will c3p0 try to acquire new connection(s) even when
> the connection count has reached the max pool size?
> Or will it wait until there is any connection released by
> the app? I am just wondering why c3p0 is trying to
> acquiring new connection in this case.

No. At maxPoolSize, attempts to check Connections out of the pool will block until Connections are returned. c3p0 will not hold more than maxPoolSize Connections open at one time. c3p0 may need to acquire Connections at any time, for a variety of different reasons: Connection tests or idle timeouts may bring the Connection count below minPoolSize, or client load may trigger Connection acquisition attempts, until the pool is full. (Given that you've set the minimum size to 0, it is probably client load that is triggering acquisition attempts.)

> 3. Here is our c3p0 configuration in the
> hibernate.properties. We didn't set any extra properties:

> hibernate.c3p0.max_size 2
> hibernate.c3p0.min_size 0
> hibernate.c3p0.timeout 5000
> hibernate.c3p0.max_statements 100
> hibernate.c3p0.idle_test_period 3000
> hibernate.c3p0.acquire_increment 1
> hibernate.c3p0.validate false

Please do send along c3p0's config dump, rather than just your hibernate config. It's better to see exactly what configuration c3p0 is actually "picking up". It goes to System.err for c3p0-0.8.5, or your logging library for c3p0-0.9.0-preX.

> 4. Is it possible that this problem happened because
> postgres db's max connection count had been reached?
> (Our app is sharing the same db with some other app.)
> In this case, can I minimize the problem by increasing
> the initial connection pool size and timeout?

Yes, if your app is not permitted to acquire further Connections because the database has reached its limit of open Connections, you would see this error. You might be able to work around this, for a while, by setting a higher min pool size and not timing them out, if your database is less busy when you start the application than it comes to be later on.

good luck!
Steve

1. Sorry, I misunderstood the exception question. Yes, it's the first exception we saw. We didn't see other exceptions when it happened.

2. Here are the c3p0 parameters (dumped to stderr):
Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@c4fe76 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@b23210 [ 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, idleConnectionTestPeriod -> 3000, initialPoolSize -> 0, maxIdleTime -> 5000, maxPoolSize -> 2, maxStatements -> 100, maxStatementsPerConnection -> 0, minPoolSize -> 0, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@19360e2 [ description -> null, driverClass -> null, factoryClassLocation -> null, jdbcUrl -> jdbc:postgresql://localhost:5433/rox, properties -> {user=******, password=******} ] , preferredTestQuery -> null, propertyCycle -> 300, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ] , factoryClassLocation -> null, numHelperThreads -> 3, poolOwnerIdentityToken -> c4fe76 ]

3. Thanks for the clarification on the max pool size and others. I think for now I will increase the minPoolSize to 1 and set timeout to 0 to work around the problem.