IBM Demystifying the WebSphere StaleConnectionException - United States  TECHNOTE (TROUBLESHOOTING)

PROBLEM(ABSTRACT)
 What is the StaleConnectionException, why is it issued by WebSphere Application Server, and what should be done about it? 

RESOLVING THE PROBLEM
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.


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 [https://www.ibm.com/support/knowledgecenter/SSAW57_8.5.5/com.ibm.websphere.nd.doc/ae/cdat_conpool.html]

Knowledge Center article Exceptions pertaining to data access [https://www.ibm.com/support/knowledgecenter/SSAW57_8.5.5/com.ibm.websphere.nd.doc/ae/cdat_daexcp.html] contains details on the StaleConnectionException error




Cross reference information Segment Product Component Platform Version Edition Application Servers Runtimes for Java Technology Java SDK
Why am I getting StaleConnectionException on WebSphere side while Oracle is throwing 'OALL8 is in an inconsistent state'? 

In the WebSphere Application Server SystemOut.log file we see this message:

     ConnectionEve A   J2CA0056I: TheConnection Manager received a fatal connection error from the Resource Adapter for resource jdbc/<data source name>.  The exception which was received is com.ibm.websphere.ce.cm.StaleConnectionException: OALL8 is in an inconsistent state:java.sql.SQLException: OALL8 is in an inconsistent state.

How can this be resolved?
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.