Monitoring locks

You can analyze information about locks and monitor locks by viewing information in the internal lock table that contains stored locks.

View the lock table with onstat -k. onstat -k output shows sample output for onstat -k.
Figure 1: onstat -k output
Locks
address  wtlist   owner    lklist   type     tblsnum  rowid    key#/bsiz
300b77d0 0        40074140 0        HDR+S    10002    106        0
300b7828 0        40074140 300b77d0 HDR+S    10197    123        0
300b7854 0        40074140 300b7828 HDR+IX   101e4    0          0
300b78d8 0        40074140 300b7854 HDR+X    101e4    102        0
 4 active, 5000 total, 8192 hash buckets
In this example, a user is inserting one row in a table. The user holds the following locks (described in the order shown):
  • A shared lock on the database
  • A shared lock on a row in the systables system catalog table
  • An intent-exclusive lock on the table
  • An exclusive lock on the row
To determine the table to which the lock applies, execute the following SQL statement. For tblsnum, substitute the value shown in the tblsnum field in the onstat -k output. 
SELECT *
   FROM SYSTABLES
   WHERE HEX(PARTNUM) = "tblsnum";
Where tblsnum is the modified value that onstat -k returns. For example, if onstat -k returns 10027f, tbslnum is 0x0010027F.

You can also query the syslocks table in the sysmaster database to obtain information about each active lock. The syslocks table contains the following columns.

Column Description
dbsname Database on which the lock is held
tabname Name of the table on which the lock is held
rowidlk ID of the row on which the lock is held (0 indicates a table lock.)
keynum The key number for the row
type Type of lock
owner Session ID of the lock owner
waiter Session ID of the first waiter on the lock