If you have access to the Oracle Enterprise Manager toolkit, Lock Manager
shows you who is locking who (and it's easy to use!).
<markp7832_at_my-deja.com> wrote in message news:7qogp1$b49$1_at_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 Tue Sep 07 1999 - 23:20:21 CDT
