How to know the user who has locked a record

[roel aguilar]

Hi.

I need to know (in a run time) if some user has locked the record i'm going to use, and who is that user.

For example, I have a table called "users" that has only one record, and I'm going to update that record but another user has it locked. So, when I try to update the record, progress returns me an error saying something like

" No ' users ' record is available. "

but this is because another user has it locked, 'cause when I disconnect all users (one by one) and try to update the record,
I can do it well, with no matters.

So I need to know who's the user who has locked the record, because thus I could display a message saying wich of the users I have to disconnect.

That's all.

Thank's for your help and sorry for my horrible english !!!.


God bless you !!!

 

[jamesmc]

Hi there,

You can find the record you are wanting with the NO-LOCK, NO-WAIT and NO-ERROR options, then used the LOCKED function to see if the record is locked. I don't think it is possible to see what user has the record locked (can VST's give you this information?). It would be useful to know the version of Progress you use though.

PS: thanks for the blessing.

HTH,

James.

 

[greuceanu]

Hi there,

If you have VST (Virtual System tables) enabled, you can find out the user who locked your record by searching in the _Lock or _UserLock tables. I don't know exactly in which one. Check out the Progress Database Administration Guide and reference, chapter 19, page 19-27.

If you don't have the VST tables, you could create a locked-by field, in which you store whatever you want when a user successfully locks the record. When the record is locked, just read the locked-by field.

Regards,
Greuceanu

 

[sathish]

FIND FIRST dbname._file WHERE dbname._file._File-Name = "tablename" NO-LOCK NO-ERROR .
IF AVAIL dbname._file THEN
DO:
for each dbname._lock WHERE dbname._lock._Lock-Id > 0
AND dbname._lock._Lock-RecId = Recid("recid of the record which locked")
AND dbname._lock._Lock-table = dbname._file._file-number
NO-LOCK .
IF AVAIL dbname._lock THEN
DO:
DISP _Lock-Usr _Lock-Id _Lock-Name.
END.
END.
END.

you can use this VST table _file and _lock which help you to find the user who locked the record

tablename = table name of the Record locked
recid of the record which locked = you can find the recid of the record which got locked using the no-lock.


please let me know if you require any information.

i hope this will solve your problem.

_
Sathish

 

[TomBascom]

The code above in post #2 is wrong and the code immediately above in post #4 will be very inefficient on a busy system with a large -L value (the FOR EACH _LOCK may never finish executing).

This problem really has two pieces to it.

1) How do I determine if a record that I wish to update is locked?

The code in post #2 says to read it with no-lock no-wait no-error and then test the locked() function. However, if you read it with no-lock locked() will always be false. This is simple to demonstrate to yourself. Open two "mpro" windows against a "sports2000" database. In the first one run:

find first customer exclusive-lock.
update name.

Just let it sit there on the "name" prompt...

In the second window enter:

find first customer no-lock no-wait no-error.  /* as suggested in post #2...  although omitting no-wait and no-error makes no difference... */
display locked( customer ).

and you will get a result of "no".

But the error that you are trying to avoid can just as easily demonstrated by changing window #2 to:

find first customer exclusive-lock.
update name.

Now you get the error.

The second question :

2)

To avoid the error change window #2 to:

find first customer exclusive-lock no-wait.
if locked( customer ) then
  message "Someone else has the record locked!".
 else
  update name.

To determine who is locking the record change window #2 to:

find first customer exclusive-lock no-wait.
if locked( customer ) then
  do:
    find _file no-lock where _file-name = "customer".
    for each _lock no-lock while _lock-usr <> ? and _lock-file = _file-name and _lock-recid = recid( customer ):
      display _lock-usr.
    end.
  end.
 else
  update name.

This may still perform poorly on a very busy system. But it will work better than the plain old FOR EACH.

 

[sathish]

Agreed that the code written in the #4 does not determine the exact record that you want to update is locked ?

But the code written #4 is just generic sample code just to get the user who locked the record

If you can see in the #4 i have mentioned the following parameter in the code.

tablename = table name of the Record locked
Recid of the record which locked = you can get the recid of the same record if you find it in NO-LOCK.

Its upto the devloper how can he use this code in his programme but this are two table where the devloper can get the user who locked the record.

However the performance will be poor but you can get the output.


Strange thing is if you use find instead of foreach for finding the _lock table maximum time it will give wrong output .( i have faced the problem).

_
Sathish.

 

[TomBascom]

Perhaps I need to highlight what is different. When scanning _Lock you should always use:

for each _lock no-lock [B][COLOR="#B22222"]WHILE[/COLOR] _lock-usr <> ?[/B] /* and whatever ... */

VST's do not have indexes. So the usual query optimization techniques don't always yield the best result. _Lock is an especially notorious example.

The WHILE is much more efficient in this case because it will stop the scan once unknown _lock-usr records are found. But because new locks can come into the _Lock table faster than you can scan them, on very busy systems the query may run forever. To avoid that I often add a counter to the loop and give up after N records have been scanned. (Where N is usually a modest number like 1,000.)

 

[GregTomkins]

FOR EACH ... WHILE, new to me, thanks for teaching me something new! I don't get it though, and yes, I read the doc. So, why does this return all records:

FOR EACH customers WHILE customers.name <> ?

... but this version doesn't return any? (Assume your name is in the table):

FOR EACH customers WHILE customers.name BEGINS "TOM":

Edit: fixed WHERE to WHILE, I promise that's what I used when I actually tried it.

 

[TomBascom]

Because the first record failed the WHILE criteria. At least it does in my copy of sports