When a user tries to acquire a lock on a record that is already locked by another user, it can be problematic for a
DBA to find out who holds the original lock.
While
well-coded applications will try to acquire record locks with a NO-WAIT on the FIND statement in conjunction with the LOCKED function, so that the informational message 2624 ("
<file-name> in use by <user> on <tty>. Wait or choose CANCEL to stop.") will not appear for the user who is trying [and failing] to acquire the lock that eventually raises a STOP condition when the
-lkwtmo expires,
Lock wait timeout of <seconds> expired (8812)
FIND customer WHERE customer.custNum = someCustomerNumber EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
IF LOCKED customer THEN /* ..... do some waiting, or issue some graceful message .... */To provide development teams further information, refer to Article
How to trap condition thrown by Lock Wait Timeout (-lkwtmo)
Applications usually fail to provide the user or anybody else with information regarding who really is holding the lock at the time. This situation can leave the DBA with the onerous task of trying to find who the original lock holder (the "culprit" of the record lock) really is. The following is a piece of ABL code can help in these scenarios.
- The code looks for lock requests that are queued, waiting for other locks to be released, and displays information regarding the original lock holder.
- A Temp-Table is explicitly used as the _Lock metaschema table itself is heavily used by the underlying database locking functionality
- _Lock._Lock-Usr = ? means the VST lock "record" is currently not in use. For further information refer to Article Why does the _Lock table have records where _Lock-Usr = ?
- The code is provided as is, with no guarantees of suitability for a particular purpose. Users are strongly encouraged to review and understand what the code is doing before employing it in critical environment, and to adjust the code as needed.
/* findLockCulprit.p
Finds the user who is locking a record and causing others to wait. */
DEFINE TEMP-TABLE ttLocks NO-UNDO LIKE _Lock
INDEX byRecid IS PRIMARY
_Lock-RecId ASCENDING
_Lock-Table ASCENDING.
DEFINE BUFFER culprit FOR ttLocks.
/* Access to _Lock is slow, so copy _Lock to an indexed temp-table ttLocks first. */
FOR EACH _Lock WHILE _Lock._Lock-table <> ?:
CREATE ttLocks.
BUFFER-COPY _Lock TO ttLocks.
END.
FOR EACH ttLocks WHERE ttLocks._Lock-Flags MATCHES "*Q*":
/* Do the FIND's with NO-ERROR, as the lock table is just a snapshot of very volatile data... */
FIND FIRST culprit WHERE culprit._Lock-RecId = ttLocks._Lock-RecId
AND culprit._Lock-Table = ttLocks._Lock-Table
AND NOT culprit._Lock-Flags MATCHES "*Q*"
NO-ERROR.
IF AVAILABLE culprit THEN DO WITH SIDE-LABELS TITLE " Users holding other users records ":
FIND _Connect WHERE _Connect._Connect-Usr = culprit._Lock-Usr NO-ERROR.
IF AVAILABLE _Connect AND _Connect._Connect-TransId <> 0 THEN
FIND _Trans WHERE _Trans._Trans-Usr = _Connect._Connect-Usr NO-ERROR.
ELSE /* Ensure no _Trans record is available. */
RELEASE _Trans NO-ERROR.
FIND _File WHERE _File._File-num = culprit._Lock-Table NO-LOCK NO-ERROR.
DISPLAY culprit._Lock-Usr COLON 17
culprit._Lock-Name COLON 17
_Connect._Connect-Device WHEN AVAILABLE _Connect LABEL "On"
culprit._Lock-Table COLON 17 LABEL "Table" FORMAT "ZZ,ZZ9"
_File._File-Name WHEN AVAILABLE _File NO-LABEL
culprit._Lock-RecID COLON 17
culprit._Lock-Type COLON 17 LABEL "Lock type"
culprit._Lock-Flags
.
IF AVAILABLE _Trans THEN
DISPLAY _Trans._Trans-State COLON 17
_Trans._Trans-Txtime COLON 17 "Transaction start"
.
IF AVAILABLE _Connect THEN
DISPLAY _Connect._Connect-Type COLON 17 LABEL "Client type"
_Connect._Connect-Time COLON 17
.
END.
END.
