MSSQL Server connection goes to sleeping state and blocking another queries

[ravirajsinh-lab]

I am encountering an issue with a sleeping connection in MSSQL production server. My application is a multi-tenant application that connects to 6 databases and has 30 Tomcat instances. I have used the following libraries:

• JDK: Azul 11.0.12.0.101
• Apache Tomcat/9.0.54
• Mssql Driver Version 12.8.0.jre11
• HikariCP Version 5.1.0 (latest)

Hikari cp config

• "maxLifetime": 1320000,
• "idleTimeout": 238000,
• "connectionTimeout": 30000,
• "minimumIdle": 5,
• "maximumPoolSize": 10,
• "keepaliveTime": 30000,
• "connectionTestQuery": "IF @@TRANCOUNT > 0 ROLLBACK TRAN", // added this test query, but still not working.
• "leakDetectionThreshold": 60000
• "dataSource.ConnectRetryCount": 5,
• "dataSource.ConnectRetryInterval": 5,
• "dataSource.queryTimeout": 1200,
• "dataSource.cancelQueryTimeout": 300,
• "dataSource.lockTimeout": 1500000,
• "dataSource.socketTimeout": 1500000,

I have attempted to adjust the above Hikari pool configuration multiple times, but I am still receiving the sleeping connection issue, which is causing other queries to become blocked. When checking sp_whoisactive in the MSSQL server, I can see that sessions are in sleeping states for hours. It appears that HikariCP is not closing connections properly.

During my investigation of the HikariCP code, I discovered that in the method PoolBase.quietlyCloseConnection, it attempts to close connections. However, I found that it sets the NetworkTimeout hardcoded to 15 seconds. This leads me to question whether this shorter timeout is causing the connections to not be closed properly.

Can you provide any assistance? Thank you in advance.

Note: There is an Azure gateway between the application and database.

[ravirajsinh-lab]

@brettwooldridge can you please help here?

[quaff]

@ravirajsinh-lab I created #2247 to allow configuring closeTimeout.

[brettwooldridge]

This should be resolvable by configuring the socket timeout for your driver and ensuring that keepalive is properly configured, see Connection Recovery for MSSQL. I do recommend configuring keepalive at the OS level, regardless of what drivers say they support, see Setting OS TCP Keepalive.