Tomcat connection pool & idle connections

Question

We are developing a website using

Tomcat 7
JDBC
PostgreSQL 9.2

We've had some connection leaks and think we corrected them (the database no longer stops responding), but the behaviour of the connection pool still seems leaky, as we have a number of idle connections greater than the maxIdle set in context.xml. I'd like to be sure the problem is fixed.

For testing purposes, I'm using the following context.xml :

 <Resource
    auth="Container"
    name="jdbc/postgres"
    factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
    type="javax.sql.DataSource"

    username="admin"
    password="..."

    driverClassName="org.postgresql.Driver"
    url="jdbc:postgresql://127.0.0.1:5432/..."
    initialSize="1"
    maxActive="50"
    minIdle="0"
    maxIdle="3"
    maxWait="-1"
    minEvictableIdleTimeMillis="1000"
    timeBetweenEvictionRunsMillis="1000"
    />

If I understand correctly, we should have 1 idle connection on startup and from 0 to 3 depending on the load, right ?

What is happening is : 1 connection on startup, up to 3 idle connections if the load is low, and more than 3 idle connections after a high load. Then these connections are not closed immediatly, and we don't know when/if they will be closed (sometime some of them are closed).

So the question is : is this behaviour normal, or not ?

Thanks for your help

EDIT : added factory attribute, didn't change the problem

EDIT 2 : using removeAbandoned & removeAbandonedTimeout make the idle connexions being closed every removeAbandonedTimeout. So we probably still have some connection leaks. Here are some pieces of code we are using to connect to the database and execute requests :

PostgreSQLConnectionProvider, just a static class to provide a connection :

public class PostgreSQLConnectionProvider {

    public static Connection getConnection() throws NamingException, SQLException {

        String dsString = "java:/comp/env/jdbc/postgres";
        Context context = new InitialContext();
        DataSource ds = (DataSource) context.lookup(dsString);
        Connection connection = ds.getConnection();

        return connection;
    }
}

DAO abstract class :

public abstract class DAO implements java.lang.AutoCloseable {

    // Private attributes :
    private Connection _connection;

    // Constructors :
    public DAO() {

        try { _connection = PostgreSQLConnectionProvider.getConnection(); }
        catch (NamingException | SQLException ex) { 
            Logger.getLogger(DAO.class.getName()).log(Level.SEVERE, null, ex); 
        }
    }

    // Getters :
    public Connection getConnection() { return _connection; }

    // Closeable :
    @Override
    public void close() throws SQLException {

        if(!_connection.getAutoCommit()) {

            _connection.rollback();
            _connection.setAutoCommit(true);
        }

        _connection.close();
    }
}

UserDAO, a small DAO subclass (we have several DAO sublasses to request the database) :

public class UserDAO extends DAO {

    public User getUserWithId(int id) throws SQLException {

        PreparedStatement ps = null;
        ResultSet rs = null;

        User user = null;

        try {

            String sql = "select * from \"USER\" where id_user = ?;";

            ps = getConnection().prepareStatement(sql);
            ps.setInt(1, id);

            rs = ps.executeQuery();
            rs.next();

            String login = rs.getString("login");
            String password = rs.getString("password");
            String firstName = rs.getString("first_name");
            String lastName = rs.getString("last_name");
            String email = rs.getString("email");

            user = new User(id, login, password, firstName, lastName, email);
        }
        finally {

            if(rs != null) rs.close();
            if(ps != null) ps.close();
        }

        return user;
    }
}

An example of a DAO subclass use :

try(UserDAO dao = new UserDAO()) {

    try {

        User user = dao.getUserWithId(52);
    }
    catch (SQLException ex) {

        // Handle exeption during getUserWithId
    }
}
catch (SQLException ex) {

    // Handle exeption during dao.close()
}

Try to use tomcat's connection pool implementation - it's more feature-rich and has good documentation people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html — Konstantin V. Salikhov
Thank you, but it is already what I'm doing (you can see the parameters I'm using in the context.xml). — Tim Autin
I can't see the main part - factory attribute and appropriate classname — Konstantin V. Salikhov
Oops you're right, this attribute is required, I didn't saw it. But adding it didn't change anything (my parameters were already taken into account : initialSize is always respected). — Tim Autin
Well using removeAbandoned and removeAbandonedTimeout make the idle connexions gone every removeAbandonedTimeout, so I guess we still have a connexion leak :/ . — Tim Autin

Michael Murphree Michael Murphree · Accepted Answer · 2014-10-14T18:37:06

Looking at the code it appears the connection is grabbed for the lifetime of the DAO, not the lifetime of the statement, which is the usual expectation. Normally, you would grab a connection from the pool just as you're about to execute the statement, and call close() on it when you're done in order to return it to the pool.

Additionally, in your finally clause, both rs.close() and ps.close() can throw exceptions resulting in missing the last call against the prepared statement.

In Java 7 you can also use a try with resources statement that will close both the prepared statement and the connection for you. According to the spec, the driver is supposed to close the result for you when the statement is closed.

Tomcat connection pool & idle connections

1 Answers