HikariCP not adhering connectionTimeout #867
Comments
|
@MikeN123 Thanks for the report. I'll probably have to dig into the jTDS code to figure out what is going on. From the stacktrace I can tell you that the connectionTimeout is not coming into play. There was an existing connection that HikariCP was trying to validate (isConnectionAlive()), and was running a query Statement. Clearly that connection was broken, but for whatever reason that call |
|
Yeah, the difference between 2.4 and 2.5 is a bit weird. So, your conclusion is: connection was broken -> HikariCP tries to validate -> hangs on network-level so needs to wait for the socket timeout (instead of connectionTimeout? Is there any way to interrupt this while still allowing long-running queries? (which was a reason we increased the socket timeout) It would be fine if HikariCP declares the connection dead after 10 seconds, but it's not fine if that means we cannot run a query that takes more than 10 seconds. Not sure why it is 2x the socketTimeout, but I think I saw that before. Maybe jTDS is doing something weird, or maybe HikariCP is checking two connections, instead of giving up after one? |
|
@MikeN123 The I actually found the cause of the socketTimeout x2 event, though it took staring at the code for a minute to see it. In 152 try (Statement statement = connection.createStatement()) {
153 if (isNetworkTimeoutSupported != TRUE) {
154 setQueryTimeout(statement, (int) MILLISECONDS.toSeconds(Math.max(1000L, validationTimeout)));
155 }
156
157 statement.execute(config.getConnectionTestQuery());
158 }The log you provided above contains an exception and a nested exception. The first exception: points to The second exception: points to Combined the two resulted in a 600194ms timeout. This is rather disappointing behavior by jTDS. I would have hoped that the first socket read timeout exception would have resulted in the Connection being marked as closed, and therefore the call to HikariCP does its best in the absence of support for Unfortunately, support for I went ahead and implemented support for In the meantime, one option would be to truly scrutinize your code to consider what the true maximum query time is, and decrease the jTDC socketTimeout to something above that but less than the current 300 seconds. For example, if your longest query is ~40 seconds, you might consider doubling that for your socket timeout. Keep in mind that if jTDS is not flagging the connection as closed when it encounters a socket timeout, you could easily see very long recovery times, either in the presence or absence of HikariCP. For example, if a thread is in user-land code (not in the pool) executing code like: 01 try (Connection conn = ds.getConnection();
02 Statement stmt = conn.createStatement()) {
03 try (ResultSet rs = stmt.executeQuery("SELECT something FROM sometable")) {
04 rs.getString("somefield");
05 ...
06 }
07 }and the connection is severed just after the thread passes line 3, jTDS is going to encounter a socketTimeout length delay at line 4, a socketTImeout length delay at line 6 (close ResultSet), and two socketTimeout length delays at line 7 (close Statement and close Connection). |
|
Many thanks for your lengthy comment and explanation. And for the PR to the Microsoft JDBC driver - the lack of timeout support has always been an important reason for us to stick with jTDS. Unfortunately, we have some long running migrations, which require a larger timeout. I will check if we can adjust the timeout temporarily during the migrations, or maybe we can use a separate connection for this. |
|
@MikeN123 Just so you know, the Microsoft JDBC 6.0 preview driver already has support for a global socket timeout (like jTDS). My pull request is to add support for per-Connection socket timeouts, via It also looks like they are about to merge my change, as it passed review with only minor cosmetic changes. Also, if you do decide to switch to the new MS driver, you can remove your validation query as their driver supports the |
|
You might want to prefer to use a configured validation query with the new MS driver. The new MS driver currenlty implements Since |
|
microsoft/mssql-jdbc#253 has been merged. |
|
@brettwooldridge Thanks for the improvement, much appreciated. I did not expect a solution for this odd issue, but you fixed it. |
|
@brettwooldridge I want to implement querytimeout for my application.when query exceeds 5 minutes then it should timeout.Is there any prooperty available in Hikari Datasource.please suggest. |
Environment
I just rebooted our databaseserver, and it seems HikariCP had some issues reconnecting. Looking at the logs, I see a request timed out after 600 seconds, but the Hikari connectionTimeout is set to 10 seconds, the JTDS socketTimeout is set to 300 seconds and the JTDS loginTimeout is set to 5 seconds.
Shouldn't the connection acquire timeout after 10 seconds, instead of hanging for 10 minutes?
Only 3 customers sites experienced this issue, and they have in common that they run HikariCP 2.5.1. Other sites, not showing the same issue, run HikariCP 2.4.1 or 2.4.7.
I haven't been able to reproduce this problem locally, making it difficult to pinpoint the exact problem.
Detailed HikariCP settings:
Stacktrace:
The text was updated successfully, but these errors were encountered: