Retry on error

Published on: 23th June 2025

Overview

Working on a busy database is always challenging because every query or stored procedure that you are executing is competing in locking resources and has a higher chance of having a deadlock situation. It is difficult to have a solution that can prevent deadlock from happening.

Deadlock, for example, user A is saving a new transaction and user B is retrieving an account balance with the latest transaction while user A wants to update the account balance. This might end up in a deadlock situation.

In the Postgres database context

One of the ways to avoid deadlock from happening, is to set the lock timeout for the query. In Postgres, we issue the following command to set the lock to be timed out in two seconds.

SET LOCK lock_timeout 2000;

The next thing is to run the query again in case the lock has timed out.

In Node.js context

Let's write some JavaScript code on how to re-run the query.

1. You need a function that will put the current query back to sleep for a while before re-run. Here's a simple sleep() function to achieve that.

function sleep(i) {
    return new Promise((resolve, reject) => {
        setTimeout(() => {
            resolve();
        }, i);
    });
}

2. A function that will execute the query and here's a simplified version for illustration purposes. If you need to implement this idea, make sure you call the SET LOCAL LOCK_TIMEOUT=2000 and start a database transaction.

function execute_sql_stm(retry){
    return new Promise((resolve, reject) => {
        if (retry < 3) {
            reject('the table has been locked..');
        }
        else {
            console.log((new Date).toISOString(), 'the record has been saved..retry=', retry);
            resolve();
        }
    });
}

3. The retry loop (for illustration purpose). Basically, we implement a retry loop that will increment the sleep time if the resource cannot be locked. This means, every retry on executing the query, it will wait for a longer time.

(async () => {
    const max_retry = 5;
    let retry = 0;
    let sleep_ms = 1000;

    while (retry < max_retry) {
        try {
            console.log((new Date).toISOString(), 'inserting record..retry=', retry);

            await execute_sql_stm(retry);

            console.log((new Date).toISOString(), 'successfully saved the record');

            // completed without err, exit
            break;
        } catch (err) {
            console.log((new Date).toISOString(), err);

            retry++;

            // incremental sleep time.
            await sleep(sleep_ms);

            // every retry increase 1s.
            sleep_ms += 1000;
        }
    }

    console.log((new Date).toISOString(), 'done');

})().then(() => {;
    //no action
});

4. Here's the output of the above code which will re-run the query a few times.

2025-06-23T10:55:41.031Z inserting record..retry= 0
2025-06-23T10:55:41.035Z the table has been locked..
2025-06-23T10:55:42.049Z inserting record..retry= 1
2025-06-23T10:55:42.050Z the table has been locked..
2025-06-23T10:55:44.063Z inserting record..retry= 2
2025-06-23T10:55:44.064Z the table has been locked..
2025-06-23T10:55:47.080Z inserting record..retry= 3
2025-06-23T10:55:47.080Z the record has been saved..retry= 3
2025-06-23T10:55:47.080Z successfully saved the record
2025-06-23T10:55:47.080Z done

Conclusion

This is the basic idea for a retry loop which you may implement into any situation.

For illustration purposes, the sleeping time has a constant incremental value and you might still be caught in a deadlock situation. To use this retry loop in the production, the least thing that you have to do is to add a random value to the sleep_ms variable that will reduce the chance of getting a deadlock.

Back to #NODEJS blog

Back to #blog listing

Author

Lau Hon Wan, software developer.