Answered Is It Possible To Get Record Lock Duration

progdev1

Member
Does anyone know if it is possible to get the duration that a record have been locked for via a program? I know it is possible to get a transaction duration using virtual system table _trans. But this does not help me because share-locks don't generate a transaction.

Our software supplier has some screens that have read lock and exclusive locks. Sometimes the user forgets to log out at night and it can cause overnight jobs to fail due to locking.

What I would like to do is is to create a utility program that does the below pseudocode before running the overnight utility:
Code:
FOR EACH _locks NO-LOCK:
    if the lock-type is share-lock or exclusive-lock then
        check the lock duration
        if the lock duration is > 5 minutes then
           disconnect the user.
        end.
    end
END.
Does anyone know if this is posible.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
With questions like this one about VST schema it's helpful to state your OE version as these things change over time, even from one service pack to another. Also, the name of the table is _lock, not _locks.

Anyway, I'm not aware of a way to directly get the duration of a given record lock. But you do have information about user, table, and recid that, if it does not change over time, lets you try to determine duration by sampling.

WARNING: You should not even consider querying _lock, via ABL or SQL, if you are on a release prior to 11.6. You should not consider query _userlock if you are on a release prior to 11.7. It is highly inefficient and can cause significant latch contention. It can also result in queries that, especially with a large lock table, run for a long time; think dozens of minutes, maybe hours.

I'm reading between the lines that this is primarily a business-hours application, at least for the interactive users and that they shouldn't have sessions logged in for days at a time. We take a different approach in that case. We have a program the client can run as a scheduled job prior to their batch window that disconnects any users it finds that shouldn't be connected. These are remote users in our case but the selection logic could be different for you. If you don't have interactive users who should legitimately be connected during the batch window then this approach is both safer and easier to write.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
One more point I meant to add: if you are bound and determined to get lock information in real time, a much safer and more efficient way is with promon scripting.

Create a file called promon_input.txt that looks like this:
Code:
m         //Modify defaults
1         //page size
9999
q         //quit
4         //Record locking table
1         //all users
q         //quit

Note: do not include the comments on the right in the file. They are there for explanatory purposes in this post.

Then invoke promon with this input. Example:
Code:
promon dbname -NL < promon_input.txt > promon_output.txt
 

Cringer

ProgressTalk.com Moderator
Staff member
Interesting you use the -NL parameter for promon. Why is that out of interest?
 

progdev1

Member
Thanks for your tips I'm on OE10.2b patch 08 by the way. We have an app this is primarily business hours. In theory we could have users in the system at the time when the end of day runs, unlikely but possible. Hence I'd rather just disconnect users with a long record lock. The problem we have is that some users go into data entry screen and forget to log out before they go home. This leaves locks in the system. Maybe the way to completely solve this will be to force windows to to log them out if they are no keystrokes in 60 minutes etc. Okay thanks for your help.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Interesting you use the -NL parameter for promon. Why is that out of interest?
It's not a requirement or anything. If I were doing this very frequently on a busy system I would use -NL; without it, promon does more latch locking.

Promon isn't perfect and it doesn't present a perfectly consistent view of shared memory. As I see it, there are trade-offs either way, with or without -NL.

More info: Knowledge Article
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Maybe the way to completely solve this will be to force windows to to log them out if they are no keystrokes in 60 minutes etc.
When you test this, check how long the user sessions live in the database after such a user is logged out. Maybe they will disappear from _Connect right after they are logged out in Windows, or maybe they will live for the duration of the server's TCP keepalive timer; I'm not sure offhand. Depending on the OS and its configuration, this could be a couple of hours. That could impact whether this is a viable strategy.
 

cj_brandt

Active Member
I was in a situation where it was worth the risk of reading the _Lock table every 3 minutes so we could track down user locking issues.

I would recommend checking the total number of locks before attempting to read the _Lock table. If the total number of locks exceeded a certain value (I think it was 20,000) then we would not access the _Lock table - we would issue a warning and then check again 3 minutes later. I believe _DbStatus-Numlocks will provide the number of locks currently held. That value is also available in a promon screen if you want to use promon. I think it is screen 6 or 7.
 

TomBascom

Curmudgeon
Correction:

_DbStatus._DbStatus-NumLocks is the *current* number of locks. The high water mark is _DbStatus._DbStatus-MostLocks
 

ron

Member
I agree with all above comments - getting lock details via _Lock on any OE version prior to 11.4 is inviting some big trouble. On a large, busy, production system it can take HOURS to make one single pass through _Lock - and at the same time it will impact system performance dreadfully.

We have a system that is old (and not-so-well-written) - and we suffer from lock problems all the time. So - we have developed a process to monitor and report lock problems across six production databases. It tests the OE version. If it is 11.4 or later it gets its data from _Lock - otherwise promon. But it does check _dbstatus-numlocks first and if it's more than 30,000 it skips one 10-minute cycle. Parsing the output from promon is not trivial. We did it first using bash and it took a long time - so we now use awk, which is much faster.

Going back to the original question posted: you can see when a transaction is started - but not so with locks. I don't think that's an oversight - locks get created and deleted at a furious rate on large, busy systems and I think the overhead of maintaining user and date/time details for each lock would be too great (unfortunately).

Ron.
 
Top