VST Table _Lock


Hi to all.
I am studying VST Tables.
I am interested on table _Lock
I need to know every possible values i can have in fields :


I found this useful article http://knowledgebase.progress.com/articles/Article/21639 that explain how this field is set in PROMON Utility..

How can i test with 4GL ????

I.E I want to display only Shared Locks ...


IF _Lock._Lock-Flags = "S"

Thanks to all ...


The _LOCK VST is not to be trifled with.

Especially not in production.

You can learn some interesting things using it in development but you need to be extremely hesitant to deploy code that references _LOCK in production.

The time needed to access a record, even in a FOR EACH, is exponentially related to the size of the -L parameter and that record's position in the _LOCK table. For any non-trivial values of -L this makes _LOCK a) unusably slow and b) an enormous resource hog.


I'm not kidding. It is very, very bad.

(Having said that -- starting with OpenEdge release 11.4 the underlying algorithm has been changed significantly. There is reason to hope that in 11.4+ the problems noted above have been fixed. But there are not many sites running that yet and experience with the new code is not widespread. Be careful.)

Anyhow... "how to test with 4gl"? Simple. Write some test programs that you think create share locks. You will need to open multiple windows or create a batch oriented test harness but run lock1.p and lock2.p to get some known records locked in a known state. Then use PROMON to confirm that your sessions are locked the way that you expect and lastly run your VST code to see if it agrees with PROMON.

Feel free to post actual test programs and your real VST code if you're having trouble.

Rob Fitzpatrick

ProgressTalk.com Sponsor
I second Tom's opinion. Do not do this in production. Ever.

And regarding "for any non-trivial values of -L", please don't look at a production system and say "well we're using the default of 8192 so it's safe for me to query _Lock." Database startup parameters can change at any time and developers often are not notified.

For example, it may happen that one developer ships code that queries the lock table and then a second developer (whose bad code was responsible for a prior lock table overflow) goes to the DBA and says "could you please *temporarily* increase -L to 100,000 so I can get my program to run?" And so the impact of the first developer's _Lock query is suddenly magnified.


Active Member
Also strongly suggest to avoid using _Lock in production.

If you are having that many issues with lock conflicts then you seriously need to have the developers read up on strong scoping (DO FOR buffer1,buffer2 TRANSACTION) and work on that instead.


Thanks to all of you for kind replies.

I try to explain my need.

We use an italian Retail software written in Progress 10.2B Version.
It runs on AIX system.
I work as It help-desk and programmer.
Our company has 1300 sessions every days working in real time.
Our company has 80 stores.
Our stores are working every day in the week , also in Saturday and Sunday.

My need was to have an OpendEdge procedure that checks if there are LOCKS that holds any records.
Sometimes during day , it happens that is hold as exclusive -lock or shared lock some record of important tables as Invoices Numerator or Customer Numerator and so on.
This event block all activies of all stoes that need to have the same record of a certain table ..
During time , programs will be modified from software house to improve lock control.
But the same we can have problems.
So this software house wrote a program (called Findlock.p) that do this work.

This program test only _lock. flags that matches "*Q*"
This program start in the morning and ends automatically at the end of working day.
It sends mail when it encounter a *Q* _lock-flag , that locj on a record of a table.
I modified it adding ERP information and send mail to IT Group...
For this reason i opened a thread , to know which possibiles values can have _Lock.flags field, because i understood that program cannot detect SHARED LOCK ...

Now how can i proceed if usign VST Tables is not good solution ??
What are alternatives to write a 4GL Program to test that ???

If you nedd i can send a mail with this findlock.p (source code).

I inform that this program, send a mail with lock warning only if a record is locked for 120 seconds , and check all _lock table only every N seconds (These are parameters)



ProgressTalk.com Moderator
Staff member
If you start your sessions with -NL then any queries that have no specified lock will default to no-lock. The users will get errors and you can fix the code.


The answers are not quite correct. If you scan _Lock naively than yes, the time is related to the size of -L.
But see the following knowledge base entries:

You have to do the following, than the time is only related to the number of currently locked records:
    IF _Lock._Lock-Usr = ? THEN LEAVE.
    /* ... */
_Lock always has (value of -L) entries. The used entries are at the beginning, when you encounter one unused entry you will only encounter unused entries to the end. And the fields are not indexed so "WHERE _Lock._Lock-Usr > ''" doesn't help.
Caution: The behaviour changes with 11.4, the order can change. But now you can use a where-clause with acceptable performance.

BTW, we are using _Lock in our application. When you try to open a record for editing and it can't be locked you are basically asked "Do you want to know who is locking this record? This may take a few seconds." and when you select yes _Lock is scanned to give an answer to the user. Performance is usually acceptable. This is integrated into some core ADM procedures so it works basically everywhere.


Locks are not your problem.

"Blocked users" are the problem.

There are many, many locks that occur all of the time -- getting a list of exclusive locks or share locks will not help you figure out which ones are related to problems that end users care about.

