Connections and transactions

+

 

Contents

  1. One-phase commits and two-phase commits
  2. Connection life cycle
  3. Getting connections
  4. Returning connections
  5. Connection pooling
  6. Benefits of connection pooling
  7. When to use connection pooling
  8. How connections are pooled together
  9. Avoiding a deadlock
  10. Unshareable and shareable connections
  11. Factors that determine sharing
  12. Sharing a connection with a CMP bean

 

Overview

JDBC connections occur as part of global and local transactions and, when first obtained through a getConnection call, default to...

AutoCommit = TRUE

With global transactions you can share connections, the AutoCommit setting is ignored, and the database transaction service controls commit and rollback processing.

With local transactions you can reuse connections by serially excuting get/use/close connections using the same data source. If resolution-control is set to ContainerAtBoundary, AutoCommit is FALSE before work begins, then commits or rolls back the work as appropriate at the end of the local scope.

If you use multiple distinct connections within a global transaction, all work is guaranteed to commit or roll back together, which is not the case for local transactions

 

One-phase commits and two-phase commits

With one-phase commits all transactions complete or fail atomically. Everything is either commited, or everything is rolled back, with only one connection per global transaction.

With two-phase commits, multiple physical connections can be instantiated using getConnection calls that specify res-sharing-scope=Unshareable and JTA Enabled. If you fail to enable JTA, the follow XAException will result...

WTRN0063E: An illegal attempt to enlist a one phase capable resource with existing two phase capable resources has occurred.

 

Connection life cycle

ManagedConnection objects have three states:

DoesNotExist Before connection is created
InFreePool Connection created
InUse Connection created and allocated to an application.

Transitions between different states are controlled by guarding conditions. For example, you can make the transition from the InFreePool to InUse state only if...

  1. The application has called the data source or connection factory (getConnection).

  2. A free connection is available in the pool with matching properties (freeConnectionAvailable)

  3. One of the two following conditions are true:

    • getConnection is on behalf of a resource reference marked unsharable

    • getConnection is on behalf of a resource reference marked shareable, but no shareable connection in use has the same properties.

This transition description follows... 

InFreePool > InUse...
getConnection AND
freeConnectionAvailable AND
NOT(shareableConnectionAvailable)
Here is a list of guarding conditions and descriptions.

Condition Description
ageTimeoutExpired Connection is older then its ageTimeout value.
close Application calls close method on the Connection object.
fatalErrorNotification A connection has just experienced a fatal error.
freeConnectionAvailable A connection with matching properties is available in the free pool.
getConnection Application calls getConnection method on DataSource or ConnectionFactory object.
markedStale Connection is marked as stale, typically in response to a FatalErrorNotification.
noOtherReferences There is only one connection handle to the ManagedConnection, and the Transaction Service is not holding a reference to the ManagedConnection.
noTx No transaction is in force.
poolSizeGTMin Connection pool size is greater than the minimum pool size (minimum number of connections)
poolSizeLTMax Pool size is less than the maximum pool size (maximum number of connections)
shareableConnectionAvailable The getConnection request was for a shareable connection and one with matching properties is in use and available to share.
TxEnds The transaction has ended.
unshareableConnectionRequest The getConnection request is for an unshareable connection.
unusedTimeoutExpired Connection is in the free pool and not in use past its unused timeout value.

 

Getting connections

The first set of transitions covered are those in which the application requests a connection from either a data source or a connection factory. In some of these scenarios, a new connection to the database results. In others, the connection might be retrieved from the connection pool or shared with another request for a connection.

 

DoesNotExist

Every connection begins its life cycle in the DoesNotExist state. When an appserver starts, the connection pool does not exist. Therefore, there are no connections. The first connection is not created until an application requests its first connection. Additional connections are created as needed, according to the guarding condition 

