Transactions ensure that the data in your database maintains integrity. In this topic, you learn about implicit and explicit transactions, and how the UNDO statement undoes a transaction.

Implicit transactions

During a transaction, information on all database activity occurring during that transaction is written to a before-image (or BI) file that is associated with the database. The BI file is located on the server with the other database files. The information written to the before-image file is coordinated with the timing of the data written to the actual database files. That way, if an error occurs during the transaction, the AVM automatically uses the before-image file to restore the database to the condition it was in before the transaction started.

The statements which start an implicit transaction are:
  • FOR blocks that directly update the database
  • REPEAT blocks that directly update the database
  • Procedure blocks that directly update the database
  • DO blocks with the ON ERROR phrase that contain statements that update the database

The following example code starts a transaction for each iteration, resulting in the updates for each customer record as a separate transaction.

FOR EACH Customer:
  /* Customer update block */
END.

If your application has multiple nested blocks, each of which would be a transaction block if it stood on its own, then the outermost block is the transaction and all nested transaction blocks within it become subtransactions. All database activity occurring during a subtransaction is written to a local-before-image (or LBI) file.

A subtransaction block can be:
  • A procedure block that is run from a transaction block in another procedure
  • Each iteration of a FOR EACH block nested within a transaction block
  • Each iteration of a REPEAT block nested within a transaction block
  • Each iteration of a DO TRANSACTION or DO ON ERROR inside a transaction block

If an application error occurs during a subtransaction, all the work done since the beginning of the subtransaction is undone. You can nest subtransactions within other subtransactions. You can use the UNDO statement to programmatically undo a transaction or subtransaction.

The following example code starts a transaction at the Customer level and Order changes are done in a subtransaction:

FOR EACH Customer:  // Starts a transaction
  /* Customer update block */
  FOR EACH Order WHERE Order.CustNum = Customer.CustNum:  // Starts subtransaction
    /* Order update block */
  END.
END.

Explicit transactions

You can also start a transaction by adding the TRANSACTION keyword to a DO, FOR, or REPEAT block. If your code starts a transaction in one procedure and then calls another procedure, whether internal or external, the entire subprocedure is contained within the transaction that was started before it was called. If a subprocedure starts a transaction, then it must end within that subprocedure as well, because the beginning and end of the transaction are always the beginning and end of a particular block of code.

The following example code puts a transaction block around the whole update of both the Customer and any modified Orders:

DO TRANSACTION:
  DO:
    /* Customer update block */
  END.
  FOR EACH Order WHERE Order.CustNum = Customer.CustNum:
    /* Order update block */
  END.
END.  // TRANSACTION block

UNDO statement

A transaction is automatically undone when an unhandled error occurs that kicks you out of the transaction block. Your application logic can also undo a transaction when you detect a violation within your business logic. The UNDO statement lets you control when to cancel the effects of a transaction on your own. It also lets you define just how much of your procedure logic to undo.

You can use the UNDO keyword as its own statement. In this case, the AVM undoes the innermost containing block with the error property, which can be:
  • A FOR block
  • A REPEAT block
  • A procedure block
  • A DO block with the TRANSACTION keyword or ON ERROR phrase

The basic syntax for the UNDO statement is:

UNDO [ LEAVE | NEXT | RETRY | THROW error-or-stop-object-expression 
     | RETURN [  ERROR | NO-APPLY ] [ return-value ] ] ]

In the following example code, a transaction is started at the Customer level and Order changes are done in a subtransaction:

FOR EACH Customer:  // Starts a transaction
  /* Customer update block */
  FOR EACH Order WHERE Order.CustNum = Customer.CustNum:  // Starts a subtransaction
    /* Order update block */
    /* If validation fails, exit */
    UNDO, LEAVE.     // This undoes the subtransaction
  END.
END.
You can also specify UNDO as an option on a DO, FOR, or REPEAT block. In the following example code, upon encountering an error, the current block is undone and execution resumes in the code following the block.
DO TRANSACTION ON ERROR UNDO, LEAVE:
  DO:
    /* Customer update block */
  END.
  FOR EACH Order WHERE Order.CustNum = Customer.CustNum:
    /* Order update block */
  END.
END.  // TRANSACTION block

You can also specify UNDO, LEAVE label, if you want to leave a specified transaction block identified by label, upon encountering the condition. See the LEAVE statement for more detail on using a label with LEAVE.

See also

Managing Transactions

Understanding the UNDO concept