Feedback
Did this article resolve your question/issue?

   

Article

_DBStatus VST: Current Locks in use and the Lock Table High Water Mark

« Go Back

Information

 
Title_DBStatus VST: Current Locks in use and the Lock Table High Water Mark
URL NameP148123
Article Number000140810
EnvironmentProduct: Progress
Version: 9.x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
Which VST table gives information on the total number of current locks in use and the lock table high water mark?
Which VST table has a total value for the number of locks currently in use within the lock table?
Which VST table has a value for the most locks that were used in the Lock Table?
How to ensure that the Lock Table is large enough for current transaction processing?
How to monitor Lock Table entries when isolating "Lock Table Overflow (915)" application transaction scope?
Using the _DBStatus VST table to monitor the Lock table with regard to: the total number of current locks in use, the lock table high water mark and which users are holding the most locks.

 
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
This _DBStatus VST table can be used to find information on the Lock Table:
  •  _DbStatus-NumLocks will show the number of locks currently in use 
  • _DBStatus-MostLocks will provide the High Water Mark of the Lock Table. 
This Lock Table information is also available through PROMON:
promon dbname -> R&D -> 1.  Status Displays -> 13.  Shared Resources.

Example: ABL Code to report Lock Table information 
  • If the Lock Table value is not known, it can queried:
  • When the number of locks currently in use is roughly 85% of the (default) Total Lock Table Value (-L 8192).
  • It will also report the connected users that have more than 500 locks at this time.
  • This code can be customised to requirement in order to monitor the Lock Table to assist with diagnosing the Lock Table Overflow (915) condition.
FIND FIRST _DbStatus NO-LOCK.
IF _DbStatus._DbStatus-NumLocks > 7000 THEN
DO:
    DISPLAY
         _DbStatus._DbStatus-NumLocks SKIP
         _DbStatus._DbStatus-MostLocks SKIP(2)
        "Connections with at least 500 locks:" SKIP.
        
    FOR EACH _UserLock NO-LOCK 
        WHERE _UserLock-Usr <> ? AND
              _UserLock-type[500] <> ?:
              
    /* _userlock table is indexed (_DbStatus-Id) but only stores information about the first 512 locks held by a given user */
    FIND _Connect where _Connect._Connect-usr = _UserLock._UserLock-Usr NO-LOCK NO-ERROR.
    FIND _lock where _lock._lock-id = 500 NO-LOCK NO-ERROR.
    find _file where _file._file-number =  _lock._lock-table NO-LOCK NO-ERROR.

    DISPLAY    
      _UserLock._UserLock-Usr
      _UserLock._UserLock-Name
      SUBSTRING( _Connect._Connect-Device,6) WHEN AVAILABLE _connect
      _UserLock._UserLock-Type[500]
      _UserLock._UserLock-Flags[500]
      _UserLock._UserLock-recid[500]    SKIP
      _lock._lock-table 
      _file._file-name.
    END.
END.

The Total Locks can be increased when the number of locks currently in use is high: Interrogating the Identified user connections holding 500 or more lock table entries:

1.  Analyse the user sessions clientlog (if enabled) 2.  Dump a stack trace: 3. View the Database Statement Cache, (if the query is still running Server-Side processing). 
Workaround
Notes
Last Modified Date11/20/2020 7:04 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.