Oracle databases allow many end users to examine and modify data concurrently. Locks are the mechanism Oracle uses to keep concurrent modifications from interfering with each other. Locking tables and rows helps to maintain the consistency and integrity of the data.
Oracle Forms normally handles locking automatically, but designers and end users can exercise control over when and how it locks resources.
When an end user updates a row, Oracle Forms locks the row to prevent other users from updating the same row until the first user commits or rolls back changes. Unless the block's Locking Mode property has the value Delayed, Oracle Forms automatically tries to obtain exclusive row locks in response to end user events and trigger and transaction processing.
An exclusive row lock:
Locks caused by end users When an end user changes the value of a base table item or explicitly requests a lock on a record, Oracle Forms tries to place an exclusive lock on the corresponding row in the database. The exclusive row lock statement has the following form:
SELECT <all base table items in block>
FROM <base table for the block>
WHERE ROWID = <the unique row ID for the row to be locked>
FOR UPDATE OF <all base table items in block>
The use of SELECT FOR UPDATE ensures that the data the end user changes while working in the form is identical to the corresponding data in the database.
Locks caused by triggers When a trigger makes a change to a table through a Data Manipulation Language (DML) command, the database tries to place an exclusive lock on the affected row. Oracle Forms does not manage locks caused by DML statements.
Oracle Forms tries to place an exclusive lock on the row corresponding to a fetched record when a trigger uses either of the following Built-ins:
In general, any action (Built-in invocation, PL/SLQ assignment, updateoperation) that modifies a value in a queried record causes Oracle Forms to request an exclusive lock.
There are two kinds of locking: implicit and explicit. They accomplish identical functions but happen at different times.
All Oracle Forms applications use implicit locking, which means that end users never have to lock rows explicitly. Oracle Forms tries to place an exclusive lock on a row at a time determined by the block's Locking Mode property. This property takes one of three values: Automatic, Immediate, or Delayed.
For Oracle databases, Automatic and Immediate mean the same thing: Oracle Forms tries to place an exclusive lock on a row as soon as the end user begins to enter a change into an item in the corresponding record. The Automatic value directs Oracle Forms to detect and adjust to the database's capabilities -- with no need for changes to properties or trigger code.
If the block's Locking Mode property has the value Delayed, Oracle Forms does not lock rows until it is time to commit changes. At that time Oracle Forms looks for differences between the current values in the database and those returned by its original query. If it finds differences, it warns the end user and does not commit the changes.
Note: When validating at the item level, Oracle Forms does not allow the change if it cannot acquire the lock. Otherwise Oracle Forms allows the change and tries to place an exclusive lock on the row during record validation.
An end user or a trigger can explicitly ask Oracle Forms to lock a specific row immediately. From the default menu the end user selects Record | Lock to do this. A trigger requests a lock explicitly by invoking the LOCK_RECORD Built-in. An ON-LOCK trigger must do this to obtain a lock explicitly, because its action replaces Oracle Forms's standard locking procedure.
Oracle Forms tries to obtain a lock a fixed number of times, then asks whether it should retry. The end user can choose whether to keep waiting for the lock or to terminate the locking procedure.
If a user has a table exclusively locked, other users can query records from the table, but cannot updateor delete those rows. This condition persists, without notice, until the resource becomes available. For this reason Oracle recommends minimizing the use of exclusive table locks.
The database releases locks when
Oracle Forms maintains locks when it rolls back to a savepoint, as might happen after an unsuccessful commit. This allows the end user to make necessary changes and commit again.
Note:
The ON-LOCK trigger fires whenever Oracle Forms would normally try to obtain a lock. The code of the On-Lock trigger completely replaces Oracle Forms's standard locking procedure.