Try one more experiment. To decrease the size of a transaction, uncomment the DO TRANSACTION statement and its matching END statement. Move the END statement up to just after the end of the DO block for the Order record. Now your procedure structure looks like this:
DO TRANSACTION ON ERROR UNDO, LEAVE:
DO:
/* Order update block */
END.
END. /* TRANSACTION block. This used to be at the end of the procedure. */
FOR EACH ttOline:
/* OrderLine update block */
END.
How does this affect your transactions? Now that you removed the FOR EACH block from the larger transaction, it becomes a true transaction block of its own at block level 1, as the new listing file shows:
166 1 FOR EACH ttOline WHERE ttOline.TransType = "":
167 1 /* Bring the updated version into the other buffer. */
This means that the two transaction blocks identified by the listing are now separate and distinct transactions:
File Name Line Blk. Type Tran Blk. Label
-------------------- ---- --------- ---- --------------------------------
...ter8\orderlogic.p 82 Procedure No Procedure fetchOrder
...ter8\orderlogic.p 111 For No
...ter8\orderlogic.p 124 Procedure No Procedure saveOrder
Buffers: bUpdateOrder
sports2000.Order
...ter8\orderlogic.p 144 Do Yes ...ter8\orderlogic.p146 Do No
...ter8\orderlogic.p151 Do No
...ter8\orderlogic.p156 Do No
...ter8\orderlogic.p 166 For Yes Buffers: sports2000.OrderLine
BUpdateOline
If the AVM encounters an error in the update of the Order, it leaves that block and continues on with the OrderLines. If the OrderLines update succeeds, then the newly modified OrderLines are in the database, but the failed Order update is not. Likewise, if the Order block succeeds but there is an error in the OrderLines block, then the Order update is in the database but the OrderLines update is not. You must decide when you put your procedures together how large your updates need to be to maintain your data integrity. In general, you should work to keep your transactions as small as possible so that you do not lock more records or lock records for longer periods of time than is absolutely necessary. But your transactions must be large enough so that related changes that must be committed together either all get into the database or are all rejected.
