Controlling the size of a transaction

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.

The following example shows 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 OrderLine:
    /* 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 OrderLine 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.

If you want any error that occurs while updating an OrderLine record to undo the Order updates as well, then FOR EACH OrderLine should be rewritten as follows:

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 */

The error will be thrown into the containing DO TRANSACTION block and cause that block to undo, thus undoing the entire transaction.

Taking a look at this 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:

    144   1    DO TRANSACTION ON ERROR UNDO, LEAVE:
    145   1       FIND ttOrder WHERE ttOrder.TransType = "" NO-ERROR.
    146   1       IF AVAILABLE (tOrder) THEN
        /* If this rec is there then the Order was updated on the client. */
    147   1
    148   2      DO:

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:

164 2 FOR EACH ttOline WHERE ttOline.TransType = "":
165 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's 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
-------------------- ---- --------- ---- --------------------------------
...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

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 144 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 164 is also marked as a transaction block:

...ter8\orderlogic.p  144 Do        Yes
...ter8\orderlogic.p  146 Do        No
...ter8\orderlogic.p  151 Do        No
...ter8\orderlogic.p  156 Do        No
...ter8\orderlogic.p 164 For       Yes
    Buffers: sports2000.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 .