Interact with connection pools to obtain and release connections

This page discusses - Interact with connection pools to obtain and release connections

Interact with connection pools to obtain and release connections

Interact with connection pools to obtain and release connections

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-ref used to look up the DataSource or Connection Factory. This property tells the connection manager whether or not you can share this connection.


<resource-ref id="ResourceRef_1122305703058">
	<description>Titan application Data Source</description>
	<res-ref-name>jdbc/TitanDS</res-ref-name>
	<res-type>javax.sql.DataSource</res-type>
	<res-auth>Container</res-auth>
	<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
					
					

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. The second usage pattern is called the cached handle pattern.

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.

The possible benefits of shared connections

  • 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.

Getting a connection from the DataSource

A connection is obtained from the DataSource. The connection is used as needed, and then released with a call to close() so that server knows it is available for a subsequent request.

It is very important that the close() is always called, even if there as an exception. Without the close(), server's database pool can loose connections. If you fail to close() a connection, server does not know that it is available for reuse, and cannot allocate it for another request. Eventually, server may run out of connections.

Always put a close() in a finally block, to guarantee that it is called. 0

The following example shows the use of a finally block that contains the close(). Because the close() is in a finally block, it will happen even if the code using the connection throws an exception:

Connection conn = null;
try {
	conn = dataSource.getConnection();
	Statement stmt = conn.createStatement();
	ResultSet rs = stmt.executeQuery(" ... ");
	...
	rs.close();
	stmt.close();
} catch (SQLException e) {
	throw new ServletException(e);
} finally {
	try {
		if (conn != null)
			conn.close();
	} catch (SQLException e) {
	}
}
					

Tutorials

  1. Appendix A. Additional materials
  2. WSAD 5.0 Practicing for IBM Test 000-287 Mind Map
  3. Deploy enterprise applications into servers
  4. Chapter 6. Assemble enterprise applications and deploy them in IBM WebSphere Application Server
  5. Configure resource and security-role references
  6. Design and develop custom tags
  7. Chapter 4. Demonstrate understanding of database connectivity and messaging within IBM WebShpere Application Server
  8. Chapter 5. EJB transactions
  9. Design and develop message-driven EJBs
  10. Design and develop entity EJBs
  11. Validate operational parameters of application server to support the enterprise application
  12. Chapter 1. Design, build and test reusable enterprise components
  13. Access container and server services from enterprise components
  14. Part I. Exam Objectives
  15. Explain implications of resource management on application design and implementation
  16. Manage end-user state and understand performance tradeoffs of using HTTP sessions
  17. Chapter 7. Validate, tune and troubleshoot an application within an IBM WebSphere Application Server environment
  18. Implement mechanisms for efficient inter-component calls
  19. IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide
  20. Chapter 3. Develop clients that access the enterprise components
  21. Implement Java clients calling Web Services
  22. Configure JMS connection factories and destinations
  23. Design, develop and test JSPs
  24. Use JTA to control transaction demarcation
  25. Manipulate transactional behavior of EJBs using deployment descriptors
  26. Implement mechanisms which support loose coupling between clients and components
  27. Identify misbehaving application components
  28. Preface
  29. WSAD 5.0 Practicing for IBM Test 000-287
  30. Part II. Appendixes
  31. Interact with connection pools to obtain and release connections
  32. Describe the effects of a server failure on the application
  33. Test and debug enterprise components
  34. Chapter 2. Design, build and test web components
  35. Chapter 2. Design, build and test web components