problem with lockwait query
AIX 5.2 IDS 9.40.FC3
I've been using the following query for a few years to identify user
sessions who are waiting on locks. It always ran fine in 7.31. Think it
did anyway, I don't have a 7.31 instance any more to check. Since I went to
9.40 the query no longer returns sessions which are not the next on the list
for a locked resource. I modified the query where you see the original
lines commented out below. It returns the correct data in a test with one
session holding a lock (1862) and two sessions waiting for that lock (1878
and 1879). Without the changes it returns only the first session waiting on
the lock(1878).
Can anyone tell me what the rows with a tabname of 'a' are? I'd guess that
they have something to do with the sysmaster query I am running at the time.
The session id increments for the owner each time I run it. Is is safe to
add "where tabname != 'a'" to the query and assume I'll be getting the
correct results?
Regards,
Bill
select dbsname,
b.tabname,
rowidr,
keynum,
e.txt type,
d.sid owner,
hex(d.address) ownrstcb,
g.username ownname,
j.pid ownpid,
f.sid waiter,
hex(f.address) waitrstcb,
h.username waitname,
i.pid waitpid,
a.type typeint,
i.is_wlock
from syslcktab a,
systabnames b,
systxptab c,
sysrstcb d,
sysscblst g,
flags_text e,
sysrstcb f , sysscblst h, syssessions i, syssessions j
where a.partnum = b.partnum
and a.owner = c.address
and c.owner = d.address
and (a.wtlist = f.address or bitval(f.flags, '0x4') = 1)
-- and a.wtlist = f.address
and d.sid = g.sid
and e.tabname = 'syslcktab'
-- and e.flags = a.type
and (e.flags = a.type and a.type = 9)
and f.sid = h.sid
and h.sid = i.sid
and d.sid = j.sid
into temp A;
select
tabname[1,18],
type[1,4],
owner,
ownname[1,8],
waiter,
waitname[1,8],
typeint
from A;
devldb:[/usr/informix]> dbaccess sysmaster lockwait
Database selected.
4 row(s) retrieved into temp table.
tabname type owner ownname waiter waitname typeint
tab1 X 1862 informix 1878 informix 9
tab1 X 1862 informix 1879 informix 9
a X 1886 informix 1878 informix 9
a X 1886 informix 1879 informix 9
4 row(s) retrieved.
Database closed.
devldb:[/usr/informix]>
sending to informix-list