Tomcat Connection pool : few methods not releasing

Question

I am using tomcat connection pooling. But I was following exception org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object

So I put in the following lines in context.xml to find the leak : removeAbandoned="true" logAbandoned="true" removeAbandonedTimeout="3"

Then I started getting following exception org.apache.tomcat.dbcp.dbcp.AbandonedTrace$AbandonedObjectException: DBCP object created 2015-01-17 22:12:18 by the following code was never closed: So I found two culprit methods that were causing this leak. The two methods have common way of getting connection i.e. calling unwrap to get access to driver specific connection.

try (Connection conn = DataSourceConnectionPool.getConnection().unwrap(OracleConnection.class);
        OracleCallableStatement cstmt = (OracleCallableStatement) conn.prepareCall(MIGRATE_ACCOUNT)) {
        ...
        ....
)

Important think to notice is that I am using try block from JDK7 i.e. Automatic Resource Management, so i dont need the finally block. Connection closing is handled by JDK automatically. But why this unwrapped connection is not closing. And when I am trying to do following :

try (Connection poolConn = DataSourceConnectionPool.getConnection();
    Connection conn = poolConn.unwrap(OracleConnection.class);

I am getting java.sql.SQLException: Already closed. So how close this connection. Do I have to do it manually without using try block? Shouldn't try block handle be able to handle this?

Answer 1

This is incorrect usage of a connection pool. You should never call close() on an unwrapped connection.

The normal flow of using a pooled connection is

1. Get the Connection, the pool gets a physical connection and returns it wrapped in its own wrapper
2. You use the Connection
3. You call close() on the Connection. This doesn't actually close anything, the pool's wrapper intercepts the close() call and simply returns the (still active) connection to the pool.

This works because the pool has a wrapper class, say PoolableConnection that implements Connection. PoolableConnection delegates to the underlying connection for carrying out actual work, but it implements (amongst other things) close() differently. This destroys the current PoolableConnection wrapper and returns the underying Connection to the connection pool. For example.

This way, your program logic can get a connection from a DataSource, use the Connection and then close(), just as it would a normal, unpooled, Connection.

It's exactly this transparency that makes connection pools so easy to use.

Now, when you call unwrap, the PooledConnection gives you access to it's internal, real, Connection delegate.

What you do is call close() on the delegate!

This has two effects:

1. it does not call close() on PooledConnection, so the the Connection does not get returned to the pool.
2. it closes the underying connection from underneath the pool. This shouldn't be a problem, as the pool would deal with dropped connections itself.

So you need to be very careful. Always call close() on the Connection you have gotten from the pool, to return it to the pool. Never call close() on the underlying connection.

So your code should be:

try (final Connection poolConn = DataSourceConnectionPool.getConnection()) {
    final Connection conn = poolConn.unwrap(OracleConnection.class);
    //do stuff with conn
    //do not close conn!!
}
//poolConn is returned to the pool