Maizels.NU Progress Page

Please Note:

 

Who is that locking with VSTs

by Grant Maizels

The first issue of Progressions that I ever saw (Number 4) had an article which I found very interesting. It was titled "Who 's That Locking on My Key?" and was by Ray Norrish. The article dealt with a problem which has existed for as long as Progress, if you attempt to lock a record which is already locked, a message appears at the bottom of the screen showing who (the user and their tty or computer name) has the lock and suggesting that you wait or press the STOP key. If you use the NO-WAIT option, you can used the LOCKED function to check whether the record is locked but there is no way to find the other user's user name or the device from which they are connected.

Ray provided a method of scraping the data out of promon and obtaining the user name of the locking user, however the method used was limited to unix systems and was quite complex due to the need to run an external program to collect the data. With the development of Virtual System Tables (VSTs) in progress version 8.2 there is now a better way to do this.

Virtual System Tables are a great new feature in Progress to allow progress applications to lock at what is going on in the database engine. VSTs are principally a better method of looking at data that has been available in previous versions of progress from promon. There are 30 new tables created when you enable VSTs in a database, but we will only look at two of then.

_Connect contains details of each connected user, server, broker, or promon session for the database there is one entry of each user up to the -n limit. Records with values all ? values represent available, unused slots in the servers connection table.

_Lock contains details of all record locks up to the -L limit. Records with all ? values represent unused slots in the locking table.

Two programs are presented here, the first (locktest.p) attempts to find a customer with an exclusive lock and runs the second program (lockdetl.p) to get the locking details. The programs have been written to be used with the sports database with an employee table added and VSTs enabled. To enable VSTs you need to use the proutil command as follows.

proutil database-name -C enablevst

You can examine VSTs in the data dictionary by selecting view in the tool bar and choosing "Show Hidden Tables".

/* locktest.p */

define variable cust_recid as recid no-undo.
define variable usr_name as char no-undo.
define variable phone as char no-undo.
define variable device as char no-undo.

create alias vstdb for database sports.

find customer no-lock
   where customer.cust-num = 1
   no-error.
if not available customer then
do:
   message "Customer not available" view-as alert-box.
   return.
end.
cust_recid = recid(customer).

find customer exclusive-lock
   where recid(customer) = cust_recid
   no-wait
   no-error.
if locked customer then
do:
   run lockdetl.p (cust_recid, output usr_name, output device, output phone).
   message "locked" cust_recid usr_name device phone view-as alert-box.
end.
else
   message "not locked" view-as alert-box.
/* lockdetl.p -- get lock details from vsts */

define input parameter rid as recid.
define output parameter uid as character.
define output parameter dev as character.
define output parameter phone_nbr as character.

assign
    uid = ?
    dev = ?
    phone_nbr = ?
    .

find first vstdb._lock no-lock where _lock-recid = integer(rid) no-error. 

if available vstdb._lock then
    find first vstdb._connect no-lock where _connect-usr = _lock-usr no-error.

if available vstdb._connect then
    find employee no-lock where employee.emp-name = _connect-name no-error.

assign
    uid = _lock-name when available vstdb._lock
    dev = _connect-device when available vstdb._connect
    phone_nbr = employee.phone when available employee
    .

Grant Maizels has been using progress since 1986 when he first used version 2. Grant is currently employed by COGITA Business Services in Sydney, Australia where he is part of a team developing "UBi/Now" an activity and locking monitor for MFG/PRO.

Back to Maizels NU

If you want more info, please contact progress@maizels.nu.

Last updated 18th January 2000