RAC internals - GLOBAL ENQUEUE SERVICES DEADLOCK DETECTED
in [Oracle]
|
Prev: SP2-0332: Cannot create spool file
Next: Strange SQL
From: Jonathan Lewis on 3 Nov 2005 07:37 "Dusan Bolek" <spambin(a)seznam.cz> wrote in message news:1131018603.285444.191120(a)f14g2000cwb.googlegroups.com... > Thank you, that looks like something. I will try to catch a deadlock > message in the alert log as quickly as possible, then find user > responsible (via pid) and from V$SQL the statement that could be > causing this trouble. It is not 100% reliable way, but could be > usefule. > > Dusan > If the deadlock is on mode 5 enqueues at both ends, it is most likely to be the foreign key locking issue, possibly with a referential integrity constraint declared as 'on delete cascade' but not covered by a suitable index. Look of foreign keys without indexes, and then look for code that deletes the parent, or updates the columns of the indexes protecting the parent key. -- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html Cost Based Oracle: Fundamentals Now available to pre-order. http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/appearances.html Public Appearances - schedule updated 4th Sept 2005
From: Dusan Bolek on 3 Nov 2005 11:39 Thank you, Jonathan. That looks very interesting. Unfortunately, the code accessing this database is too complex to be investigated in this way. However, I have got from dba_constraints all referential constraints without associated index that were created with ON DELETE CASCADE. There are twelve of them and I will ask the guy responsible for this database to create appropriate indexes for all of them. Maybe that will solve the problem. -- Dusan Bolek
From: Dusan Bolek on 4 Nov 2005 10:45 > 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. The strange thing is that I can find none of the processes that are participating in a deadlock. When checked against V$PROCESS, even immediately after a new dead lock emerges in trace file, there is no row returned from that table. -- Dusan Bolek
From: yong321 on 4 Nov 2005 15:24 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. > > The strange thing is that I can find none of the processes that are > participating in a deadlock. > When checked against V$PROCESS, even immediately after a new dead lock > emerges in trace file, > there is no row returned from that table. > > -- > Dusan Bolek 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
From: dusan75 on 6 Nov 2005 13:46
Yes, I have got info from Oracle that in some cases this numbers should be related to transaction id instead of process id. However, I do not know how it is encoded, because I'm also using Log Miner to find offending SQL, but still with no success. :-( -- Dusan Bolek |