Make a transaction smaller

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:

    159   1     FOR EACH ttOline WHERE ttOline.TransType = "":
    160   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            
-------------------- ---- ----------- ---- --------------------------------
.\h-OrderLogic.p       84 Procedure   No   Procedure fetchOrder             
.\h-OrderLogic.p      107 For         No                                    
.\h-OrderLogic.p      123 Procedure   No   Procedure saveOrder              
    Buffers: bUpdateOrder
             sports2020.Order

.\h-OrderLogic.p      138 Do          Yes                                   
.\h-OrderLogic.p      142 Do          No                                    
.\h-OrderLogic.p      146 Do          No                                    
.\h-OrderLogic.p      150 Do          No                                    
.\h-OrderLogic.p      159 For         Yes                                   
    Buffers: sports2020.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.