Feedback
 
Did this article resolve your question/issue?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful. Please provide us a way to contact you, should we need clarification on the feedback provided or if you need further assistance.

Characters Remaining: 1025

 


Article

Record Locking - How to know who has locked the record using VST?

« Go Back

Information

 
Article Number000001335
EnvironmentProduct: Progress
Version: 8.x, 9.x
Product: OpenEdge
Version: 10.x, 11.x
OS: All supported platforms
Other: RDBMS, VST
Question/Problem Description
Record Locking - How to Know Who Has the Record Using VST?
How to find out who has a particular record locked through the use of Virtual System Tables (VST)?
Which user is locking a record?
How to know who has locked the record using VST?
Is it possible to know the date / time when the record was locked?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution
In Progress Versions prior to 8.2

VST's are not available. Instead a program that accesses PROMON and parses the output could be employed.

The information is available from: PROMON > Option 4 - Record Locking table.
Where a filter by user number, range of user numbers, by recordcan be selected and list all entries in the lock table.


In Progress Versions post 8.2

To find out which locks exist for a given record, the RECID of the record must be found. This can be done by fetching the record with NO-LOCK and using the RECID() function, or finding this information in PROMON output.

With the RECID of the record, by querying the VST tables _connect and _lock the owner of that particular record locked can be found:
  • _connect = Connection information
  • _lock = Record Locking Table information

In Progress Versions 8.2 and 8.3:

VST must first be enabled offline by running the command:
$   proutil dbname -C enablevst.

The following sample program uses the record key to get the RECID of the record and uses it to query the _lock table. Then it queries the _connect table to get the information about the device the user is connected to:
 
DEFINE VARIABLE wrecid AS INTEGER NO-UNDO.

   /* Find the recid of the record */
   FIND customer WHERE cust-num = 15 NO-LOCK.
   ASSIGN wrecid = RECID(customer).

   /* Use repeat loop - More efficient than FIND ... WHERE due to lack of suitable index on _lock table */
   REPEAT:
      FIND NEXT _lock NO-LOCK NO-ERROR.

      IF _lock-recid = wrecid AND
         _lock-flag MATCHES "*X*" 
         /* typically we're interested in any form of exclusive lock, so we test for X lock flag */
      THEN LEAVE.

   IF AVAILABLE(_lock) THEN DO:
      DISPLAY _lock-usr _lock-name _lock-flag WITH FRAME A.
      FIND FIRST _connect WHERE _connect-usr = _lock-usr NO-LOCK.
      DISPLAY _connect-usr _connect-name _connect-device WITH FRAME b.
   END.

In Progress 9.x to OpenEdge 11.3, 11.4 and later if Table Partitioning is not enabled:

VST is enabled by default. This program is very similar to that for Versions 8.2 and 8.3, but since the RECIDs in Version 9 are not unique in the database but only within a storage area, the program must do additional filtering. Usually, filtering by table is the most useful; to do so the following example code additionally uses the _file table:
DEFINE VARIABLE wrecid AS INTEGER NO-UNDO.
   DEFINE VARIABLE wtable AS INTEGER NO-UNDO.

   /* Find the recid of the record */
   FIND customer WHERE cust-num = 15 NO-LOCK.
   ASSIGN wrecid = RECID(customer).
   FIND _file WHERE _file-name = "customer" NO-LOCK.
   ASSIGN wtable = _file._file-num.

   /* Use repeat loop - More efficient than FIND ... WHERE due to lack of suitable index on _lock table */
   REPEAT:
      FIND NEXT _lock NO-LOCK NO-ERROR.

      IF _lock-recid = wrecid AND
         _lock-table = wtable AND
         _lock-flag MATCHES "*X*" 
         /* typically we're interested in any form of exclusive lock so we test for X lock flag */
      THEN LEAVE.

      IF _lock-recid = ? THEN DO:
          RELEASE _lock.
          LEAVE.
      END.
   END.

   IF AVAILABLE(_lock) THEN DO:
      DISPLAY _lock-usr _lock-name _lock-flag WITH FRAME A.
      FIND FIRST _connect WHERE _connect-usr = _lock-usr NO-LOCK.
      DISPLAY _connect-usr _connect-name _connect-device WITH FRAME b.
   END.

The performance of the code provided is highly dependent on the size of the lock table and the number of active locks. This is because of the lack of a suitable index.  To this end, Article 000033245, How to monitor locks using VST?  provides a working example of how Temp-Tables are typically employed when querying the _lock table that defines a temp-table index. Further examples are provided in the Articles Referenced below.

Note: _Lock & _Connect VSTs do not have datetime info about record locks and there is no other VST which contains this information.
 
Workaround
Notes
Attachment 
Last Modified Date2/3/2020 12:28 PM