Feedback
Did this article resolve your question/issue?

   

Article

How to find who is preventing a user from acquiring a record lock

Information

 
TitleHow to find who is preventing a user from acquiring a record lock
URL NameP125003
Article Number000128058
EnvironmentProduct: Progress
Version: 9.x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Question/Problem Description
How to find who is preventing another user from acquiring a record lock.
How to determine who is holding the record lock that other users need
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
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.

 
Workaround
Notes
Last Modified Date11/20/2020 7:25 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.