HikariCP hanging on getConnection

Question

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 config:

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

JTDS: 1.3.1

Update:

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.

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

score 2 · Answer 1 · answered Jan 13 '16 at 15:13

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.

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.

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.

Thanks Brett. You're right, this seems to be a JTDS problem (see my update in the question). Also you're right about my PC (where I have my dev SQL server install) going to sleep. I hadn't realised it did that at night until this problem. Do you know of any way to implement the missing `setNetworkTimeout` call in JTDS? It seems a lot of JDBC drivers are missing this functionality. — David, Jan 13 '16 at 15:23

Nitin · Answer 2 · 2016-01-13T07:51:17.210

0

If you update to 2.4.3 it has subtle changes in getConnection() and some other that fixed issues in some/similar situation. please report if it solves or makes any difference in behavior or stack-trace

edited Jan 13 '16 at 07:51

answered Jan 13 '16 at 05:59

Nitin

1,582
11
12

I've upgraded. I'll keep an eye on it over the next few days and report back. – David Jan 13 '16 at 12:34
@Dave Wondering if your upgrade solved your problem. Thanks! – javadev Apr 09 '20 at 09:01
@javadev I updated the question (see the "Update:" section) with what I found. It turned out not to be a hikari problem. – David Apr 14 '20 at 08:14

HikariCP hanging on getConnection

2 Answers2

Linked