WAS reference: [DevWorks] Understanding connection transitions: Avoiding multi-threaded access to a JCA connection in WebSphere Application Server

Understanding connection transitions: Avoiding multi-threaded access to a JCA connection in WebSphere Application Server

Introduction

The IBM WebSphere Application Server connection pool helps to alleviate connection management overhead as well as decrease development tasks for data access. With WebSphere Application Server connection pooling, most user requests do not incur the overhead of creating a new connection because the data source can locate and use an existing connection from the pool of connections. The WebSphere Application Server connection manager supports both unshareable and shareable connections and manages the state of connection objects between the free pool and shared/unshared pool. It also provides local transaction containment (LTC) in an unspecified transaction context.

To use connections effectively and to avoid any multi-threaded access to a connection in an application, it is important to understand connection transitions between the free pool and shared/unshared pool with respect to transactions. These concepts are described in detail in this article.

Connection sharing terminology

A connection handle is a representation of a physical connection and is not the physical connection to the back end resource manager. A connection handle is returned by the connection manager when the getConnection() method is invoked from an application.

An unshareable connection cannot be shared with other components in an application. The component using this connection has full control over it. Access to a resource marked as unshareable means that there is a one-to-one relationship between the connection handle that a component is using and the physical connection with which the handle is associated. This access implies that every call to the getConnection() method returns a connection handle solely for the requesting user.

The use of a shareable connection means that, if conditions allow it, different getConnection() requests by an application will actually receive a handle for the same physical connection to the resource. The physical connection is shared through multiple connection handles instead of retrieving a new physical connection from the connection pool for every getConnection() invocation.

Factors that determine sharing include:

each getConnection() request should have the same connection properties.
each getConnection() request should be made within the same sharing scope.

Connection sharing conditions are such that a connection can be shared only within a sharing scope. The most common sharing scope is a transaction scope where multiple active connection handles can share the same physical connection. There are two transaction scopes in WebSphere Application Server:

Global transaction
Local transaction containment (LTC)

Within an LTC boundary, there cannot be multiple active connection handles that use the same physical connection. However, the physical connection can be reused if the previous connection handle that wraps the same physical connection is closed.

Connection reuse occurs when this pattern is used:

Get connection 1 -> use connection 1 -> commit/rollback connection 1 (optional) -> close connection 1 -> get connection 2 -> use connection 2 -> commit/rollback connection 2 (optional) -> close connection 2 ->...

Connection transition

The conditions causing the transition of a connection from the shared/unshared pool to free pool are illustrated in Figure 1; Table 1 explains the terms shown in the figure. The conditions are:

When an application invokes close() method on a connection in the unshared pool, the connection is returned to the free pool, if "AutoCommit" is set to true and there are no other references to the connection held by the application orConnection Manager.
When an application invokes close() method on a connection in the shared pool and the LTC or global transaction is still active, the connection is not returned to the free pool. The connection remains in the shared pool and can be reused within the transaction. The connection is returned to the free pool when the LTC or global transaction ends.

Figure 1. Transitions of a connection between different pools when close() method is invoked on a connection object

Table 1. Terminology description for Figure 1

Terminology	Description
Shared Pool	Pool holding currently used shared connections.
Unshared Pool	Pool holding currently used unshared connections.
Free Pool	Pool holding available free connections.
Close()	Application invokes close() method on the connection object.
Local Transaction Containment (LTC)	Application which operates outside of a global transaction acquires a default context in WebSphere Application Server called LTC.
Global Transaction (GT)	A global transaction context might be created automatically for EJB methods by using container managed transaction support and specifying an appropriate transaction attribute for the method. The Java Transaction API (JTA) UserTransaction interface can also be used to create a global transaction context from within a servlet, a message driven bean, or an EJB component that specifies the use of bean managed transactions. Within a global transaction, multiple resource managers can be accessed and the transaction manager will coordinate among all the resource managers to ensure the atomicity of updates.

Table 2 explains the state of a connection when an application invokes the close() method on connection object, releasing a connection to free pool.

Table 2. Conditions to release a connection to free pool

