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 two categories:
If an object that explicitly started a bean-managed transaction receives a StaleConnectionException on a database operation, close the connection and roll back the transaction. At this point, the application developer can decide to begin a new transaction, get a new connection, and retry the operation.
The following code fragment shows an example of handling StaleConnectionExceptions in this scenario:
//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(com.ibm.websphere.ce.cm.StaleConnectionException sce) { //if a StaleConnectionException is caught // rollback and retry the action 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; } } catch (java.sql.SQLException sqle) { //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) ;
public class MyEJBClient { //... other methods here ... public void myEJBClientMethod() { MyEJB myEJB = myEJBHome.findByPrimaryKey("myEJB"); boolean retry = false; do { try { retry = false; myEJB.insertValue(); } catch(RetryableConnectionException retryable) { retry = true; } catch(Exception e) { /* handle some other problem */ } } while (retry); } } //end MyEJBClient public class MyEJB implements javax.ejb.EntityBean { //... other methods here ... public void insertValue() throws RetryableConnectionException, java.rmi.EJBException { try { conn = ds.getConnection(); stmt = conn.createStatement(); stmt.execute("INSERT INTO my_table VALUES (1)"); } catch(com.ibm.websphere.ce.cm.StaleConnectionException sce) { getSessionContext().setRollbackOnly(); throw new RetryableConnectionException(); } catch(java.sql.SQLException sqle) { //handle other database problem } finally { 21 //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 } } } } //end MyEJB
MyEJBClient first gets a MyEJB bean from the home interface, assumed to have been previously retrieved from the Java Naming and Directory Interface (JNDI). It then calls insertValue() on the bean. The method on the bean gets a connection and tries to insert a value into a table. If one of the methods fails with a StaleConnectionException, it marks the transaction for rollbackOnly (which forces the caller to roll back this transaction) and throws a new RetryableConnectionException, cleaning up the resources before the exception is thrown. The RetryableConnectionException is simply an application-defined exception that tells the caller to retry the method. The caller monitors RetryableConnectionException and, if it is caught, retries the method. In this example, because the container is beginning and ending the transaction, no transaction management is needed in the client or the server. Of course, the client could start a bean-managed transaction and the behavior would still be the same, provided that the client also committed or rolled back the transaction.
If you use container-managed persistence (CMP), the container persists the bean, and it is the container that sees StaleConnectionException. If a stale connection is detected, by the time the exception is returned to the client it is simply a RemoteException, and so a simple catch-block does not suffice. There is a way to determine if the root cause of a RemoteException is a StaleConnectionException. When RemoteException is thrown to wrap another exception, the original exception is usually retained. All RemoteException instances have a detail property, which is of type java.lang.Throwable. With this detail, you can trace back to the original exception and, if it is a StaleConnectionException, retry the transaction. In reality, when one of these RemoteExceptions flows from one Java Virtual Machine API to the next, the detail is lost, so it is better to start a transaction in the same server as the database access occurs. For this reason, the following example shows an entity bean accessed by a session bean with bean-managed transaction demarcation.
public class MySessionBean extends javax.ejb.SessionBean { ... other methods here ... public void mySessionBMTMethod() throws java.rmi.EJBException { javax.transaction.UserTransaction tran = getSessionContext().getUserTransaction(); boolean retry = false; do { try { retry = false; tran.begin(); // causes ejbLoad() to be invoked myBMPBean.myMethod(); // causes ejbStore() to be invoked tran.commit(); } catch(java.rmi.EJBException re) { try { tran.rollback(); } catch(Exception e) { //can ignore } if (causedByStaleConnection(re)) retry = true; else throw re; } catch(Exception e) { // handle some other problem } 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); } public boolean causedByStaleConnection(java.rmi.EJBException EJBException) { java.rmi.EJBException re = EJBException; Throwable t = null; while (true) { t = re.detail; try { re = (java.rmi.EJBException)t; } catch(ClassCastException cce) { return (t instanceof com.ibm.websphere.ce.cm.StaleConnectionException); } } } } public class MyEntityBean extends javax.ejb.EntityBean { ... other methods here ... public void ejbStore() throws java.rmi.EJBException { try { conn = ds.getConnection(); stmt = conn.createStatement(); stmt.execute("UPDATE my_table SET value=1 WHERE primaryKey=" + myPrimaryKey); } catch(com.ibm.websphere.ce.cm.StaleConnectionException sce) { //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 } // rollback the tran when method returns getEntityContext().setRollbackOnly(); throw new java.rmi.EJBException("Exception occurred in ejbStore", sce); } catch(java.sql.SQLException sqle) { // handle some other problem } } }
In mySessionBMTMethod():
When the local transaction occurs in an enterprise bean running in an unspecified transaction context, the enterprise bean client object, outside of the local transaction containment, could use the method described in the previous bullet to retry the transaction. However, 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.