Locking: Matching up sessions

Locking: Matching up sessions

Post by Ben Rya » Sun, 31 Dec 1899 09:00:00



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

Post by Stephane faroul » Sun, 31 Dec 1899 09:00:00



> 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

Post by markp7.. » Sun, 31 Dec 1899 09:00:00





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

 
 
 

Locking: Matching up sessions

Post by Ben Rya » Sun, 31 Dec 1899 09:00:00


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

Locking: Matching up sessions

Post by Ryan Pritchar » Sun, 31 Dec 1899 09:00:00


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

 
 
 

1. Sessions in the lock list with no locks (0,0)

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

2. TN-Memphis-64603--ORACLE-Database Applications-Project Management-U.S. Citizen-DBA Administrator

3. zombie server processes

4. Multiple query lock-ups

5. Oracle 7.3.2.3 and SCO OS5.04

6. Version 6.5 lock ups...........

7. OH-Columbus-89397--COBOL-UNIX-MVS-Shell Prog. Lang-JCL-ORACLE-Programmer

8. SQL server lock ups

9. System lock-ups

10. MS SQL6.5 Lock ups

11. Problem with Sybase and PowerBuilder (Lock-ups!)

12. 4.5 & Novell lock-ups