Kbase P125003: How to find who is preventing a user from acquiring a record lock
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
GOAL:
How to find who is preventing a user from acquiring a record lock
FIX:
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.
Indeed, well-coded applications will try to acquire record locks with the NO-WAIT option of 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:
FIND customer WHERE customer.custNum = someCustomerNumber EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
IF LOCKED customer THEN ..... do some waiting, or issue some graceful error message ....
However, applications usually fail to provide the user or anybody else with information regarding who really is holding the lock. This situation can leave the DBA rather helpless in detecting who the original lock holder (the "culprit" of the record lock) really is. Following is a piece of code that 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.
The code is provided as is, with no guarantees of suitability for a particular purpose.
You are strongly encouraged to review and understand what the code is doing before employing it in critical environment.
You are also encouraged to make your own adjustment to better fit your needs.
/* 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 can be painfully slow, so copy
_Lock to an indexed temp-table 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 ":
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"
&nb.sp; _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.
.