Home » SQL & PL/SQL » SQL & PL/SQL » Getting session tree lock (10.2.0.4)
Getting session tree lock [message #433841] Fri, 04 December 2009 08:34 Go to next message
Michel Cadot
Messages: 58602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm trying to get the lock tree (tree of sessions holding or waiting for a lock) but currently fail.
I read Natalka Roshak's article at http://www.orafaq.com/node/854, some threads in OTN forum, search on Google but nothing seems to have be published.

Here's a simple test case:
Session 1 (SID=24 in below statements):
create table t1 (col integer);
create table t2 (col integer);
insert into t1 values(0);
insert into t2 values(0);
commit;
update t1 set col=1;
update t2 set col=1;
-- Do not commit

Session 2 (SID=26)
update t2 set col=2;
-- Here it is blocked

Session 3 (SID=37)
update t2 set col=3;
-- Here it is blocked

Session 4 (SID=20)
update t1 set col=4;
-- Here it is blocked

So tree logically is:
Ses1 (24) (holder)
  Ses2 (26) (waits on t2)
    Ses3 (37) (waits on t2)
  Ses4 (20) (waits on t1)

dba_waiters view is of no help (at least in 10.2.0.4):
SQL> col MODE_REQUESTED format a12
SQL> col MODE_HELD format a9
SQL> col LOCK_TYPE format a12
SQL>     select 
  2             waiting_session, holding_session, lock_type,
  3             mode_held, mode_requested, lock_id1, lock_id2
  4      from dba_waiters order by 1,2, 3, 4
  5  /
WAITING_SESSION HOLDING_SESSION LOCK_TYPE    MODE_HELD MODE_REQUEST   LOCK_ID1   LOCK_ID2
--------------- --------------- ------------ --------- ------------ ---------- ----------
             20              20 Transaction  None      Exclusive        196609      19474
             20              24 Transaction  Exclusive Exclusive        196609      19474
             20              26 Transaction  None      Exclusive        196609      19474
             20              37 Transaction  None      Exclusive        196609      19474
             26              20 Transaction  None      Exclusive        196609      19474
             26              24 Transaction  Exclusive Exclusive        196609      19474
             26              26 Transaction  None      Exclusive        196609      19474
             26              37 Transaction  None      Exclusive        196609      19474
             37              20 Transaction  None      Exclusive        196609      19474
             37              24 Transaction  Exclusive Exclusive        196609      19474
             37              26 Transaction  None      Exclusive        196609      19474
             37              37 Transaction  None      Exclusive        196609      19474

12 rows selected.

It gives all combinations from waiters to all other ones: 3*4=12 rows, including on themselves (quite a bug or I don't understand the purpose of this view)!

The standard query (or any of its derivatives) to get who blocks who is of no help:
SQL> Col sess format a20 heading "User"
SQL> Col ib              heading ""
SQL> Col sid             heading "Sid"
SQL> select (select username from v$session where sid=a.sid) sess,
  2         a.sid,
  3         ' is blocking ' ib,
  4         (select username from v$session where sid=b.sid) sess,
  5         b.sid
  6  from v$lock a, v$lock b
  7  where a.block = 1
  8  and b.request > 0
  9  and a.id1 = b.id1
 10  and a.id2 = b.id2
 11  /
User                        Sid               User                        Sid
-------------------- ---------- ------------- -------------------- ----------
OPS$MCADOT                   24  is blocking  OPS$MCADOT                   20
OPS$MCADOT                   24  is blocking  OPS$MCADOT                   26
OPS$MCADOT                   24  is blocking  OPS$MCADOT                   37

3 rows selected.

It shows all sessions are waiting on the first one which is not (directly) true: if I commit session 1, session 3 is still blocked by session 2, so I want to see it waiting on this latter one.
Oracle standard locktree script (utllockt.sql) gives the same thing.

The following script published in OTN forum (sorry I don't remember who did it) is interesting but does not give the correct answer:
SQL> BEGIN
  2  dbms_output.enable(1000000);
  3  for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner, 
  4                  b.object_name object_name, b.object_type object_type
  5                  FROM v$locked_object a, dba_objects b
  6         WHERE xidsqn != 0
  7      and b.object_id = a.object_id)
  8  loop
  9      dbms_output.put_line('.');
 10      dbms_output.put_line('Blocking Session   : '||do_loop.session_id);
 11   dbms_output.put_line('Object (Owner/Name): '||do_loop.owner||'.'||do_loop.object_name);
 12      dbms_output.put_line('Object Type        : '||do_loop.object_type);
 13  
 14   for next_loop in (select sid from v$lock
 15                   where id2 = do_loop.xidsqn
 16       and sid != do_loop.session_id)
 17  
 18   LOOP
 19        dbms_output.put_line('Sessions being blocked   :  '||next_loop.sid);
 20      end loop;   
 21  end loop;
 22  
 23  END;
 24  /
.
Blocking Session   : 24
Object (Owner/Name): OPS$MCADOT.T1
Object Type        : TABLE
Sessions being blocked   :  20
Sessions being blocked   :  26
Sessions being blocked   :  37
.
Blocking Session   : 24
Object (Owner/Name): OPS$MCADOT.T2
Object Type        : TABLE
Sessions being blocked   :  20
Sessions being blocked   :  26
Sessions being blocked   :  37

PL/SQL procedure successfully completed.

There it says that session 20 (my session 4) is waiting on T2 which is wrong as it just does an update on T1.

If anyone has an idea...

Regards
Michel
Re: Getting session tree lock [message #433843 is a reply to message #433841] Fri, 04 December 2009 08:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is it certain that when a blocking lock is released, the first session that got blocked is the one to gain access to the object?

I thought that it was less deterministic than that.
Re: Getting session tree lock [message #433844 is a reply to message #433843] Fri, 04 December 2009 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good question.
I know it is wrong and totally undeterministic for latches but I had the idea locks were in chained structures. Will check it.
Thanks for the remark.

Regards
Michel
Re: Getting session tree lock [message #433990 is a reply to message #433843] Sun, 06 December 2009 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For those who are interested, after reading about 100 Metalink notes yesterday, I can confirm that locks are in linked lists (sorry can't currently give you the note number as MOS is down).
Despite my tests I was unable to find these links in all X$ tables I could find more or less related to locks (x$kdnssf x$ksqeq x$ktadm x$ktatrfsl x$ktatl x$ktstusc x$ktstuss x$ktstusg x$ktcxb x$ktatrfil).
This does not surprised me as in the tests I have made many years ago I could see that these kinds of links are not exposed in x$ tables and can only be seen with a memory dump.

I will continue my tests and keep you informed.

Regards
Michel
Re: Getting session tree lock [message #434239 is a reply to message #433990] Tue, 08 December 2009 01:38 Go to previous message
Michel Cadot
Messages: 58602
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Continuing my investigation I complexify the example with 7 sessions and 3 tables (the last one to study the effect of "lock table" statement) as follow:
-- Session 1, Sid = 24
drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
create table t1 (col integer);
create table t2 (col integer);
create table t3 (col integer);
insert into t1 values(0);
insert into t1 values(1);
insert into t2 values(0);
insert into t2 values(1);
commit;
lock table t3 in share mode;
update t1 set col=1 where col=1;
update t2 set col=1 where col=1;

-- Session 2, Sid = 26
update t2 set col=2 where col=0;
update t1 set col=2 where col=1;

-- Session 3, sid = 37
update t2 set col=3 where col=0;

-- Session 4, Sid = 20
update t1 set col=4 where col=1;

-- Session 5, Sid = 12
update t2 set col=5 where col=1;

-- Session 6, Sid = 23
insert into t3 values(0);

-- Session 7, Sid = 22
lock table t3 in exclusive mode;

The logical graph is now the following one:
Ses1 (sid 24)
  +-- Ses2 (26)
     +-- Ses3 (37)
     +-- Ses4 (20)
  +-- Ses5 (12)
  +-- Ses6 (23)
     +--Ses7 (22)


v$lock gives the following (for the moment I limit my investigation to TM and TX locks):
select sid, type, id1, id2, lmode, request, block
from v$lock
where type in ('TM','TX')
order by sid, lmode, request
/

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        12 TX     458772      14392          0          6          0
        12 TM      90762          0          3          0          0
        20 TX     458772      14392          0          6          0
        20 TM      90761          0          3          0          0
        22 TM      90763          0          0          6          0
        23 TM      90763          0          0          3          0
        24 TM      90762          0          3          0          0
        24 TM      90761          0          3          0          0
        24 TM      90763          0          4          0          1
        24 TX     458772      14392          6          0          1
        26 TX     458772      14392          0          6          0
        26 TM      90762          0          3          0          0
        26 TM      90761          0          3          0          0
        26 TX     131092      19501          6          0          1
        37 TX     131092      19501          0          6          0
        37 TM      90762          0          3          0          0

And the classic "blocking query":
Col ib              heading ""
Col sid             heading "Sid"
select a.sid, ' is blocking ' ib, b.sid
from v$lock a, v$lock b
where a.block = 1
  and b.request > 0
  and a.id1 = b.id1
  and a.id2 = b.id2
/

       SID IB                   SID
---------- ------------- ----------
        24  is blocking          20
        24  is blocking          26
        24  is blocking          22
        24  is blocking          12
        24  is blocking          23
        26  is blocking          37

For those with old version which has not the "block" column replace "a.block = 1" by "a.lmode > 0 and a.request=0".

Now as T. Kyte said in an old topic in AskTom (quoting by memory): "as soon as Oracle sees the row you want to lock is already locked, you no more wait for the row but for the transaction holding it" and this is quite true and explain why you have no information about the "actual" graph.
So wanting to enhance the classic query to get for which actual object the waiting sessions are waiting I created the following query. Note that the session are (mostly) waiting on transactions given by the TX locks when wanted objects are pointed by TM locks but sometimes a session has a TM lock without a TX one.
This is the case of session 6 (sid 23) because the lock comes from a "lock table" statement which does not require rollback data at this point and session 7 (sid 22) because the first statement is blocking and this is an insert and so rollback data is not needed yet.
col line format a80
with 
  lock1 as (  -- Search blockers and waiters
    select a.sid blocker, b.sid waiter, a.type,
           decode(a.type,
                  'TM', decode(o.object_name, 
                               NULL, to_char(a.id1),
                               o.owner||'.'||o.object_name||
                                 decode(o.subobject_name, 
                                        NULL, '',
                                        '.'||o.subobject_name))
                  ) object
    from v$lock a, v$lock b, dba_objects o
    where a.block = 1
      and b.request > 0
      and b.id1 = a.id1
      and b.id2 = a.id2
      and b.type = a.type
      and a.type in ('TX','TM')
      and o.object_id (+) = a.id1
  ),
  lock2 as ( -- Search associated TM lock and object
    select to_char(l.blocker) blocker, l.waiter,
           decode(l.type,
                  'TM', l.object,
                  o.owner||'.'||o.object_name||
                    decode(o.subobject_name, 
                           NULL, '',
                           '.'||o.subobject_name)
                 ) object
    from lock1 l, v$lock c, v$session s, dba_objects o
    where c.sid (+) = l.waiter
      and c.type (+) = 'TM'
      and s.row_wait_obj# (+) = c.id1
      and s.sid (+) = c.sid
      and o.object_id (+) = s.row_wait_obj#
      and ( l.type = 'TM'              -- Previous row already a TM
          or c.sid is null             -- No TM found
          or o.object_name is not null -- if TM then object in v$session
          )
    order by 1, 2
  ),
  top_blockers as (
    select distinct l.blocker
    from lock1 l
    where blocker not in (select waiter from lock2)
  )
-- Now build hierarchy
select lpad(' ',3*(level-1))||l.blocker||' <- '||l.waiter|| ' / '||l.object line
from lock2 l
connect by prior waiter = blocker
start with blocker in (select blocker from top_blockers)
/

LINE
---------------------------------
24 <- 12 / OPS$MCADOT.T2
24 <- 20 / OPS$MCADOT.T1
24 <- 22 / OPS$MCADOT.T3
24 <- 23 / OPS$MCADOT.T3
24 <- 26 / OPS$MCADOT.T1
   26 <- 37 / OPS$MCADOT.T2

6 rows selected.


Next step is finding the actual graph but I put this one as it could be useful in most of the cases (and my customer is satisfied with this level of information Wink ).
By the way, MOS finally got up and I could retrieve the note stated locks are in linked lists: "Tracing sessions: waiting on an enqueue" [ID 102925.1].
Now which session in the list Oracle takes when a lock is released is not indicated but we can make the following remarks:
* There are 2 kind of linked lists: LIFO and FIFO
* There is no reason Oracle chooses to lose cpu cycles searching a session in the middle of the list
* So either it takes the first one or the last one
* I assume the list is FIFO and so the first one is taken.
Still now all my tests never showed this assumption was false, it is obviously not a proof but I will work on this; if anyone has seen this is false in a case, please let me know and I will investigate deeper (if I can as I have no access to Oracle code).

Regards
Michel

[Updated on: Tue, 08 December 2009 10:24]

Report message to a moderator

Previous Topic: copy a row and insert into same table
Next Topic: calculate cummulative frequency
Goto Forum:
  


Current Time: Mon Jul 28 08:39:47 CDT 2014

Total time taken to generate the page: 0.10373 seconds