HikariCP - 连接不可用

Question

HikariCP - 连接不可用

122

在我们的项目中，我们使用Spring-boot/Hibernate/PostgreSQL应用程序，并使用Hikari作为连接池。我们不断遇到以下问题：几个小时后，活动连接数量增加到限制并且我们会收到如下错误（完整的堆栈跟踪在问题末尾）：

Caused by: java.sql.SQLTransientConnectionException: HikariPool-0 - Connection is not available, request timed out after 30000ms.
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:213) ~[HikariCP-2.4.1.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:163) ~[HikariCP-2.4.1.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:85) ~[HikariCP-2.4.1.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:139) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:380) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:228) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    ... 126 common frames omitted

以下是版本信息：

Spring-boot version:   1.2.3.RELEASE
HikariCP version:      2.4.1
Hibernate version:     4.3.6.Final
Postgresql jdbc:       9.3-1102-jdbc41
Server version:        Apache Tomcat/8.0.23
JVM Version:           1.8.0_45-b14

JPA/Hibernate 配置：

jpa:
    database-platform: org.hibernate.dialect.PostgreSQL82Dialect
    database: POSTGRESQL
    openInView: false
    show_sql: false
    generate-ddl: false
    hibernate:
        ddl-auto: none
        naming-strategy: org.hibernate.cfg.EJB3NamingStrategy
    properties:
        hibernate.cache.use_second_level_cache: true
        hibernate.cache.use_query_cache: false
        hibernate.generate_statistics: false
        hibernate.cache.region.factory_class: org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory
        hibernate.enable_lazy_load_no_trans: true
        hibernate.event.merge.entity_copy_observer: allow

HikariCP配置：

2015-10-06 12:26:44,252 DEBUG [localhost-startStop-1] HikariConfig: HikariPool-0 - configuration:
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: allowPoolSuspension.............false
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: autoCommit......................true
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: catalog.........................
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: connectionInitSql...............
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: connectionTestQuery.............
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: connectionTimeout...............30000
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: dataSource......................
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: dataSourceClassName.............org.postgresql.ds.PGSimpleDataSource
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: dataSourceJNDI..................
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: dataSourceProperties............{user=postgres, password=<masked>, databaseName=lms, serverName=*.*.*.*:5432}
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: driverClassName.................
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: healthCheckProperties...........{}
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: healthCheckRegistry.............
2015-10-06 12:26:44,274 DEBUG [localhost-startStop-1] HikariConfig: idleTimeout.....................30000
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: initializationFailFast..........true
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: isolateInternalQueries..........false
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: jdbc4ConnectionTest.............false
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: jdbcUrl.........................
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: leakDetectionThreshold..........0
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: maxLifetime.....................1800000
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: maximumPoolSize.................20
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: metricRegistry..................com.codahale.metrics.MetricRegistry@63d2fc48
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: metricsTrackerFactory...........
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: minimumIdle.....................10
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: password........................<masked>
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: poolName........................HikariPool-0
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: readOnly........................false
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: registerMbeans..................false
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: scheduledExecutorService........
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: threadFactory...................
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: transactionIsolation............
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: username........................
2015-10-06 12:26:44,275 DEBUG [localhost-startStop-1] HikariConfig: validationTimeout...............5000
2015-10-06 12:26:44,276 INFO  [localhost-startStop-1] HikariDataSource: HikariPool-0 - is starting.
2015-10-06 12:26:44,432 DEBUG [localhost-startStop-1] PoolElf: HikariPool-0 - Connection.setNetworkTimeout() is not supported (Method org.postgresql.jdbc4.Jdbc4Connection.getNetworkTimeout() is not yet implemented.)

完整的堆栈跟踪:

