Introduction

Database connections are not free, and that’s the reason for using a connection pooling solution in the first place. However, the connection pool alone does not solve every issue associated to managing database connections. The application developer must make sure that every Connection is closed when no longer needed. Behind the scenes, the connection pool gives a logical transaction which, when being closed, it returns back to the pool so that it can be further reused by other concurrent transactions.

A connection leak happens when a connection is acquired without ever being closed.

When should connection leaks be detected?

Every relational database offers a way to inspect the underlying connection status, so one can easily open a new SQL terminal and check if there are any connections dangling. However, this minimalistic approach is wrong because it implies that we deployed a broken version of our application into production.

Connection leaks should be detected during testing, therefore preventing connection leaks from occurring in a production environment.

This post is going to demonstrate how you can automate the connection pool detection using mere unit testing. This approach allows us to detect connection leaks both in our actual code base as well as in our test routines. If unit tests are leaking connections, then the Continous Integration process will get broken when the maximum database connections threshold is being reached.

Connection leak detector

To inspect if a given test class is leaking connections, we are going to check the number of dangling connections before and after a given class is being used by the JUnit Test Runner:

@BeforeClass
public static void initConnectionLeakUtility() {
    if ( enableConnectionLeakDetection ) {
        connectionLeakUtil = new ConnectionLeakUtil();
    }
}

@AfterClass
public static void assertNoLeaks() {
    if ( enableConnectionLeakDetection ) {
        connectionLeakUtil.assertNoLeaks();
    }
}

The ConnectionLeakUtil utility looks like this:

public class ConnectionLeakUtil {

    private JdbcProperties jdbcProperties = JdbcProperties.INSTANCE;

    private List idleConnectionCounters = 
        Arrays.asList(
            H2IdleConnectionCounter.INSTANCE,
            OracleIdleConnectionCounter.INSTANCE,
            PostgreSQLIdleConnectionCounter.INSTANCE,
            MySQLIdleConnectionCounter.INSTANCE
    );

    private IdleConnectionCounter connectionCounter;

    private int connectionLeakCount;

    public ConnectionLeakUtil() {
        for ( IdleConnectionCounter connectionCounter : 
            idleConnectionCounters ) {
            if ( connectionCounter.appliesTo( 
                Dialect.getDialect().getClass() ) ) {
                this.connectionCounter = connectionCounter;
                break;
            }
        }
        if ( connectionCounter != null ) {
            connectionLeakCount = countConnectionLeaks();
        }
    }

    public void assertNoLeaks() {
        if ( connectionCounter != null ) {
            int currentConnectionLeakCount = countConnectionLeaks();
            int diff = currentConnectionLeakCount - connectionLeakCount;
            if ( diff > 0 ) {
                throw new ConnectionLeakException( 
                    String.format(
                        "%d connection(s) have been leaked! Previous leak count: %d, Current leak count: %d",
                        diff,
                        connectionLeakCount,
                        currentConnectionLeakCount
                    ) 
                );
            }
        }
    }

