Transactions

The Oracle RDBMS is a transaction-based system. All of the work that you perform while using Oracle must occur within a transaction, whether that work is being done through Database Connectors for Oracle or another 4GL application. When you use the Database Connectors, a transaction is implicitly started for you by the database engine itself with the first file I/O operation performed on a file associated with Oracle. Because all operations with Database Connectors occur within a transaction, any record locked during processing remains locked until either a COMMIT WORK or ROLLBACK WORK is issued by Database Connectors. This action results in behavior similar to the LOCK ON MULTIPLE RECORDS clause in COBOL.

The benefits of this transaction management system are best illustrated by an example. A COBOL application that handles order entry might perform these steps to accept an order:

  • Write an invoice record.
  • Update a customer record.
  • Write a payroll record for sales commissions.
  • Update an inventory record.

This series of four file operations is a logical unit. If the program were interrupted and had completed only some of the four file operations, the files would be in an inconsistent state. For example, if the program terminated unexpectedly after it updated the customer record, but before it updated the inventory record, a subsequent run might access non-existent inventory.

The solution to this problem is to provide a method for the programmer to define a set of operations that should either all occur or all not occur. Then, if the program encounters an error or terminates, the files are left in a consistent state.

All file operations that are part of a transaction are logged. Once logged, they can be either committed or rolled back (undone) by the program.

If a program dies or the system fails, the log file can be used to reconstruct complete transactions, returning all files to a consistent state. Transaction logging thus offers these two facilities:

  • It provides the programmer with the ability to define transactions and the ability to commit them or undo them (usually in response to an error condition); this undo facility is called a rollback
  • It provides the ability to reconstruct files into a consistent state after a program dies or system failure occurs; this operation is called recovery