2015-10-06 12:09:36,885 DEBUG [http-nio-8080-exec-25] PoolElf: HikariPool-0 - Reset (nothing) on connection org.postgresql.jdbc4.Jdbc4Connection@3cc4d919
2015-10-06 12:09:42,651 DEBUG [Hikari housekeeper (pool HikariPool-0)] HikariPool: Before cleanup   pool HikariPool-0 stats (total=20, active=20, idle=0, waiting=1)
2015-10-06 12:09:42,652 DEBUG [Hikari housekeeper (pool HikariPool-0)] HikariPool: After cleanup    pool HikariPool-0 stats (total=20, active=20, idle=0, waiting=1)
2015-10-06 12:10:06,885 DEBUG [http-nio-8080-exec-25] HikariPool: Timeout failure   pool HikariPool-0 stats (total=20, active=20, idle=0, waiting=0)
2015-10-06 12:10:06,885 WARN  [http-nio-8080-exec-25] SqlExceptionHelper: SQL Error: 0, SQLState: null
2015-10-06 12:10:06,885 ERROR [http-nio-8080-exec-25] SqlExceptionHelper: HikariPool-0 - Connection is not available, request timed out after 30000ms.
2015-10-06 12:10:06,885 DEBUG [http-nio-8080-exec-25] PoolElf: HikariPool-0 - Reset (nothing) on connection org.postgresql.jdbc4.Jdbc4Connection@3cc4d919
2015-10-06 12:10:06,886 ERROR [http-nio-8080-exec-25] ErrorPageFilter: Forwarding to error page from request [/api/courses/121387/quizzes/i6fa2562510bf8578712380a87a433e97/student/30175] due to exception [org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.JDBCConnectionException: Could not open connection]
java.lang.RuntimeException: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.JDBCConnectionException: Could not open connection
    at lms.security.xauth.XAuthTokenFilter.doFilter(XAuthTokenFilter.java:86) ~[XAuthTokenFilter.class:na]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:120) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:91) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:53) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:213) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:176) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) [catalina.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.23]
    at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:102) ~[spring-boot-actuator-1.2.3.RELEASE.jar:1.2.3.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) [catalina.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.23]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:85) ~[spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) [catalina.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.23]
    at org.springframework.boot.context.web.ErrorPageFilter.doFilter(ErrorPageFilter.java:113) [spring-boot-1.2.3.RELEASE.jar:1.2.3.RELEASE]
    at org.springframework.boot.context.web.ErrorPageFilter.access$000(ErrorPageFilter.java:59) [spring-boot-1.2.3.RELEASE.jar:1.2.3.RELEASE]
    at org.springframework.boot.context.web.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:88) [spring-boot-1.2.3.RELEASE.jar:1.2.3.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.boot.context.web.ErrorPageFilter.doFilter(ErrorPageFilter.java:106) [spring-boot-1.2.3.RELEASE.jar:1.2.3.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) [catalina.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.23]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219) [catalina.jar:8.0.23]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) [catalina.jar:8.0.23]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) [catalina.jar:8.0.23]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:142) [catalina.jar:8.0.23]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) [catalina.jar:8.0.23]
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:617) [catalina.jar:8.0.23]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) [catalina.jar:8.0.23]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:518) [catalina.jar:8.0.23]
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1091) [tomcat-coyote.jar:8.0.23]
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:668) [tomcat-coyote.jar:8.0.23]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1521) [tomcat-coyote.jar:8.0.23]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1478) [tomcat-coyote.jar:8.0.23]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_45]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_45]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.0.23]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_45]
Caused by: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.hibernate.exception.JDBCConnectionException: Could not open connection
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:978) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:868) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:648) ~[servlet-api.jar:na]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:842) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:729) ~[servlet-api.jar:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:291) [catalina.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.23]
    at com.codahale.metrics.servlet.AbstractInstrumentedFilter.doFilter(AbstractInstrumentedFilter.java:104) ~[metrics-servlet-3.1.1.jar:3.1.1]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) [catalina.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.23]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-websocket.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) [catalina.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.23]
    at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration$ApplicationContextHeaderFilter.doFilterInternal(EndpointWebMvcAutoConfiguration.java:291) ~[spring-boot-actuator-1.2.3.RELEASE.jar:1.2.3.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) [catalina.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.23]
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77) ~[spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:239) [catalina.jar:8.0.23]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) [catalina.jar:8.0.23]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:316) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:126) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:90) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:122) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:168) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:48) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330) ~[spring-security-web-4.0.0.RELEASE.jar:na]
    at lms.security.xauth.XAuthTokenFilter.doFilter(XAuthTokenFilter.java:84) ~[XAuthTokenFilter.class:na]
    ... 46 common frames omitted
