Concurrent database calls timeout (with postgres)

[Shadow-Devil]

Description: I'm currently adding Ballerina to the FrameworkBenchmark and found a bug while implementing the query test.

Here is my primitive implementation of this test:

import ballerina/http;
import ballerina/io;
import ballerina/random;
import ballerinax/postgresql;
import ballerinax/postgresql.driver as _;

type World record {|
    int id;
    int randomNumber;
|};

final postgresql:Client dbClient = check new ("tfb-database", "benchmarkdbuser", "benchmarkdbpass", "hello_world");

service / on new http:Listener(8080) {

    # Test 3
    isolated resource function get queries(@http:Query string? queries) returns World[]|error {
        io:println("start");
        int queriesInternal;
        if queries is () {
            queriesInternal = 1;
        } else {
            var castedQueries = int:fromString(queries);
            if castedQueries is error || castedQueries < 1 {
                queriesInternal = 1;
            } else {
                queriesInternal = int:min(500, castedQueries);
            }
        }
        World[] result = [];
        foreach int i in int:range(0, queriesInternal, 1) {
            var randomId = check random:createIntInRange(1, 10000);
            World world = check dbClient->queryRow(`SELECT id, randomNumber FROM World WHERE id = ${randomId}`);
            result.push(world);
            io:println("mid", result.length());
        }
        io:println("end", result.length());
        return result;
    }
}

But when verifying this locally this does not work as expected: There are 512 concurrent requests made that all set ?queries=20. Therefore 512 * 20 = 10240 database calls should be made. But when executing the test, some requests just timeout.

I've added debug logs to understand more easily where the requests are time outing:

• there are (always) 512 start printed, so every request was successfully started executing
• there are ~503 mid1 printed
• there are ~490 mid2 printed
• ...
• there are ~200 end20 printed

My assumption is that the different calls to db->queryRow(...) create a deadlock.

Other things that I tried:

• I've tried making the remote function not isolated and incrementing a global counter for each request and then everything worked as expected.
• I've changed the postgres:Client to the jdbc:Client and got the same timeouts.

You can see the full code here: https://github.com/Shadow-Devil/FrameworkBenchmarks/tree/master/frameworks/Ballerina/ballerina Run ./tfb --test ballerina --type query to run the test case.