Database Connector | Error "An attempt by a client to checkout a Connection has timed out" Due to Unreleased DB Pool Connections When Result Set Is Not Consumed in Time

Mule 3:

Consume the result set, e.g. using OBJECT TO JSON, after running the query. Once the result set is consumed, the connection will be released
If you are calling a Stored Procedure returning no result set, the solution is to disable streaming.

Mule 4:

Consume the result set after running the query.

In the following example, the "Transform Message" operation is used as an example to consume the result set after the SELECT statement, which is a streaming component.

Before (the result set of the SELECT statement is not consumed):

<db:select doc:name="Select * from test" doc:id="c81a6931-16cf-43db-9959-25b71f4cc2be" config-ref="Database_Config"> 
             <db:sql><![CDATA[SELECT count(*) as counter FROM demo]]></db:sql> 
</db:select>

After (the "Transform Message" operation consumes the result set):

<db:select doc:name="Select * from test" doc:id="c81a6931-16cf-43db-9959-25b71f4cc2be" config-ref="Database_Config">
            <db:sql><![CDATA[SELECT count(*) as counter FROM demo]]></db:sql>
</db:select>
<ee:transform doc:name="Transform Message" doc:id="70ef20e3-1817-48d8-9357-9166c938807b" >
	<ee:message >
		<ee:set-payload ><![CDATA[%dw 2.0
output application/json
---
payload]]></ee:set-payload>
	</ee:message>
</ee:transform>

Note: If the transformation is using application/java, it will not help consume the payload and release the database connection.

output application/java
---
payload

If the consumption of the result set is late in the flow or takes time and the connection pool gets maxed out due to a large number of requests, there are two options to consider:

1. Increase the "Max pool size" to a bigger number to accommodate the number of connections required

2. Send the result to a VM queue for asynchronous processing, allowing the database connection to be released back to the connection pool promptly.

For more details, refer to the knowledge article below:

Error "An attempt by a client to checkout a Connection has timed out." when a HTTP request follows Database Connector