Connection type	AutoCommit	LTC (Local Transaction Containment)	GT (Global Transaction)	Transition
Shared	True	True	False	Connection does not return to the free pool. The sharing scope is the transaction and until the LTC ends, the connection will not be returned to the free pool.
	False	True	False	Changing the Autocommit value will not affect the release of shared connection from shared pool to free pool. Only when transaction ends the shared connection is returned to the free pool.
	True/False	False	True	Within a global transaction, the database ignores the Autocommit setting. Transaction Manager takes care of commit or rollback as per application call, and once the transaction ends the connection is returned back to the free pool.
Unshared	True	True	False	Connection is returned to the free pool immediately.
	False	True	False	If Autocommit is set to false, then the connection cannot be returned back to the free pool. Application has to invoke commit on connection object explicitly prior to closing the connection. This would return the connection back to free pool.
	True/False	False	True	Connections are returned back to the free pool when the connection is closed and global transaction ends. The Autocommit setting is ignored.

Multi-threaded access to a connection

Multi-threaded access to a connection occurs when an application shares a connection handle across multiple threads before closing it. This condition should be avoided, as it can lead to various unexpected failures during the execution of the application.

This section describes some scenarios that could lead to multi-threaded access to a connection. These scenarios have been recreated with WebSphere Application Server V7.0 and V8.0 using and IBM DB2 database.

Scenario 1: Multiple threads sharing a single connection

The code in Listing 1 creates a connection object at class scope and then shares the same database connection object across multiple threads. Each thread creates a statement using the connection object, executes the statement and then closes the connection.

Listing 1. Scenario 1: Multiple threads sharing a single connection

              
import java.sql.*;
import javax.naming.*;
import javax.sql.*;
public class Test extends Thread{ 
     static Connection conn = null ; 
     static DataSource ds = null;
     
     public static void main (String args [])   {
         try{
                Context initialContext = new InitialContext();
                ds = (DataSource)initialContext.lookup("java:comp/env/test");
                //get connection from datasource
                conn = ds.getConnection();
         }catch (Exception e) {
                 // handle exception
                 e.printStackTrace();
         }
         int NUM = 5;
         // create multiple threads
         Thread[] multithread = new Thread[NUM];
         // spawn threads
         for (int i = 0; i < NUM; i++)   {
         multithread[i] = new Test();
         multithread[i].start();
      }
    }

    public void run()  {
         // Create a Statement
        try{
                 Statement  stmt = conn.createStatement (); 
                 ResultSet rs = stmt.executeQuery ("select * from EMPLOYEE");
                 stmt.close();
                 rs.close();
                 conn.close();
        }catch (Exception e){
                //handle exception 
                e.printStackTrace();
        }
    }
}

In this scenario, when one thread closes the connection, the other threads operating on the same connection will be affected. This could result in the exception shown in Listing 2.

Exception description (Listing 2): Attempted to perform an operation on a Statement object that is already closed. Retrieve a new instance of the Statement object on which to perform the operation.

Listing 2. Scenario 1: Exception

              
com.ibm.db2.jcc.am.SqlException: [jcc][10120][10943][3.63.75] Invalid operation: 
statement is closed. ERRORCODE=-4470, SQLSTATE=null
        at com.ibm.db2.jcc.am.fd.a(fd.java:663)
        at com.ibm.db2.jcc.am.fd.a(fd.java:60)
        at com.ibm.db2.jcc.am.fd.a(fd.java:103)
        at com.ibm.db2.jcc.am.yn.wb(yn.java:4177)
        at com.ibm.db2.jcc.am.yn.a(yn.java:1695)
        at com.ibm.db2.jcc.am.yn.getMoreResults(yn.java:1095)
        at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement

The exception shown in Listing 3 could also be thrown.

Exception description (Listing 3): Attempted to perform an operation on a Connection object that is already closed. Retrieve a new instance of the Connection object on which to perform the operation.

