Try OpenEdge Now
skip to main content
DataServer for Oracle
Initial Programming Considerations : Record locking
 

Record locking

OpenEdge applications rely on the Oracle RDBMS to handle all record locking for the target Oracle database. OpenEdge locks do not apply to your Oracle database. The following table compares OpenEdge locks to their Oracle equivalents.
Table 16. ABL and Oracle locks
OpenEdge lock
Oracle lock
Transaction processing option lock1
NO-LOCK
None
None
SHARE-LOCK
None
None
EXCLUSIVE-LOCK
SHARE UPDATE(row-level)
SHARE UPDATE(row-level)
EXCLUSIVE-LOCK . . . UPDATE
SHARE UPDATE (row-level) . . EXCLUSIVE (table-level)
SHARE UPDATE(row-level) ...ROW EXCLUSIVE (row-level)
1 The Oracle Transaction Processing option provides a low-level locking manager.

In applications that use the DataServer, locks occur as a result of ABL statements that the DataServer translates into SQL statements and sends to the Oracle RDBMS.
The following table shows examples of ABL statements, the SQL statements they generate, and the resulting Oracle locks in an Oracle database. The examples assume the default is SHARE-LOCK. The notes that follow the table help explain the locking behavior.
Table 17. Oracle locking
ABL statement
SQL statements generated
Oracle locks
FIND customer.
SELECT . . . FROM customer;
None
FIND customer EXCLUSIVE-LOCK.
SELECT . . . FROM customer
FOR UPDATE;
Share Update

FIND customer.
.
.
.
UPDATE customer.1

SELECT . . . FROM customer
SELECT . . . FROM customer
FOR UPDATE;
Compares records
UPDATE customer . . . ;2

None
Share Update
{Row} Exclusive3
1 When ABL encounters an UPDATE statement that involves an Oracle database, it uses a FIND . . . EXCLUSIVE-LOCK statement to check whether the record referenced by the UPDATE statement is already locked.If the record in the buffer is locked, ABL starts the UPDATE. If not, it immediately issues an SQL SELECT . . . FOR UPDATE statement to determine whether the value in the buffer is the same as the value in the database. This statement also locks the record. If the values are different, ABL returns a run-time error. When the SELECT . . . FOR UPDATE statement completes successfully, the UPDATE starts.When ABL UPDATE completes, ABL generates an SQL UPDATE statement that performs the actual change to the Oracle database. For example, if you have to retrieve a record for a subsequent update, use the EXCLUSIVE-LOCK modifier with the FIND statement to avoid the second SELECT . . . FOR UPDATE operation.NOTE: The last ABL statement in the table is an example of a lock upgrade.

2 When ABL encounters an UPDATE statement that involves an Oracle database, it uses a FIND . . . EXCLUSIVE-LOCK statement to check whether the record referenced by the UPDATE statement is already locked.If the record in the buffer is locked, ABL starts the UPDATE. If not, it immediately issues an SQL SELECT . . . FOR UPDATE statement to determine whether the value in the buffer is the same as the value in the database. This statement also locks the record. If the values are different, ABL returns a run-time error. When the SELECT . . . FOR UPDATE statement completes successfully, the UPDATE starts.When ABL UPDATE completes, ABL generates an SQL UPDATE statement that performs the actual change to the Oracle database. For example, if you have to retrieve a record for a subsequent update, use the EXCLUSIVE-LOCK modifier with the FIND statement to avoid the second SELECT . . . FOR UPDATE operation.NOTE: The last ABL statement in the table is an example of a lock upgrade.

3 If you use Oracle with the Transaction Processing option, the result is a Row Exclusive Lock. Without Transaction Processing, the result is a table-level Exclusive Lock.

ABL and Oracle release locks at different points in a transaction. When an application issues an UPDATE, ABL releases the lock once the new data is input. Oracle does not release the lock until the application issues a COMMIT or ROLLBACK. ABL allows you to hold a lock outside of a transaction or beyond a transaction's scope, but Oracle always releases all locks at the end of a transaction.
The Oracle database supports "deferred constraints" which are enforced at a transaction boundary. If a deferred constraint violation occurs at a transaction boundary, the transaction is rolled back and the application is terminated.
See the Oracle documentation for details on Oracle locking. See OpenEdge Getting Started: ABL Essentials for details on how ABL transactions and locks work.
* Monitoring locks