Example: Handling data access exception - StaleConnectionException

When an application receives a stale connection exception on a database operation, it indicates that the connection currently held is no longer valid. While it is possible to get a stale connection exception on any database operation, the most common time to see a stale connection exception issued 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. The stale connection exception occurs less often if each method that accesses the database gets a new connection from the pool.

Many stale connection exceptions 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:

Objects operating in a bean-managed global transaction context begun in the same method as the database access.

A servlet or session bean with bean-managed transactions (BMT) can start a global transaction explicitly by calling begin() on a javax.transaction.UserTransaction object, which you can retrieve from naming or from the bean EJBContext object. To commit a bean-managed transaction, the application calls commit() on the UserTransaction object. To roll back the transaction, the application calls rollback(). Entity beans and non-BMT session beans cannot explicitly begin global transactions.

If an object that explicitly started a bean-managed transaction receives a stale connection exception 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 stale connection exceptions 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) ;

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 stale connection exception does not have direct control over the transaction, such as in a container-managed transaction case, 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 creating an application exception that indicates to retry that operation. However this action is not always allowed, and often a method is defined only to create a particular exception. This is the case with the ejbLoad() and ejbStore() methods on an enterprise bean. The next two examples explain each of these scenarios.

Example 1: Database access method creates an application exception

When the method that accesses the database is free to create whatever exception is required, the best practice is to catch the stale connection exception and create some application exception that you can interpret to retry the method. The following example shows an EJB client calling a method on an entity bean with transaction demarcation TX_REQUIRED, which means that the container begins a global transaction when insertValue() is called:

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 stale connection exception, it marks the transaction for rollbackOnly (which forces the caller to roll back this transaction) and creates a new retryable connection exception, cleaning up the resources before the exception is thrown. The retryable connection exception is simply an application-defined exception that tells the caller to retry the method. The caller monitors the retryable connection exception 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.

Example 2: Database access method creates an onlyRemote exception or an EJB exception

Not all methods are allowed to throw exceptions defined by the application. If you use bean-managed persistence (BMP), use the ejbLoad() and ejbStore() methods to store the bean state. The only exceptions issued from these methods are the java.rmi.Remote exception or the javax.ejb.EJB exception, so you cannot use something similar to the previous example.

If you use container-managed persistence (CMP), the container manages the bean persistence, and it is the container that sees the stale connection exception. If a stale connection is detected, by the time the exception is returned to the client it is simply a remote exception, and so a simple catch-block does not suffice. There is a way to determine if the root cause of a remote exception is a stale connection exception. When a remote exception is created to wrap another exception, the original exception is usually retained. All remote exception 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 stale connection exception, retry the transaction. In reality, when one of these remote exceptions 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.getCause(); 
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() of the previous example:

• The session bean first retrieves a UserTransaction object from the session context and then begins a global transaction.

• Next, it calls a method on the entity bean, which calls the ejbLoad() method. If ejbLoad() runs successfully, the client then commits the transaction, causing the ejbStore() method to be called.

• In ejbStore(), the entity bean gets a connection and writes its state to the database; if the connection retrieved is stale, the transaction is marked rollbackOnly and a new EJBException that wraps the StaleConnectionException is thrown. That exception is then caught by the client, which cleans up the JDBC resources, rolls back the transaction, and calls causedByStaleConnection(), which determines if a stale connection exception is buried somewhere in the exception.

• If the method returns true, the retry flag is set and the transaction is retried; otherwise, the exception is re-issued to the caller.

• The causedByStaleConnection() method looks through the chain of detail attributes to find the original exception. Multiple wrapping of exceptions can occur by the time the exception finally gets back to the client, so the method keeps searching until it encounters a non-Remote exception. If this final exception is a stale connection exception, you find it and true is returned; otherwise, there is no stale connection exception in the list (because a stale connection exception can never be cast to a remote exception), and false is returned.

• If you are talking to a CMP bean instead of to a BMP bean, the session bean is exactly the same. The CMP bean's ejbStore() method would most likely be empty, and the container after calling it would persist the bean with generated code.

• If a stale connection exception occurs during persistence, it is wrapped with a remote exception and returned to the caller. The causedByStaleConnection() method would again look through the exception chain and find the root exception, which would be stale connection 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 that do not begin transactions with the UserTransaction interface, as well as enterprise beans running in unspecified transaction contexts. As with global transactions, 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 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.