Listing 3. Scenario 1: Exception

              
com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Connection is closed.
at com.ibm.ws.rsadapter.jdbc.WSJdbcWrapper.createClosedException(WSJdbcWrapper.java:114)
[4/22/12 2:10:18:825 CDT] 00000079 SystemErr     R at 
com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.runtimeXIfNotClosed(WSJdbcConnection.java:3427)
[4/22/12 2:10:18:825 CDT] 00000079 SystemErr     R  at 
com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.createStatement(WSJdbcConnection.java:1684)
[4/22/12 2:10:18:825 CDT] 00000079 SystemErr     R  at 
com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.createStatement(WSJdbcConnection.java:1634)

Scenario 2: Application passing connection handle

Application code should not pass a cached connection handle from one instance of a data access client to another client instance. Transferring the connection handle between client instances creates the problematic contingency of one instance using a connection handle that is referenced by another.

For example, when the application code of a client instance that receives a transferred handle closes the handle and the client instance that retains the original reference to the handle tries to reclaim it, the application server issues an exception.

Listings 4 and 5 shown some exceptions expected in this case.

Exception description (Listing 4): An exception was detected cleaning up the ManagedConnection for a destroy operation. Refer to the error reported by the database software to help determine the cause of the error.

Listing 4. Scenario 2: Exception

              
0000004d WSRdbManagedC W   DSRA0180W: Exception detected during 
ManagedConnection.destroy().  The exception is:  
com.ibm.ws.exception.WsException: DSRA0080E: An exception was received by 
 the Data Store Adapter Invalid operation: Connection is closed. ERRORCODE=- 
4470, SQLSTATE=08003. With SQL State: 08003 SQL Code : -4470

Exception description (Listing 5): An exception was detected while closing JDBC Connection which was already closed.

Listing 5. Scenario 2: Exception

              
java.lang.IndexOutOfBoundsException: Index: 1, Size: 0
at java.util.ArrayList.RangeCheck(ArrayList.java:572)
at java.util.ArrayList.get(ArrayList.java:347)
at com.ibm.ws.rsadapter.jdbc.WSJdbcObject.closeChildWrappers(WSJdbcObject.java:222)
at com.ibm.ws.rsadapter.jdbc.WSJdbcObject.close(WSJdbcObject.java:184)

Scenario 3: Closing connections in finalize method

If an application closes the connection handle in the finalize() method, then the garbage collection thread might run and try to close the connection at the same time as the connection manager cleanup, resulting in unexpected behavior. Listing 6 illustrates the invocation of the close() method on a connection object within the finalize() method.

Listing 6. Scenario 3: Closing connections in finalize method

              
public void example {
    Connection Conn=null; 
    // other methods
    
    protected void finalize() throws Throwable {
        conn.close();                
     }

There could also be a problem if a JDBC object is not closed by the thread that is using the object. An example of such a scenario is when a reference to this JDBC object is stored in a different object and the reference is closed by a finalize() method during garbage collection.

The exception that could be thrown here is shown in Listing 7.

Exception description (Listing 7): The connection manager caught an exception while trying to perform an operation on a ManagedConnection.

Listing 7. Scenario 3: Exception

              
MCWrapper     E   J2CA0081E:
Method cleanup failed while trying to execute method cleanup on
ManagedConnection WSRdbManagedConnectionImpl@7dd47dd4 from resource <resource name>

Scenario 4: SQLj applications must close SQLj connection context

The IBM implementations of JDBC and SQLJ provide a number of application programming interfaces, properties, and commands for developing JDBC and SQLJ applications. (See Resources for more about SQLJ.) Not closing the SQLJ connection context will result in the finalize method of the SQLJ connection context object being run by the garbage collector. Part of the SQLJ connection context finalize method is to close the SQLJ connection context underlying objects (for example, prepared statements, connections, profiles, and so on). The fact that the closure happens by the garbage collector on the finalizer thread, results in violation of the WebSphere Application Server programming model as multiple threads access the same WebSphere Application Server JDBC object (the normal thread, and the finalizer thread). This is shown in Figure 2.

Figure 2. Multithreaded access to connection object

The code in Listing 8 illustrates this scenario.

Listing 8. Scenario 4: SQLj applications must close SQLj connection contex

              
import java.sql.*; 
import javax.naming.*; 
import javax.sql.*;

#sql context CtxSqlj;       // Create connection context class CtxSqlj
Context ctx=new InitialContext();
DataSource ds=(DataSource)ctx.lookup("jdbc/sampledb");
Connection con=ds.getConnection();
String empname;             // Declare a host variable

con.setAutoCommit(false);   // Do not autocommit
CtxSqlj myConnCtx=new CtxSqlj(con);
 // Create connection context object myConnCtx
#sql [myConnCtx] {SELECT LASTNAME INTO :empname FROM EMPLOYEE 
   WHERE EMPNO='000010'};  
  // Use myConnCtx for executing an SQL statement 
   
