The use of c3p0.idle_test_period

Question

I'm new to c3p0, and confused about the use of :

c3p0.idle_test_period

In this link : HowTo configure the C3P0 connection pool

idleTestPeriod :  Must be set in hibernate.cfg.xml (or hibernate.properties), Hibernate default:  
0, If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out  
connections, every this number of seconds.

What is the purpose of this kind of test (idel, pooled connections), and the relationship between c3p0.idle_test_period and c3p0.timeout?

Jhvh

Link is broken. Use this instead: developer.jboss.org/docs/DOC-14014 – Overexcite

Answer 1

The database server may close a connection on its side after a certain amount of time - causing some error in your application, because it'll attempt to send a query on a connection which is no longer available on the server side.

In order to avoid this you can let the pool periodically check a connection (Think of a ping) for it's validity. This is what idle_test_period is for.

timeout is the timespan after which the pool will remove a connection from the pool, because the connection wasn't checked out (used) for a while and the pool contains more connections than c3pO.min_size.

Antecedency

Please keep in mind, that hibernate.c3p0.idle_test_period value must never exceed that of hibernate.c3p0.timeout. Otherwise C3P0 will never detect connections that has been closed. – Phellem

@Phellem I suppose in that case it would just remove connections from the pool instead of ever checking them for liveness...worth realizing for sure but not necessarily a "must never" me thinks :) – Fogdog

A connection going bad can happen for more reasons than server-side idle timeout -- there are HA layers that can trigger it when a failover occurs, for example. – Everglades

Answer 2

I think this setting is used in hibernate in order to validate pooled connection after every few seconds . Consider a scenario if on database side, password is changed. Hibernate already pooled connections on old password. So it is security breach having pool with wrong password.So when hibernate will validate after few seconds it . It will invalidate that pooled connection.

Houghton

I don't see why this answer deserves downvotes. though the answer is not relevant to the question asked, it is perfectly correct – Edda