记录一次基于使用hikari数据库连接池的连接泄露排查过程，数据库mysql

最近工作中接手了一个老旧的项目（ssh，非Maven的那种......）开发了新的功能并把数据库连接池从c3p0切换到hikari（当然这不是导致连接泄露的原因），提测后，测试人员反映项目每运行一段时间就会瘫痪，我查看了报错信息如下：

org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Cannot open connection
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:602)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:427)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:276)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.orm.hibernate3.HibernateInterceptor.invoke(HibernateInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
	at com.sun.proxy.$Proxy35.getFailLog(Unknown Source)
	at com.dragonsoft.fwyy.schedule.common.work.PreStatRetryerWork.run(PreStatRetryerWork.java:42)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection
	at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
	at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
	at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
	at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1353)
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:560)
	... 11 more
Caused by: java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 30000ms.
	at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190)
	at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
	at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:85)
	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
	... 16 more

很明显是数据库连接获取超时，连接池中连接被耗尽了。一般情况造成这种情况的原因是程序中有连接没有释放。楼主使用

SHOW FULL PROCESSLIST; 命令排查最终发现后台的某一个定时任务每执行一次，mysql连接数就会增加一个,最终定位到这个类上，阅读的类的时候发现代码写的虽然很不规范（像是实习生写的），但是基本能够做到显示获取连接、关闭连接的操作。

为了定位具体哪一行代码导致连接泄露，楼主将  hikari 日志级别调整为 debug ：

<Logger name="com.zaxxer.hikari" level="debug"></Logger>

注：将该代码放在工程的日志配置文件即可（楼主的是logback.xml） 。

加入此配置控制台中会每隔30秒打印一次连接占用情况，如下：

com.zaxxer.hikari.pool.HikariPool - HikariPool-2 - Before cleanup stats (total=10, active=0, idle=10, waiting=0)

这样通过调试可以具体到某一行代码导致连接泄露

希望对你有帮助！