WebSphere Application Server - Express provides a special subclass of java.sql.SQLException when using connection pooling to access a relational database. This com.ibm.websphere.ce.cm.StaleConnectionException subclass exists in both a Version 4.0 data source and in the new data source using the relational resource adapter, and is used to indicate that the connection currently held is no longer valid. This situation can occur for many reasons, including the following:
The application tries to get a connection and fails, as when the database is not started.
A connection is no longer usable because of a database failure. When an application tries to use a previously obtained connection, the connection is no longer valid. In this case, all connections currently in use by the application can get this error when they try to use the connection.
The connection is orphaned (because the application had not used it in at most two times the value of the unused timeout setting) and the application tries to use the orphaned connection. This case applies only to Version 4.0 data sources.
The application tries to use a JDBC resource, such as a statement, obtained on a stale connection. A connection is closed by the Version 4.0 data source auto connection cleanup and is no longer usable. Auto connection cleanup is the standard mode in which connection management operates. This mode indicates that at the end of a transaction, the transaction manager closes all connections enlisted in that transaction. This enables the transaction manager to ensure that connections are not held for excessive periods of time and that the pool does not reach its maximum number of connections prematurely.
One ramification of having the transaction manager close the connections and return the connection to the free pool after a transaction ends, is that an application cannot obtain a connection in one transaction and try to use it in another transaction. If the application tries this, a StaleConnectionException is thrown because the connection is already closed.
In the case of trying to use an orphaned connection or a connection cleaned up by automatic connection cleanup, a StaleConnectionException indicates that the application has attempted to use a connection that already returned to the connection pool. It does not indicate an actual problem with the connection. However, other cases of a StaleConnectionException indicate that the connection to the database has gone bad, or stale. Once a connection has gone stale, you cannot recover it.
Detecting stale connections
When a connection to the database becomes stale, operations on that connection result in an SQLException from the JDBC driver. Because an SQLException is a rather generic exception, it contains state and error code values that you can use to determine the meaning of the exception. However, the meanings of these states and error codes vary depending on the database vendor. The connection pooling run time maintains a mapping of which SQL state and error codes indicate a StaleConnectionException for each database vendor supported. When the connection pooling run time catches any SQLException, it checks to see if this SQLException is considered a StaleConnectionException for the database server in use.
Recovering from stale connections
Recovering from stale connections is a joint effort between the application server run time and the application developer. From an application server perspective, the connection pool is purged based on its PurgePolicy setting.
Explicitly catching a StaleConnectionException is not required in an application. Because applications are already required to catch java.sql.SQLException, and StaleConnectionException extends SQLException, StaleConnectionException can be thrown from any method that is declared to throw SQLException, and is caught automatically in the general catch block. However, explicitly catching StaleConnectionException makes it possible for an application to recover from bad connections. When application code catches StaleConnectionException, it should take explicit steps to handle the exception.
Handling the StateConnectionException data access exception
When an application receives a StaleConnectionException on a database operation, it indicates that the connection currently held is no longer valid. While it is possible to get a StaleConnectionException on any database operation, the most common time to see a StaleConnectionException thrown is the first time that a connection is used, just after it is retrieved. Because connections are pooled, a database failure is not detected until the operation immediately following its retrieval from the pool, which is the first time communication to the database is attempted. It is only when a failure is detected that the connection is marked stale. StaleConnectionException occurs less often if each method that accesses the database gets a new connection from the pool.
Many StaleConnectionExceptions are caused by intermittent problems with the network of the database server. Obtaining a new connection and retrying the operation can result in successful completion without exceptions to the end user. In some cases it is advantageous to add a small wait time between the retries to give the database server more time to recover. However, applications should not retry operations indefinitely, in case the database is down for an extended period of time.
Before the application can obtain a new connection for a retry of the operation, roll back the transaction in which the original connection was involved and begin a new transaction. You can break down details on this action into these categories:
Objects operating in a global transaction context and transaction not begun in the same method as the database access.
When the object which receives the StaleConnectionException does not have direct control over the transaction, the object must mark the transaction for rollback, and then indicate to its caller to retry the transaction. In most cases, you can do this by throwing an application exception which indicates to retry that operation. However this action is not always allowed, and often a method is defined only to throw a particular exception.
Objects operating in a local transaction context.
When a database operation occurs outside of a global transaction context, a local transaction is implicitly begun by the container. This includes servlets or JSPs which do not begin transactions with the UserTransaction interface. As with global transactions, you must roll back the local transaction before the operation is retried. In these cases, the local transaction containment usually ends when the business method ends. The one exception is if you are using activity sessions. In this case the activity session must end before attempting to get a new connection.
When the local transaction containment takes place as part of a servlet or JSP file, there is no client object available to retry the operation. For this reason, it is recommended to avoid database operations in servlets and JSP files unless they are a part of a user transaction.