我正在使用Spring Boot(1.5.6),Hibernate,Postgres和Hikari(2.7.8)。我的配置如下:
spring.datasource.hikari.minimumIdle=1
spring.datasource.hikari.maximumPoolSize=20
spring.datasource.hikari.idleTimeout=30000
spring.datasource.hikari.poolName=SpringBootJPAHikariCP
spring.datasource.hikari.maxLifetime=50000
spring.datasource.hikari.connectionTimeout=30000
我所期望的是,空闲连接应在 30000
毫秒或 30 秒后释放。问题是每次请求都创建一个新连接,使所有空闲连接保持不变。因此,经过一段时间后,我会拥有 20 个空闲连接,并且在发出新请求时,Hikari 尝试获取新的连接并收到了 SpringBootJPAHikariCP - Connection is not available, request timed out after 30001ms.
的错误信息。那么,我做错了什么?还是我误解了配置?
Hikari 初始化日志:
SpringBootJPAHikariCP - configuration:
allowPoolSuspension.............false
autoCommit......................true
catalog.........................none
connectionInitSql...............none
connectionTestQuery.............none
connectionTimeout...............30000
dataSource......................none
dataSourceClassName.............none
dataSourceJNDI..................none
dataSourceProperties............{password=<masked>}
driverClassName................."org.postgresql.Driver"
healthCheckProperties...........{}
healthCheckRegistry.............none
idleTimeout.....................30000
initializationFailFast..........true
initializationFailTimeout.......1
isolateInternalQueries..........false
jdbc4ConnectionTest.............false
jdbcUrl.........................jdbc:postgresql://localhost:5432/dbname
leakDetectionThreshold..........0
maxLifetime.....................50000
maximumPoolSize.................20
metricRegistry..................none
metricsTrackerFactory...........none
minimumIdle.....................1
password........................<masked>
poolName........................"SpringBootJPAHikariCP"
readOnly........................false
registerMbeans..................false
scheduledExecutor...............none
scheduledExecutorService........internal
schema..........................none
threadFactory...................internal
transactionIsolation............default
username........................"postgres"
validationTimeout...............5000
更新: 在过去24小时中,我尝试了来自不同线程的几个解决方案,但没有一个能解决我的问题。因此,以下是可能很重要的观察结果。
- 发现日志
SpringBootJPAHikariCP - Reset (autoCommit) on connection org.postgresql.jdbc.PgConnection@1344bbf1
,研究了HikariCP中连接的自动提交重置这个线程。尝试在hibernate和Hikari两端设置相同的自动提交
(true),并且还尝试在两端都设置为false,但仍然没有成功。 启用了
leakDetectionThreshold
,出现泄漏检测异常。因此,尝试了解hibernate/spring事务管理器是否释放了连接。从下面的日志中看来,Hibernate工作得很好。28 22:19:35- DEBUG - o.s.orm.jpa.JpaTransactionManager-371 :: Opened new EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@4212be39] for JPA transaction 28 22:19:35- DEBUG - o.h.e.t.internal.TransactionImpl-51 :: begin 28 22:19:35- DEBUG - o.s.orm.jpa.JpaTransactionManager-403 :: Exposing JPA transaction as JDBC transaction [org.springframework.orm.jpa.vendor.HibernateJpaDialect$HibernateConnectionHandle@243e942] 2com.someentity.MyEntity#ac918eed-345f-4a6c-8539-fe14e7fc41e2 28 22:19:35- DEBUG - o.h.r.j.i.LogicalConnectionManagedImpl-137 :: Initiating JDBC connection release from afterTransaction 28 22:19:35- DEBUG - c.zaxxer.hikari.pool.ProxyConnection-242 :: SpringBootJPAHikariCP - Executed rollback on connection org.postgresql.jdbc.PgConnection@1344bbf1 due to dirty commit state on close(). 28 22:19:35- DEBUG - o.h.e.i.AbstractFlushingEventListener-132 :: Processing flush-time cascades 28 22:19:35- DEBUG - o.h.e.i.AbstractFlushingEventListener-174 :: Dirty checking collections 28 22:19:35- DEBUG - org.hibernate.internal.SessionImpl-508 :: Disconnecting session 28 22:19:35- DEBUG - o.s.orm.jpa.JpaTransactionManager-759 :: Initiating transaction commit 28 22:19:35- DEBUG - o.s.orm.jpa.JpaTransactionManager-512 :: Committing JPA transaction on EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@4212be39] 28 22:19:35- DEBUG - o.h.e.t.internal.TransactionImpl-62 :: committing 28 22:19:35- DEBUG - o.h.r.j.i.LogicalConnectionManagedImpl-137 :: Initiating JDBC connection release from afterTransaction 28 22:19:35- DEBUG - o.h.r.j.i.LogicalConnectionManagedImpl-137 :: Initiating JDBC connection release from afterTransaction 28 22:19:35- DEBUG - o.s.orm.jpa.JpaTransactionManager-600 :: Closing JPA EntityManager [org.hibernate.jpa.internal.EntityManagerImpl@4212be39] after transaction 28 22:19:35- DEBUG - o.s.o.jpa.EntityManagerFactoryUtils-435 :: Closing JPA EntityManager
从
postgres
的角度来看,所有空闲连接都是idle,而从Hikari的角度来看则是活跃的(active
)。因此,当数据库中存在5个空闲连接时,在Hikari日志中有total=5, active=4, idle=, waiting=0
。
注意:
也许我遇到了https://github.com/brettwooldridge/HikariCP/issues/109中描述的确切问题,在我的情况下,每次事务处理时活动连接都会增加。
HikariCP - connection is not available也是同样的问题,但没有人提供清晰的解决方案。顺便说一下,我一开始就像被接受的答案建议的那样使用了
@Transactional
。
leakDetectionThreshold
后出现了几个泄漏检测异常,所以可能并不是池本身的问题,可能是我的应用程序做错了什么。仍然希望得到建议。 - Saif