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:
- Since OpenEdge 10.1C , the Total Locks (-L) can be increased online.
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).
