Re: Locking: Matching up sessions

From: <markp7832_at_my-deja.com>
Date: Fri, 03 Sep 1999 13:00:24 GMT
Message-ID: <7qogp1$b49$1@nnrp1.deja.com>

In article <37CF7DA4.24C3_at_oriolecorp.com>,   sfaroult_at_oriolecorp.com wrote:
> Ben Ryan wrote:
> > Imagine three sessions are each updating a single row in
> > the table and none of them have yet commited the change.
> > Call the sessions A, B and C. Let us say
> > A is updating r1
> > B is updating r2
> > C is updating r3.
> >
> > Imagine two more sessions called x and y. Which,
> > are both trying to a update a single row in the table
> > and they were both initiated after sessions A, B and C
> > had acquired their locks. Lets us say that
> > x is trying to update r1 and
> > y is trying to update r2.
> >
> > By querying v$lock.id1 I can see how to say that
> > some combination of sessions A, B and C are blocking
> > sessions x and y.
> >
> > Is there anyway to discover that
> > A is blocking x and
> > B is blocking y
>
> If I remember well, in V$LOCK you have not only locks which are held
but
> also those which are requested. There is in $ORACLE_HOME/rdbms/admin
(I
> assume a Unix box!) a script or two which give a better depiction of
> locking than the raw V$LOCK. I think there are utlsomething.sql
scripts,
> assuming Unix once again try a grep -i blocking *.sql on the said
> directory I am sure there is something. You can also search for
scripts
> on a number of sites; we have a monlocks.sql for lock monitoring on
our
> site although to be honest I don't remember what it displays exactly.
> --
> Regards,
>

Since version 7.2 the v$session table has contained columns that nmake up the rowid of a waited row: row_wait_* Try looking at them.

Otherwise here is a script that will show who is waiting for who set echo off
rem
rem lock_blockers.sql
rem
rem SQL*Plus script to produce a report showing all sessions waiting on rem a particular lock under the session holding the lock. rem
rem 19960411 Mark D Powell New lock blocking script based off news-

rem                            group post, YY's script, and Oracle pro-
rem                            vided admin script

rem
set pagesize 30
column username format a12

column sid         format 9990
column type        format a4

column "Mode Held" format a9
column "Mode Req " format a9
break on id1 skip 2 dup
rem

select s.username, s.sid, s.serial#, l.type,
       decode(l.lmode,0,'WAITING'  ,
                      1,'Null'     ,
                      2,'Row Shr'  ,
                      3,'Row Exc'  ,
                      4,'Share'    ,
                      5,'Shr Row X',
                      6,'Exclusive',
                      to_char(lmode))   "Mode Held",
       decode(request,0,'None'     ,
                      1,'Null'     ,
                      2,'Row Shr'  ,
                      3,'Row Exc'  ,
                      4,'Share'    ,
                      5,'Shr Row X',
                      6,'Exclusive',
                      to_char(request)) "Mode Req ",
       l.id1, l.id2
from v$lock l, v$session s
where  (s.sid      =  l.sid
  and   l.request ^= 0 )
  or   (s.sid      =  l.sid

  and l.request = 0
  and l.lmode ^= 4
  and (id1,id2) in (select lk.id1, lk.id2

                     from   v$lock  lk
                     where  lk.request ^= 0
                     and    l.id1       = lk.id1
                     and    l.id2       = lk.id2
                    )
       )

order by l.id1, l.id2, l.request
/
clear breaks

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Sep 03 1999 - 08:00:24 CDT