Redshift connector hanging when strand count exceeds BALLERINA_SQL_MAX_POOL_SIZE

Description: Consider the below code

import ballerina/io;
import ballerina/sql;
import ballerinax/aws.redshift;
import ballerinax/postgresql.driver as _;

configurable string jdbcUrl = ?;
configurable string user = ?;
configurable string password = ?;

final redshift:Client dbClient = check new (jdbcUrl, user, password);

type User record {|
    int user_id;
    string username;
    string email;
    int age;
|};

public function main() returns error? {
    future<User|error>[] futures = [];
    int count = 51; // Change the number on strands
    foreach int i in 1 ... count {
        future<User|error> res = start getUser(i);
        futures.push(res);
    }
    foreach int i in 1 ... count {
        User user = check wait futures.pop();
        io:println(user);
    }
}

isolated function getUser(int id) returns User|error {
    io:println(string `STATED for ID: ${id}`);
    sql:ParameterizedQuery sqlQuery = `SELECT * FROM Users WHERE user_id = ${id}`;
    User user = check dbClient->queryRow(sqlQuery);
    return user;
}

I observed that the threads are getting blocked if the strand count exceeds 50, as is evident from the strand dump.

Ballerina Strand Dump [2024/10/07 17:42:58]
===========================================

Total strand group count    :   54
Total strand count          :   102
Active strand group count   :   2
Active strand count         :   2

group 3 [QUEUED]: [1]
    strand 1 "main" [$anon...0:main] [WAITING]:
        at  $anon...0.0.0:main(query.bal:27)
            $moduleExecute

group 45 [QUEUED]: [1]
    strand 43 "res" [$anon...0:main][1] [BLOCKED]:
        at  $anon...0.0.0:getUser(query.bal:35)

===========================================

This is probably due to Redshift internally using a ThreadPoolExecutorof sql module, which is configured with a default pool size of 50. Therefore, whenever a strand is spawned on the Ballerina side, a thread from the thread pool is utilized to serve that particular strand.

However, scheduling more strands than the number of available threads results in a hang issue. Changing the pool size to a higher value via the environment variable BALLERINA_SQL_MAX_POOL_SIZE could temporarily solve the issue.

However, ideally, the strand should not get blocked when the pool size is exhausted. It should wait until a thread in the pool becomes available to serve the request. This should be handled on the sql module side.

Affected Versions:

OS, DB, other environment details and versions:

Related Issues (optional):

Suggested Labels (optional):

Suggested Assignees (optional):

ballerina-platform / ballerina-library

Redshift connector hanging when strand count exceeds BALLERINA_SQL_MAX_POOL_SIZE #7244