JMeter : Check JDBC connection open or close after each thread execution

I am trying to check the JDBC connection status after each thread execution whether it is close or open?.

In my thread group there are three things

1. JDBC connection configuration

2. JDBC request (select * from employee)

3. JSR223 PostProcessor

   Script :

   def connection = org.apache.jmeter.protocol.jdbc.config.DataSourceElement.getConnection('ConnectionString')
   log.info('*************Connection closed: '+ connection.isClosed())

Above script is logging the connection status after each thread execution when loop count is 1. Problem here as soon as I change the loop count to >= 2. it started throwing the error below error

Problem in JSR223 script, JSR223 PostProcessor
javax.script.ScriptException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object

And when I remove the Post processor and increase the loop count it is working fine.

Logs :

2023-02-19 16:15:33,599 INFO o.a.j.t.JMeterThread: Thread started: DB Thread Group 2-1

2023-02-19 16:15:38,054 DEBUG o.a.j.p.j.AbstractJDBCTestElement: executing jdbc:SELECT * FROM EMPLOYEE

2023-02-19 16:15:38,623 INFO o.a.j.e.J.JSR223 PostProcessor: *************Connection closed: false

2023-02-19 16:15:58,637 ERROR o.a.j.e.JSR223PostProcessor: Problem in JSR223 script, JSR223 PostProcessor
javax.script.ScriptException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object, borrowMaxWaitDuration=PT10S
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:320) ~[groovy-jsr223-3.0.11.jar:3.0.11]
at org.codehaus.groovy.jsr223.GroovyCompiledScript.eval(GroovyCompiledScript.java:71) ~

I can think of 2 possible reasons:

1. Either your database is down/overloaded/not reachable via JDBC

2. Or your connection pool settings need to be tweaked, i.e. max number of connections and/or wait time need to be increased:

   

In general I don't think your approach is correct, as per JavaDoc:

>This method generally cannot be called to determine whether a connection to a database is valid or invalid. A typical client can determine that a connection is invalid by catching any exceptions that might be thrown when an operation is attempted.

So you might want to increase debug logging verbosity for JMeter, your JDBC driver and Java SQL namespace instead

In the JDBC configuration are you using a connection pool to request connections?

What your test shows is that the JSR223 script is closing the connection, which is probably a good thing from a coding perspective, but the next iteration of the loop tries to execute a request with a closed Connection and blammo. If you switch from raw connections to a connection pool when the JSR 223 closes the connection it'll be returned to the pool and remain open for the next iteration of the loop. You'll have to switch to using DataSource API typically for this, but it's a minor tweak to the script.