Question Leave statement in a For each loop will release the lock

Hi,
Please clarify my doubt. In the below code When i = 3, Progress will release the lock and then leave OR, 3rd record will be in the Exclusive lock until we write the Release statement and then Leave?

Code:
DEFINE VARIABLE i AS INTEGER     NO-UNDO.
FOR EACH Customer where customer.custnu < 5 EXCLUSIVE-LOCK: /* 5 Records */
    i = i + 1.
    customer.name = customer.name + Sting(i).
    IF i = 3 THEN LEAVE.
    PAUSE 25.
END.

Thanks
-Philip-
 
Last edited by a moderator:

Rob Fitzpatrick

ProgressTalk.com Sponsor
Please enclose your code within CODE tags for readability.

Exclusive locks only happen within transactions and are not released until the transaction ends. To determine what happens with locks you need to understand your transaction scope and record scope. So start with a compile listing.

If the above were the entirety of the code, the transaction scope and Customer record scope would begin with the FOR statement and end at the END statement. After each END, the exclusive lock on the current Customer is released and the next iteration and transaction starts, obtaining an exclusive lock on the next customer record in the query.

In the third iteration, the IF expression is true and the LEAVE is executed. At this point you still have a record lock. You can confirm this yourself by stepping through the code in the debugger and watching the promon lock table screen. After the LEAVE is the final END. After the END, the exclusive lock is released.

One other note: your example is complicated by the fact that other code being run by the AVM. When run against a sports2000 database, it has a write trigger for Customer, so it isn't just the code above that is relevant to record locking. That code takes share locks and exclusive locks.
 
Thanks a lot. If I understood correctly In the third iteration after the Leave statement control will come to final END. I thought after the Leave (3rd iteration ) final END will be skipped. Thanks again.
 

TomBascom

Curmudgeon
You seem to be thinking that an END does something executable. It doesn't. It just marks the END of a block. This is syntactic sugar for the coder's benefit.

You also seem to think that RELEASE has something to do with record locks. It doesn't. From the documentation:
RELEASE

Verifies that a record complies with mandatory field and unique index definitions. It clears the record from the buffer and unites it to the database if it has been changed.

Notice how that says nothing about record locks? That's because RELEASE doesn't do anything about locks. Zero. Zilch. Nil. Nada. The use of a RELEASE statement is almost always a sign that the programmer is confused about fundamental issues of blocks, buffers, transactions, and record locks.

For an in-depth discussion of the interactions between blocks, buffers, transactions, and record locks you might like to look at:

 

Eric A

New Member
Code:
DEFINE VARIABLE i AS INTEGER NO-UNDO.
FOR EACH Customer where customer.custnu < 5 EXCLUSIVE-LOCK: /* 5 Records */
i = i + 1.
customer.name = customer.name + Sting(i).
IF i = 3 THEN LEAVE.
PAUSE 25.
END.

DISPLAY customer.name.
If you reference customer outside a weakly scoped block (for each is weakly scoped) the record is scoped to the larger block. The lock drops to a share-lock and an END-ERROR will undo the last change of the iterating block.
 

Eric A

New Member
p.s. In the below instance the release will clear the customer buffer and there will be a 'record not available' error with the DISPLAY statement, but the customer record remains locked.

Code:
DEFINE VARIABLE i AS INTEGER NO-UNDO.
FOR EACH Customer where customer.custnu < 5 EXCLUSIVE-LOCK: /* 5 Records */
i = i + 1.
customer.name = customer.name + Sting(i).
IF i = 3 THEN LEAVE.
PAUSE 25.
END.
RELEASE customer.
DISPLAY customer.name.
 

Eric A

New Member
My apologies - since there are no updates to the customer record outside of the for each loop the lock is released.

Eric
 

TomBascom

Curmudgeon
It is easier to see what is happening if you run this version of the code:
Code:
DEFINE VARIABLE i AS INTEGER NO-UNDO.

FOR EACH Customer where customer.custnu < 5 EXCLUSIVE-LOCK: /* 5 Records */

  i = i + 1.
  customer.name = customer.name + String(i).

  message "record" i.
  pause.

  IF i = 3 THEN LEAVE.

END.

message "out of loop".
pause.

RELEASE customer.

message "after release".
pause.

And then run PROMON in a second window watching the status changes in the lock table (menu 4.1) with each step.
 
Top