Using "dummy" SQL call to verify JDBC Connection okay for pool

It was suggested that doing a simple "dummy" SQL query each time a JDBC
Connection is returned to the pool is a valid way to determine if a given
JDBC Connection object is still usable for subsequent SQL queries/updates.

I'd be interested in hearing other feedback on whether they think this is
suitable or not.  Clearly, the advantage is that I can put this code inside
the pool code so there's only one place that needs to worry about it, and
it's the code that already worries about creating the connections, so it's
nice.  But whether it has issues surrounding transactions might arise (would
the 'select 1;' query fail because it's not inside a transaction -- or do I
need to rollback the transaction that was involved in the SQLException
first?)

But, is this how most people's pools work?  Or do most people simply suffer
restarts when such errors occur so that the connections are created fresh
again?

Clearly, if the dummy call occurs each time a connection is returned to the
pool, this would be wasteful, albeit Dmitry says it's probably very little
overhead.

My current take would be to use such a dummy call only when handling an
SQLException, so that I could pass along the connection object that may be
in trouble (most SQLExceptions won't imply the database socket is broken
unless your network is really in trouble) back to the pool and have the pool
figure out if the connection itself is dead or not.  This would certainly
only use the overhead when an SQLException occurs, which is typically a low
percentage of SQL calls, but it does mean instrumenting the code further so
that whenever an unexpected SQLException occurs, a new bit of code has to be
executed.

I suppose another way would be to simply assume all SQLExceptions that are
not handled by the business logic (like working around a duplicate key
problem perhaps) will require that the Connection be re-opened.  Assuming
that your code has been properly debugged so that invalid SQL won't occur,
this would only cause unnecessary re-opening of a Connection for duplicate
keys, contraint violations, etc.

Anyway, I'd love to hear other thoughts on this before committing to code
changes throughout all of the JDBC calls we do.

David

----- Original Message -----
From: "Dmitry Tkach" <dmitry@openratings.com>
To: "David Wall" <d.wall@computer.org>
Cc: "pgsql-jdbc-list" <pgsql-jdbc@postgresql.org>
Sent: Monday, July 21, 2003 10:52 AM
Subject: Re: [JDBC] Detecting 'socket errors' - closing the Connection
object


> You can use other means to figure out if the connection is still valid
> when it is returned to the pool, instead of relying on isClosed ().
> I do something like this in my connection pool:
>
> try
> {
>     c.createStatement ().execute ("select 1;");
>     idlingConnections.add (c);
> }
> catch (Exception e)
> {
>     log ("OOPS: the connection seems dead: ", e);
>     try
>     {
>        c.close (); //Just in case...
>     }
>    catch (Exception e)
>    {
>    }
>
>    numOpenConnections--;
> }
>
>
> I hope it helps...
>
> Dima.