Database Connection Management 

Basics

The management of the database connections follows the basic rules, which the Connector Container Service applies to the connections to other types of EIS. The specific feature about working with the database connections is the option to cache SQL statements.

We recommend that you do not use cached statements.

From the transaction management perspective, there are four possible states of each connection:

●      Closed

●      Active and enlisted in a JTA transaction

●      Active and a local transaction is running

●      Active and no transaction is running

To reduce costs and time consumption, the AS Java provides connection pooling which comprises a single-time creation of connections and their reusability. Different applications share the connection pools.

More information:

Sharing Connections

Connection Pooling

Automatic Connection Cleanup

Sometimes an application does not close a connection, that is, it does not return it to the pool, or the application uses the connection for a long time. This prevents other applications from using the connection and increases their waiting time, making their performance unacceptably slower.

The automatic connection dissociation mechanism is implemented as a solution to this problem.

After each call to a Web component (servlet, JSP, portal, iView) or to a stateless EJB method, the mechanism checks for active connections and closes them, if possible. The same check is also performed after a commit/rollback of a JTA transaction or a local transaction.

Different resource adapters provide different connections and from the database connection management perspective, resource adapters are divided into three groups depending on their properties:

●      Resource adapters which support dissociation

When the resource system determines a connection handle as not in a transaction, the resource adapter can dissociate the physical connection from this connection handle, thus releasing the physical connection for further use. In this case, the application can still use the connection handle associated with a new physical connection.

JDBC Connector (DB) connections support dissociation.

●      Resource adapters which support reassociation but do not support dissociation

The connection handle can be associated with another physical connection but cannot be dissociated.

●      Resource adapters which do not support dissociation or reassociation

Stateless Components

After the stateless component’s method ends, the system performs the following operations depending on the connection state and the relevant resource adapter properties:

Connection state	Resource adapter supports dissociation	Resource adapter supports reassociation	Resource adapter does not support dissociation or reassociation
Closed	No action	No action	No action
Active and enlisted in a JTA transaction	The connection handle is dissociated but remains usable. The physical connection is returned to the connection pool when the transaction is completed.	The connection handle is reassociated with a new physical connection which is not in a JTA transaction. The physical connection is returned to the connection pool when the transaction is completed.	The application server attempts to reassociate the connection handle with a new physical connection which is not in a JTA transaction. If this operation is not successful, the behavior of the system depends on the resource adapter implementation. The connection handle usually remains active and associated with the previous physical connection.
Active and a local transaction is running This is a wrong application scenario. All local transactions must be completed during one and the same method call.	The physical connection is returned to the connection pool. The connection handle remains unusable.	The physical connection is returned to the connection pool. The connection handle remains unusable.	The physical connection is returned to the connection pool. The connection handle remains unusable.
Active and no transaction is running	The connection handle is dissociated but remains usable. The physical connection is returned to the connection pool.	No action	No action

Stateful Components

For stateful components, connection dissociation or reassociation is not performed after method calls.

JTA Transactions

After a commit/rollback of a JTA transaction, the system performs the following operations depending on the connection state and the relevant resource adapter properties:

Connection state	Resource adapter supports dissociation	Resource adapter supports reassociation	Resource adapter does not support dissociation or reassociation
Closed	No action	No action	No action
Active	The connection handle is dissociated but remains usable. The physical connection is returned to the connection pool.	The connection handle is reassociated with a new physical connection which is not in a JTA transaction.	The application server attempts to reassociate the connection handle with a new physical connection that is not in a JTA transaction. If this operation is not successful, the behavior of the system depends on the resource adapter implementation.

Local Transactions

After a commit/rollback of a local transaction, the system performs the following operations depending on the relevant resource adapter properties:

Connection state	Resource adapter supports dissociation	Resource adapter supports reassociation	Resource adapter does not support dissociation or reassociation
Active	The physical connection is returned to the pool. The connection handle remains unusable.	No action	No action

For better performance and usage of the connections for each application, service and library, we recommend that you keep the connection only when it is being used. Do not keep connections for subsequent requests and complete all local transactions during one and the same method call.

If a library or service is invoked from a Java EE component and a connection is acquired from this library or service, then the management of this connection is the same as that of connections acquired directly from the Java EE component.