1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Answered Is It Possible To Get Record Lock Duration

Discussion in 'Database Admin' started by progdev1, Jul 14, 2017.

  1. progdev1

    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 (progress):
    1.  
    2. FOR EACH _locks NO-LOCK:
    3.     if the lock-type is share-lock or exclusive-lock then
    4.         check the lock duration
    5.         if the lock duration is > 5 minutes then
    6.            disconnect the user.
    7.         end.
    8.     end
    9. END.
    10.  
    Does anyone know if this is posible.
     
  2.  
  3. Rob Fitzpatrick

    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.
     
    TomBascom likes this.
  4. TomBascom

    TomBascom Curmudgeon

    What Rob said is 1000% correct.
     
  5. Rob Fitzpatrick

    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 (text):
    1. m         //Modify defaults
    2. 1         //page size
    3. 9999
    4. q         //quit
    5. 4         //Record locking table
    6. 1         //all users
    7. 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 (text):
    1. promon dbname -NL < promon_input.txt > promon_output.txt
     
  6. Cringer

    Cringer ProgressTalk.com Moderator Staff Member

    Interesting you use the -NL parameter for promon. Why is that out of interest?
     
  7. progdev1

    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.
     
  8. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com Sponsor

    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
     
  9. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com Sponsor

    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.
     
  10. cj_brandt

    cj_brandt 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.
     
  11. Rob Fitzpatrick

    Rob Fitzpatrick ProgressTalk.com Sponsor

    The lock table HWM is _dbstatus._dbstatus-numlocks; promon 6.
     
  12. TomBascom

    TomBascom Curmudgeon

    Correction:

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

    Rob Fitzpatrick ProgressTalk.com Sponsor

    Sorry, Tom is correct. I was agreeing with CJ who was referencing locks currently held and said HWM in error.
     
  14. ron

    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.
     

Share This Page