Stale connections
WAS ND v7.0 provides a special subclass of the java.sql.SQLException class for using connection pooling to access a relational database. This com.ibm.websphere.ce.cm.StaleConnectionException subclass exists in both a WebSphere 4.0 data source and in the most recent version data source that use the relational resource adapter. This class serves 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 V4.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 V4.0 data source auto connection cleanup feature 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.
A negative ramification does ensue, however, when the transaction manager closes the connections and returns the connection to the free pool after a transaction ends. An application cannot obtain a connection in one transaction and try to use it in another transaction. If the application tries this, a stale connection exception occurs because the connection is already closed.
In the case of trying to use an orphaned connection or a connection that is made unavailable by auto connection cleanup, a stale connection exception indicates that the application has attempted to use a connection already returned to the connection pool. It does not indicate an actual problem with the connection. However, other cases of a stale connection exception indicate that the connection to the database has gone bad, or stale. Once a connection has gone stale, we cannot recover it, and completely close the connection rather than returning it to the pool.
Detecting stale connections
When a connection to the database becomes stale, operations on that connection result in an SQL exception from the JDBC driver. Because an SQL exception is a rather generic exception, it contains state and error code values that we 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 stale connection exception for each database vendor supported. When the connection pooling run time catches an SQL exception, it checks to see if this SQL exception is considered a stale connection exception for the database server in use.
Recovering from stale connections
An application can catch a stale connection exception, depending on the type of error detection model configured on the data source:
- When the error detection model is configured to exception mapping, the appserver replaces the exception that is raised by the JDBC driver with StaleConnectionException. In this case, the application might trap for a stale connection exception.
- When the error detection model is configured to exception checking, the appserver still consults the error map in order to manage the connection pool, but it does not replace the exception. In this case, the application should not trap for a stale connection exception.
Because of the differences between error detection models, the appserver provides an API that applications can use with either case to identify stale connections. The API is com.ibm.websphere.rsadapter.WSCallHelper.getDataStoreHelper(datasource).isConnectionError(sqlexception).
Applications are not required to explicitly identify a stale connection exception. Applications are already required to catch the java.sql.SQLException, and the stale connection exception or the exception that is raised by the JDBC driver, always inherits data from the java.sql.SQLException. The stale connection exception, which can result from any method that is declared to raise SQLException, is caught automatically in the general catch-block. However, explicitly identifying a stale connection exception makes it possible for an application to recover from bad connections. When application code identifies a stale connection exception, it should take explicit steps to recover, such as retrying the operation under a new transaction and new connection.
Subtopics
Example: Handling data access exception - stale connection
Related concepts
Relational resource adapters and JCA
Data sources
Connection pooling
Related
Connection pool settings