Lock Release

[paulwesterman]

I have a problem with getting exclusive locks released.

I am updating a record in the table that stores my sales order numbers. The table (param-file) stores all kinds of different numbers that we use for various purposes and each record has a unique indentifier field. For example the sales order numbers are linked to the field 'number' where the value is 'sales' and the value of the sales order number is stored in the field 'ordref'. I have a procedure that runs an external procedure to get the current sales order number 'ordref" and then increases the number by 1 and then the external procedure does a bunch of other stuff before returning to the original procedure. My code in the external procedure is:

find param-file where param-file.number = 'sales' exclusive-lock no-error.
if avail param-file
then do:
assign v-ordref = param-file.ordref
param-file.ordref = param-file.ordref + 1.
end.
find current param-file no-lock no-error.

<lots more things happen here then I return to the calling procedure.>

My problem is 'find current param-file no-lock no-error' doesn't change the lock status. The exclusive-lock doesn't release. We have a multi-user environment and if the user goes to lunch in the middle of the procedure other users are locked out of the param-file.

We are running Progress Version 9.1C19 on UNIX.

 

[AndySmith]

Hi Paul!

Using FIND CURRENT...NO-LOCK is not guaranteed to downgrade the lock. To be more specific, it will fail to do so if the transaction is still active at that point in time. The most likely cause of this is an earlier reference to the param-file buffer you're updating, probably in the calling procedure. So the transaction is scoped over the whole thing. You can check this by using the TRANSACTION function - message it out all over the place.

One solution would be to explicitly scope the param-file buffer to your update procedure. So you could just add...

DEFINE BUFFER param-file FOR param-file.

...to the top of your update procedure listed below. You could also replace...

if avail param-file
then do:

...with...

if avail param-file
then do TRANSACTION:

...just to be even more explicit. Bear in mind that you may actually WANT this update to be part of some bigger transaction, so if you do go down this route you'll have to manually undo your changes.

You may want to consider replacing this table with some DB sequences. They can be useful for avoiding this scenario.

Regards,

Andy.

 

[Olivier_Desmars]

Instead of

find current param-file no-lock no-error.

use

Release param-file.

This will free the lock.

 

[paulwesterman]

Andy, Oliver
Thanks for your replys. It turned out that I had an exclusive-lock on another table and it was scopped to the procedure block and that keep my lock 'open' on the table that I wanted to change to no-lock. I created an internal procedure and moved the exclusive-lock on the other table into the internal procedure and that solved my problem.

Paul