Progress
DataServer
for ORACLE Guide
Record Locking
Progress applications rely on the ORACLE RDBMS to handle all record locking for the target ORACLE database. Progress locks do not apply to your ORACLE database. Table 2–8 compares Progress locks to their ORACLE equivalents.
Table 2–8: Progress and ORACLE Locks Progress 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)
In applications that use the DataServer, locks occur as a result of Progress statements that the DataServer translates into SQL statements and sends to the ORACLE RDBMS. Table 2–9 shows examples of Progress 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 2–9: ORACLE Locking Progress 4GL Statement SQL Statements Generated ORACLE
Locks FIND customer. . . . UPDATE customer.2 SELECT . . . FROM customer SELECT . . . FROM customer FOR UPDATE; Compares records UPDATE customer . . . ;2 None Share Update {Row} Exclusive3
- When Progress 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, Progress 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, Progress returns a run-time error. When the
SELECT . . . FOR UPDATE statement completes successfully, the UPDATE starts.
When the Progress UPDATE completes, Progress 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 Progress statement in the table is an example of a lock upgrade.- 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.
Progress and ORACLE release locks at different points in a transaction. When an application issues an UPDATE, Progress releases the lock once the new data is input. ORACLE does not release the lock until the application issues a COMMIT or ROLLBACK. Progress 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.
See the ORACLE documentation for details on ORACLE locking. See the Progress Programming Handbook for details on how Progress transactions and locks work.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |