[devwork] 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.

---

Back to top

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 ->...

---

Back to top

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 or Connection 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.

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:

1. Get a connection
2. Begin a global transaction
3. Use the connection
4. Commit a global transaction
5. 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

Related topics
Here are some of the custom properties and configurations in WebSphere Application Server that affect multi-threaded access to a connection.
Re-using the connections across servlets
DisableMultiThreadedServletConnectionMgmt is a custom property that is meant to allow connections to be reused across servlets. This property can be set as a web container custom property (Figure 3). In the administrative console, navigate to Application servers > server_name > Web container > Custom properties. Create a property named DisableMultiThreadedServletConnectionMgmt and set its value to true.
With this property enabled, if the connection handle is not closed and the servlet ends, the web container, as part of postinvoke, parks the connection and does not close the connection handle.


Non-transactional data sources
a non-transactional data source specifies that the application server will not enlist the connections from this data source in global or local transactions. An application must explicitly call setAutoCommit(false) on the connection if it wants to start a local transaction on the connection, and it must commit or rollback the transaction that they start.
To enable a non-transactional data source, navigate form the administrative console to Data sources > datasource_name > WebSphere Application Server data source properties.
Select the Non-transactional data source checkbox (Figure 4).

maxNumberOfMCsAllowableInThread
maxNumberOfMCsAllowableInThread is a custom property that can be set on the connection pool and helps in detecting higher than expected usage of number of managed connections on a thread. Figure 5 shows the property set in the administrative console.

Sharing Local Transaction Containment (LTC)
In WebSphere Application Server V7.0 and later, an LTC can be shareable; that is, a single LTC can span multiple application components, including web application components and enterprise beans that use container-managed transactions, so that these components can share connections without using a global transaction.
This can be achieved by setting the Shareable attribute in the deployment descriptor of each component.
When you set the Shareable attribute, the extended deployment descriptor XML file includes the line shown in Listing 17.

Listing 17. Setting Shareable attribute in extended deployment descriptor

<local-transaction boundary="BEAN_METHOD" resolver="CONTAINER_AT_BOUNDARY" unresolved-action="COMMIT" shareable="true"/>

---

Back to top

Detecting MultiThreaded access to a connection
To determine the source of multi-threaded access in an application, you can enable the enableMultithreadedAccessDetection property on the data source. When this property has a value set to true, the WebSphere Application Server relational resource adapter will log a DSRA8720W message if it detects multi-threaded access to JDBC objects. The exception also provides Last Used ThreadId, Current ThreadId, and stack trace of the current thread.
To enable this property using the administrative console, navigate to Resources > JDBC > JDBC providers > JDBC_provider > Data sources > data_source name > WebSphere Application Server data source properties, and select the check box to enable this property (Figure 6).

Multi-thread use JCA programming model violation diagnostic alert
Multi-thread use of a connection raises an alert when an application component acquires a connection handle using a connection factory, and then the component uses the handle on a different thread from which the handle was acquired. This can be enabled as part of the Performance and Diagnostic Advisors from the administrative console, as depicted in Figure 7.

Conclusion
This article provided information about the transition of connections between shared/unshared and free pools and about the conditions that lead to such transitions. It also illustrated scenarios that could lead to multi-threaded access to a JCA connection, the various resulting exceptions, and the multi-threaded access detection capabilities in WebSphere Application Server.
Acknowledgements
The authors thank James M Stephens and Manu T George for their valuable suggestions and feedback.

Resources
Learn

• Default behavior of managed connections in WebSphere Application Server
  
  
• WebSphere Application Server Information Center
  
  
• Technote: SQLj Applications must close Connection Context after usage
  
  
• Technote: Multiple thread access to a JDBC resource adapter object can cause an IndexOutOfBoundsException exception
  
  
• JDBC and SQLJ connection pooling support
  
  
• WebSphere Application Server product information
  
  
• IBM developerWorks WebSphere
  

Get products and technologies

• Download WebSphere Application Server V8 trial