myconnCtx,close(KEEP_CONNECTION);

In Listing 8, KEEP_CONNECTION is a constant that can be passed to the close() method. It indicates that the underlying JDBC Connection object should not be closed.

The application must close the SQLJ connection context when it is done using it. Closing the context must be done with the KEEP_CONNECTION option set to false, as shown in Listing 9.

Listing 9. Scenario 4: Usage of KEEP_CONNECTION

              
public static final boolean KEEP_CONNECTION=false;
myContext.close(KEEP_CONNECTION);

Failure to close the context with the KEEP_CONNECTION option will result in an exception in WebSphere Application Server as illustrated in Listing 10.

Exception description (Listing 10): Attempted to perform an operation on a Connection object that is already closed. Retrieve a new instance of the Connection object on which to perform the operation.

Listing 10. Scenario 4: Exception

              
com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Connection is closed.

Scenario 5: Caching a connection in an application

Listing 11 illustrates the scenario where a connection handle is cached and then reused across successive servlet invocations. Each request to the servlet is serviced by a new separate thread and all these threads share a single connection handle.

Listing 11. Scenario 5: Caching a connection in an application

              
/**
 * Servlet implementation class ServletTest
 */
public class ServletTest extends HttpServlet {
Connection conn=null;
protected void doGet(HttpServletRequest request, HttpServletResponse response) 
    throws ServletException, IOException {
    try{
        Context initialContext = new InitialContext();
        DataSource ds1 =(DataSource)initialContext.lookup("java:comp/env/test");
        if (conn==null){
            conn = ds1.getConnection();
            Statement stmt = conn.createStatement ();
            ResultSet rs = stmt.executeQuery ("select * from EMPLOYEE");
            conn.close();
        } else {
            Statement stmt = conn.createStatement ();
            ResultSet rs = stmt.executeQuery ("select * from EMPLOYEE");
            conn.close ();
        }
    }catch(Exception e ) {
         // handle the exception
         e.printStackTrace();
    }
  }
}

Once the connection handle is closed, the next servlet request, which is a new thread, would try to create a statement on same closed handle and fail with ObjectClosedException, shown in Listing 12.

Exception description (Listing 12): Attempted to perform an operation on a Connection object that is already closed. Retrieve a new instance of the Connection object on which to perform the operation.

Listing 12. Scenario 5: Exception

              
com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Connection is closed.
[2/14/12 11:55:07:768 IST] 0000001c SystemErr     R  
at com.ibm.ws.rsadapter.jdbc.WSJdbcWrapper.createClosedException(WSJdbcWrapper.java:109)
[2/14/12 11:55:07:768 IST] 0000001c SystemErr     R  
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.activate(WSJdbcConnection.java:2812)
[2/14/12 11:55:07:768 IST] 0000001c SystemErr     R  
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.createStatement(WSJdbcConnection.java:1605)
[2/14/12 11:55:07:768 IST] 0000001c SystemErr     R  
at com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.createStatement(WSJdbcConnection.java:1585)

Invoking the close() method on the connection handle frees up the connection to the free pool, and the connection handle will still be available for reuse when getConnection() method is called on the data source.

When the close method is not invoked on the connection handle, the connection is released to the free pool at the end of the LTC and the connection handle is dissociated from the connection.

Scenario 6: Handling connection error

