Example: Handling data access exception - stale connection

These code samples demonstrate how to programmatically address stale connection exceptions for different types of data access clients in different transaction scenarios.

When an application receives a stale connection exception on a database operation, it indicates that the connection currently held is no longer valid. Although it is possible to get an exception for a stale connection on any database operation, the most common time to see a stale connection exception issued is after the first time the connection 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.

Avoid trouble: If developing applications for the appserver with an integrated development environment (IDE) like Eclipse, we might need to import the APP_ROOT/plugins/com.ibm.ws.runtime.jar file into the development environment to take advantage of code that is provided.

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. We 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 we 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(java.sql.SQLException sqlX)
  {
    
// If the error indicates the connection is stale, then
    
// rollback and retry the action
    if (com.ibm.websphere.rsadapter.WSCallHelper
        .getDataStoreHelper(ds)
        .isConnectionError(sqlX))
    {
      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;
      }
    }
    else
    {
      
//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, we 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 we 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(java.sql.SQLException sqlX)
    {
      
// Find out if the error indicates the connection is stale
      if (com.ibm.websphere.rsadapter.WSCallHelper
          .getDataStoreHelper(ds)
          .isConnectionError(sqlX))
      {
        getSessionContext().setRollbackOnly();
        throw new RetryableConnectionException();
      }
      else
      {
        
//handle other database 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
      }
    }
  }
}  
//end MyEJB

MyEJBClient first gets a MyEJB bean from the home interface, assumed to have been previously retrieved from the 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 we 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 we cannot use something similar to the previous example.If we 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, we 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 re)
  {
    
// Search the exception chain for errors
    
// indicating a stale connection
    for (Throwable t = re; t != null; t = t.getCause())
      if (t instanceof RetryableConnectionException)
        return true;

    
// Not found to be stale
    return false;
  }
}
 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(java.sql.SQLException sqlX)
    {
      
// Find out if the error indicates the connection is stale
      if (com.ibm.websphere.rsadapter.WSCallHelper
          .getDataStoreHelper(ds)
          .isConnectionError(sqlX))
      {
        
// rollback the tran when method returns
        getEntityContext().setRollbackOnly();
        throw new java.rmi.EJBException(
          "Exception occurred in ejbStore",           new RetryableConnectionException(sqlX));
      }
      else
      {
        
// 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
      }
    }
  }
}

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 RetryableConnectionException 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 stale connection exception and true is returned; otherwise, there is no stale connection exception in the list and false is returned.

• If 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 we 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 JSPs unless they are a part of a user transaction.

---

Subtopics

Stale Connection on Linux systems
Example: Handling servlet JDBC connection exceptions
Example: Handling connection exceptions for session beans in container-managed database transactions
Example: Handling connection exceptions for session beans in bean-managed database transactions
Example: Handling connection exceptions for BMP beans in container-managed database transactions