Stale connections

 

+

Search Tips   |   Advanced Search

 

WAS provides a special subclass of the java.sql.SQLException class for using connection pooling to access a relational database: com.ibm.websphere.ce.cm.StaleConnectionException. It 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 StaleConnection 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 StaleConnection 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 StaleConnection exception indicate that the connection to the database has gone bad, or stale. Once a connection has gone stale, one 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 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 StaleConnection 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 StaleConnection exception 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 StaleConnection exception is not required in an application. Because applications are already required to catch the java.sql.SQL exception, and the StaleConnection exception extends an SQL exception, a StaleConnection exception can result from any method that is declared to create an SQL exception, and is caught automatically in the general catch-block. However, explicitly catching a StaleConnection exception makes it possible for an application to recover from bad connections. When application code catches a StaleConnection exception, it should take explicit steps to handle the exception.

 

See also


Example: Handling data access exception - StaleConnectionException

 

See Also


Resource adapter
Data sources
Connection pooling

 

See Also


Connection pool settings