In Listing 13, a connection handle is not closed by the application and is reused across successive servlet invocations. In the event of a database hang or a network problem between WebSphere Application Server and the database, the connection is marked as stale and the connection or connection pool is purged, as per the configured purge policy.

Listing 13. Scenario 6: Handling connection Error

              
public class ServletTest extends HttpServlet {
    Connection conn=null;
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
    throws ServletException, IOException {
       try {
           Context initialContext = new InitialContext();
           DataSource ds1 = (DataSource)initialContext.lookup("java:comp/env/test");
           conn = ds1.getConnection();
           Statement stmt = conn.createStatement ();  //Database hang or network problem
           rs = stmt.executeQuery ("select * from EMPLOYEE");
           rs.close();
           stmt.close();
       } catch (Exception e) {
           // handle exception
           e.printStackTrace();
       }
    }
}

Any subsequent request using the same connection handle will result in the exception illustrated in Listing 14.

Exception description (Listing 14): Attempted to perform an operation on a Connection object that is already closed. Retrieve a new instance of the Connection object on which to perform the operation.

Listing 14. Scenario 6: Exception

              
com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: Connection is closed.

Scenario 7: Caching the ResultSet objects in an application

In Listing 15, the ResultSet object is cached and then reused across successive servlet invocations.

Listing 15. Scenario 7: Caching the ResultSet objects in an application

              
public class ServletTest extends HttpServlet {
    ResultSet rs=  null;
    Connection conn = null;
    
    protected void doGet(HttpServletRequest request, HttpServletResponse response) 
    throws ServletException, IOException {
        try{
            Context initialContext = new InitialContext();
            DataSource ds1 = (DataSource)initialContext.lookup("java:comp/env/test");
            conn = ds1.getConnection();
            if(rs==null){
                Statement stmt = conn.createStatement();
                rs = stmt.executeQuery("select * from EMPLOYEE");
        }
        System.out.println(rs.next());
        } catch (Exception e) {
            // handle exception
            e.printStackTrace();
        }
    }
  }

When the LTC ends, the connection is returned back to the free pool, and child objects of the connection including the ResultSet are closed. Therefore, any new request to access the cached ResultSet would encounter the exception shown in Listing 16.

Exception description (Listing 16): Attempted to perform an operation on a ResultSet object that is already closed. Retrieve a new instance of the ResultSet object on which to perform the operation.

Listing 16. Scenario 7: Exception

              
[3/14/12 17:14:26:667 IST] 00000023 SystemErr     R
com.ibm.websphere.ce.cm.ObjectClosedException: DSRA9110E: ResultSet is closed.
[3/14/12 17:14:26:667 IST] 00000023 SystemErr     R at 
com.ibm.ws.rsadapter.jdbc.WSJdbcWrapper.createClosedException(WSJdbcWrapper.java:109)
[3/14/12 17:14:26:667 IST] 00000023 SystemErr     R at 
com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.runtimeXIfNotClosed(WSJdbcResultSet.java:3359)
[3/14/12 17:14:26:667 IST] 00000023 SystemErr     R at 
com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.next(WSJdbcResultSet.java:3130)
[3/14/12 17:14:26:667 IST] 00000023 SystemErr     R at 
ServletTest.doGet(ServletTest.java:55)

Scenario 8: Application accessing a shareable cached connection across transactions and methods

When an application accesses a shareable cached connection across transactions and methods, it follows this pattern:

Get a connection
Begin a global transaction
Use the connection
Commit a global transaction
Use the connection again

Be aware that, Statements, PreparedStatements, and ResultSets are closed implicitly after a transaction ends, while the connection remains valid. Table 3 lists the valid and invalid sequence of actions in this scenario.

Table 3. Valid and invalid sequence of actions

Valid	Invalid (Exception will be thrown)
start transaction	start transaction
get connection	get connection
use connection	use connection
	Close connection
Commit transaction	Commit transaction
pass connection to new method	pass connection to new method
start new transaction	start new transaction
use connection	use connection
close connection	close connection
commit transaction	commit transaction