Idle connections are not getting released when used with Try-with-resources

[VikChauhan20]

Facing issue where few select statements executed leaks connections in try with resource but code works for update and delete statements which too are in try with resource.
We are using postgresql DB - spring.datasource.driver-class-name=org.postgresql.Driver

Code snippet: PFA the code snippet.. few values changed for security purposes

Hikari Properties being used:
spring.datasource.hikari.minimumIdle=10
spring.datasource.hikari.maximumPoolSize=50
spring.datasource.hikari.idleTimeout=10000
spring.datasource.hikari.connectionTimeout=240000
spring.datasource.hikari.maxLifetime=300000

Error Screenshot -- Query in image is different but issue is same

Please help.

[quaff]

Connection keep open for reusing, that's the purpose of pool, what's the problem here?

[VikChauhan20]

Connection keep open for reusing, that's the purpose of pool, what's the problem here?

The problem is that they are not being reused as well.. Those connections are stuck in that idle stage and not utilized for other transactions. Thus our total pool connections get exhausted when these stuck connections number become huge.

[quaff]

Connection keep open for reusing, that's the purpose of pool, what's the problem here?

The problem is that they are not being reused as well.. Those connections are stuck in that idle stage and not utilized for other transactions. Thus our total pool connections get exhausted when these stuck connections number become huge.

What makes you think they are not reused? please note the default minIdle is 10.

[VikChauhan20]

Connection keep open for reusing, that's the purpose of pool, what's the problem here?

The problem is that they are not being reused as well.. Those connections are stuck in that idle stage and not utilized for other transactions. Thus our total pool connections get exhausted when these stuck connections number become huge.

What makes you think they are not reused? please note the default minIdle is 10.

@quaff Basis of my understanding is presented by this screenshot as we have 4 pods running each with 10 minIdle... so total should be 40 but from yellow highlighted you can see it is 42 connections and also state change is more then 46 mins for these 2 connections.. Request help

[quaff]

so total should be 40

The total should be 40 to 200 because the maximumPoolSize is 50 if concurrency happens, and the total should be 40 after idleTimeout elapsed.

[VikChauhan20]

so total should be 40

The total should be 40 to 200 because the maximumPoolSize is 50 if concurrency happens, and the total should be 40 after idleTimeout elapsed.

@quaff
Yes, the problem is that it is not coming to 40.... even when idleTimeout elapsed like here 2 idle connections have exceeded timeout but are not being released. Thus those 2 connections in idle state will remain until we restart the pods.
These idle connections keep on increasing and ultimately max pool size reaches 200 as these idle connections are not being released and our application start giving Exception "Connection Timeout Error" as no more connections are available in pool for allocation :(

[VikChauhan20]

Any suggestion is appreciated as issue still persists :(

[brettwooldridge]

@VikChauhan20 Have you enabled the leakDetectionThreshold and DEBUG level logging?