Re: How to avoid waiting for locks during delete

Hmmmmm,

• actually i dont want to make void the whole table, and even if..... as far as i know, you cannot rollback a "truncate" statement, right ? so it is not transaction safe.... thats unacceptable.
• If you suggest to lock everything explicitely, that means that when issue my delete command i actually have to know all the cascading foreign key relations that need to be traced. but i dont (want) to know that. my data model will develop and get more enhanced in the future... each time i introduce a new foreign key constraint to a base table that is to delete cascadingly, i would have to adjust the code around my delete-command to make additional locks... thats also unacceptable because its a source of errors and iconsistencies.

  really, no other possibility ? That would be VERY disappointing and smash my whole picture of Oracle being so sophisticated when it comes to locking mechanisms.... :(

AH
:(

"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> schrieb im Newsbeitrag news:c7f7jvgu8fp3ri11bastnp9miaecfa9dok_at_4ax.com...
> 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:49:23 CDT