Kbase P182215: How to minimize time records are locked when updating records within a loop
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/21/2011 |
|
Status: Verified
GOAL:
How to minimize time records are locked when updating records within a loop
GOAL:
How to avoid locking records for a long time when updating records in a FOR EACH loop
GOAL:
Example code using an alternate buffer to update selected records in a FOR EACH block
FACT(s) (Environment):
All Supported Operating Systems
Progress/OpenEdge Product Family
FIX:
It is often necessary to loop through a large number of records and select only a few for update or deletion based on some criteria. In this case it is not desirable lock every record that is read, because only a few will need to be updated. The following example code shows how to read a large number of records using FOR EACH ... NO-LOCK, and reread only the records that should be updated in an alternate buffer with an EXCLUSIVE-LOCK. Enclose the FIND and update statements in a block that both references the alternate buffer (so references will be strongly scoped to this block) and starts a transaction (so transaction scope will not default to the outer FOR EACH block and delay the write of the record to the database). The example works with the Sports2000 sample database. The code incorporates alert box messages to stop execution of the code so the reader can observe how locks are created and released using the PROMON utility.
DEFINE BUFFER bfCustomer2 FOR Customer.
DEFINE VARIABLE vBretagne AS LOGICAL NO-UNDO.
/* Put as much of the selection criteria as possible into a WHERE clause on the
FOR EACH to minimize the number of records retrieved from the database.
In this example, the only selection criteria is in the IF statement.
*/
FOR EACH Customer NO-LOCK:
vBretagne = FALSE.
IF Customer.State = 'Bretagne' THEN
DO FOR bfCustomer2 TRANSACTION:
vBretagne = TRUE.
MESSAGE 'Check promon - no lock before find on alternate buffer'
VIEW-AS ALERT-BOX.
FIND bfCustomer2 EXCLUSIVE-LOCK
WHERE ROWID(bfCustomer2) = ROWID(Customer).
/* Note that RECID is used instead of ROWID for the
message in order to match the promon display. Normally
ROWID is preferred.
*/
MESSAGE 'Check promon to see lock:'
RECID(bfCustomer2)
bfCustomer2.State
bfCustomer2.CustNum
bfCustomer2.NAME
VIEW-AS ALERT-BOX.
/* Statements to update the record go here. */
END.
IF vBretagne THEN
MESSAGE 'Check promon - lock is released'
VIEW-AS ALERT-BOX.
END.