Monitoring lock waits and lock errors

You can view information about sessions, lock usage, and lock waits.

If the application executes SET LOCK MODE TO WAIT, the database server waits for a lock to be released instead of returning an error. An unusually long wait for a lock can give users the impression that the application is hanging.

In Figure 1, the onstat -u output shows that session ID 84 is waiting for a lock (L in the first column of the Flags field). To find out the owner of the lock, use the onstat -k command.

Figure 1. onstat -u output that shows lock usage

onstat -u

Userthreads
address  flags   sessid user     tty    wait    tout locks nreads nwrites
40072010 ---P--D 7      informix -         0       0     0     35      75
400723c0 ---P--- 0      informix -         0       0     0      0       0
40072770 ---P--- 1      informix -         0       0     0      0       0
40072b20 ---P--- 2      informix -         0       0     0      0       0
40072ed0 ---P--F 0      informix -         0       0     0      0       0
40073280 ---P--B 8      informix -         0       0     0      0       0
40073630 ---P--- 9      informix -         0       0     0      0       0
400739e0 ---P--D 0      informix -         0       0     0      0       0
40073d90 ---P--- 0      informix -         0       0     0      0       0
 40074140 Y-BP--- 81    lsuto    4  50205788       0     4    106     221
400744f0 --BP--- 83     jsmit    -         0       0     4      0       0
400753b0 ---P--- 86     worth    -         0       0     2      0       0
40075760 L--PR-- 84      jones    3   300b78d8    -1     2      0       0
 13 active, 128 total, 16 maximum concurrent

onstat -k

Locks
address  wtlist   owner    lklist   type    tblsum rowid  key#/bsiz
300b77d0 0        40074140 0        HDR+S   10002  106     0
300b7828 0        40074140 300b77d0 HDR+S   10197  122     0
300b7854 0        40074140 300b7828 HDR+IX  101e4  0       0
 300b78d8 40075760 40074140 300b7854 HDR+X  101e4  100     0
300b7904 0        40075760 0            S   10002  106     0
300b7930 0        40075760 300b7904     S   10197  122     0
 6 active, 5000 total, 8192 hash buckets

To find out the owner of the lock for which session ID 84 is waiting:

Obtain the address of the lock in the wait field (300b78d8) of the onstat -u output.
Find this address (300b78d8) in the Locks address field of the onstat -k output.
The owner field of this row in the onstat -k output contains the address of the user thread (40074140).
Find this address (40074140) in the Userthreads field of the onstat -u output.
The sessid field of this row in the onstat -u output contains the session ID (81) that owns the lock.

To eliminate the contention problem, you can have the user exit the application gracefully. If this solution is not possible, you can stop the application process or remove the session with onmode -z.