HikariCP挂在getConnection上 [英] HikariCP hanging on getConnection

问题描述

我有一个使用HikariCP对使用JTDS的SQL Server DB进行连接池连接的应用程序.如果我让该应用程序在一夜之间运行，那么在早上，第一个查询将与下面的堆栈跟踪一起挂起.如果我再次请求，则新查询将正常运行.我是否缺少超时，这将停止第一个查询的挂起?它似乎挂了大约10-> 20分钟，然后开始工作.因此，在10-> 20分钟后，sockRead和挂起的线程消失了.我认为挂起的查询即可完成，但我可能是错的.我的Hikari配置也在下面.

I have an application that uses HikariCP for connection pooling against an SQL Server DB using JTDS. If I leave the app running overnight, in the morning the first query will hang with the stack trace below. If I request again then new queries will work OK. Is there a timeout I'm missing that will stop the first query from hanging? It seems to hang for about 10->20 minutes and then it starts working. So after 10->20 minutes the sockRead and hung thread disappears. I think the hung query then completes but I could be wrong. My Hikari config is below too.

java.lang.Thread.State: RUNNABLE
      at java.net.SocketInputStream.socketRead0(SocketInputStream.java:-1)
      at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
      at java.net.SocketInputStream.read(SocketInputStream.java:170)
      at java.net.SocketInputStream.read(SocketInputStream.java:141)
      at java.io.DataInputStream.readFully(DataInputStream.java:195)
      at java.io.DataInputStream.readFully(DataInputStream.java:169)
      at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:850)
      at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:731)
      - locked <0xeeb> (a java.util.concurrent.ConcurrentHashMap)
      at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:477)
      at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:114)
      at net.sourceforge.jtds.jdbc.ResponseStream.peek(ResponseStream.java:99)
      at net.sourceforge.jtds.jdbc.TdsCore.wait(TdsCore.java:4127)
      at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1086)
      - locked <0xeec> (a net.sourceforge.jtds.jdbc.TdsCore)
      at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:563)
      at net.sourceforge.jtds.jdbc.JtdsStatement.executeImpl(JtdsStatement.java:809)
      at net.sourceforge.jtds.jdbc.JtdsStatement.execute(JtdsStatement.java:1282)
      at com.zaxxer.hikari.pool.PoolElf.isConnectionAlive(PoolElf.java:224)
      at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:188)
      at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:163)
      at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:85)
      at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:204)
      at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
      at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:130)

Hikari配置:

hconf.setJdbcUrl(url) // url looks like: jdbc:jtds:sqlserver://host/DB
hconf.setConnectionTestQuery("select 1")
hconf.setDriverClassName("net.sourceforge.jtds.jdbcx.JtdsDataSource")
// set username and password

hconf.setConnectionTimeout(5 * 1000)
hconf.setValidationTimeout(1000)
hconf.setIdleTimeout(10 * 60 * 1000)
hconf.setMaxLifetime(30 * 60 * 1000)
hconf.setLeakDetectionThreshold(60 * 1000)
hconf.setInitializationFailFast(false)
val numThreads = 10
hconf.setMaximumPoolSize(numThreads * 5)
hconf.setMinimumIdle(numThreads)
hconf.setPoolName(name)
hconf.setRegisterMbeans(false)

HikariCP:2.4.1

HikariCP: 2.4.1

JTDS:1.3.1

JTDS: 1.3.1

更新:

我现在知道问题出在哪里.在HikariCP isConnectionAlive中，它尝试在调用setConnectionTestQuery中设置的查询之前将网络超时设置为在setValidationTimeout中指定的超时.但是，JTDS不支持java.sql.Connection.setNetworkTimeout，因此读取超时是操作系统的默认设置.大约需要10分钟.

I now know what the problem is. In HikariCP isConnectionAlive it tries to set the network time-out to what is specified above in setValidationTimeout before calling the query set in setConnectionTestQuery. However JTDS doesn't support java.sql.Connection.setNetworkTimeout so the read timeout is the OS default. Which is about 10 minutes.

理论上，您应该可以将驱动程序属性套接字超时设置为可解决的方法(尽管它对我而言不起作用).但这并不是一个很好的解决方法，因为您真的希望测试查询与实际查询的查询超时有所不同.

In theory you should be able to set the driver properties socket timeout as a work around (although it's not working for me). But that isn't a great work around as you really want the query timeout to be different for test queries vs real queries.

所以这不是HikariCP问题，而是JTDS问题.

So this isn't a HikariCP problem, it's a JTDS problem.

推荐答案

我建议通过log4j或您使用的任何日志记录框架为HikariCP软件包启用调试级别的日志记录. HikariCP应该记录连接的退出和创建情况，您可以使用它来验证连接是否在其配置的maxLifetime上正确循环.

I suggest enabling debug level logging for the HikariCP package, through log4j or whatever logging framework you are using. HikariCP should log connection retirement and creation, and you can use it to verify that connections are properly cycling at their configured maxLifetime.

但是，查看您发布的stacktrace，在HikariCP isConnectionAlive()检查期间，线程似乎挂在JTDS驱动程序中. HikariCP在这里可以提供的帮助很少.但是，这很奇怪.

However, looking at the stacktrace you posted, the thread appears to be hung in the JTDS driver during the HikariCP isConnectionAlive() check. There is not much HikariCP can do here to help. It is however strange.

您说早上第一个查询将挂起".电脑要睡觉了吗? SQL Server会进入睡眠状态吗?这样做会阻止HikariCP在夜间正确循环连接.再次，调试日志记录将在这里有所帮助-即使应用程序处于空闲状态，也应该有相当规律的退役和创建活动在一夜之间发生.

You said "in the morning the first query will hang". Is the computer going to sleep? Is the SQL Server going to sleep? Doing so would prevent HikariCP from properly cycling connections during the night. Again, the debug logging would help here -- there should be fairly regular retirement and creation activity happening overnight, even though the application is idle.

这篇关于HikariCP挂在getConnection上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！