Database transactions happen parallely instead of sequentially in loop

Question

I have an endpoint which loops through an array and updates the database as follows.

 app.post('/create', Authenticate, async (req, res) => {
  const {
    products,
  } = req.body;
  const trxProvider = knex.transactionProvider();
  const trx = await trxProvider();

  try {
    const formattedProduct = await Promise.all(products.map(async (product) => {

     // Get Current value
      const warehouseProducts = await trx('warehouse_products')
        .select('available_qty as qty')
        .where('product_code', product.product.code)
        .first();

      const finalQty = warehouseProducts.qty - product.orderQty;

     // Update database
      await trx('warehouse_products')
        .update({ available_qty: finalQty })
        .where('product_code', product.product.code);
    }));
    await trx('ordered_products')
      .insert(formattedProduct);
    trx.commit();
    console.log('Transaction successful');
    return send(res, 201, { success: true });
  } catch (err) {
    console.log(err);
    trx.rollback();
    const errors = {};
    errors.message = 'something went wrong';
    return send(res, 500, errors);
  }
});

The issue arises when i try to update the same row of the warehouse_products table within the loop. In the loop initially the qty value is taken from the warehouse_products table for a particular product then an arithmetic operation is done and the qty value is updated.

Ideally if both iterations access the same row, the second iteration's initial qty value should be what the first iteration updated. However the issue is that the second iteration too starts with the initial value of the first iteration. Its almost as if both iterations are happening parallel to each other instead of occurring sequentially.

Answer 1

Since you are using Promise.all it is supposed to happen in paralle. For sequential processing change this code

await Promise.all(products.map(async (product) => {
// logic here
});

to

for(const product of products) {
  // logic here
}