The best way to detect database connection leaks

拼搏现实的明天。 2023-10-18 20:49 55阅读 0赞

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:

The ConnectionLeakUtil utility looks like this:

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 </td> 
 <td> public class ConnectionLeakUtil { &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;private JdbcProperties jdbcProperties = JdbcProperties.INSTANCE; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;private List idleConnectionCounters = &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Arrays.asList( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;H2IdleConnectionCounter.INSTANCE, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;OracleIdleConnectionCounter.INSTANCE, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PostgreSQLIdleConnectionCounter.INSTANCE, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MySQLIdleConnectionCounter.INSTANCE &nbsp;&nbsp;&nbsp;&nbsp;); &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;private IdleConnectionCounter connectionCounter; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;private int connectionLeakCount; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;public ConnectionLeakUtil() { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;for ( IdleConnectionCounter connectionCounter : &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;idleConnectionCounters ) { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if ( connectionCounter.appliesTo( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Dialect.getDialect().getClass() ) ) { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;this.connectionCounter = connectionCounter; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;break; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if ( connectionCounter !=&nbsp;null ) { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connectionLeakCount = countConnectionLeaks(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;} &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;public void assertNoLeaks() { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if ( connectionCounter !=&nbsp;null ) { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int currentConnectionLeakCount = countConnectionLeaks(); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;int diff = currentConnectionLeakCount - connectionLeakCount; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if ( diff &gt;&nbsp;0 ) { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;throw new ConnectionLeakException( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;String.format( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"%d connection(s) have been leaked! Previous leak count: %d, Current leak count: %d", &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;diff, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;connectionLeakCount, &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;currentConnectionLeakCount &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;) &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;} &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;private int countConnectionLeaks() { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;try ( Connection connection = newConnection() ) { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return connectionCounter.count( connection ); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;catch ( SQLException e ) { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;throw new IllegalStateException( e ); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;} &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;private Connection newConnection() { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;try { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return DriverManager.getConnection( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;jdbcProperties.getUrl(), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;jdbcProperties.getUser(), &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;jdbcProperties.getPassword() &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;catch ( SQLException e ) { &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;throw new IllegalStateException( e ); &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} &nbsp;&nbsp;&nbsp;&nbsp;} } </td> 
 </tr> 
 </tbody> 
</table>

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

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.

Oracle

PostgreSQL

MySQL

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:

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> org.hibernate.testing.jdbc.leak.ConnectionLeakException: 1 connection(s) have been leaked! Previous leak count: 0, Current leak count: 1 </td> 
 </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
 <tr> 
 <td> 1 </td> 
 <td> org.hibernate.testing.jdbc.leak.ConnectionLeakException: 1 connection(s) have been leaked! Previous leak count: 2, Current leak count: 3 </td> 
 </tr> 
 </tbody> 
</table>

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.

[connection pooling]: https://vladmihalcea.com/the-anatomy-of-connection-pooling/
[Hibernate ORM]: http://hibernate.org/orm/