Re: How to avoid waiting for locks during delete

Hi,

  I am not saying there is a fault in Oracle. Of course child rows must be deleted as well. Thats the point of consistency.

  What I am sort of unhappy with is that in some circumstances (SELECT .. FOR UPDATE NOWAIT) Oracle is able to tell you when there is a LOCK and prevent you from waiting for an uncertain amount of time but instead returning an error message... and in some other circumstances (cascading delete) Oracle does NOT tell you that but you are stuck with a wait, which is nasty.

Andre
:)

"Billy Verreynne" <vslabs_at_onwe.co.za> schrieb im Newsbeitrag news:1a75df45.0308102242.2a6e38e6_at_posting.google.com...
> "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
Received on Tue Aug 12 2003 - 03:58:35 CDT