Managing connection pool issue when updating several rows in oracle db with node.js and knex.js

Question

I'm using a Node.js application which inserts and updates data on a Oracle database. There is a situation where I need to update a column value of a table for number of rows within a single API call. However, It is observed to be time consuming task and sometimes connection max pool size is reached, giving the below error. Increasing pool size was not helpful either.

Knex: Timeout acquiring a connection. The pool is probably full. Are you missing a .transacting(trx) call?

To mitigate this issue, a transaction is created for all updates and used Bluebird.js to perform update calls in a concurrent manner as given below.

 knex.transaction((trx) => {
          ...........
          ...........
          Bluebird.map(allObjects, (item) => { 
            .......
            trx('Table_Name')
           .where('ID', item.ID)
           .update({abc: JSON.stringify(updatedAbc)}) 
           ........
           },
           { concurrency: 10 }).then(()=>{ trx.commit });;

However, This also gives above mentioned issue. When calling endpoint consecutively, Above pool size exceeding issue arises. Is there a better way to solve this problem. Help is really appreciated. Thank you.

Answer 1

You are at least missing some return statements. Also there is no reason to try to update multiple lines concurrently in transaction, because in transaction all queries are executed sequentially and driver just queues them if one tries to do something like that.

 // more or less fixed version of the code above:
 knex.transaction((trx) => {
   return Bluebird.map(d, (item) => { 
     return trx('Table_Name')
           .where('ID', item.ID)
           .update({abc: JSON.stringify(updatedAbc)});
    }, { concurrency: 10 }).then(() => console.log("transaction completed with success")).catch(err => console.log("Transaction was rolled back", err)));

Better way to do it:

// throws an error if transaction did fail and was rolled back
await knex.transaction(async (trx) => {
  for (let item of allObjects) {
    await trx('Table_Name').where('ID', item.ID)
      .update({abc: JSON.stringify(updatedAbc)});
  }
});