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

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. 

 

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.

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.

 

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.

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.

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):
 Accessing _Lock VST takes a long time
 How to determine which users are holding record locks on a specific table
 How to find who is locking what table(s) using 4GL
 How to monitor locks using VST?
 Why does the _Lock table have records where _Lock-Usr = ?