From: "André Hartmann" <andrehartmann@hotmail.com>
Newsgroups: comp.databases.oracle.misc
Subject: How to avoid waiting for locks during delete
Date: Fri, 8 Aug 2003 14:54:32 +0200
Lines: 30
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 217.9.112.74
X-Original-NNTP-Posting-Host: 217.9.112.74
Message-ID: <3f339d88$1@olaf.komtel.net>
X-Trace: olaf.komtel.net 1060347272 217.9.112.74 (8 Aug 2003 14:54:32 +0200)
Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news2.telebyte.nl!news.tele.dk!news.tele.dk!small.news.tele.dk!renate.komtel.net!olaf.komtel.net!not-for-mail
Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:130369

Hi everyone,

  I have a problem with waiting for release of locked rows during a
cascading delete. I am aware of the NOWAIT option but it is not available to
a DELETE statement. Consider this to see my problem:

create table a (Id INT primary key, Name VARCHAR2(100));
create table b (Id INT primary key, MyA INT REFERENCES a(Id) ON DELETE
CASCADE);
insert into a values (1, 'hello');
insert into b values(5, 1);
commit;

now session 1 does this:

delete from b where Id = 5; //no commit!!!!

now session 2 does this:

delete from a;
//session 2 is stuck here and has to wait
//for session 1 to commit or rollback because of the cascading
// delete in table b. I would like to receive an error like when you
// do SELECT ... FOR UPDATE NOWAIT. How can i do that ??

AH
:(