Demystifying the WebSphere StaleConnectionException

In WebSphere Application Server, the StaleConnectionException is issued when the database vendor issues an exception indicating that a connection currently in the connection pool is no longer valid. This can happen for many reasons, including:

• The application tries to get a connection and fails, as when the database is not started.
   
• A connection is no longer usable due to a database failure. When an application tries to use a connection it has previously obtained, the connection is no longer valid. In this case, all connections currently in use by an application can get this error when they try to use the connection.
   
• The application using the connection has already called close() and then tries to use the connection again.
   
• The application tries to use a JDBC™ resource, such as a statement, obtained on a now-stale connection.

• Idle session (connection) timeouts on the database server.

When the StaleConnectionException is issued, the behavior is determined by the Purge Policy setting for the connection pool. If the Purge Policy is set to EntirePool, the entire pool is flushed. But if the Purge Policy is set to FailingConnectionOnly, only the connection that caused the StaleConnectionException is purged from the pool.

An application can recover from bad connections by explicitly catching the StaleConnectionException and getting a new connection from the pool. Numerous exceptions issued by DB2, Oracle, DataDirect (for connecting to SQLServer), Sybase, and Informix are currently mapped to the StaleConnectionException, which makes recovery easier for the application programmer. The following pseudocode provides an example of how to recover from a StaleConnectionException:

//get a userTransaction
javax.transaction.UserTransaction tran = getSessionContext().getUserTransaction();
//retry indicates whether to retry or not
//numOfRetries states how many retries have
// been attempted
boolean retry = false;
int numOfRetries = 0;
java.sql.Connection conn = null;
java.sql.Statement stmt = null;
do {
 try {
   //begin a transaction
   tran.begin();
   //Assumes that a datasource has already been obtained
   //from JNDI
   conn = ds.getConnection();
   conn.setAutoCommit(false);
   stmt = conn.createStatement();
   stmt.execute("INSERT INTO EMPLOYEES VALUES
             (0101, 'Bill', 'R', 'Smith')");
   tran.commit();
   retry = false;
 } catch(java.sql.SQLException sqlX)
 {
   // If the error indicates the connection is stale, then
   // rollback and retry the action
   if (com.ibm.websphere.rsadapter.WSCallHelper
       .getDataStoreHelper(ds)
       .isConnectionError(sqlX))
   {
     try {
       tran.rollback();
     } catch (java.lang.Exception e) {
       //deal with exception
       //in most cases, this can be ignored
     }
     if (numOfRetries < 2) {
       retry = true;
       numOfRetries++;
     } else {
       retry = false;
     }
   }
   else
   {
     //deal with other database exception
     retry = false
   }
 } finally {
   //always cleanup JDBC resources
   try {
     if(stmt != null) stmt.close();
   } catch (java.sql.SQLException sqle) {
     //usually can ignore
   }
   try {
     if(conn != null) conn.close();
   } catch (java.sql.SQLException sqle) {
     //usually can ignore
   }
 }
} while (retry) ;

Further details about WebSphere Connection Pooling are located in the WebSphere Application Server Knowledge Center article Connection pooling

Knowledge Center article Exceptions pertaining to data access contains details on the StaleConnectionException error