How does isValid() actually work?

[vghero]

Hey guys,
we're currently investigating an issue with Hikari 4.0.3 and Postgresl JDBC driver 42.6.1 where checkouts of a connection from the pool takes about ~800ms. One idea was, that it might come from a connection test query that is executed when a connection is borrowed from the pool. We didn't explicitly configure one though. Debugging shows, that isValid() is called on the connection. It seems that an empty SQL ("") is used for that within the PG driver. We're wondering how this actually works under the hood to check whether a connection is still valid or not. Does someone have a high level explanation for that? Any insights are really appreciated.

Whether it's isValid() or something in the Hikari Pool is not identified yet. We just looked at the Hikari metrics and that acquire metrics out of the sudden take more than before. Sadly we can see it only happening on one prod environment and they all run the same version. So maybe some kind of race condition. I also saw that there were quite some changes around the isValid recently - even in Jan with the latest version. Before there were some lockings involved, which got removed now. But no idea whether this could be something.

Anyhow, if somehow knows how that connection check works, it would be awesome to share it, thanks :D!

[davecramer]

The empty sql "" is still a valid query and it replies with a response so we consider it valid

[vghero]

Hey @davecramer, thanks for taking a look, really appreciated! So it means it really does a roundtrip to the server and hence it knows the connection is still valid?

[davecramer]

Correct!

[vghero]

I wanted to give some feedback on our issue. It turned out that it was on AWS Aurora side that (only) the writer instance had "dropped packages". We had to re-create it to get rid of the issue. Just in case someone has similar issues. Like mentioned, we could see increased latency (for 99% and 99.9 percentile) of e.g. acquire operations (borrow connection from pool) in Hikari suddenly appearing. We did a failover to the reader first and saw that the issue went away. So something to try first and narrow down this issue.