SQL to identify the users involved in sessions with lock contention

Resolving The Problem

Q. How can I identify the users causing lock contention problems?

A. In a multi-user Informix® Dynamic Server (IDS) environment where users have their isolation set higher than dirty read, and/or multiple users are performing update activity (i.e. insert, update or delete actions, rather than read-only), multiple users can all be attempting to place mutually exclusive locks on the same record.

You may want to identify the tables/records under contention and reconfigure or change code (possibly using the SET LOCK MODE TO WAIT statement).

Tracing who has which locks and why using onstat involves joining entries from onstat -k, -u and -g sql. As locks are often held for very short periods of time, the evidence can disappear before all the necessary command can be run. The following SQL statements, run against the sysmaster datebase "tables" do all the joining and filtering for you.

This SQL returns information on locks and the users involved:

select t.username waituser, t.sid waitsess, s.username hasuser,
s.sid hassess, l.type locktype, l.dbsname database,
l.tabname table, hex(l.rowidlk) rowid
from sysmaster:syslocks l, sysmaster:syssessions s, sysmaster:syssessions t
where s.sid = l.owner
and l.waiter = t.sid ;


Note: The commented out clause "dbsname <> 'sysmaster'", if un-commented, will avoid returning the shared lock every user places when they connect to a database, and the locks that this monitoring SQL places when running.

The output looks like this:

user   informix
sessn  168
type   S
dbase  sysmaster
table  sysdatabases
rowid  0x00000205

user   informix
sessn  167
type   S
dbase  sysmaster
table  sysdatabases
rowid  0x00000205

user   informix
sessn  173
type   S
dbase  sysmaster
table  sysdatabases
rowid  0x00000201

user   informix
sessn  167
type   X
dbase  stores9
table  state
rowid  0x00000000

user   informix
sessn  173
type   S
dbase  sysmaster
table  sysdatabases
rowid  0x00000205

A variation on the SQL is this:

select trim(s.username)||":"||s.sid||" has "||trim(l.type)||
" lock on "||trim(l.dbsname)||":"||trim(l.tabname)||"-"||hex(l.rowidlk) L
from sysmaster:syslocks l, sysmaster:syssessions s
where s.sid = l.owner
-- and dbsname <> 'sysmaster'
order by 1 ;

Note: The select portion of the query must be entered all on one line, not split over several as it appears here. This query returns the same data as above, but with the columns wrapped with text onto one line per session and lock, like this:

l  informix:167 has S lock on sysmaster:sysdatabases-0x00000205
l  informix:167 has X lock on stores9:state-0x00000000
l  informix:168 has S lock on sysmaster:sysdatabases-0x00000205
l  informix:173 has S lock on sysmaster:sysdatabases-0x00000201
l  informix:173 has S lock on sysmaster:sysdatabases-0x00000205