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.

if you are using `bluebird` promises, you could use `Promise.map` rather `Promise.all` — AZ_, Jan 29 '20 at 09:50
Does this answer your question? [How to execute promises sequentially, passing the parameters from an array?](https://stackoverflow.com/questions/43082934/how-to-execute-promises-sequentially-passing-the-parameters-from-an-array) — AZ_, Jan 29 '20 at 09:53
@AZ_ is there any other way to do it without using timeouts ? also is there a pure js implementation of Promise.map ? — Muljayan, Jan 29 '20 at 10:18
`Promise.map` is not natively included in Javascript but will be in `ES2020`, if you dont want to use external library like `Bluebird` or `Async` you can go with simple `for loop` — AZ_, Jan 29 '20 at 10:21
I initially tried a for loop but i cant use async await inside right ? I also tried for each without Promise.all but when i do that the output gets sent before the loop finishes... — Muljayan, Jan 29 '20 at 10:27

score 2 · Accepted Answer · answered Jan 29 '20 at 09:47

2

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
}

answered Jan 29 '20 at 09:47

Ashish Modi

7,529
2
20
35

I need to use await to fetch database values. How to make this approach accommodate it ? – Muljayan Jan 29 '20 at 10:15
just put them inside the for loop. `for..of` loop supports await so yuo can just add the logic the same way you did using `romise.all` – Ashish Modi Jan 29 '20 at 11:15

score 0 · Answer 2 · answered Jan 29 '20 at 09:49

0

Have a look at the definition for Promise.all()

It is typically used after having started multiple asynchronous tasks to run concurrently and having created promises for their results, so that one can wait for all the tasks being finished.

answered Jan 29 '20 at 09:49

Kevin

66
2
4

AZ_ · Answer 3 · 2020-01-29T10:44:43.550

0

if you don't want to use an external library like Bluebird or Async

you can go with simple for a loop as following

let delay = (t) => new Promise((resolve) => {
 setTimeout(() => {
  return resolve(new Date().getMilliseconds())
 }, t*1000);
});

let array = [1,1,1,1];

//using simple for loop
async function test() {
 let out = [];
 for (let i = 0; i < 4; i++) {
  const res = await delay(array[i]);
  out.push(res);
 }
 return out;
}

test().then(res => {
 console.log(res)
})

edited Jan 29 '20 at 10:44

answered Jan 29 '20 at 10:33

AZ_

3,094
1
9
19

Whats the purpose of pushing res inside out ? also could you elaborate as to where i should include my db queries in the above snippet ? – Muljayan Jan 29 '20 at 11:02
if you want to chain the promises and return the final response, if not you simply can use `Array.reduce` for the `waterfall` model. – AZ_ Jan 29 '20 at 12:27

Database transactions happen parallely instead of sequentially in loop

3 Answers3