Transactions and promises in Node.js

Prerequisites:

We have a database which need to be consistently updated with some batch of data and Node.JS application which executes queries in database.

These actions executes by following code:

try {
    await connection.transaction();
    const promises = [];
    for (const elem of data) {
        promises.push(connection.query('updateElem', elem));
    }
    await Promise.all(promises) 
    await connection.commit();
} catch(error) {
    await connection.rollback();
}

Problem:

Using this code we expect the following behavior: when one query fails, transaction rolls back and data should stay consistent. But this code will lead you to one strange bug: when one query fails, other queries roll back partially (e.g. 6 of 10 queries roll back, but other ones modifiy data so database becomes inconsistent)

Reason:

To understand how we got this problem we need to read MDN docs about Promise.all(). It says to us:

It rejects with the reason of the first promise that rejects

And no words about stopping of other promises execution.

So, this bug can be illustrated by the following code:

const p1 = new Promise((resolve, reject) => {
    setTimeout(() => resolve(console.log('1')), 1000);
});
const p2 = new Promise((resolve, reject) => {
    setTimeout(() => resolve(console.log('2')), 2000, 'two');
});
const p3 = new Promise((resolve, reject) => {
    setTimeout(() => resolve(console.log('3')), 3000, 'three');
});
const p4 = new Promise((resolve, reject) => {
    reject('reject');
});

Promise.all([p1, p2, p3, p4]).then(values => {
    console.log(values);
}).catch(reason => {
    console.log(reason);
});

And result will be:

reject
1
2
3

Something like that happened when we used Promise.all() inside transaction:

1. Transaction started;
2. Queries queued to execution;
3. One query failed;
4. Transaction rolled back;
5. Some slow queries executed after transaction rollback.

Solutions:

1. Thanks to the Chad Elliott for it: As of NodeJS@12.10.0 you can use Promise.allSettled(). This will not only wait for all promises to complete, but will give you an array with the result of each promise. Just iterate through the results and verify that none have a status of ‘rejected’.
2. Not the best solution but it works: do not use Promise.all() inside transctions, use sequential query execution.

P.S.

If you know better solution of this problem feel free to contact me with comments to this post or by email alkor@alkor.pw