Re: RAC internals - GLOBAL ENQUEUE SERVICES DEADLOCK DETECTED

From: <yong321_at_yahoo.com>
Date: 10 Nov 2005 13:23:12 -0800
Message-ID: <1131657792.064950.107260@g49g2000cwa.googlegroups.com>

Jonathan Lewis wrote:
> <yong321_at_yahoo.com> wrote in message
> news:1131135857.443704.31690_at_g47g2000cwa.googlegroups.com...
> > Dusan Bolek wrote:
> >> > Metalink Note:262226.1 offers some interpretation. Your process 131321
> >> > on node 1 is waiting for a TX lock in mode 5 (the two hex numbers,
> >> > 0x2b90011 and 0x5f20, may be id1 and id2 in v$lock, respectively).
> >> > Process 131317 on the same node is holding it.
> >>
> > You're right. I did some testing and find that the two numbers,
> > [131321,1285], in your case, do not in any way denote a process
> > (Note:262226.1 says the first number is PID). Instead they correspond
> > to transaction_id0 and transaction_id1 of v$ges_blocking_enqueue,
> > respectively (or the same in v$dlm_locks). Documentation says they're
> > lower and upper 4 bytes of the transaction identifier where the lock
> > belongs to. I can't find more information about it. Perhaps for our
> > purpose, we can conceptually think of the combination of the two
> > numbers, i.e. a transaction identifier, as a process identifier.
> >
> > By the way, I do see the SQL involved in the global deadlock (tested in
> > 9.2.0.7.0 on Linux):
> > ...
> > *** 2005-11-04 13:38:33.199
> > user session for deadlock lock 0x7553ab14
> > ...
> > Current SQL Statement:
> > update test set a = :"SYS_B_0" where a = :"SYS_B_1"
> > Global Wait-For-Graph(WFG) at ddTS[0.28] :
> > BLOCKED 0x7553ab14 5 [0xf001d][0x8353],[TX] [2162689,7995] 0
> > ...
> >
> > In any case, follow Jonathan's practical advice.
> >
> > Yong Huang
> >

>

> Given that the documentation and notes are wrong about the
> process id / transaction id - might the comment about the
> mode 5 lock also be wrong ?

>

> Could you see a transaction holding or requesting a TX mode 5
> in the few seconds before the deadlock was reported ?

>
>

> --
> Regards

>

> Jonathan Lewis

>

> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ

>

> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> Cost Based Oracle: Fundamentals

>

> http://www.jlcomp.demon.co.uk/appearances.html
> Public Appearances - schedule updated 4th Nov 2005

I just tested again. I do not see lock mode 5. When the two sessions involving a deadlock were hung, the session that later detected ORA-60 had this in v$lock:

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
--- -- ---------- ---------- ---------- ---------- ----------

---

133 TX 327689 60114 6 0 72 2
133 TM 114196 0 3 0 72 2
133 TX 852009 33959 0 6 60 0

and these in v$ges_blocking_enqueue:

HANDLE GRANT_LEV REQUEST_L OPEN_OPT_DEADLOCK WHICH_QUEUE STATE

-------- --------- --------- ----------------- ----------- -----
68D149C0 KJUSEREX  KJUSEREX                  1           1 GRANTED
68D157E4 KJUSERNL  KJUSEREX                  1           2 OPENING

After this session detected ORA-60, followed by a few seconds delay, the last row in v$lock output (where LMODE=0) is gone and the second row in v$ges_blocking_enqueue is also gone. The lmd0 process trace file shows:

Global Wait-For-Graph(WFG) at ddTS[0.5e] :

BLOCKED 0x68d157e4 5 [0xd0029][0x84a7],[TX] [2621441,14115] 0
BLOCKER 0x6d6964bc 5 [0xd0029][0x84a7],[TX] [1835010,4352] 1
BLOCKED 0x6c633798 5 [0x50009][0xead2],[TX] [1835010,4352] 1
BLOCKER 0x68d149c0 5 [0x50009][0xead2],[TX] [2621441,14115] 0

So you're right. According to v$lock, there's no mode 5 lock involved. In fact, showing 5 on all four lines in this Wait-For-Graph plus the fact that 5 is the only number I see in all WFGs I find makes me wonder if this number is hardcoded in Oracle code.

Yong Huang Received on Thu Nov 10 2005 - 15:23:12 CST