[Bug]: Successful concurrent transactions can exhaust the Hikari connection pool

[buehlefs]

Description

Successfull transactions can exhaust the HikariPool of connections when they are executed concurrently. The issue does not appear when the transactions are run sequentially in a while-true loop in a single thread.
Later transactions will then result in stacktraces like this after a long timeout:

error DatabaseError ("Error while executing SQL query: INSERT INTO hello VALUES ('test'). error while getting the connection for SQLClientConnector. HikariPool-1 - Connection is not available, request timed out after 30000ms..",errorCode=0,sqlState=null)

    callableName: execute moduleName: ballerinax.java.jdbc.1.Client fileName: client.bal lineNumber: 79
    callableName: dbTest moduleName: test.playground.0 fileName: main.bal lineNumber: 24
    callableName: checkForPanic moduleName: test.playground.0.TestTask fileName: main.bal lineNumber: 78
    callableName: execute moduleName: test.playground.0.TestTask fileName: main.bal lineNumber: 66

Steps to Reproduce

The issue should be reproducible with the test code in issue #41281
All transactions must commit normally to make the issue more likely to occur.

Affected Version(s)

Ballerina 2201.8.5 (Swan Lake Update 8)
Ballerina 2201.8.4

OS, DB, other environment details and versions

OS: Ubuntu 20.04 running inside WSL2

JDBC Connector used:

[[platform.java11.dependency]]
artifactId="sqlite-jdbc"
version="3.36.0.1"
groupId="org.xerial"

Related area

-> Compilation

Related issue(s) (optional)

No response

Suggested label(s) (optional)

No response

Suggested assignee(s) (optional)

No response

[buehlefs]

I can still reproduce this in Ballerina 2201.9.0
@dsplayerX is this issue being worked on?

[dsplayerX]

Hi @buehlefs,

We believe this issue arose from the locking implemented to make transactions concurrent safe in this PR. The delayed release of these locks and concurrent requests could be causing the Hikari pool to be exhausted. We're currently searching for a method to address this issue.

[buehlefs]

I can no longer reproduce this issue using Ballerina 2201.10.2 so it might have been fixed.