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
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
IDS OnLine IIF Universal Server
Was this topic helpful?
16 June 2018