How to avoid waiting for locks during delete

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
:( Received on Fri Aug 08 2003 - 07:54:32 CDT