Re: How to avoid waiting for locks during delete

On Fri, 8 Aug 2003 14:54:32 +0200, "André Hartmann" <andrehartmann_at_hotmail.com> 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 Received on Fri Aug 08 2003 - 10:09:57 CDT