Re: Locking: Matching up sessions

From: Ben Ryan <benryan_at_my-deja.com>
Date: Sun, 05 Sep 1999 01:36:40 GMT
Message-ID: <7qshf8$2t9$1@nnrp1.deja.com>

Thanks for the replies, I think I have got it now. [N.B. The script is called $ORACLE_HOME/rdbms/admin/catblock.sql] Looking through this script I see that when a table row is locked two rows appear in v$lock one of type TM and one of type TX. If another session attempts to update the same row then two more entries appear in v$lock again with types TM and TX. Matching the blocking session to the waiting session is done by joining the two TX entries (via id1 and id2).

My mistake was that my query was only selecting the TM entries from v$lock. (I need the TM entries to link to the all_objects table, so that I can show the name of the table).

Thanks for the help.

In article <7qogp1$b49$1_at_nnrp1.deja.com>,   markp7832_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sat Sep 04 1999 - 20:36:40 CDT