From oracle-l-bounce@freelists.org Mon Jun 27 04:12:46 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j5R9CkvU015310 for ; Mon, 27 Jun 2005 04:12:46 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180] (may be forged)) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j5R9CgIP015298 for ; Mon, 27 Jun 2005 04:12:42 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5918E1C46BA; Mon, 27 Jun 2005 03:09:12 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20081-09; Mon, 27 Jun 2005 03:09:12 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D96EC1C4809; Mon, 27 Jun 2005 03:09:11 -0500 (EST) Mime-Version: 1.0 (Apple Message framework v622) In-Reply-To: <1119715976l.4712l.0l@medo.noip.com> References: <161657AE46CE0740BAC823B28AE90AFC1F2977@CERO-EXBE-01.USG.NET> <1119678424l.9138l.0l@medo.noip.com> <1119715976l.4712l.0l@medo.noip.com> Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: From: Karthik Subject: Re: ORA-600 Deadlock Issues Date: Mon, 27 Jun 2005 13:41:41 +0530 To: oracle-l@freelists.org X-archive-position: 21703 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: karthik@apple.com Precedence: normal Reply-To: karthik@apple.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-4.9 required=5.0 tests=AWL,BAYES_00 autolearn=ham version=2.63 The issue turned out to be a very interesting one. try this. open two db sessions. (session 1) create table p( x number primary key); create table c( x number references p); insert into p values(1); insert into c values(1); insert into p values (2); insert into c values(2); insert into p values(3); insert into c values(3); commit; (session 1) update p set x = 2 where x = 2; (session 2) update p set x = 1 where x = 1; (session 1) -- this will hang update c set x = 2 where x = 2; (session 2) update c set x = 1 where x = 1; you will find session 1 comes out with deadlock detected error. now create index c_idx on c(x); try the same test case and the hang will be gone. look up chapter 7 of tom kytes' book - Indexes on Foreign Keys. (page 142 in my book). "The number one cause of deadlocks in the Oracle database, in my experience, is unindexed foreign keys" - says the Guru himself. Thanks, Karthik On 25-Jun-05, at 9:42 PM, Mladen Gogala wrote: > > On 06/25/2005 03:04:25 AM, Egor Starostin wrote: > >> Are you sure that your first session doesn't receive ORA-60? >> Probably, you just don't look in first session output. >> I reproduced you case and ORA-60 was generated (which is expected). >> >> Note that hanganalyze in your case shows lock, not a deadlock. >> Deadlocks are printed in 'Cycles' section of hanganalyze trace file. >> Sessions from 'Open Cahins' section are just locks. > > That is correct, my first session did receive ora-0060 but I wasn't > able to > type fast enough. Yes, when I come to think of it, oracle will break > the > chain as soon as it discovers it. Hanganalyze will not have time enough > to catch it. > -- > Mladen Gogala > Oracle DBA > > > -- > http://www.freelists.org/webpage/oracle-l > -- http://www.freelists.org/webpage/oracle-l