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




Feedback
Did this article resolve your question/issue?

   

Article

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

« Go Back

Information

 
TitleRecord Locking - How to know who has locked the record using VST?
URL Name19136
Article Number000173666
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
Last Modified Date2/3/2020 12:28 PM
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.