检测数据库连接泄漏的最佳方法

介绍

数据库连接不是免费的，这就是首先使用连接池解决方案的原因。但是，单独的连接池并不能解决与管理数据库连接相关的所有问题。应用程序开发人员必须确保Connection在不再需要时关闭每一个。在幕后，连接池提供了一个逻辑事务，当它被关闭时，它会返回到池中，以便其他并发事务可以进一步重用它。

当连接被获取而从未被关闭时，就会发生连接泄漏。

何时应检测到连接泄漏？

每个关系数据库都提供了一种检查底层连接状态的方法，因此可以轻松打开一个新的 SQL 终端并检查是否有任何悬空连接。但是，这种简约的方法是错误的，因为它意味着我们将应用程序的损坏版本部署到生产环境中。

在测试期间应检测连接泄漏，从而防止在生产环境中发生连接泄漏。

这篇文章将演示如何仅使用单元测试来自动化连接池检测。这种方法使我们能够在我们的实际代码库以及我们的测试例程中检测连接泄漏。如果单元测试正在泄漏连接，那么当达到最大数据库连接阈值时，持续集成过程将中断。

连接检漏仪

要检查给定的测试类是否泄漏连接，我们将检查 JUnit 测试运行器使用给定类之前和之后的悬空连接数：

1 2 3 4 5 6 7 8 9 10 11 12 13

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

该ConnectionLeakUtil实用程序如下所示：

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

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 );         }     } }

该IdleConnectionCounter接口定义了使用特定于数据库的实现来计算非活动连接数的协定。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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); }

对于我们在测试期间使用的每个受支持的 Hibernate Dialect，都
需要有一个IdleConnectionCounter实现，以便我们可以检查泄漏连接的数量。

H2

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

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 );         }     } }

甲骨文

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

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

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

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

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

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 );         }     } }

测试时间

我构建了这个实用程序，以便我们可以跟踪Hibernate ORM项目中所有泄漏连接的单元测试。当针对 运行它时hibernate-core，我可以很容易地发现罪魁祸首测试：

1 2 3 4 5 6 7 8 9 10 11 12 13

: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

当我打开 的报告时EntityManagerFactoryClosedTest，我什至可以看到有多少连接被泄露：

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

SynchronizationTypeTest甚至表明以前的连接也存在泄漏：

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

结论

检测连接泄漏是每个企业应用程序的强制性要求。虽然您可以找到定期运行并终止所有空闲数据库连接的脚本，但这只是一种创可贴的方法。

处理连接泄漏的最佳方法是修复底层代码库，以便始终正确关闭连接。为了确保生产环境没有连接泄漏，每个集成测试都必须验证实际测试的源代码或测试逻辑本身没有泄漏连接。