Caused by: org.hibernate.exception.JDBCConnectionException: Could not open connection
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:65) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:235) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:171) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1435) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:250) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.collection.internal.AbstractPersistentCollection.readElementByIndex(AbstractPersistentCollection.java:376) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.collection.internal.PersistentMap.get(PersistentMap.java:164) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at java_util_Map$get.call(Unknown Source) ~[na:na]
    at lms.service.QuizService.processAnswers(QuizService.groovy:66) ~[QuizService.class:na]
    at lms.service.QuizService$$FastClassBySpringCGLIB$$4dcc8beb.invoke(<generated>) ~[spring-core-4.1.6.RELEASE.jar:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717) ~[spring-aop-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) ~[spring-tx-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653) ~[spring-aop-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at lms.service.QuizService$$EnhancerBySpringCGLIB$$37a60c0a.processAnswers(<generated>) ~[spring-core-4.1.6.RELEASE.jar:na]
    at lms.web.rest.CourseResource.saveQuizResults(CourseResource.java:537) ~[CourseResource.class:na]
    at lms.web.rest.CourseResource$$FastClassBySpringCGLIB$$e3d2ba4d.invoke(<generated>) ~[spring-core-4.1.6.RELEASE.jar:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717) ~[spring-aop-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:68) ~[spring-security-core-4.0.0.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at com.ryantenney.metrics.spring.TimedMethodInterceptor.invoke(TimedMethodInterceptor.java:48) ~[metrics-spring-3.0.4.jar:na]
    at com.ryantenney.metrics.spring.TimedMethodInterceptor.invoke(TimedMethodInterceptor.java:34) ~[metrics-spring-3.0.4.jar:na]
    at com.ryantenney.metrics.spring.AbstractMetricMethodInterceptor.invoke(AbstractMetricMethodInterceptor.java:59) ~[metrics-spring-3.0.4.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653) ~[spring-aop-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at lms.web.rest.CourseResource$$EnhancerBySpringCGLIB$$ff854301.saveQuizResults(<generated>) ~[spring-core-4.1.6.RELEASE.jar:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_45]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_45]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_45]
    at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_45]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221) ~[spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137) ~[spring-web-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:966) ~[spring-webmvc-4.1.6.RELEASE.jar:4.1.6.RELEASE]
    ... 81 common frames omitted
Caused by: java.sql.SQLTransientConnectionException: HikariPool-0 - Connection is not available, request timed out after 30000ms.
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:213) ~[HikariCP-2.4.1.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:163) ~[HikariCP-2.4.1.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:85) ~[HikariCP-2.4.1.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:139) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:380) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:228) ~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]
    ... 126 common frames omitted
2015-10-06 12:10:12,651 DEBUG [Hikari housekeeper (pool HikariPool-0)] HikariPool: Before cleanup   pool HikariPool-0 stats (total=20, active=19, idle=1, waiting=0)
2015-10-06 12:10:12,652 DEBUG [Hikari housekeeper (pool HikariPool-0)] HikariPool: After cleanup    pool HikariPool-0 stats (total=20, active=19, idle=1, waiting=0)

任何线索都将有所帮助！

- dfche

1

这对我来说发生的原因是我忘记连接到我的工作VPN，这意味着连接被阻止了... - AO_

6个回答

52

来自堆栈跟踪:

HikariPool: 超时失败 HikariPool-0 统计信息 (总数=20, 活动的=20, 空闲的=0, 等待的=0) 意味着池达到了配置中设置的最大连接限制。

下一行: HikariPool-0 - 连接不可用，请求在30000毫秒后超时。意味着池等待了30000毫秒以获取空闲连接，但与此同时您的应用程序没有返回任何连接。

通常这是连接泄漏 (从池中借出的连接未关闭)，将 leakDetectionThreshold 设置为您期望 SQL 查询执行所需的最大值。

否则，您的 "同时" 最大连接要求高于20！

- Nitin

在启动时立即使用了3个连接：（总计=13，活动=3，空闲=10，等待=0）

2015-10-06 16:20:00,188警告[Hikari housekeeper (pool HikariPool-0)]LeakTask：触发连接泄漏检测连接org.postgresql.jdbc4.Jdbc4Connection@34e1598c，以下是堆栈跟踪 java.lang.Exception：检测到明显的连接泄漏 在org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection（DatasourceConnectionProviderImpl.java:139）~[hibernate-core-4.3.6.Final.jar:4.3.6.Final]

- dfche

1

泄漏检测阈值已设置，并在15秒后触发。将阈值增加到启动应用程序所需的时间和一些其他时间？ [INFO] lms.Application - 应用程序已在46.102秒内启动（JVM运行时间为49.097秒） - Nitin

