Re: keeping Connection alive - Mailing list pgsql-jdbc

On 13/12/2009 9:37 AM, Andreas wrote:
> Hi Jovan,
>
> thanks for your reply!
>
>> On Dec 12, 2009, at 6:43 PM, Andreas Brandl wrote:
>>> I'm using a PostgreSQL as backend for a Java application, which may
>> idle for quite a while (i.e. hours) and now and then issue a query on
>> the backend.
>>
>> Is this a standard Java application or is it running in a Java EE or
>> servlet container?
>
> This is a standard Java application.

Is its connection to the server diect via a local Ethernet segment? Or
is it going through routers - possibly routers doing NAT, such as most
home user modem/routers?

NAT is the most common cause of connection drop-outs. You can use TCP
keepalives to address this. See the archives and documentation.

(Hmm, we need a FAQ entry for this - added to my todo)

> I did work with a pure Connection before, until I realized things go wrong when application is idle for long time.
>
> In either case, the problem is how to detect whether a Connection is valid or not.

Don't. Even if the connection is ok when you test it, it might not be
when you then try to use it. Instead of trying to ensure that the
connection is perfect, make your application tolerant of failure. Batch
things into transactions, catch SQLExceptions, and re-try operations
that fail.

After all, database operations may fail due to serialization errors (in
SERIALIZABLE mode), locking deadlocks if you make mistakes with lock
ordering, etc. While you want to try to avoid these things, you should
also handle them in the app if they come up.

If you try to ensure that your connection is ok and then rely on it
staying OK, you *will* have problems. A user might lose wifi/cellular
coverage, a network might drop out, etc. You're setting yourself up for
a race condition you will inevitably lose some of the time.

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


int retries = MAX_RETRIES;
do {
   try {
     try {
       Connection conn = myprovider.getConnection();
       // do work
       break;
     } finally {
       try {
         stmt.close();
       } catch (SQLException e) {
         // log to complain about statement close failure
       }
     }
   } catch (SQLException e) {
     myProvider.invalidateConnection(conn, e);
     retries--;
   }
} while (retries > 0);


... where "invalidateConnection(Connection, Throwable)" tells the
provider/pool that the connection is broken.

If you're using Connection directly, you'll just close() one you think
is broken and set the connection var to null so you know you need to
create a new one next time around.

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
             );

    /**
      * Attempt to figure out whether a given database error could be
      * transient
      * and might be worth a retry. Detects things like network timeouts,
      * transaction deadlocks, serialization failures, connection drops,
      * etc.
      *
      * @param e Exception thrown by persistence provider
      * @return True if the error might be transient
      */
     public static boolean isDbErrorTransient(Throwable e) {
         final SQLException se = getSQLException(e);
         if (se == null)
             return false;
         final String sqlState = se.getSQLState();
         for (String s : transientStates) {
             Logger.getLogger(TheNameClass.class).warn(
                "Got possibly transient SQL error " + sqlState, e);
             if (sqlState.startsWith(s))
                 return true;
         }
         return false;
     }


--
Craig Ringer