Article

Accessing _Lock VST takes a long time

« Go Back

Information

 
Article Number000014135
EnvironmentProduct: Progress
Version: 9.x
Product: OpenEdge
Version: 10.x, 11.1, 11.2, 11.3
OS: All supported platforms
Question/Problem Description
Accessing _Lock VST takes a long time
The time taken to read _Lock does not scale linearly to the number of locks. It scales exponentially.
Reading _ Lock is very slow.
Slow performance using the _Lock VST table.
-L (Lock table size) is large
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
This is expected behavior.

The _Lock table is not indexed in any way. Reading the lock table VST is slow because the underlying data structures of the lock table are not organized as a simple table. They are significantly more complex and also the structures change while they are being read. That makes the conversion into a flat table slow when the lock table (-L) is large.
Resolution
This Article does not apply to OpenEdge 11.4 and later.

The behavior of the _Lock VST changed starting with OpenEdge 11.4: Performance of the _Lock VST now directly scales with total size of lock table instead of active locks, and will show much better performance in most cases. Refer to Article 000056304, Entries in _Lock no longer appear at top of resultset from OpenEdge 11.4 onward for further details.

Prior to OpenEge 11.4:
  • When a record with an unknown value for a lock status field such as _Lock-Usr or _Lock-Recid  is reached, exit the query. Records with unknown values in these fields are unused records; they do not represent current locks.
  • Queries on _Lock can be sped up by aborting the loop if a field other than _lock-id becomes unknown.  This will ensure that data is read only for the locks that are actually in use, not for unused records.
  • Specifying the check for unknown values in the WHERE clause is less effective due to how the query mechanisms work in this particular instance. The difference will be smaller if large numbers of records are being locked.
  • As of this writing there are no plans to index the _Lock table.
  • Example:
FOR EACH _Lock NO-LOCK:
    IF _Lock._Lock-Usr = ? THEN LEAVE.
    /* other code here */
​END.

Sample _Lock Table code:

In some cases, the _Userlock VST will suffice.  It shows the first 512 locks associated with a particular user and is very fast since it does it in one request and stores the entries in an array. Article 000021994, What is the Record locking table _UserLock   

Workaround
Notes
Attachment 
Last Modified Date4/21/2017 1:21 PM
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.



Feedback
 
Was this article helpful?

   

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