DB version: 9.2
OS : Solaris 9
There are some blocking sessions in the DB. I have 2 questions on this.
Question1.
For lock type DX (Distributed transaction) , I can see that there are 2 rows for each blocking session . The holder (blocker) session and waiter session.
But for lock type TX, I can see only waiter sessions as shown in the output below. What does this mean ? Why is there no blocking sessions listed for these waiting sessions ?
Question2. The below query provides the SID of the blocking and waiting sessions. But, how can I find V$SESSION.serial# of the blocking session.
I mean how to join v$lock to v$session to get the serial# ?
col sess format a25
set lines 200
select substr(DECODE(request,0,'Holder: ', 'Waiter: ')||sid,1,12) sess, id1, id2, lmode,
request, type, inst_id, ctime
from GV$LOCK
where (id1,id2, type) in
(select id1, id2, type from GV$LOCK where request >0)
order by id1, request;
SESS ID1 ID2 LMODE REQUEST TY INST_ID CTIME
------------------------- ---------- ---------- ---------- ---------- -- ---------- ----------
Holder: 139 0 0 6 0 DX 1 282
Waiter: 221 0 0 0 4 DX 1 252
Waiter: 64 0 0 0 4 DX 1 252
Holder: 98 47 0 6 0 DX 1 282
Waiter: 281 47 0 0 4 DX 1 252
Waiter: 126 47 0 0 4 DX 1 252
Holder: 371 50 0 6 0 DX 1 281
Waiter: 107 50 0 0 4 DX 1 252
Waiter: 34 50 0 0 4 DX 1 252
Holder: 289 65 0 6 0 DX 1 282
Waiter: 296 65 0 0 4 DX 1 252
Waiter: 371 2752537 515724 0 6 TX 1 283
Waiter: 289 2752537 515724 0 6 TX 1 284
Waiter: 139 2752537 515724 0 6 TX 1 284
Waiter: 98 2752537 515724 0 6 TX 1 284
15 rows selected.