Record Locking Issues in Transactions

Applications that perform work in small operation-based logical units, benefit greatly from Oracle's transaction management systems. Other applications may encounter difficulty with record locking when operating against a system (Oracle) 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. As no COMMIT or ROLLBACK has been issued, all of the records remain locked until the application ends, thus ending 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.

Note that the first application is not locked out. A process can read its own locked records.