IBM Support

SQL to identify the users involved in sessions with lock contention

Troubleshooting


Problem

This article provides sample SQL that can be used to diagnose users involved in lock contention issues.

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

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Not Applicable","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"10.0;11.1;11.5;9.4;11.7","Edition":"Workgroup;Enterprise","Line of Business":{"code":"","label":""}}]

Product Synonym

IDS OnLine IIF Universal Server

Document Information

Modified date:
16 June 2018

UID

swg21226344