OpenEdge record lock debugging

1
votes

I am using OpenEdge version 11.1. I am occasionally getting the following error message during runtime:

Lock wait timeout of 10 seconds expired (8812)

According to my debug log, the error is occurring in various classes which makes it exceptionally difficult to debug. Is there any way I can print to the debug log some details about the error, such as which record is locked?

2
openedgeprogress-4glprogress-db

2 Answers

1
votes

That usually means you're trying to lock a record that is already locked. OpenEdge waits for 10 seconds (or the time specified by the -lkwtmo startup parameter) and then gives up, leaving the error in the log.

To find out what records are locked, look in the _Lock table. It's a hidden table; you can view its schema by going into the Data Dictionary and choosing View - Show Hidden Tables. The _Lock records have info such as the record ID, the type of lock (share/exclusive), the user who has it locked, etc. You can use this code to dump the lock table to a file. Hopefully it'll help you track down the locked record.

OUTPUT TO VALUE("locktable.txt").

FOR EACH _Lock WHERE _Lock._Lock-RecID <> ? NO-LOCK: 
    FIND _File WHERE _File._File-Number = _Lock._Lock-Table NO-LOCK NO-ERROR.
    PUT UNFORMATTED
        _Lock._Lock-Name " "
        _Lock._Lock-Usr " "
        _File._File-Name " "
        _Lock._Lock-RecID " "
        _Lock._Lock-Type " "
        _Lock._Lock-Flags
        SKIP.
END.

OUTPUT CLOSE.

To catch the lock error, add a NO-WAIT to your FIND statement:

FIND FIRST <TableName> WHERE ... EXCLUSIVE-LOCK NO-WAIT NO-ERROR.
IF LOCKED(<TableName>) THEN ...

You can then add your debugging code in the IF-THEN statement.

0
votes

It always depends of the context but 10 seconds is very very short !! I'm used to 1800 seconds heh. It's a database startup system parameter that you could change.

It's a big company with thousand of users. Never found a very good way to totally be in control of locking problems but what I did is to add some logic to a Unix script that monitor a bunch of stuff 24/24.

What I achieved is to at least have an alert sent to our helpdesk before the lock timeout actually occurs.

Using _lock, _file (table) and _user you can have pretty detailed information on the user which is locked, what table and what record.

Our situation is probably different but we don't care about 1 user locking a record for 10 minutes...but we want to hunt down a user locking a record that another user wait for. Because when lock timeout occurs it also undo the transaction which depending on the context could be trouble !

So we keep note of a lock and if it doesn't move for more than 10 minutes we trigger the alert which send everything we can. Including the other users "fighting" for the same record. More often then not the problem is a user which is updating a critical table and left the screen open for some reason.