Transaction Problem with SELECT and followed by UPDATE

[alugunov]

Hello community,

I have a question regarding transaction handling in CAP, maybe you can help me with my question, I am beginner with Node.js.

There is a following code snippet for overwriting of generic handler for update.

... (entities are renamed to abstract)

const tx = cds.transaction(req);

const ParentEntity= req.data;

const Children = ParentEntity.children;

if ( Children ) {

Children.forEach(async child=> {

let childDB = await tx.run(SELECT.one('Children').where({ID: child.ID})); //get from DB one child

if(child.version > childDB.version ) {

await tx.run(UPDATE.entity('Children').with( child).where({ ID: child.ID }));

} } return ParentEntity;

Idea: get Children from ParentEntry of request and for every entry, select one from DB and check version. If version is greater in request, then update. It works fine with the first request.

But, with the next request it got into wait mode and after timeout it is failed. It happens locally and in SCP. From log in SCP I understand that previous transaction was not closed. But how to do it? I return from function a ParentEntity. Thanks in advance.

best regards,

Andrey

[Daniel7]

The issue is that the Children.forEach runs an async function without await-ing its outcome. In effect, you thereby spawn a background execution thread (continuation), that ends up in a race condition with the main thread, keeping on using the database connection even after the main thread finished and returned the connection to the pool.

You've two major options to fix this ...
(Using convenient methods for more concise code, but that's equivalent to your code above, e.g. tx.read(Foo,key) is the same than tx.run(SELECT.one.from(Foo).where({ID:key})) )

1. use a classic for-of loop instead:

if ( Children ) for (let child of Children) { 
   let stored = await tx.read('Children',child.ID)
   if (child.version > stored.version) {
       await tx.update('Children',child.ID).with(child)
   } 
}

2. Use Promise.all():

if ( Children ) await Promise.all (Children.map (async child => { 
   let stored = await tx.read('Children',child.ID)
   if (child.version > stored.version) {
       await tx.update('Children',child.ID).with(child)
   } 
}))

The latter is more optimised, as it would parallelise the execution of each loop. The first one serialises all calls in all loops (→ e.g. see Escaping the async/await hell)

P.S. If you prefer less awaits, you can also go for that variant (a bit more geeky maybe, and using projection to only read .version instead of SELECT *):

if ( Children ) await Promise.all (Children.map (c => 
  tx.read('Children', c.ID, c => c.version) .then (s => 
     (c.version > s.version) && tx.update('Children',c.ID).with(c)
  )
))

[Daniel7]

One more thing... with the introduction of `cds.context` you don't have to do that cds.tx(req) thing any longer. E.g. your whole code would reduce to:

const Children = req.data.children;
if ( Children ) await Promise.all (Children.map (c => 
   SELECT('version').from('Children',c.ID) .then (s => 
      (c.version > s.version) && UPDATE('Children',c.ID).with(c)
   )
))
return req.data

It's still tagged experimental, but at least you can give it a try. Chances are that we will remove the experimental tag soon.

Does not work with Node 10 though → you should anyways use latest Node 12 or 14.

[hschaefer123]

Hi Daniel,

thanks a lot for the tip. Spending some hourse of investigating, luckily i searched for this and found the reason and solution.

Also thanks to Andrej for asking this question.

Just did not know, that i had a question 😉

Regards
Holger

[alugunov]

Hi Daniel,

cool! Thanks a lot!

Variants from first answer are working!

I have Node veriosn 12, so variant from second answer might work as well, however I will try it later.

best regards,

Andrey