Tracing lock waits

Sometimes you are faced with the question from your users that their session is hitting a lock wait problem. The users want to know which session is holding a lock causing their session to wait. This can be done simply by using the onstat -u and onstat -k commands. But sometimes this can be a nontrivial task especially if you have a lot of sessions holding many locks and causing other sessions also holding locks to wait.

The SQL script below will try to simplify this task by selecting from sysmaster tables and generate the list of sessions with waiting list. The script can only create up to 2 levels of lock waits.

-- This sql generates 2 temporary tables to determine the
-- lock wait tree of sessions waiting for Locks
-- The output will show the first column as the session
-- holding the lock and 1 or more sessions in the waiting list.
-- Usage:
-- dbaccess sysmaster locks.sql

set isolation to dirty read;

select a.us_sid wsid, a.us_name wname, d.us_sid osid, d.us_name oname
from sysuserthreads a, systxptab b, syslcktab c, sysuserthreads d
where bitval(a.us_flags,'0x4')=1
and a.us_lkwait=c.address
and c.owner=b.address
and b.owner=d.us_address
into temp lcktab;

select b.osid, b.wsid bwsid, a.wsid awsid
from lcktab b left outer join lcktab a
on a.osid=b.wsid
order by 1
into temp lcktab2;

select osid session, bwsid waitlist, awsid _
from lcktab2
where awsid is not null
or (( awsid is null) and
(bwsid not in
(select awsid from lcktab2 where awsid is not null)));


-- Display # of locks held by session

select osid session, count(osid) locks
from lcktab2
where awsid is not null
or (( awsid is null) and
(bwsid not in
(select awsid from lcktab2 where awsid is not null)))
group by osid;

Sample output:

=================================================
$ dbaccess sysmaster locks.sql

Database selected.


Isolation level set.


5 row(s) retrieved into temp table.


6 row(s) retrieved into temp table.



session waitlist _

29 31 33
29 31 145
29 80
31 33 167

4 row(s) retrieved.

session locks

29 3
31 1

2 row(s) retrieved.

Database closed.


=================================================