Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!in.100proofnews.com!in.100proofnews.com!news.maxwell.syr.edu!sn-xit-03!sn-xit-01!sn-post-01!supernews.com!corp.supernews.com!not-for-mail From: Sybrand Bakker Newsgroups: comp.databases.oracle.misc Subject: Re: How to avoid waiting for locks during delete Date: Fri, 08 Aug 2003 17:09:57 +0200 Organization: Posted via Supernews, http://www.supernews.com Message-ID: Reply-To: postbus@sybrandb.demon.nl References: <3f339d88$1@olaf.komtel.net> X-Newsreader: Forte Agent 1.93/32.576 English (American) MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: abuse@supernews.com Lines: 42 Xref: newssvr20.news.prodigy.com comp.databases.oracle.misc:130375 On Fri, 8 Aug 2003 14:54:32 +0200, "André Hartmann" wrote: >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 >:( > > You can't, unless you use issue explicit lock table .. in row share mode nowait prior to the delete. If you plan to delete all records, you'd better issue truncate table Sybrand Bakker, Senior Oracle DBA To reply remove -verwijderdit from my e-mail address