Re: How to avoid waiting for locks during delete

"André Hartmann" <andrehartmann_at_hotmail.com> wrote

> 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.... :(

No sure what the logic is behind saying that Oracle is at fault here.

There is a very basic issue at stake here. Data integrity.

You start a transaction on a parent table. Oracle needs to ensure that irrespective of what that transaction does, the child table after the commit on the parent, is not out-of-sync with the parent table or corrupted. Ditto with the transactions on the child table - Oracle needs to ensure the data integrity of the parent table.

Simple example. Let's say you roll your own parent-child implementation using triggers and PL/SQL. (you did btw asked for alternatives - this is one that you can do yourself, foregoing the Oracle fk constraints and cascade deletes).

How would you code the parent table's delete trigger? Remember, you _must_ ensure that after the commit there are no parentless child rows.

Try it - and then you will see that Oracle's behaviour is indeed correct and is the only way that data integrity can be assured.

--
Billy