This is where ProTop comes to the rescue :) http://dbappraise.com/protop.html

If you look at "blocked users" you will see which users are waiting for something. If a user is waiting for a record lock then the user# holding that record and the tale and recid will be shown. If you have enabled client statement caching you will also see what line of what program the blocker is executing that is causing the problem.

If client statement caching is enabled you will also get a stack trace showing how the oldest blocked user got in that position.


So above we have usr 13 blocked on recid 91708 in table "xnote". Usr 16 is blocking usr 13 and is executing line 30 of test/bigrow.p. (We can also see that usr 16 has the oldest active transaction on the system and that it has a duration of 17 seconds. In that 17 seconds it has caused the rest of the system to fill 2+ bi clusters...)

Usr 13, the victim, is trying to execute line 7 of xnote.p

If _LOCK behaved you /could/ get some of the underlying data from that source. Namely the table name. But none of the rest of it depends on knowing anything about locks.

ProTop does use _LOCK for this purpose in 11.4+. But prior to that it resorts to screen-scraping PROMON because it is several zillion orders of magnitude faster and more reliable to do so.


BTW, we are using _Lock in our application. When you try to open a record for editing and it can't be locked you are basically asked "Do you want to know who is locking this record? This may take a few seconds." and when you select yes _Lock is scanned to give an answer to the user. Performance is usually acceptable. This is integrated into some core ADM procedures so it works basically everywhere.
With all due respect I seriously doubt that that works anywhere near as well as you hope that it does.

I have seen this sort of code embedded in applications many times. Outside of development it never works as well as the designers think that it does.

If it works without major problems it will only be because you very tightly control all coding and do not EVER allow large numbers of locks. That is certainly laudable but you must also have no bugs. Nor any 3rd parties that write bad code.

Which is a big part of the reason that people have been begging for _LOCK to be fixed for so long. It looks very attractive and appears to work well in development. Then you get it out into the real world and it goes boom.

The technique of bailing when you find an unknown value is also not 100% reliable. As the kb says -- the underlying data structures change very rapidly.


As the kbase entry says -- time to scan _LOCK is exponentially related to the number of locks scanned.

Size of the lock table is irrelevant except that larger lock tables obviously permit you to scan many more locks and thus feel even more pain.

Aborting the scan because you found a ? field or because you decided to limit yourself to 1,000 locks or because you have a 100ms govenor or whatever will make the pain less intense. But the time between scanning 1, 2, 3, 4, 5 ... locks increases exponentially as N increases. That increase is unrelated to the size of -L.


Active Member
Getting _Lock fixed would be nice.

Getting a few attributes/functions so you can tell in the 4Gl which user has the record locked (like when you use NO-WAIT vs WAIT) would also be great.... still waiting on that after 20+ years.

Rob Fitzpatrick

ProgressTalk.com Sponsor
It is also worth noting that as of 11.4 it is no longer the case that all locks are at the beginning of the lock table. So code that makes this assumption (e.g. IF _Lock._Lock-Usr = ? THEN LEAVE) will no longer function as intended.


Yes, a 4GL function that would return the user holding the lock would make almost all of the application code that tries to use _LOCK go away. The 4gl obviously has this information available -- the default "user X is locking your record" message knows.


FYI -- I may have to eat some words... the kbase appears to be wrong about "exponential". The growth as you scan more more locks appears to be mostly linear after the first 5,000 or so. And it actually *improves* a bit in the first part of the table.


The size of -L does appear to have an impact. (10.2B08)

With a default -L the stable "cost per lock scanned" is around .231 ms

With -L 100,000 it is 1.678 ms

With -L 1,000.000 it is 20+ ms !!! The first _LOCK takes 49ms (vs 3ms with a default -L)

/* locktime.p
* use this to see how much fun scanning _LOCK is

define variable i as integer no-undo.
define variable n as integer no-undo.
define variable x as integer no-undo.
define variable y as integer no-undo.

pause 0 before-hide.

do while true:

  i = 0.

  if n < 10 then
   n = n + 1.
  else if n < 100 then
   n = n + 10.
  else if n < 1000 then
   n = n + 100.
  else if n < 10000 then
   n = n + 1000.
  else if n < 100000 then
   n = n + 10000.
  else if n < 1000000 then
   n = n + 100000.

  etime( yes ).

  for each _Lock no-lock:
    i = i + 1.
    if i >= n then leave.

  x = etime.

    i  label "_Locks"
    x  label "ms"
    ( x / i ) label "ms per _Lock" format ">,>>>,>>9.999"

  if i < n then leave.  /* exceeded size of -L */

  y = x.



If you're a glutton for punishment -L 10,000,000 checks in with a first _LOCK time of 511ms and a stable time around 220ms per lock.

In case anyone is wondering -- I have seen customers set -L in excess of 1 billion.


Active Member
Want some real fun? Start off with a smallish -L setting and run multiple sessions looking through _Lock at the same time.