Re: How to avoid waiting for locks during delete

"André Hartmann" <andrehartmann_at_hotmail.com> wrote in message news:<3f339d88$1_at_olaf.komtel.net>...
> 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 only run into this locking issue when you have unindexed foreign keys. go to asktom.oracle.com type in 'index foreign key' he has a script that tells you
which foreign keys are unindexed.

index them. now only the rows in 'question' will have this problem and not the whole table. Received on Mon Aug 11 2003 - 11:44:59 CDT