The connection management architecture for both relational and procedural access to enterprise information systems (EIS) is based on the Java™ EE Connector Architecture (JCA) specification. The Connection Manager (CM), which pools and manages connections within an application server, is capable of managing connections obtained through both resource adapters (RAs) defined by the JCA specification, and data sources defined by the Java Database Connectivity (JDBC) specification. IBM WebSphere Application Server enables administrators to establish a pool of back end connections that applications can share on an application server. Connection pooling can improve the response time for applications because the overhead of creating a new connection is avoided for every new request to access the backend resource.
Optimal connection usage
Typically, the application component does a lookup of a connection factory in the Java Naming and Directory Interface (JNDI) name space and then uses the connection factory to get a connection to the underlying EIS. Careful usage of these connections can avoid connections becoming exhausted, and can also help applications scale.
Here are the precautions you must take in order to achieve optimal connection usage:
- Explicit and immediate connection close
The application component invokes the getConnection method on the connection factory to get an EIS connection. The returned connection instance represents an application-level handle to an underlying physical connection. After the application component finishes with the connection, it should close the connection using the close method on the Connection interface. If an application component fails to close an allocated connection after its use, that connection is closed by the application server only as part of cleanup of the transaction later. This would deny other requests to use that connection and limit scalability.
The application component is recommended to explicitly close the connection handle as soon as the handle has been used and is not required later. This reduces the possibility of connection leaks and enhances the application server’s ability to pool physical connections to the EIS.
- Shared connections and LocalTransactionContainment
Connections in WebSphere Application Server are shared by default. 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. For shared connections, the sharing scope is transaction. An application which operates outside of a global transaction acquires a default context in WebSphere Application Server called LocalTransactionContainment(LTC).
The shared connections will not be available for requests from other transactions until the LocalTransactionContainment ends. So it is always prudent to not have long running and nested LocalTranactionContainment that holds on to shared connections for long periods of time and leads to connection pool exhaustion.
An application component can use the JTA UserTransaction interface or a transaction demarcation API specific to an EIS to control the transaction boundaries and avoid long running LTC.
- Long running queries
A slow running query will hold the connection to a database and that connection will not be available to be used by any new requests trying to access the same resource. A connection can process only one operation at any point in time; for example, if a query takes 10 seconds, then another request to the same resource would be blocked to utilize that connection. Further, slow running queries combined with higher load would eventually lead to the connection pool getting exhausted. It would be helpful to constantly monitor UseTime and other statistics by enabling connection pool statistics as part of your Performance Monitoring Infrastructure.
- Unshared connections
Access to a resource marked as unshareable means that there is a one-to-one relationship between the connection handle 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. Typically, you must choose unshareable if you might do things to the connection that could result in unexpected behavior occurring in another application that is sharing the connection.
Choosing unshared connections might increase the number of connections being used and lead to connection pool exhaustion at higher load as a new connection will be used for every request.
- AutoCommit and LocalTransactionContainment
When a connection is in an auto-commit mode, an operation on the connection automatically commits after it has been executed. The auto-commit mode will be off if multiple interactions have to be grouped in a single transaction, either local or XA, and committed or rolled back as a unit.
For connections that are used outside a transaction, the auto-commit mode is set to true and LocalTransactionContainment will be active. When using Unshared connections, if Autocommit is set to false by the application, then the connection will not be available for other requests even with applications closing the connection. An application has to invoke commit on a connection object explicitly prior to closing the connection.
- Transaction Isolation and Read Only properties
An application that changes the transaction isolation level or read only properties would be provided with a new connection rather than providing the same shared connection, even in the same transaction. Frequent changes to the transaction isolation level from the application will render different connections and increase the number of connections needed, ultimately leading to exhausting the connection pool.
The same principal means the same user and password are required for relational databases. If applications use a different user ID and password to acquire the connection, the physical connections returned from the getConnection calls will be different. When an application's resource authentication setting is set to Application, you can acquire the connections with either getConnection() or getConnection(String userid, String password). The connections acquired from these two calls are not shared, even if the component-managed authentication alias associated with the datasource has the same user ID and password used in getConnection(String userid, String password).
- Unshareable JMS sessions
JMS connections for the JMS provider will not be shareable because they are non-transactional, and the Java EE Connector Architecture (JCA) specification only permits transactional resources to be shareable. If the res-sharing-scope is set to shareable in a JMS resource reference, the setting will be ignored and unshareable connections will be used. However, JMS sessions are transactional, and can be shareable. JMS sessions are shareable by default.
When a shareable JMS session gets enlisted in the Transaction, closing the session will not release it to the pool. However, closing the connection makes the connection available to other applications. Next, when the same application asks for a connection, in some instances it might probably get a new connection and in such scenarios a new session will be created when the application creates a session.
In such scenarios, to release the session to the pool as soon as it is closed, sessions can be made unshareable as well. This means that whenever an application calls Session.close(), the JMS session is automatically released from any transaction that it is associated with and returned to the session pool. It would be appropriate to confirm the above scenario and understand other implications before making sessions unshareable.
Application usage of JCA connections plays an important role in optimal usage of pooled connections. Following some usage patterns and having knowledge of connection behavior can help avoid exhausting the connection pool and help supplement scalability.
The author thanks Murali K Surampalli and Rispna Jain for their valuable suggestions and feedback.
- WebSphere Application Server V8.5 Information Center
- Shared and Unshared Connection
- Local transaction containment
- IBM developerWorks WebSphere