Skip site navigation (1) Skip section navigation (2)

Re: keeping Connection alive

From: Andreas Brandl <ml(at)3(dot)141592654(dot)de>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: keeping Connection alive
Date: 2009-12-21 02:24:35
Message-ID: 14912910.37.1261362275792.JavaMail.root@store1.zcs.ext.wpsrv.net (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

Responses

pgsql-jdbc by date

Next:From: Craig RingerDate: 2009-12-21 03:05:53
Subject: Re: keeping Connection alive
Previous:From: Craig RingerDate: 2009-12-21 01:36:55
Subject: Re: Cheapest way to poll for notifications? & Driver improvement question re SSL and notify

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group