有进展了吗？请检查Spring和Hibernate的配置。为了保持人们的兴趣集中，从问题中删除其他标签。只需要Spring Boot、Hibernate和HikariCP即可。 - Nitin

在将maxLifeTime=30000和maxConnections=50设置后，即使在非常轻微的使用情况下，我们仍然发现活动连接数缓慢增长。我计划对系统进行负载测试以尝试确定问题所在。我认为Postgres的使用可能是一个重要细节。 - dfche

通常情况下，maxLifeTime=30000太低了，而maxConnections=50对大多数人来说又太高了。请参见https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing。 - Nitin

显示剩余6条评论

8

通过在application.properties文件中添加以下属性，解决了我的问题：

  spring.datasource.hikari.connection-test-query=SELECT 1 FROM DUAL
  spring.datasource.hikari.connection-timeout=600000
  spring.datasource.hikari.maximum-pool-size=500
  spring.datasource.hikari.max-lifetime=1800000
  spring.datasource.hikari.minimum-idle=20
  spring.datasource.hikari.validation-timeout=3000
  spring.datasource.hikari.idle-timeout=60000

- Daniel Krait

2

只是增加了spring.datasource.hikari.maximum-pool-size就解决了我的问题。谢谢。 - Caio Santos

5

我曾遇到过类似的问题，罪魁祸首是使用jdbc的“queryForStream”。下面的文章提供了更多信息，但本质上您需要显式地关闭流或连接将保持开放状态。

https://mjchi7.github.io/posts/jdbctemplate-queryforstream-connection-leak/

- user19316412

2

我曾在与同事合作的项目中遇到了同样的问题。他使用的是JDBC，但在完成后忘记关闭一些数据库连接，导致了这个错误。如果您没有使用Hibernate或JDBC模板，请确保每个数据库连接都已关闭。点击此处查看更多示例：https://mkyong.com/jdbc/hikaripool-1-connection-is-not-available-request-timed-out-after-30002ms/

- mumbasa

0

AO_提到的VPN问题是相当普遍但被忽视的问题。

可能的原因之一是一个人正在请求服务器资源，而这些请求被组织的VPN阻止了。在这种情况下，可以尝试通过VPN连接。我过去也遇到过这种情况。

在分布式工作文化中，这种情况可能会在多个场合发生，主要是当Internet连接丢失/重置时会断开VPN连接。在这种情况下，唯一的方法就是在Internet连接恢复后重新连接到VPN。

- Asif Kamran Malick

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- dfche · Accepted Answer

97

我最终修复了它。问题与HikariCP无关。问题仍然存在是因为REST控制器中的一些复杂方法通过JPA存储库执行多个更改。由于某些原因，对这些接口的调用会导致“冻结”的活动连接数量增加，耗尽连接池。将这些方法注释为@Transactional或将所有逻辑包装在单个事务服务方法的调用中似乎可以解决问题。

- dfche

19

你是如何发现这个有问题的方法的？我也遇到了同样的问题 :( 谢谢 - Fabio Ebner

1

据我记得，我只是手动多次调用每个疑似的控制器方法，并进行突发式同时请求（模拟真实使用情况），以检查是否会触发Hikari问题。我可能会对服务方法进行调试，以了解在出现问题时Hibernate的操作方式，但只需在短时间内多次调用每个方法并检查是否会耗尽池资源即可。 - dfche

2

@Chhaiya Harshad @Transactional 可用于包装包含多个调用 DB 的业务方法，这些方法应在同一事务中执行。例如，您必须从表 1 中读取某些值，对其进行处理，并在单个业务方法中将更改写入表 2 和 3。如果您希望在写入表 3 发生错误的情况下回滚表 2 中的更改，则应将整个方法包装在 @Transactional 中以获得可预测的结果。 - dfche

你能详细解释一下吗？例如，我有一个类似的情况，其中包括调用@Async方法和对Sybase数据库的多次调用。 :'( - Marlon López

@MarlonLópez 我已经有点脱离上下文了，但你可以检查一下在异步方法内外的方法中是否有事务（即 @Transactional 注释）。此外，这可能会有所帮助：https://dev59.com/TmIj5IYBdhLWcg3wv3i2 - dfche

显示剩余2条评论