"Progress implicitly starts a transaction whenever you update or exclusive-lock a database record within an outermost loop (e.g. REPEAT, FOR EACH, etc.)
"I would put that differently. A TRANSACTION is SCOPED to the OUTER-MOST BLOCK in which there is a statement that updates the database or a statement that EXCLUSIVE-LOCKs a record.
Some BLOCKS provide automatic TRANSACTION SERVICES (FOR EACH, REPEAT), others do not (DO, DO WHILE TRUE). The PROCEDURE BLOCK also provides transaction services.
That is why you get a compiler error when you use the TRANSACTION keyword with the FOR EACH - it's redundant. The TRANSACTION keyword is useful for adding transaction services to DO blocks.
All updates performed in a block that provides transaction services are undo-able, thus you will use system resources to maintain that undo-ability. This becomes magnified with the presence of iterative blocks. So it is really a matter of planning your transaction structure accordingly. It works quite well for relational databases.
Consider the following trivial procedure (lets say there are 50 order-lines):
FIND FIRST order.
FOR EACH order-line OF order:
ASSIGN item-date = TODAY.
END.
The scope of the transaction? 1 record.
Lets add 1 line of code to RAISE THE SCOPE of the the TRANSACTION:
FIND FIRST order.
ASSIGN order.order-date = TODAY.
FOR EACH order-line OF order:
ASSIGN item-date = TODAY.
END.
The scope? 51 records. This is normally what you would want.
Notice that the following makes no difference:
FIND FIRST order.
FOR EACH order-line OF order:
ASSIGN item-date = TODAY.
END.
ASSIGN order.order-date = TODAY.
The scope is still 51 records, because the ASSIGN statement, regardless of the fact that it comes AFTER the FOR EACH, is still at the PROCEDURE level
(outer-most block). With blocks, It is import to not think LINEARLY.
If you to create 2 single record scopes do as follows:
DO TRANSACTION:
FIND FIRST order.
ASSIGN order.order-date = TODAY.
END.
FOR EACH order-line OF order:
ASSIGN item-date = TODAY.
END.
THIS LOWERS the SCOPE of the TRANSACTION. But, in the relational context, you may not want this, because in the event of an undo, you could end up with the order date different fro the order line dates.
As is the case with any language, the basic constructs, which seem elegant and straight-forward at the elemental level, can blossom in to ones that are quite complex and hard to de-bug. But, compared to any SQL-based system, I'll take the Progress 4GL everytime.
But, in any event, the SEQUENCE is the answer to the problem that started this thread.