Feedback
Did this article resolve your question/issue?

   

Article

Entries in _Lock no longer appear at top of resultset from OpenEdge 11.4 onward

« Go Back

Information

 
TitleEntries in _Lock no longer appear at top of resultset from OpenEdge 11.4 onward
URL Name000056304
Article Number000142362
EnvironmentProduct: OpenEdge
Version: 11.4, 11.5
OS: All supported platforms
Question/Problem Description
Entries in _Lock no longer appear at top of resultset from OpenEdge 11.4 onward.

In versions prior to 11.4, executing the following query:
FOR EACH _Lock NO-LOCK:
 DISPLAY _lock.
END.

Would return any active locks at the top of the result list.

From 11.4 onward, this is no longer the case.
Active locks appears towards the end (but not at the very end) of the result list. 

 
Steps to Reproduce
Clarifying Information
In 11.3.3 and lower, current locks would appear at the top of the list removing the necessity to read the whole of _Lock.

Code querying _Lock for the earlier releases commonly follows a pattern similar to:
FOR EACH _Lock:
  IF _Lock-Recid NE ? THEN LEAVE.
  DISPLAY _lock.
END.
for optimal performance. This pattern is no longer guaranteed to return all active record locks. 


The issue depends only on which OpenEdge release is used to start the database broker.
Error Message
Defect/Enhancement NumberDefect PSC00322250
Cause
The algorithm used to map the internal record lock structures to the VST table representation was replaced in order to make provide a much more consistent view of the _lock table as well as retrieve the data without causing performance issues on the rest of the system. The intent was to filter out the unused locks entirely and return only the ones in use.
Therefore the defect is that the unused entries are still returned. The fact that those unused entries now appear before used ones is an unwanted side-effect.

 
Resolution
Upgrade to OE 11.5.1 or later.

Starting with OE 11.5.1 the unused slots in the lock table are no longer returned by the VST at all.
Workaround
Add the following condition to, or as, a WHERE clause:
_Lock-Recid NE ?

Example:
FOR EACH _Lock NO-LOCK WHERE _Lock-Recid NE ?:
  DISPLAY _lock.
END.

In earlier releases, this pattern has severe performance cost as the size of the lock table (-L database startup parameter) increases. However, also due to the algorithm changes, in 11.4 table scans on _Lock are much faster.
To compare: Assuming the same hardware, with databases started with -L 200000. Where a database running on OpenEdge 11.3.3 or earlier will take minutes to complete the scan, one running on OpenEdge 11.4 or later takes less than a second.
Notes
Comparing the "aborting table scan" pattern used in 11.3.3 and earlier (see Clarifying Information) with the "always full scan" in 11.4 and later: Querying the _Lock table will be faster in most circumstances, the exception being when the number of active locks is less than approx. 5% of the size of the lock table. See attached 000056304.p for a benchmark example that runs using a Sports2000 database.

References to other documentation:

Progress article(s):
000014135, Accessing _Lock VST takes a long time
000017174, How to determine which users are holding record locks on a specific table
000020323, How to find who is locking what table(s) using 4GL
000033245, How to monitor locks using VST?
000044741, Why does the _Lock table have records where _Lock-Usr = ?
Last Modified Date2/27/2017 4:22 PM
Attachment 
Files 1. 000056304.p
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.