Hello this question should apply to more connection pool managers than c3p0 but I'll use that one for example.
One option c3p0 offers for connection pool management is to test the connections before they are checked into the connection pool. Does this mean after the application has used the connection? If so, wouldn't the connection always be valid if it was just used by the application?
Unless my understanding is wrong, this seems like a useless time to check whether a connection is valid since an invalid connection would have thrown an exception when the app tried using it.
Update: The real question I was asking is whether or not a stale connection that just failed in the app would still be checked back into the pool to ultimately fail again and again? If the answer is no, then test on check in would be pointless since 'exploded' connections would never be sent back to the pool. I've been using c3p0 with testOnCheckIn and a test query interval for years without actually diving this deep to understand why.
Whether testing Connections on checkout is a performance problem largely depends on how efficient the test is, and how much work a Connection does when it is checked out. As you say, the safest, simplest way to configure Connection testing is to test on checkout. But it also means that clients must experience the latency of the test.
If you want Connections to be tested asynchronously, that is outside of client codepaths, then you want to test Connections on check-in, and periodically while the Connections are checked in. Basically, the strategy is to ensure that Connections idle in the pool are very unlikely to be invalid, so that you can safely check them out without testing. ("Safely" of course is a matter of degree. Even with test-on-checkout, a Connection can become invalidated between the test and the first client use.)
It is not a good idea to presume that a Connection is valid on check-in because a client has just used it. First of all, the fact that a client experiences an Exception doesn't necessarily signal to the pool that the Connection is invalid. For example, calls to Connection.commit() can fail because of concurrent modifications of a row in the transaction, even though the Connection is perfectly valid. Fundamentally, Exceptions experienced when Connections are checked-out are the client's business, not the pool's. c3p0 (I think unusually) does notice Exceptions experienced by clients, and triggers a silent Connection test so that the pool can reason about Connection validity. But there are lots of reasons why clients might experience Exceptions from a valid Connection.
Secondly, the pool cannot rely upon prompt check-in of the Connection following last-use by the client. It's a bad idea, but clients often hold Connections open for much longer than their actual database work. Any window of time between las client use and check-in would obviously be a window during which a Connection could go bad.
So, if you want asynchronous Connection testing -- i.e. Connection testing that (for a sufficiently sized pool) makes no contribution to client latency -- you want a test-on-checkin and then frequent tests while Connections are pooled and idle.
All of this said, in practical terms, the need for asynchronous Connection testing has diminished because JDBC drivers now offer fast, reliable tests via Connection.isValid(). Once upon a time, in order to define DBMS independent, reliable Connection tests, c3p0 had to default to an often-very-slow query against database metadata. The latency of this test could be a real hit to client performance. With Connection.isValid() (or an efficient preferredTestQuery), tests are often fast enough that the simplicity and robustness of test-on-checkout more than outweighs the small client latency hit. c3p0 docs used to advise use of asynchronous Connection testing for performance reasons. The current documentation advises synchronous test-on-checkout first, only backing off to asynchronous testing as a potential optimization if test latency affects performance. For the most part, in practice, you usually end up just using testConnectionsOnCheckout now.