Error: Acquire Timeout Exceeded for queued queries.

[ankurgupta4891]

Hi, I'm trying to submit multiple queries (where query execution time (~2-3 mins) is more than acquire time out(1 min)) more than max connections in pool and I get Acquire timeout exceeded error.

Expected Behavior

Queries which couldn't get connection should be queued and connection acquire time should start when there is a free connection available.

Steps to Reproduce (for bugs)

Here is sample code:

function initPool() {
    let poolConfig = {
        min: 1,
        max: 3,
        log: true
    };

    let connectionConfig = {
        "userName": "username",
        "password": "pword",
        "server": "server",
        "options": {
            "database": "database",
            "requestTimeout": "300000",
        }
    };

    // create the pool
    pool = new ConnectionPool(poolConfig, connectionConfig);

    pool.on("error", function (err) {
        console.error(err);
    });
}
function execute2(query: string) {
    pool.acquire(function (err, connection) {
        if (err) {
            console.error(err);
            return;
        }
        let request = new Request(query, function (err, rowCount) {
            if (err) {
                console.error(err);
                return;
            }
            console.log("rowCount: " + rowCount);
            // release the connection back to the pool when finished
            connection.release();
        });
        request.on("row", function (columns) {
        });
        connection.execSql(request);
    });

}
initPool();

for (let i = 0; i < 6; i++) {
    execute2("select top 100000 * from USERS");
}

Console Log:
Tedious-Connection-Pool: filling pool with 1
Tedious-Connection-Pool: creating connection: 1
Tedious-Connection-Pool: filling pool with 1
Tedious-Connection-Pool: creating connection: 2
Tedious-Connection-Pool: filling pool with 1
Tedious-Connection-Pool: creating connection: 3
Tedious-Connection-Pool: connection connected: 1
Tedious-Connection-Pool: acquired connection 1
Tedious-Connection-Pool: connection connected: 2
Tedious-Connection-Pool: acquired connection 2
Tedious-Connection-Pool: connection connected: 3
Tedious-Connection-Pool: acquired connection 3
[Error: Acquire Timeout Exceeded]
[Error: Acquire Timeout Exceeded]
[Error: Acquire Timeout Exceeded]
rowCount: 100000
Tedious-Connection-Pool: connection reset: 2
rowCount: 100000
Tedious-Connection-Pool: connection reset: 1
rowCount: 100000
Tedious-Connection-Pool: connection reset: 3

Environment

• Node.js Version: v4.4.5
• Windows/Mac/Linux: Windows

[ben-page]

Looks like this is working as intended. The acquire timeout is how long acquire will wait for an available connection.

[ankurgupta4891]

Does that mean I should not run queries more than max connections in pool ? I think acquire time should start when there is a free connection available and a query request is trying to acquire a connection from the pool, but it seems if there are more queries than no of connections in pool, many queries (that couldn't get connections) will die because of acquire time out. What is ideal way to queue queries then?

[ankurgupta4891]

Also from this example, there shouldn't be any available connections for i=3,4,5. So those queries should have waited for connections to be released and available into the pool.

[ben-page]

Does that mean I should not run queries more than max connections in pool

In general, no. It does not mean that. But I would not allow slow queries to take all of the available connections. No other work can get done. This is the purpose of the timeout: to throw an error rather than just wait forever for a connection.

I'm not sure what you are trying to do. But you could create a dedicated connection pool for your slow queries and disable the acquire timeout. But be careful. If one of your queries creates a deadlock or has an infinite loop, you'll never know. The connection pool will just wait forever.

Also from this example, there shouldn't be any available connections for i=3,4,5. So those queries should have waited for connections to be released and available into the pool.

That's right. 0,1,2 get the connections immediately. When any of those 3 connections are released, it will be made available to 3,4,5.

[ankurgupta4891]

I get your point and understood acquire time out definition totally wrong. Thank you so much for clarification.

[ben-page]

No problem.