FOR EACH table EXCLUSIVE-LOCK TRANSACTION

Posted by Grant Holman on 26-Nov-2015 08:48

FOR EACH table EXCLUSIVE-LOCK TRANSACTION:

/* update table */

END.

What purpose does the TRANSACTION clause serve here?  A FOR EACH has an implicit transaction on each iteration anyway.

All Replies

Posted by James Palmer on 26-Nov-2015 08:55

If you were to have the for each with a no-lock and do some work on a sub record inside the for each then the transaction would be scoped to the sub block, and a subsequent undo would only undo the last transaction. But if you specify the transaction keyword on the for each you are extending the scope to that block.

But in your case, yes the transaction keyword does seem a little redundant. All the same it could be there for readability.

As a side note, and I don't mean to patronise, transactions scoped to a whole for each should be avoided if at all possible. You run the risk of blowing the lock table, and leaving many records locked for other users whilst the transaction is ongoing. Do your for each no lock, and then find a named buffer exclusive lock within the for each to make your changes. That way your transaction scope is really tight and you won't run into these sorts of problems.

I realise this is a lot easier to say in theory than in practise!

Posted by Thomas Mercer-Hursh on 26-Nov-2015 09:26

One really good reason for that transaction keyword is to document your intent.  Moreover, should you make a mistake and do something which would cause the transaction to be scoped to a larger block, which is all too easy, then the compiler will let you know you have made a mistake when it encounters the transaction keyword inside a transaction.

Posted by George Potemkin on 26-Nov-2015 09:29

> A FOR EACH has an implicit transaction on each iteration anyway.

I'd always set an explicit transaction. Transaction scope is very important. That is why I don't want a misunderstanding between 4GL and me.

Posted by Grant Holman on 26-Nov-2015 10:05

Thanks to all.

Hi James,  thanks - that's a good point re sub-records.

This thread is closed