Kbase 21485: 4GL -- FOR EACH and RECID(), Locked Records Behavior
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/8/2006 |
|
Status: Unverified
GOAL:
How Progress will manage the record locking mechanism in a FOR EACH query with a RECID() in the WHERE clause.
GOAL:
FOR EACH and RECID(), Locked Records Behavior
FACT(s) (Environment):
Progress 9.1x
OpenEdge 10.x
CAUSE:
The following three cases let you compare the behavior of the FOR EACH with and without RECID().
First Case
DO TRANSACTION:
FOR EACH CUSTOMER WHERE RECID(CUSTOMER) = 0 OR CUSTOMER.phone > "ZZZ" EXCLUSIVE-LOCK:
DISPLAY cust-num.
END.
PAUSE.
END.
(COMPILE XREF Result)
2 SEARCH sports.Customer Cust-Num WHOLE-INDEX.
In this case the Full Table is locked. Thus, all the records are locked.
Second Case (RECID() clause commented out)
DO TRANSACTION:
FOR EACH CUSTOMER WHERE /* RECID(CUSTOMER) = 0 OR */ CUSTOMER.phone > "ZZZ" EXCLUSIVE-LOCK:
DISPLAY cust-num.
END.
PAUSE.
END.
(COMPILE XREF Result)
2 SEARCH sports.Customer Cust-Num WHOLE-INDEX.
No table lock and no record locked.
Third Case (OR clause commented out)
DO TRANSACTION:
FOR EACH CUSTOMER WHERE RECID(CUSTOMER) = 0 /* OR CUSTOMER.phone > "ZZZ" */ EXCLUSIVE-LOCK:
DISPLAY cust-num.
END.
PAUSE.
END.
(COMPILE XREF Result)
No SEARCH in XREF.
No table lock no record locked.
FIX:
Use the RECID() qualifier with some caution.
If the WHERE clause consists of a clear, RECID() = some-global-value, (and perhaps with other things) Progress will get the records by the ROWID.
In the Third case, where there is nothing but the RECID() condition, it runs fast, retrieving only 1 record.
In the Second case, where there is no RECID(), it is fast because it does all the selection on the Server side, thus only what passes is locked and sent to the client.
In the First case, the combination of OR with RECID() causes the whole thing to become client selection, since the Server does not handle RECID() as selection, only as a single FIND directive. Thus, all records are passed back to the client as locked so as to verify the RECID().
FIX:
References to Written Documentation:
Progress Language Reference Manual -- ROWID(), RECID() functions, COMPILE statement