Oracle Concepts Overview

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. This action results in behavior similar to the LOCK ON MULTIPLE RECORDS clause in COBOL.

The benefits of a 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

Record-locking Issues in Transactions

Applications that are written for transaction management systems, or that perform work in small operation-based logical units, benefit greatly from Oracle's transaction management systems. Applications that are not written for transaction management encounter difficulty with record locking when operating against a system that enforces transaction management.

The difficulty can occur with an application that is performing more than one logical task at a time. Any operation that modifies or reads data in an I/O mode without the WITH NO LOCK phrase causes a lock to be placed in the database system. As a result, the application may have many more record locks present than would be expected by the normal rule of COBOL file locking. The application would act similarly to when the LOCKS ON MULTIPLE RECORDS clause in COBOL is used. This can best be illustrated by an example:

1. The user is entering a customer's order.
2. As each line item is entered into the order, the inventory file is modified to reflect that items have been removed from the stock on hand.
3. The user must switch to a different part of the application to perform a different task, perhaps as a result of a phone call from a new customer.
4. All of the records that were locked, or modified, by the application before the switch remain locked because the first order is not complete. No COMMIT or ROLLBACK has been issued to complete the transaction. All of the records locked by the transaction remain locked until the application ends the transaction.
5. Because one order is open and not yet committed, other applications may be locked out of certain order items if they are still locked by the processing of the first order. The second order entry may be held up until the first order is completed.
6. Note that the first application is not locked out. A process can read its own locked records.