Re: keeping Connection alive

On 21/12/2009 10:24 AM, Andreas Brandl wrote:

> 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].

Hmm, I'd never noticed the subtypes of SQLException.

> 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?

It does not appear to - at least, those exception names don't appear
anywhere in the sources. It only seems to throw PSQLException, which is
a subtype of SQLException.

The full heirarchy is here, by the way:

http://java.sun.com/javase/5/docs/api/java/sql/SQLException.html

Unfortunately, the exception descriptions don't seem to be as clear as
they could be regarding what exactly "transient" means.

SQLRecoverableException describes something the app can recover from by
at minimum re-connecting and re-trying. That's clear enough.

SQLTransactionRollbackException would be a good one to be able to use
for deadlock-rollback. It's similarly clear.

SQLTransientException is for when "a previoulsy failed operation might
be able to succeed when the operation is retried without any
intervention by application-level functionality." This isn't very clear.
Does that mean "retrying *this* *statement* may work, you don't have to
re-try the whole transaction" ? That's how I read it. If it refers to
just the one JDBC operation as it seems to, it doesn't really apply to Pg.

In that case, is there even a suitable class for the most important case
in Pg - "if you re-try the whole transaction this will probably succeed,
but re-trying this particular statement is pointless. You don't have to
reconnect." ?

Anyway: To implement the use of SQLTransientException etc might be
interesting given that the Pg driver supports JDBC2, JDBC3 _and_ JDBC4
drivers in one source package. It'd need an exception factory that was
part of the JDBC-version-specific code, which took an exception type
param as well as the usual msg, sqlstate and cause. The factory for
JDBC2 / JDBC3 would return a normal PSQLException, and the JDBC4 factory
would override it to return a PSQLException subclass derived from one of
the JDBC SQLException subtypes (for JDBC4).

The JDBC4 factory wouldn't even get built when building the JDBC2/3
drivers, so the exceptions being missing from older JDKs wouldn't matter.

> 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.

Closing the statement and associated result set shouldn't take long. I'd
be reluctant to add the additional complexity, myself. Also, while the
PgJDBC driver _is_ thread safe, this still seems like it's asking for
threading issues. I'm no JDBC expert, though.

--
Craig Ringer