FOR EACH with EXCLUSIVE-LOCK means TABLE locking???

[yoachan]

Wanted to ask something.
Using an EXCLUSIVE-LOCK on a FIND FIRST statement caused the current record to be locked so that other users can't change the record's contents (CMIIW). Only on a single record.

But what about if I'm using EXCLUSIVE-LOCK on a FOR EACH statement? My senior said that using EXCLUSIVE-LOCK on a FOR EACH means I'm locking whole table. So in a case a user (I call him user A)updating a table using FOR EACH with EXCLUSIVE-LOCK all other user won't be able to update this table, even on records that user A don't need or don't visit on his loop.
In return, he told me to use repeated FIND FIRST with EXCLUSIVE LOCK.

Is this statement that my senior said was correct? Can anyone please explain about this? For if this was right perhaps I have to do a bunch of changes on my codes.
What about RELEASE syntax, what is it really for?
If my senior was right, can't I just use RELEASE on every looping I made on my FOR EACH?
PS. I'm working with WebSpeed on OpenEdge 10.1B.


Thanks in advanced

Regards

YoChan

 

[jongpau]

Hi,

Progress only does record level locking and does not know the concept of locking a table. The only way you could achieve such a thing is by locking every individual record in the table -- definitely not something I would consider advisable by the way.

Release is a bit of a nasty one because it does not exactly do what the name implies. Progress never actually releases a record lock until it has reached the end of the transaction block you are in. It has to do this because in the event of something going wrong it has to be able to undo the entire transaction. Releasing locks mid-way through a transaction would mean that someone else could update a record that has just been changed by your transaction that has not finished yet --- as you can imagine that is very much a no no. It may be a good idea to read the online help and documentation relating to transactions scoping and record locking.

It would be best to try and keep your transactions as small as possible. So in the case of your for each, would it be possible within the flow of your program to limit the size of the transaction?

 

[DevTeam]

Hi,

What about using a query, with "EXCLUSIVE-LOCK" on your GET FIRST / GET NEXT ? This may work the same way as your FOR EACH, but with the possibility of making your transaction smaller.

In return, he told me to use repeated FIND FIRST with EXCLUSIVE LOCK.

Won't it return you always the same record ?

 

[KnutHandsome]

It may be a good idea to read the online help and documentation relating to transactions scoping and record locking.

Yoachan: It may also be a good idea to suggest (diplomatically) your 'senior' reads it as well, as his mentoring appears to be harmful in this case.

You could have proved your (correct) suspicion for yourself by having two sessions (in effect two users) attempt to lock two different records in the table at the same time (both using FOR EACHes if you like), and having your senior comment on the discrepancy between his advice, and the results in front of your eyes (and confirmed in the documentation).

Diplomacy is key though; you may have misunderstood what he said; and some senior types don't like to learn from their juniors (the best ones do, within reason).

 

[AndrewSmith]

YoChan,

If your code is using well-scoped buffers, your FOR EACH...EXCLUSIVE will lock one record at a time. If your code allows the record scope of the buffer in the FOR EACH to expand, then you have a problem because the transaction will be active before the FOR EACH begins, resulting in each iteration of the FOR block being involved in one big transaction.

Now, your senior may have tried to say this, but used poor terminology. I suppose if your FOR EACH had no WHERE clause, and you had poor transaction scope, you could actually say that you were locking the "whole table", albeit adding one record to the transaction at a time. But that's more of an indication of bad buffer scoping than a problem with FOR EACH...EXCLUSIVE, which is a perfectly valid way of updating things (as long as you don't need to check for NO-WAIT, IF LOCKED conditions, which need a FIND).

RELEASE won't help in this case. It's NOT a method of controlling transation scope, it's suppose to be a way of clearing down a buffer (in terms of AVAILABILITY). Transation scope is controlled by using buffers, blocks and (if necessary) the TRANSACTION keyword.

 

[tamhas]

What your senior may be warning you about is a FOR EACH EXCLUSIVE which has selection criteria which only select a limited number of records and where there is no supporting index. If, for example, record 25 matches the criteria and the next record that matches is 253, then 26 to 252 will get locked until it gets to 253. The normal solution to this is to do the FOR EACH or QUERY with NO-LOCK and then re-read any qualifying record EXCLUSIVE-LOCK in a buffer.

 

[BenKlein]

I agree with the last post.
I would suggest using a buffer.
Do the for each or query with a no-lock and when you found the record you want to update with the buffer name do a find with exclusive-lock.
You can use a RECID to refer to the record you want to update.
And then use a RELEASE after the update.

 

[yoachan]

Guys, a big thanks for all of you.
It clears the clouds covered my eyes

and for DevTeam,
I've to make a correction. You were right about FIRST and NEXT.
To be detail, the correct code have to be like this

FIND FIRST ..... EXCLUSIVE-LOCK NO-ERROR.
REPEAT WHILE AVAIL .....

     FIND NEXT ..... EXCLUSIVE-LOCK NO-ERROR.
END.

My regards

YoChan