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.


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

Tracing remote clients' IP address

Getting a lot of listener-thread errors and you want to find out where these connections are coming from? The xtrace utility of IDS allows you to enable tracing of specific components within the engine. This should only be enabled if the problem can be reproduced or happens at regular intervals.

To enable xtrace for IP tracing:

xtrace heavy -c XTF_IPTRACE -f XTF_SYSCALLS
xtrace size 50000
xtrace on

To disable xtrace, use:

xtrace off

To dump the trace, use any of the commands below:

xtrace fview
xtrace rview

Monitoring temporary dbspaces

This script was created by my colleague. The objective is to dump out onstat -d outputs whenever the free spaces in the temporary dbspaces is running low. The script can be modified to change the duration and the amount of free space left before it starts dumping outputs of onstat -d.


chk_space.sh
=================================================
#!/bin/sh
i=0
while [ $i -lt 3600 ]
do
onstat -d | awk -f t_flex.awk |tee ./tt$$.out
fsum=`cut -f 5 -d " " ./tt$$.out`
if [ $fsum -lt 1000000 ]
then
echo "Space is being monitored" $fsum
date >> chk_tmp_$$.out
onstat -d >> chk_tmp_$$.out
fi
i=`expr $i + 1`
echo "Finish Loop: " $i
sleep 1
done
rm ./tt$$.out

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

t_flex.awk
=================================================
/dev\/rawlinks\/temp/ { ssum+=$5;fsum+=$6 }
END { print "Total:",ssum," Free:",fsum }
=================================================

/dev\/rawlinks\/temp/ is the chunk path of temporary dbspaces.

Detecting long transactions

SQL to detect how long a transaction has been open and displays all transactions that have less than a percentage amount left of logs before going into long transaction.

database sysmaster;
set isolation to dirty read;
select a.sid, b.longtx, b.logbeg, c.cf_effective ltxhwm,
d.cf_effective logfiles, e.uniqid currlog,
round(((e.uniqid - b.logbeg)/d.cf_effective)*100) pctused
from sysrstcb a, systxptab b, syscfgtab c, syscfgtab d, syslogs e
where a.address>0
and a.sid>0
and b.logbeg>0
and c.cf_name="LTXHWM"
and d.cf_name="LOGFILES"
and e.is_current=1
and c.cf_effective - round(((e.uniqid - b.logbeg)/d.cf_effective)*100) <= 10
and a.address=b.owner
order by pctused desc

The query will display the following columns:

sid - session id of the user thread
longtx - 1 if in long transaction already
logbeg - begin log of this transaction
ltxhwm - long transaction high water mark set in onconfig
logfiles - number of logical logs
currlog - current log used by the instance
pctused - percentage of the total number of logs (i.e. logfiles) from logbeg to currlog of this transaction

The output will be displayed in the order of pctused. Only those transactions that have 10% or less amount of logs remaining before reaching the LTXHWM will be displayed. You can change this condition by changing the following line:

and c.cf_effective - round(((e.uniqid - b.logbeg)/d.cf_effective)*100) <= 10 (change to 20, 30, etc, this is the percentage left before reaching LTXHWM)

Locked databases

SQL to get the session/thread holding a lock on a database.

dbaccess sysmaster;
set isolation to dirty read;

select b.name database_name, "HDR+"||a.type lock_type,
c.us_sid session_id, c.us_name owner,
d.th_id tid, d.th_name thread
from syslocks a, sysdatabases b, sysuserthreads c, systhreads d
where a.rowidlk=b.rowid
and a.owner=c.us_sid
and c.us_mttcb=d.th_addr
and b.name="<database name>";

For example:

select b.name database_name, "HDR+"||a.type lock_type,
c.us_sid session_id, c.us_name owner,
d.th_id tid, d.th_name thread
from syslocks a, sysdatabases b, sysuserthreads c, systhreads d
where a.rowidlk=b.rowid
and a.owner=c.us_sid
and c.us_mttcb=d.th_addr
and b.name="stores1"

-----

database_name stores1
lock_type HDR+S
session_id 97
owner informix
tid 140
thread sqlexec