On 06/12/11 15:55, Samuel Gendler wrote:
If you're using a 3rd party connection pool, I don't believe there is a contract that they will necessarily validate that a connection is still open before returning it. Most pools can be configured to issue a 'select 1' type of query on a periodic basis to keep connections from timing out, or just to validate that a connection is still alive before returning it from the pool.
Relying on that alone is madness, because there's an inevitable gap between the `SELECT 1;' succeeding and the connection being passed to the app, and that's time the connection can become invalid during. It's a race, bascially. Because that race can't be eliminated the app still has to be coded to be fault-tolerant and cope with failed transactions.
Validating connections is still useful if your app's idea of fault-tolerance is telling the user "something broke, please try again". You want to minimise how often that happens, so you try to make sure your pool notices a database restart promptly and flushes its connections.
If your app has better fault tolerance than that, where it remembers what it tried to do and does it again if a transaction fails the first time, then there may be no point validating connections at all.
--
Craig Ringer