Re: keeping Connection alive

Hi Craig, folks,

I'm coming back to this topic, because I now do have an implementation which is more robust in respect to connection failures and the like and which follows Craig's idea.

> > As far as I know there is no native mechanism for checking the
> state.
>
> Trying something and catching the SQLException if it fails. That
> reflects the design of the JDBC interface and of transactional
> databases
> in general that you should try to do something and cope if it fails,
> rather than ensuring it cannot fail.
>
> > So one approach may be to implement a DataSource which does check
> (i.e. "SELECT 1") validity before returning the Connection (and
> reconnect if its not valid, i.e. throwing a SQLException?).
>
> Nononono....
>
> Have the DataSource _consumer_ deal with it.
>
> [...]
>
> ... where "invalidateConnection(Connection, Throwable)" tells the
> provider/pool that the connection is broken.
>
> [...]
>
> In practice, you'll also generally test e.getSQLSTate() to see if the
> exception might be a transient once tha doesn't imply the connection
> is
> broken, and re-try with the same connection if it is. There's no point
> dropping and re-creating the connection if it's a deadlock,
> serialization failure, or the like after all.
>
> Here's some code I use to check for SQLStates that may mean an
> operation
> is worth retrying:
>
> private static final List<String> transientStates =
> Arrays.asList(
> "08", // Connection exceptions - refused, broken, etc
> "53", // Insufficient resources - disk full, etc
> "57P0", // Db server shutdown/restart
> "40001",// Serialization failure
> "40P01"// Deadlock detected
> );
>

In the course of implementing I discovered, that in JDBC 4 there are subtypes of SQLException designed for distinguishing transient and non-transient failures. These are SQLTransientException, SQLNonTransientException and SQLNonTransientConnectionException. Quite an old source is [1].

My understanding is that in general distinguishing by the type of SQLException is sufficient for this purpose. Though I wonder if the postgresql-jdbc does implement this?

Another question arising with implementing a more robust connection handling is the following. Suppose there is a 'WorkUnit' containing the concrete jdbc-code which gets executed in a single transaction which may get rolled back and retried some more times.

The execution of the 'WorkUnit' generates some Statement- and ResultSet-instances which have to be closed after execution.

My question is if it is advisable to do this closing of resources on a background worker thread. This would have the advantage of returning faster after executing the relevant JDBC code because resources are closed on the separate background thread.

I.e. in pseudo code:

void execute(WorkUnit unit) {

do {
unit.execute();
} while (failed and retry-count is not reached};

backgroundThread.close(unit); // a background thread will close the units ressources

}

Thanks a lot, I really appreciate this (and the other pgsql-*) mailinglist(s)!

Andreas

[1] http://www.artima.com/lejava/articles/jdbc_four3.html