    private int countConnectionLeaks() {
        try ( Connection connection = newConnection() ) {
            return connectionCounter.count( connection );
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }

    private Connection newConnection() {
        try {
            return DriverManager.getConnection(
                jdbcProperties.getUrl(),
                jdbcProperties.getUser(),
                jdbcProperties.getPassword()
            );
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
}

The IdleConnectionCounter interface defines the contract for counting the number of inactive connections using a database-specific implementation.

public interface IdleConnectionCounter {

    /**
     * Specifies which Dialect the counter applies to.
     *
     * @param dialect dialect
     *
     * @return applicability.
     */
    boolean appliesTo(Class<? extends Dialect> dialect);

    /**
     * Count the number of idle connections.
     *
     * @param connection current JDBC connection to be used for querying the number of idle connections.
     *
     * @return idle connection count.
     */
    int count(Connection connection);
}

For each supported Hibernate Dialect that we use during testing,
there needs to be an IdleConnectionCounter implementation so that we can inspect the number of leaked connections.

H2

public class H2IdleConnectionCounter implements IdleConnectionCounter {

    public static final IdleConnectionCounter INSTANCE = 
        new H2IdleConnectionCounter();

    @Override
    public boolean appliesTo(Class<? extends Dialect> dialect) {
        return H2Dialect.class.isAssignableFrom( dialect );
    }

    @Override
    public int count(Connection connection) {
        try ( Statement statement = connection.createStatement() ) {
            try ( ResultSet resultSet = statement.executeQuery(
                    "SELECT COUNT(*) " +
                    "FROM information_schema.sessions " +
                    "WHERE statement IS NULL" ) ) {
                while ( resultSet.next() ) {
                    return resultSet.getInt( 1 );
                }
                return 0;
            }
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
}

Oracle

public class OracleIdleConnectionCounter implements IdleConnectionCounter {

    public static final IdleConnectionCounter INSTANCE = 
        new OracleIdleConnectionCounter();

    @Override
    public boolean appliesTo(Class<? extends Dialect> dialect) {
        return Oracle10gDialect.class.isAssignableFrom( dialect );
    }

    @Override
    public int count(Connection connection) {
        try ( Statement statement = connection.createStatement() ) {
            try ( ResultSet resultSet = statement.executeQuery(
                    "SELECT COUNT(*) " +
                    "FROM v$session " +
                    "WHERE status = 'INACTIVE'" ) ) {
                while ( resultSet.next() ) {
                    return resultSet.getInt( 1 );
                }
                return 0;
            }
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
}

PostgreSQL

public class PostgreSQLIdleConnectionCounter implements IdleConnectionCounter {

    public static final IdleConnectionCounter INSTANCE = 
        new PostgreSQLIdleConnectionCounter();

    @Override
    public boolean appliesTo(Class<? extends Dialect> dialect) {
        return PostgreSQL91Dialect.class.isAssignableFrom( dialect );
    }

    @Override
    public int count(Connection connection) {
        try ( Statement statement = connection.createStatement() ) {
            try ( ResultSet resultSet = statement.executeQuery(
                    "SELECT COUNT(*) " +
                    "FROM pg_stat_activity " +
                    "WHERE state ILIKE '%idle%'" ) ) {
                while ( resultSet.next() ) {
                    return resultSet.getInt( 1 );
                }
                return 0;
            }
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
}

MySQL

public class MySQLIdleConnectionCounter implements IdleConnectionCounter {

    public static final IdleConnectionCounter INSTANCE = 
        new MySQLIdleConnectionCounter();

    @Override
    public boolean appliesTo(Class<? extends Dialect> dialect) {
        return MySQL5Dialect.class.isAssignableFrom( dialect );
    }

    @Override
    public int count(Connection connection) {
        try ( Statement statement = connection.createStatement() ) {
            try ( ResultSet resultSet = statement.executeQuery(
                    "SHOW PROCESSLIST" ) ) {
                int count = 0;
                while ( resultSet.next() ) {
                    String state = resultSet.getString( "command" );
                    if ( "sleep".equalsIgnoreCase( state ) ) {
                        count++;
                    }
                }
                return count;
            }
        }
        catch ( SQLException e ) {
            throw new IllegalStateException( e );
        }
    }
}

Testing time

I built this utility so that we can track all the unit tests that are leaking connections in Hibernate ORM project. When running it against hibernate-core, I can easily spot the culprit tests:

:hibernate-core:test

org.hibernate.jpa.test.EntityManagerFactoryClosedTest > classMethod FAILED
    org.hibernate.testing.jdbc.leak.ConnectionLeakException

org.hibernate.jpa.test.EntityManagerFactoryUnwrapTest > classMethod FAILED
    org.hibernate.testing.jdbc.leak.ConnectionLeakException

org.hibernate.jpa.test.cdi.NoCdiAvailableTest > classMethod FAILED
    org.hibernate.testing.jdbc.leak.ConnectionLeakException

org.hibernate.jpa.test.factory.SynchronizationTypeTest > classMethod FAILED
    org.hibernate.testing.jdbc.leak.ConnectionLeakException

When I open the report for the EntityManagerFactoryClosedTest, I can even see how many connections are being leaked:

org.hibernate.testing.jdbc.leak.ConnectionLeakException: 1 connection(s) have been leaked! Previous leak count: 0, Current leak count: 1

SynchronizationTypeTest even indicates that there are previous connections leaks as well:

org.hibernate.testing.jdbc.leak.ConnectionLeakException: 1 connection(s) have been leaked! Previous leak count: 2, Current leak count: 3

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

Detecting connection leaks is a mandatory requirement for every enterprise application. While you can find scripts which run periodically and kill all idle database connections, this is just a band aid approach.

The best way to deal with connection leaks is to fix the underlying code base so that connections are always closed properly. To make sure that the production environment is connection leak free, every integration test must validate that no connection is being leaked by the actual tested source code or by the testing logic itself.

Insert details about how the information is going to be processed

DOWNLOAD NOW