Connection Pool Leak Causes

Question

I have been experiencing (I think) issues with my connection pooling. Specifically, my logs show the message:

org.apache.tomcat.dbcp.pool2.impl.DefaultPooledObject$AbandonedObjectCreatedException: Pooled object created [time] by the following code has not been returned to the pool

I have inspected the methods listed in the stack trace that the logs show but couldn't find a culprit (I always close the ResultSet, PreparedStatement and Connection at the end of each method).

I have a method that executes two queries, perhaps I am not executing it properly.

It's layout is as follows:

ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
PreparedStatement rowsPs = null;
ResultSet rs = null;
ResultSet rowsRs = null;

String query = "SELECT SQL_CALC_FOUND_ROWS ...";
String totalRowsQuery = "SELECT FOUND_ROWS() AS RowCount";

try {
    ps = connection.prepareStatement(query);
    [set ps params]
    rs = ps.executeQuery();
    [process rs]

    rowsPs = connection.prepareStatement(totalRowsQuery);
    rowsRs = rowsPs.executeQuery();
    [process rowsRs]
} catch (SQLException e) {
    [handle e]
} finally {
    DBUtil.closeResultSet(rs);
    [close rowsRs]
    [close ps]
    [close rowsPs]
    [close connection]
}

Where an example of a DBUtils method is:

public static void closeResultSet(ResultSet rs)
{
    try
    {
        if (rs != null)
            rs.close();
    }
    catch (SQLException sqle)
    {
        sqle.printStackTrace();
    }
}

Does the general layout of this method seem ok? Should I be handling the connection differently? Or is it some other method that's causing the errors to be logged?

Thank you.

Additional Info

I also get a SQLException:

java.sql.SQLException: Connection com.mysql.jdbc.JDBC4Connection@[some number] is closed

at the line: rowsPs = connection.prepareStatement(totalRowsQuery);

Meaning that somewhere prior, the connection is closed. I don't explicitly close the connection anywhere. Is it possible that some other data accessing method that is called is somehow closing the connection in this method? (pool.getConnection() calls dataSource.getConnection())

Update: I have tried using try-with-resources as suggested but the issue persists.

The ConnectionPool class that is referenced in the first code snippet above:

public class ConnectionPool 
{

    private static ConnectionPool pool = null;
    private static DataSource dataSource = null;

    public synchronized static ConnectionPool getInstance()
    {

        if ( pool == null ) {
            pool = new ConnectionPool();
        }
            return pool;
    }

    private ConnectionPool()
    {
        try {
            InitialContext ic = new InitialContext();
            dataSource = (DataSource) 
                    ic.lookup([jdbc/dbName]);
        }

        catch (Exception e) {
            e.printStackTrace();
        } 
    }

    public Connection getConnection()
    {
        try {
            return dataSource.getConnection();
        }
        catch (SQLException sqle) {
            sqle.printStackTrace();
            return null;
        }
    }
    public void freeConnection(Connection c)
    { 
        try {
            c.close();
        }
        catch (SQLException sqle) {
            sqle.printStackTrace();
        }
    }
}

More source: My Pool Resource Element:

<Resource auth="Container" driverClassName="com.mysql.jdbc.Driver"  
            logAbandoned="true" maxActive="100" maxIdle="30" maxWait="10000" 
            removeAbandonedOnBorrow="true" 
            removeAbandonedTimeout="60" type="javax.sql.DataSource" 
            testWhileIdle="true" testOnBorrow="true" 
            validationQuery="SELECT 1 AS dbcp_connection_test"/>

Update: I have turned on the slow query log but, despite the Exceptions being thrown again, the slow query log does not log anything (no query takes longer than 10 seconds).

So it appears that it in not that the query is taking longer than 60 seconds.

Still uncertain as to what is causing this.

Answer 1

As general rules of thumb when working with pooled JDBC connections:

1. Do not keep more than one ResultSet open at a time on each connection. Close the first ResultSet and its associated Statement before opening the second.

2. Always close the resources in exactly the reverse order that you created them: create(s1)->execute(r1)->close(r1)->close(s1)->create(s2)->execute(r2)->close(r2)->close(s2)

3. Ensure that a long running query processing is not exceeding the maximum connection lifetime allowed by the pool.

4. Even if you are not exceeding the maximum connection lifetime, a connection can be lost because of small interruptions in the network link.