Example of BEGIN WORK

When consecutive SQL statements perform what is logically a single unit of work, you can define a transaction by grouping them between the BEGIN WORK and COMMIT WORK statements. If the business requirements dictate that either all of the statements be performed successfully, or else that none of them be performed, you can enclose the statements of the transaction between BEGIN WORK to start a transaction and COMMIT WORK to complete the transaction successfully (or ROLLBACK WORK, to cancel the transaction, if the program detects an error).

In the following program fragment, the transaction locks the stock table (LOCK TABLE), updates rows in the stock table (UPDATE), deletes rows from the stock table (DELETE), and inserts a row into the manufact table (INSERT). In this example (with no error handling), the database server executes each of these SQL statements in sequence:

BEGIN WORK;
   LOCK TABLE stock;
   UPDATE stock SET unit_price = unit_price * 1.10
      WHERE manu_code = 'KAR';
   DELETE FROM stock WHERE description = 'baseball bat';
   INSERT INTO manufact (manu_code, manu_name, lead_time) 
      VALUES ('LYM', 'LYMAN', 14);
COMMIT WORK;

Each statement itself is atomic; it either completes successfully or else the database is unchanged afterwards. If any of these statements fail, the other statements will still be executed and the net result is as if the failed statement was never attempted. When the COMMIT WORK statement is executed, the successful changes are made permanent.

Typically, however, transactions are defined with error handling, so that the database server must perform a sequence of operations either completely or not at all. In this case, when you include all of the operations within a single transaction, the database server guarantees that all the statements are completely and perfectly committed to disk, or else the database can be restored to the same state that it was in before the transaction began.

By adding appropriate error handling (for example, by setting the DBACCNOIGN environment variable in DB-Access, or by adding EXEC SQL WHENEVER ERROR STOP in ESQL/C), the transaction can be implicitly rolled back because the program stops on an error without executing COMMIT WORK. More careful conditional coding in a programming language such as ESQL/C allows the programmer to explicitly roll back the transaction while continuing the larger program.

Error-handling and business logic in applications and UDRs can also delimit one or more portions of a transaction by including SAVEPOINT and ROLLBACK TO SAVEPOINT statements. If the ROLLBACK TO SAVEPOINT statement is issued after an error is encountered, or after the results of part of the transaction indicate a conflict with a business rule or with some other criterion, only the changes that were made to the database between the ROLLBACK statement and its specified or default savepoint are cancelled, rather than the entire transaction. The current transaction continues at the statement that follows the ROLLBACK statement, with any uncommitted changes to the data or to the schema of the database from operations that preceded the savepoint remain pending, until the entire transaction is either committed or rolled back. Any locks held by statements that were rolled back are retained until the complete transaction ends.