Feedback
Did this article resolve your question/issue?

   

Article

How to monitor locks using VST?

« Go Back

Information

 
TitleHow to monitor locks using VST?
URL Name000033245
Article Number000168559
EnvironmentProduct: Progress
Version: 9.x
Product: OpenEdge
Version: 10.x,11.x
OS: All supported platforms
Question/Problem Description
How to monitor locks using VST?
Is there any way to see what table an ACTIVE transaction is running is against?
How to relate the _Lock, _Trans and _File VST's to list the database transactions with the associated users and tables
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution

The following procedure lists all ACTIVE state transaction Ids along with the associated user and table numbers and names. 

  • The procedure starts by scanning the _Lock table to populate a TEMP-TABLE with only the _Lock records of interest.
  • It then ties this TEMP-TABLE with the _Trans table to access the transaction IDs
  • Finally, it ties the TEMP-TABLE with the _File table to obtain the table name.

 
The TEMP-TABLE is used to enhance the performance of the code and limit the processing of the _Lock table to those records that are of interest to us:

This procedure will not capture any transactions that do not involve locked tables.  For example, the ACTIVE transaction started by executing the following SQL query from a SQL client will not be captured because there are no locked tables involved:
 
"SELECT PUB.NextCustNum.NEXTVAL FROM PUB.Customer"

DEFINE TEMP-TABLE ttLock
    FIELD LockId LIKE _Lock._Lock-Id
    FIELD LockUsr LIKE _Lock._Lock-Usr
    FIELD LockName LIKE _Lock._Lock-Name
    FIELD LockTable LIKE _Lock._Lock-Table
    FIELD LockFlags LIKE _Lock._Lock-flags
    INDEX LockIdx IS PRIMARY UNIQUE LockId.
    
FOR EACH _Lock NO-LOCK:
    IF _Lock._Lock-Usr = ? THEN NEXT .
    CREATE ttLock.
    ASSIGN
        LockId    = _Lock._Lock-Id
        LockUsr   = _Lock._Lock-Usr
        LockName  = _Lock._Lock-Name
        LockTable = _Lock._Lock-Table
        lockFlags = _Lock._Lock-flags.
END.

FOR EACH ttlock:
    FIND _Trans NO-LOCK WHERE  _Trans._Trans-Usrnum = ttLock.LockUsr NO-ERROR.
    FIND _File NO-LOCK WHERE _File-Number = ttLock.LockTable.

    MESSAGE
       "Transaction Id:~t" (IF AVAILABLE _Trans THEN _Trans._Trans-Id ELSE ?) "~n"
       "User Number:~t" ttLock.LockUsr "~n"
       "User Name~t" ttLock.LockName "~n"
       "Table Number:~t" ttLock.LockTable "~n"
       "Table Name:~t" _File-Name "~n"
       "Flags:~t" ttLock.LockFlags
       VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.


 
Workaround
Notes
Last Modified Date2/7/2018 2:36 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.