getConnection AND
NOT(freeConnectionAvailable) AND
poolSizeLTMax AND
(NOT(shareableConnectionAvailable) OR
(unshareableConnectionRequest)

This transition specifies that a Connection object is not created unless the following conditions occur...

All connections begin in the DoesNotExist state and are only created when the application requests a connection. The pool grows from 0 to the maximum number of connections as applications request new connections. The pool is not created with the minimum number of connections when the server starts.

If the request is for a sharable connection and a connection with the same sharing properties is already in use by the application, the connection is shared by two or more requests for a connection. In this case, a new connection is not created. For users of the JDBC API these sharing properties are most often userid/password and transaction context; for users of the Resource Adapter CCI they are typically ConnectionSpec, Subject, and transaction context.

 

InFreePool

The transition from the InFreePool state to the InUse state is the most common transition when the application requests a connection from the pool 

InFreePool>InUse...
getConnection AND
freeConnectionAvailable AND
(unshareableConnectionRequest OR
NOT(shareableConnectionAvailable)

This transition states that a connection is placed in use from the free pool if:

Any connection request that a connection from the free pool can fulfill does not result in a new connection to the database. Therefore, if there is never more than one connection used at a time from the pool by any number of applications, the pool never grows beyond a size of one. This number can be less than the minimum number of connections specified for the pool. One way that a pool grows to the minimum number of connections is if the application has multiple concurrent requests for connections that must result in a newly created connection.

 

InUse

The idea of connection sharing is seen in the transition on the InUse state 

InUse>InUse...
getConnection AND
ShareableConnectionAvailable

This transition states that if an application requests a shareable connection (getConnection) with the same sharing properties as a connection that is already in use (ShareableConnectionAvailable), the existing connection is shared.

The same user (user name and password, or subject, depending on authentication choice) can share connections but only within the same transaction and only when all of the sharing properties match. For JDBC connections, these properties include the isolationLevel which is configurable on the resource-reference (IBM WebSphere extension) to data source default. For a resource adapter factory connection, these properties include those specified on the ConnectionSpec. Because a transaction is normally associated with a single thread, never share connections across threads.

It is possible to see the same connection on multiple threads at the same time, but this situation is an error state usually caused by an application programming error.

 

Returning connections

All of the transitions so far have covered getting a connection for application use. From this point, the transitions result in a connection closing and either returning to the free pool or being destroyed. Applications should explicitly close connections (note: the connection that the user gets back is really a connection handle) by calling close() on the Connection object. In most cases, this action results in the following transition 

InUse>InFreePool...
(close AND
noOtherReferences AND
NoTx AND
UnshareableConnection)
OR
(ShareableConnection AND
TxEnds)

Conditions that cause the transition from the InUse state are...

When the application calls close() on a connection, it is returning the connection to the pool of free connections; it is not closing the connection to the data store. When the application calls close() on a currently shared connection, the connection is not returned to the free pool. Only after the application drops the last reference to the connection, and the transaction is over, is the connection returned to the pool. Applications using unsharable connections must take care to close connections in a timely manner. Failure to do so can starve out the connection pool making it impossible for any application running on the server to get a connection.

When the application calls close() on a connection enlisted in a transaction, the connection is not returned to the free pool. Because the transaction manager must also hold a reference to the connection object, the connection cannot return to the free pool until the transaction ends. Once a connection is enlisted in a transaction, you cannot use it in any other transaction by any other application until after the transaction is complete.

There is a case where an application calling close() can result in the connection to the data store closing and bypassing the connection return to the pool. This situation happens if one of the connections in the pool is considered stale. A connection is considered stale if you can no longer use it to contact the data store. For example, a connection is marked stale if the data store server is shut down. When a connection is marked as stale, the entire pool is cleaned out by default because it is very likely that all of the connections are stale for the same reason (or you can set your configuration to clean just the failing connection). This cleansing includes marking all of the currently InUse connections as stale so they are destroyed upon closing. The following transition states the behavior on a call to close() when the connection is marked as stale 

InUse>DoesNotExist...
close AND
markedStale AND
NoTx AND
noOtherReferences

This transition states that if the application calls close() on the connection and the connection is marked as stale during the pool cleansing step (markedStale), the connection object closes to the data store and is not returned to the pool.

Finally, you can close connections to the data store and remove them from the pool.

This transition states that there are three cases in which a connection is removed from the free pool and destroyed.

  1. If a fatal error notification is received from the resource adapter (or data source). A fatal error notification (FatalErrorNotification) is received from the resource adaptor when something happens to the connection to make it unusable. All connections currently in the free pool are destroyed.

  2. If the connection is in the free pool for longer than the unused timeout period (UnusedTimeoutExpired) and the pool size is greater than the minimum number of connections (poolSizeGTMin), the connection is removed from the free pool and destroyed. This mechanism enables the pool to shrink back to its minimum size when the demand for connections decreases.

  3. If an age timeout is configured and a given connection is older than the timeout. This mechanism provides a way to recycle connections based on age.

 

Connection pooling

When accessing any database, the initial database connection is an expensive operation. Connection pooling enables administrators to establish a pool of database connections that applications can share on an appserver. When connection pooling capabilities are used, performance improvements up to 20 times the normal results are realized. WAS does not support JDBC 3.0.

Each time a resource attempts to access a backend store (such as a database), the resource must connect to that data store. A connection requires resources to create, maintain, and then release the connection when it is no longer required.

The total data store overhead for an application is particularly high for Web-based applications because Web users connect and disconnect more frequently. In addition, user interactions are typically shorter. Often, more effort is spent connecting and disconnecting than is spent during the interactions. Also, because Internet requests can arrive from virtually anywhere, you can find usage volumes large and difficult to predict.

To help lessen these overhead problems, the WAS enables administrators to establish a pool of backend connections that applications can share on an appserver. Connection pooling spreads the connection overhead across several user requests, thereby conserving resources for future requests.

WAS supports JDBC 2.0 Standard Extension APIs to provide support for connection pooling and connection reuse. The connection pool is used to direct JDBC calls within the application, as well as for enterprise beans using the database.

Each entity EJB transaction requires an additional connection to the database specifically to handle the transaction. Take this into account when calculating the number of data source connections.

On UNIX platforms, a separate DB2 process is created for each connection and these processes quickly affect performance on systems with low memory and cause errors.

If clones are used, one data pool exists for each clone. This is important when configuring the database maximum connections.

 

Benefits of connection pooling

Connection pooling can improve the response time of any application that requires connections, especially Web-based applications. When a user makes a request over the Web to a resource, the resource accesses a data source. With connection pool, 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. When the request is satisfied and the response is returned to the user, the resource returns the connection to the connection pool for reuse. The overhead of a disconnect is avoided. Each user request incurs a fraction of the cost for connecting or disconnecting. After the initial resources are used to produce the connections in the pool, additional overhead is insignificant because the existing connections are reused.

 

When to use connection pooling

Use WebSphere connection pooling in an application that meets any of the following criteria:

  1. It cannot tolerate the overhead of obtaining and releasing connections whenever a connection is used.

  2. It requires Java Transaction API (JTA) transactions within WAS.

  3. It needs to share connections among multiple users within the same transaction.

  4. It needs to take advantage of product features for managing local transactions within the appserver.

  5. It does not manage the pooling of its own connections.

  6. It does not manage the specifics of creating a connection, such as the database name, user name, or password

 

How connections are pooled together

Whenever you configure a unique data source or connection factory you are required to give it a unique JNDI name. Use this name, along with its configuration information, to create a connection pool. A separate connection pool exists for each configured data source or connection factory.

A separate instance of a given configured connection pool is created on each appserver that uses that data source or connection factory. For example, if you run a three server cluster in which all of the servers use myDataSource, and myDataSource has a maximum connections setting of 10, then you can generate up to 30 connections (three servers times 10 connections). Be sure to consider this fact when determining how many connections to your backend resource you can support.

It is also important to note that when using connection sharing, it is only possible to share connections obtained from the same connection pool.

 

Avoiding a deadlock

Deadlock can occur if the application requires more than one concurrent connection per thread, and the database connection pool is not large enough for the number of threads. Suppose each of the application threads requires two concurrent database connections and the number of threads is equal to the maximum connection pool size. Deadlock can occur when both of the following are true:

To prevent the deadlock in this case, the value set for the database connection pool must be at least one higher, one of the waiting threads to complete its second database connection and free up to allow database connections.

To avoid deadlock, code the application to use, at most, one connection per thread. If the application is coded to require C concurrent database connections per thread, the connection pool must support at least the following number of connections, where T is the maximum number of threads. 

 T * (C - 1) + 1

The connection pool settings are directly related to the number of connections that the database server is configured to support. If the maximum number of connections in the pool is raised, and the corresponding settings in the database are not raised, the application fails and SQL exception errors are displayed in the stderr.log file.

 

Unshareable and shareable connections

WAS supports both unshareable and shareable connections. An unshareable connection is not shared with other components in the application. The component using this connection has full control of this connection.

You can share a shareable connection with other components within the same transaction as long as each getConnection request has the same connection properties. To enable connection sharing for data sources, the following connection properties must be the same...

  1. JNDI name. While not actually a connection property, this requirement simply means that you can only share connections from the same dataSource in the same server.

  2. Resource authentication

  3. In relational databases...

To enable connection sharing for resource adapters within the same transaction, the following connection properties must be the same...

In addition, the ConnectionSpec used to get the connection must also be the same.

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 the handle is associated with. This access implies that every call to getConnection returns a connection handle solely for the requesting user. Typically, choose unshareable if you might do things to the connection that could result in unexpected behavior occurring to another application that is sharing the connection (for example, changing the isolation level).

Marking a resource as Shareable allows for greater scalability. Instead of creating new physical connections on every getConnection invocation, the physical connection (that is, managed connection) is shared through multiple connection handles, as long as each getConnection request has the same connection properties. But, sharing a connection means that each user must not do anything to the connection that could change its behavior and disrupt a sharing partner (for example, changing the isolation level). The user also cannot code an application expecting sharing to take place because it is up to the run time to decide whether or not to share a particular connection.

For WAS, all sharing of connections is relative to the current Unit of Work (UOW) boundary. Anyone within a specific transaction, when getting a connection from a specific connection pool, gets a handle to the same physical connection (if the sharing properties are the same).

 

Factors that determine sharing

The listing here is not an exhaustive one. WAS might or might not share connections under different circumstances.

 

Sharing a connection with a CMP bean

WAS allows you to share a physical connection between a CMP bean, a BMP bean, and a JDBC application to reduce the resource allocation or deadlock scenarios. There are several ways to ensure that all these Entity beans and the JDBC applications are sharing the same physical connection.