Control the size of a transaction

You have already learned which statements start a transaction automatically. To summarize, these are:

• FOR EACH blocks that directly update the database
• REPEAT blocks that directly update the database
• Procedure blocks or user-defined functions that directly update the database
• DO blocks with the ON ERROR or ON ENDKEY qualifiers (which you'll learn more about later) that contain statements that update the database

You can also control the size of a transaction by adding the TRANSACTION keyword to a DO, FOR EACH, or REPEAT block. This can force a transaction to be larger, but because of the statements that start a transaction automatically, you cannot use it to make a transaction smaller than ABL otherwise would make it.

Take another look at the update procedure saveOrder, in the sample logic procedure h-OrderLogic.p, to see how transaction blocks are affected by changes to the procedure.

As written, there is a DO TRANSACTION block around the whole update of both the Order and any modified OrderLines:

DO TRANSACTION ON ERROR UNDO, LEAVE:
  DO:
    /* Order update block */
  END.
  FOR EACH ttOline WHERE ttOline.TransType = "":
    /* OrderLine update block */
  END.
END. /* TRANSACTION block. */

Since DO blocks do not have the block transaction property, any changes made in the DO block (Order update block) scope to the containing transaction block, in this case the DO TRANSACTION block. Any errors that occur within the DO block cause the entire transaction to be backed out.

FOR EACH ttOline starts a new subtransaction for each iteration. If an error occurs within the FOR EACH block, only the change made for that iteration is backed out. Any changes for any previous iterations, as well as any changes made in the DO block, are committed to the database. To verify this, generate a listing file the same way you did previously:

When you run this COMPILE statement, your listing file tells you, among other things, where all the transactions begin and end. This is very valuable information. You should always use a listing file in any complex procedure to make sure that your record scope and transaction scope are as you intended.

DO TRANSACTION ON ERROR UNDO, LEAVE:
  DO:
    /* Order update block */
  END.
  FOR EACH OrderLine ON ERROR UNDO, THROW:
    /* OrderLine update block */
  END.
END. /* TRANSACTION block */

Back to the original code and taking a look at the listing file, you can see that the DO TRANSACTION block is a top-level block within its procedure, marked with a 1. The DO block inside it, where the Order is actually updated, is block level 2:

{} Line Blk
-- ---- ---
    137     
    138   1 DO TRANSACTION ON ERROR UNDO, LEAVE:
    139   1   FIND ttOrder WHERE ttOrder.TransType = "" NO-ERROR.
    140   1   IF AVAILABLE (ttOrder) THEN
    141         /* If this rec is there then the Order was updated on the client
    141   1 . */
    142   2     DO:
    143           FIND Order WHERE Order.OrderNum = ttOrder.OrderNum EXCLUSIVE-L

Further down, you can see that the FOR EACH block that operates on the OrderLines is also a nested block, at level 2 within the main DO TRANSACTION block:

    157   2     FOR EACH ttOline WHERE ttOline.TransType = "":
    158   2       /* Bring the updated version into the other buffer. */

Now if you look at the end of the file, you see a summary of all the blocks. Here is an excerpt from that part of the listing. It shows that the procedure blocks for the internal procedures fetchOrder and saveOrder are not transaction blocks:

     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

This is a good thing. You never want your transactions to default to the level of a procedure, because they are likely to be larger than you want them to be. This means that record locks are held longer than necessary and that more records might be involved in a transaction than you intended.

Next you see that the AVM identifies the first DO block at line 138 as a transaction block. This is because it has an explicit TRANSACTION qualifier on it. The nested DO block two lines later is not a transaction block because a DO block by itself does not mark a transaction.

The FOR EACH block at line 157 is also marked as a transaction block:

.\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      157 For         Yes                                   
    Buffers: sports2020.OrderLine
             bUpdateOline

What does this mean? Is this really a separate transaction? The answer is no, because the FOR EACH block is nested inside the larger DO TRANSACTION block. This code tells you that the FOR EACH block would be a transaction block (because this is the nature of FOR EACH blocks that perform updates). However, because it is nested inside a larger transaction, it becomes a subtransaction. The AVM can back out changes made in a subtransaction within a larger transaction when an error occurs, and you can also do this yourself, as described in the Subtransactions.