## Locking: Matching up sessions

### Locking: Matching up sessions

Scenario involving just one table containing just three rows,
which I will call r1, r2 and r3.

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

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

### Locking: Matching up sessions

> 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,

Stphane Faroult
Oriole Corporation
Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------

### Locking: Matching up sessions

> > 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
set pagesize 30
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
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

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.

### Locking: Matching up sessions

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.

> > > 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
> set pagesize 30
> 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

> 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.

### Locking: Matching up sessions

If you have access to the Oracle Enterprise Manager toolkit, Lock Manager
shows you who is locking who (and it's easy to use!).

> > > 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
> set pagesize 30
> 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

> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

Hi,

This is Tom Turchioe.  Locklists with zero locks are probably session
lock lists which hold all locks associated with a session.  If they are
system locklists, they won't go away until the server holding them shuts
down.  Unless the Status is "WAIT", don't worry about it.

Tom

--
Posted via http://dbforums.com