Using connection pools helps to both alleviate connection management overhead and decrease development tasks for data access.
Each time an application attempts to access a backend store (such as a database), it requires resources to create, maintain, and release a connection to that datastore. To mitigate the strain this process can place on overall application resources, WebSphere Application Server enables administrators to establish a pool of backend connections that applications can share on an application server. Connection pooling spreads the connection overhead across several user requests, thereby conserving application resources for future requests. The connection pool is used to direct JDBC calls within the application, as well as for enterprise beans using a database.
Note: This version of WebSphere Application Server does not support JDBC 3.0.
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. Because users connect and disconnect frequently with applications on the Internet, the application requests for data access can surge to considerable volume. Consequently, the total datastore overhead quickly becomes high for Web-based applications, and performance deteriorates. When connection pooling capabilities are used, however, Web applications can realize performance improvements of up to 20 times the normal results.
With 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. 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 disconnection 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 WebSphere Application Server.
- 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 application server.
- 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
When you configure a unique data source or connection factory, you must give it a unique Java Naming and Directory Interface (JNDI) name. This JNDI name, along with its configuration information, is used to create the connection pool. A separate connection pool exists for each configured data source or connection factory.
Furthermore, Application Server creates a separate instance of the connection pool in each application server that uses the 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). Consider how this behavior potentially impacts the number of connections that your backend resource can support. See Connection pool settings for more information.
Other considerations for determining the Maximum Connections setting:
Each entity bean transaction requires an additional database connection, dedicated to handling the transaction.
On supported UNIX systems, a separate DB2 process is created for each connection; these processes quickly affect performance on systems with low memory and cause errors.
If clones are used, one data pool exists for each clone.
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 Maximum Connections value for the database connection pool should be increased by at least one. Doing this would allow for at least one of the waiting threads to obtain its second database connection and to avoid a deadlock.
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.
Purging connection pools
The product provides a new MBean operation (purgePoolContents) on the DataSource MBean that purges the connection pool. The pool purging behavior depends on the string parameter passed on purgePoolContents operation.
The immediate purge pool option causes the pool purge to behave in the following ways:
No new transactions start on any connections obtained prior to the purgePoolContents() call. Instead, a StaleConnectionException is thrown.
No new handles are distributed on any connections obtained prior to the purgePoolContents() call. Instead, a StaleConnectionException is thrown.
Existing in-flight transactions continue work, and any new activities on the purgedConnection cause a StaleConnectionException or an XAER_FAIL exception.
The close() calls issued on any connections obtained prior to the purgePoolContents() call are completed asynchronously with no wait time.
Requests for new connections (not handles to existing connections) are honored.
Number of connections are decremented immediately. This action might cause the total number of connections in WebSphere Application Server to be, temporarily, out of sync with the database total number of connections.
The normal purge pool option causes the pool purge to behave in the following ways:
Existing in-flight transactions continue work.
Shared connection requests are honored.
Free connections are cleaned up and destroyed.
In use connection (connections in transactions) are cleaned up and destroyed when returned to the connection pool.
The close() calls issued on any connections obtained prior to the purgePool call are completed synchronously with a wait for the JDBC driver to come back before proceeding.
Requests for new connections (not handles to existing connections) are honored.
The immediate purge pool option should only be used if the database is down. To use the Mbean operation purgePoolContents, you must use wsadmin commands. The purgePoolContents is a DataSource or J2CConnectionFactory Mbean type .
View the following wsadmin example, assuming that the data source name is mydatasource:
%WAS_HOME\bin\wsadmin
set ds [$AdminControl queryNames *,type=DataSource,name="mydatasource"]
$AdminControl invoke $ds purgePoolContents [normal | immediate]
Connection handles
A connection handle is a representation of a physical connection.
To use a backend resource (such as a relational database) in the WebSphere Application Server you must get a connection to that resource. When you call the getConnection() method, you get a connection handle returned. The handle is not the physical connection. The physical connection is managed by the connection manager.
There are two significant configurations or usage patterns that affect how connection handles are used and how they behave. The first is the res-sharing-scope, which is defined by the resource-reference used to look up the DataSource or Connection Factory. This property tells the connection manager whether or not you can share this connection.
The second factor that affects connection handle behavior is the usage pattern. There are essentially two usage patterns. The first is called the get/use/close pattern. It is used within a single method and without calling another method that might get a connection from the same data source or connection factory. An application using this pattern does the following:
- gets a connection
- does its work
- commits (if appropriate)
- closes the connection.
The second usage pattern is called the cached handle pattern. This is where an application:
- gets a connection
- begins a global transaction
- does work on the connection
- commits a global transaction
- does work on the connection again
A cached handle is a connection handle that is held across transaction and method boundaries by an application. Keep in mind the following considerations for using cached handles:
Cached handle support requires some additional connection handle management across these boundaries, which can impact performance. For example, in a JDBC application, Statements, PreparedStatements, and ResultSets are closed implicitly after a transaction ends, but the connection remains valid.
You are encouraged not to cache the connection across the transaction boundary for shareable connections; the get/use/close pattern is preferred.
Caching of connection handles across servlet methods is limited to Java Database Connectivity (JDBC) and Java Message Service (JMS) resources. Other non-relational resources, such as Customer Information Control System (CICS) or IMS, currently cannot have their connection handles cached in a servlet; you need to get, use, and close the connection handle within each method invocation. (This limitation only applies to single-threaded servlets because multithreaded servlets do not allow caching of connection handles.)
You cannot pass a cached connection handle from one instance of a data access client to another client instance. Transferring between client instances creates the problematic contingency of one instance using a connection handle that is referenced by another. This relationship can only cause problems because connection handle management code processes tasks for each client instance separately. Hence, connection handle transfers result in run-time scenarios that trigger exceptions. For example:
The application code of a client instance that receives a transferred handle closes the handle.
If the client instance that retains the original reference to the handle tries to reclaim it, the application server issues an exception.
The following code segment shows the cached connection pattern.Connection conn = ds.getConnection();
ut.begin();
conn.prepareStatement("....."); --> Connection runs in global transaction mode
...
ut.commit();
conn.prepareStatement("....."); ---> Connection still valid but runs in autoCommit(True);
...
Unshareable connections
Some characteristics of connection handles retrieved with a res-sharing-scope of unshareable are described in the following sections.
The possible benefits of unshared connections
Your application always maintains a direct link with a physical connection (managed connection).
The connection always has a one-to-one relationship between the connection handle and the managed connection.
In most cases, the connection does not close until the application closes it.
You can use a cached unshared connection handle across multiple transactions.
The connection can have a performance advantage in some cached handle situations. Because unshared connections do not have the overhead of moving connection handles off managed connections at the end of the transaction, there is less overhead in using a cached unshared connection.
The possible drawbacks of unshared connections
Inefficient use of your connection resources. For example, if within a single transaction you get more than one connection (with the same properties) using the same data source or connection factory (same resource-ref) then you use multiple physical connections when you use unshareable connections.
Wasted connections. It is important not to keep the connection handle open (that is, you have not called the close() method) any longer then it is needed. As long as you keep an unshareable connection open you tie up the physical connection, even if you currently are not using it. Unlike a shareable connection, an ushareable connection is not closed at the end of a transaction or servlet call.
Deadlock considerations. Depending on how your components interact with the database within a transaction, using unshared connections can lead to deadlocks in the database. For example, within a transaction, component A gets a connection to data source X and updates table 1, and then calls component B. Component B gets another connection to data source X, and updates/reads table 1 (or even worse the same row as component A). In some circumstances, depending on the particular database, its locking scheme, and the transaction isolation level, a deadlock can occur.
In the same scenario, but with a shared connection, a deadlock does not occur because all the work was done on the same connection. It is worth noting that when writing code which uses shared connections, it is important that the code be written in such a way that it expects other work to be done on the same connection, possibly within the same transaction. If you decide to use an unshareable connection, you must set the maximum connections property on the connection factory or data source correctly. An exception occurs if you try to exceed the maximum connections value.
Shareable connections
Some characteristics of connection handles retrieved with a res-sharing-scope of shareable are described in the following sections.
The possible benefits of shared connectionsCached handle support requires some additional connection handle management across these boundaries, which can impact performance. For example, in a JDBC application, Statements, PreparedStatements, and ResultSets are closed implicitly after a transaction ends, but the connection remains valid.
You are encouraged not to cache the connection across the transaction boundary for shareable connections; the get/use/close pattern is preferred.
Caching of connection handles across servlet methods is limited to Java Database Connectivity (JDBC) and Java Message Service (JMS) resources. Other non-relational resources, such as Customer Information Control System (CICS) or IMS, currently cannot have their connection handles cached in a servlet; you need to get, use, and close the connection handle within each method invocation. (This limitation only applies to single-threaded servlets because multithreaded servlets do not allow caching of connection handles.)
You cannot pass a cached connection handle from one instance of a data access client to another client instance. Transferring between client instances creates the problematic contingency of one instance using a connection handle that is referenced by another. This relationship can only cause problems because connection handle management code processes tasks for each client instance separately. Hence, connection handle transfers result in run-time scenarios that trigger exceptions. For example:
The application code of a client instance that receives a transferred handle closes the handle.
If the client instance that retains the original reference to the handle tries to reclaim it, the application server issues an exception.
The following code segment shows the cached connection pattern.Connection conn = ds.getConnection();
ut.begin();
conn.prepareStatement("....."); --> Connection runs in global transaction mode
...
ut.commit();
conn.prepareStatement("....."); ---> Connection still valid but runs in autoCommit(True);
...
Unshareable connections
Some characteristics of connection handles retrieved with a res-sharing-scope of unshareable are described in the following sections.
The possible benefits of unshared connections
Your application always maintains a direct link with a physical connection (managed connection).
The connection always has a one-to-one relationship between the connection handle and the managed connection.
In most cases, the connection does not close until the application closes it.
You can use a cached unshared connection handle across multiple transactions.
The connection can have a performance advantage in some cached handle situations. Because unshared connections do not have the overhead of moving connection handles off managed connections at the end of the transaction, there is less overhead in using a cached unshared connection.
The possible drawbacks of unshared connections
Inefficient use of your connection resources. For example, if within a single transaction you get more than one connection (with the same properties) using the same data source or connection factory (same resource-ref) then you use multiple physical connections when you use unshareable connections.
Wasted connections. It is important not to keep the connection handle open (that is, you have not called the close() method) any longer then it is needed. As long as you keep an unshareable connection open you tie up the physical connection, even if you currently are not using it. Unlike a shareable connection, an ushareable connection is not closed at the end of a transaction or servlet call.
Deadlock considerations. Depending on how your components interact with the database within a transaction, using unshared connections can lead to deadlocks in the database. For example, within a transaction, component A gets a connection to data source X and updates table 1, and then calls component B. Component B gets another connection to data source X, and updates/reads table 1 (or even worse the same row as component A). In some circumstances, depending on the particular database, its locking scheme, and the transaction isolation level, a deadlock can occur.
In the same scenario, but with a shared connection, a deadlock does not occur because all the work was done on the same connection. It is worth noting that when writing code which uses shared connections, it is important that the code be written in such a way that it expects other work to be done on the same connection, possibly within the same transaction. If you decide to use an unshareable connection, you must set the maximum connections property on the connection factory or data source correctly. An exception occurs if you try to exceed the maximum connections value.
Shareable connections
Some characteristics of connection handles retrieved with a res-sharing-scope of shareable are described in the following sections.
They can share a managed connection with one or more connection handles within a sharing, boundary depending upon how the handle is retrieved and which connection properties are used.
They can more efficiently use resources. Shareable connections are not valid outside of their sharing boundary. For this reason, at the end of a sharing boundary (such as transaction) the connection handle is no longer associated with the managed connection it was using within the sharing boundary (this applies only when using the cached handle pattern). The managed connection is returned to the free connection pool for reuse. Connection resources are not held longer than the end of the current sharing scope.
If the cached handle pattern is used, then the next time the handle is used within a new sharing scope, the application server run time assures that the handle is reassociated with a managed connection appropriate for the current sharing scope and with the same properties with which the handle was originally retrieved. Remember that it is not appropriate to change properties on a shareable connection. If properties are changed, other components that share the same connection might experience unexpected behavior. Futhermore, when using cached handles, the value of the changed property might not be remembered across sharing scopes.
The possible drawbacks of shared connections
Sharing within a single component (such as an enterprise bean and its related Java objects) is not always supported. The current specification allows resource adapters the choice of only allowing one active connection handle at a time.
If a resource adapter chooses to implement this option then the following scenario results in an invalid handle exception: A component using shareable connections gets a connection and uses it. Without closing the connection, the component calls a utility class (Java object) which gets a connection (handle) to the same managed connection and uses it. Because the resource adapter only supports one active handle, the first connection handle is no longer valid. If the utility object returns without closing its handle, the first handle remains invalid and use of it causes an exception.
Note: This exception occurs only when calling a utility object (a Java object).
Not all resource adapters have this limitation, it depends on their implementation. The WebSphere Relational Resource Adapter (RRA) does not have this limitation. Any DataSource used through the RRA does not have this limitation. If you encounter a resource adapter with this limitation you can work around it by serializing your access to the managed connection. If you always close your connection handle before getting another, or close your handle before calling code which gets another handle, and you always close your handle before you return from the method, you can allow two pieces of code to share the same managed connection. You just cannot use the connection for both events at the same time.
Trying to change the isolation level on a shareable JDBC based connection in a global transaction (those supported by the RRA) causes an exception. The correct way to get connections with different transaction isolation levels is by configuring the IBM extended resource-reference.
Closing connection handles for shareable connections by an application is NOT supported and causes errors. However, you can avoid this limitation by using the Relational Resource Adapter.
Posts
0 comments:
Post a Comment