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)
  1. The ORACLE Transaction Processing Option provides a row-level locking manager.

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. 
SELECT . . . FROM 
customer; 
None 
FIND customer 
EXCLUSIVE-LOCK. 
SELECT . . . FROM 
customer
  FOR UPDATE;  
Share Update 
FIND customer.
  .
  .
  .



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

Compares records
UPDATE customer . . . ;2 
None

Share Update



{Row} Exclusive3 
  1. 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.
  2. 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