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 firstname.lastname@example.org.
Last updated 18th January 2000