Connections and transactions
- One-phase commits and two-phase commits
- Connection life cycle
- Getting connections
- Returning connections
- Connection pooling
- Benefits of connection pooling
- When to use connection pooling
- How connections are pooled together
- Avoiding a deadlock
- Unshareable and shareable connections
- Factors that determine sharing
- Sharing a connection with a CMP bean
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...
- The application has called the data source or connection factory (getConnection).
- A free connection is available in the pool with matching properties (freeConnectionAvailable)
- 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...Here is a list of guarding conditions and descriptions.InFreePool > InUse... getConnection AND freeConnectionAvailable AND NOT(shareableConnectionAvailable)
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.
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.
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 conditiongetConnection 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...
- The application calls the getConnection() method on the data source or connection factory
- No connections are available in the free pool (NOT(freeConnectionAvailable))
- The pool size is less than the maximum pool size (poolSizeLTMax)
- If the request is for a sharable connection and there is no sharable connection already in use with the same sharing properties (NOT(shareableConnectionAvailable)) OR the request is for an unsharable connection (unshareableConnectionRequest)
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.
The transition from the InFreePool state to the InUse state is the most common transition when the application requests a connection from the poolInFreePool>InUse... getConnection AND freeConnectionAvailable AND (unshareableConnectionRequest OR NOT(shareableConnectionAvailable)
This transition states that a connection is placed in use from the free pool if:
- the application has issued a getConnection() call
- a connection is available for use in the connection pool (freeConnectionAvailable),
- and one of the following is true...
- the request is for an unsharable connection (unsharableConnectionRequest)
- no connection with the same sharing properties is already in use in the transaction. (NOT(sharableConnectionAvailable)).
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.
The idea of connection sharing is seen in the transition on the InUse stateInUse>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.
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 transitionInUse>InFreePool... (close AND noOtherReferences AND NoTx AND UnshareableConnection) OR (ShareableConnection AND TxEnds)
Conditions that cause the transition from the InUse state are...
- If the application (or the container) calls close() (close) and there are no references (noOtherReferences) either by the application (application sharing) or by the transaction manager (NoTx - who holds a reference when the connection is enlisted in a transaction), the Connection object returns to the free pool.
- If the connection was enlisted in a transaction but the transaction manager ends the transaction (txEnds), and the connection was a shareable connection (ShareableConnection), the connection closes and returns to the pool.
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 staleInUse>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.
- 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.
- 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.
- 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.
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:
- It cannot tolerate the overhead of obtaining and releasing connections whenever a connection is used.
- It requires Java Transaction API (JTA) transactions within WAS.
- It needs to share connections among multiple users within the same transaction.
- It needs to take advantage of product features for managing local transactions within the appserver.
- It does not manage the pooling of its own connections.
- 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:
- Each thread has its first database connection, and all are in use.
- Each thread is waiting for a second database connection, and none would become available since all threads are blocked.
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...
- 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.
- Resource authentication
- In relational databases...
To enable connection sharing for resource adapters within the same transaction, the following connection properties must be the same...
- JNDI name. While not actually a connection property, this requirement simply means that you can only share connections from the same resource adapter in the same server.
- Resource authentication
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.
- Only connections acquired with the same resource reference (resource-ref), which specifies the res-sharing-scope as Shareable, are candidates for sharing. The resource-ref properties of res-sharing-scope and res-auth and the IBM extension isolationLevel help determine if it is possible to share a connection. IBM extension isolationLevel is stored in IBM deployment descriptor extension file; for example: ibm-ejb-jar-ext.xmi.
- You can only share connections that are requested with the same properties.
- Connection Sharing only occurs between different component instances if they are within a transaction (container- or user-initiated transaction).
- Connection Sharing only occurs within a sharing boundary. Current sharing boundaries include Transactions and LocalTransactionContainment (LTC) boundaries.
- Connection Sharing rules within an local transaction scope:
- For shareable connections, only Connection Reuse is allowed within a single component instance. Connection reuse occurs when the following actions are taken with a connection: get, use, commit/rollback, close; get, use, commit/rollback, close. Note that if you use the LTC resolution-control of ContainerAtBoundary then no start/commit is needed because that action is handled by the container.
The connection returned on the second get is the same connection as that returned on the first get (if the same properties are used). Because the connection use is serial, only one connection handle to the underlying physical connection is used at a time, so true connection sharing does not take place. The term "reuse" is more accurate.
- Shareable connections between transactions, container-managed, bean-managed, or local, follow these caching rules...
- In general, setting properties on shareable connections is not allowed because a user of one connection handle might not anticipate a change made by another connection handle. This limitation is part of the J2EE 1.3 standard.
- General users of resource adapters can set the connection properties on the connection factory getConnection call by passing them in a ConnectionSpec.
However, the properties set on the connection during one transaction are not guaranteed to be the same when used in the next transaction. Because it is not valid to share connections outside of a sharing scope, connection handles are moved off of the physical connection with which they are currently associated when a transaction ends. That physical connection is returned to the free connection pool. Connections are cleaned before going in the free pool. The next time the handle is used, it is automatically associated with an appropriate connection. The appropriateness is based on the security login information, connection properties, and (for the JDBC API) the isolation level specified in the extended resource reference, passed in on the original request that returned the current handle. Any properties set on the connection after it was retrieved are lost.
- For JDBC users, WAS provides an extension to enable one to pass the connection properties through the ConnectionSpec.
Use caution when setting properties and sharing connections in a local transaction scope. Ensure that other components with which the connection is shared are expecting the behavior resulting from your settings.
- You cannot set the IsolationLevel when using a shareable connection for the JDBC API using a relational resource adapter in a global transaction. WAS provides an extension to the resource reference to enable one to specify the isolation level. If your application requires the use of multiple isolation levels, create multiple resource references and map them to the same data source or connection factory.
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.
- Sharing a connection between CMP beans or methods
When all CMP bean methods use the same access intent, they all share the same physical connection. A different access intent policy triggers the allocation of a different physical connection. For example, a CMP bean has two methods; method 1 is associated with wsPessimisticUpdate intent, whereas method 2 has wsOptimisticUpdate access intent. Method 1 and method 2 cannot share the same physical connection within a transaction. In other words, an XA DataSource is required to run in a global transaction.
You can experience some deadlocks from a database if both methods try to access the same table. Therefore, sharing a connection is determined by the access intents that are defined in the CMP methods.
- Sharing a connection between CMP and BMP beans
There are two options to ensure that both CMP and BMP beans share the same physical connection...
- Define the same access intent on both CMP and BMP bean methods. Because both use the same access intent, they share the same physical connection. The advantage to using this option is that the backend is transparent to a BMP bean; however, this BMP is not portable because it uses the WebSphere extended API to handle the isolation level.
- Determine the isolation level that the access intent uses on a CMP bean method, then use the corresponding isolation level that is specified on the resource reference to look up a data source and a connection. This option is more of a manual process, and the isolation level might be different from database to database.
- Sharing a connection between CMP and a JDBC application that is used by a servlet or a Session Bean
Determine the isolation level that the access intent uses on a CMP bean method, then use the corresponding isolation level specified on the resource reference to look up a data source and a connection.