Safely using pooled connections in application code

Environment

• Red Hat JBoss Enterprise Application Platform (EAP) (all versions)
• Red Hat JBoss Web Server (all versions)

Issue

• How can connection leaks be avoided?
• How should connection handling be coded in application code?
• How should statement and result set objects be handled in application code?

Resolution

• Each application "component"¹ should make its own request for a connection from the pool and should retain exclusive access to that connection.
• Code using connections, statements and result sets must be structured in such a way that each is closed even if errors/exceptions occur.
  • Result sets should be closed first since they are derived from a statement.
  • Similarly, statements are derived from the connection and should be closed next.
  • Finally, the connection must be closed.
• Patterns for proper handling of connections and derived resources are shown below. The same patterns apply regardless of the specific pool implementation used.

Using try-with-resources (Java 7 and later)

// scope connection handles and related resources to try blocks to avoid leaks as well as illegal access attempts after closure
try (Connection connection = dataSource.getConnection()) {
    try (Statement statement = connection.createStatement()) {
        try (ResultSet rs = /* use the statement to execute a query */) {
           // use the result set here
        } // catch/finally optional here, result set will have been automatically closed
    } // catch/finally optional here, statement will have been automatically closed
} catch (Exception e) {
    // Important: log the complete exception (with exception message and full stack trace) here
} // connection will have been automatically closed (returned to the pool or transaction)

Using try/catch/finally

    { // scope connection handles and related resources to method/try blocks to avoid leaks as well as illegal access attempts after closure
        Connection connection = ... // get connection from the pool
        try {
            Statement statement = connection.createStatement();
            try {
                ResultSet resultSet = ... // use the statement to execute a query
                try {
                      // use the result set here
                } finally {
                      try { resultSet.close(); } catch(Exception e) { /* log the exception message and complete stack trace */ }
                }
            } finally {
                try { statement.close(); } catch(Exception e) { /* log the exception message and complete stack trace */ }
            }
            ...
        } finally {
             try { connection.close(); } catch(Exception e) { /* log the exception message and complete stack trace */ }
        }
    } // ensure no other code can (illegally) access the connection subsequently

Closure by ResultSet

If you have developed a utility class which returns a result set, then you may find it useful to implement code which extracts the statement and connection from the result set and closes each. The statement & connection must be retrieved from the result set before trying to close the result set because once the result set is closed you cannot retrieve the statement or connection from it. Note that if there is a problem with the result set or the driver implementation, it may not be possible to properly close the statement/connection objects and this can result in leaks. Using one of the patterns above is preferred.

public static void closeAllViaResultSet(ResultSet rs) {
    if(rs != null) {
        Connection con = null;
        Statement st = null;
        try {
            st = rs.getStatement();
            if (st != null) {
                      con = st.getConnection();
            }
        } catch (Exception e) {
            // log the exception message and complete stack trace
        }
        try { rs.close(); } catch(Exception e) { /* log the exception message and complete stack trace */ }
        if (st != null) {
            try { st.close(); } catch(Exception e) { /* log the exception message and complete stack trace */ }
        }
        if (con != null) {
            try { con.close(); } catch(Exception e) { /* log the exception message and complete stack trace */ }
        }
    }
}

Root Cause

• Pooled connections are not meant to be cached in the application layer (as properties/members of class instances), shared across components nor retained across requests (e.g. different requests that may execute on different threads across time).
  • Connections are meant to be used and immediately returned to the pool.
  • Sharing connection references among multiple components creates ambiguity of ownership and may lead to failure.
    • It may not be clear which component should close the connection (return it to the pool) so leaks may occur in sharing scenarios.
    • Multiple components working with the same connection may interfere with one another - e.g. calling close may prematurely terminate result sets, statements, etc. in use by other components.
• Failure to explicitly close a connection causes a connection leak. This is an error/defect in application code that should be resolved by appropriate code changes.
  • Some pool mechanisms may make it possible identify such application defects and may, in certain circumstances, be able to reclaim leaked connections¹.
  • Use of these mechanisms is not recommended as an alternative to correcting improper application code.
• Pooled connections are proxies for a physical connection created by the underlying JDBC driver.
  • Requesting a connection from the pool does not imply creating a new connection to the database server.
    • If available connections exist in the pool, a distinct proxy for the physical connection is created and returned so the cost (time for completion) of each connection request is likely to be less than a request directly to the underlying JDBC driver.
  • Closing pooled connections terminates the proxy and returns the physical connection to the pool for reuse but does not terminate the underlying JDBC connection created by the driver implementation.
    • During JTA/managed transactions, the underlying physical connection remains associated with the transaction (to ensure transaction ACID properties - transactional work for a single resources is meant to be performed using the same physical connection) and subsequent requests for a connection in the same transaction return a distinct proxy for the same underlying connection (repeated requests are typically faster than the initial/first request for a connection in the transaction).
  • Multiple proxies may reference the same physical connection at the same time (e.g. during JTA/managed transactions) making it possible for different components to cleanly/safely retrieve/"close" a connection.
  • Proxies for the same physical connection may preserve unique state for statements and results sets.

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.