Database transactions

Database transactions define a set of SQL instructions to be executed as a whole, or rolled back as a whole.

The BDL instructions to define a transaction block are:
  • BEGIN WORK
  • COMMIT WORK
  • ROLLBACK WORK

Additional BDL instructions related to transaction management is available, such as SET LOCK MODE ..., SET ISOLATION ..., SAVEPOINT, etc. For a complete description, see Database transactions

When performing a transaction instruction, the database drivers execute the corresponding native SQL instruction (or database client API call) to begin, commit or rollback a transaction.

In this example a basic transaction block executes inside a TRY/CATCH block to rollback the transaction in case of SQL error:

MAIN
   CONNECT TO ...
   TRY
     BEGIN WORK
     UPDATE tab1 SET col1 = 'aaa' WHERE pkey = 123
     UPDATE tab2 SET col2 = 'bbb' WHERE pkey = 456
     ...
     COMMIT WORK
   CATCH
     ROLLBACK WORK
   END TRY
END MAIN