Re: Integrity constraint

From: vm22 <>
Date: Wed, 4 Feb 2009 07:36:35 -0800 (PST)
Message-ID: <>

Sorry Jon - ignore my last post.

It does seem like we are inserting while the delete is running. I am really surprised that Oracle does not ignore the new records that are added but thanks for the great explanation nevertheless.

What is the solution to this - I looked at the asktom but could not find anything.


On Feb 3, 6:08 pm, "Jonathan Lewis" <> wrote:
> "Gerard H. Pille" <> wrote in messagenews:49887dab$0$2870$
> >>>> DELETE FROM x
> >>>> WHERE id NOT IN (SELECT id
> >>>> FROM y)
> >>>> AND id NOT IN (SELECT id
> >>>> FROM z);
> > Is it possible to insert child records when the parent is being deleted?
> It may be possible - but I'd want to set up a very careful test case.
> It all depends on read and write consistency and the amount of time
> it takes to do a bulk delete.
> The delete starts at SCN nnn say, so presumably the
> subqueries used for the NOT IN have to be read consistent to
> SCN nnn.
> But say it takes 60 seconds to work through table X deleting
> all the data, and after 45 seconds a user inserts a row into Y
> that is a child of the last record that ought to have been deleted.
> The parent row in X is not locked and marked as deleted, so
> Y will insert safely, and can be committed.
> The delete reaches the row in X and READ-consistency says
> the row doesn't exist in Y, but when the delete attempt goes
> to CURRENT mode from CONSISTENT mode, the row
> becomes visible and committed - so the delete fails and rolls back.
> Tom Kyte has some examples of 'write consistency' on his AskTom
> site (a couple of years old, now, but you should be able to find
> them with a search).  I don't know if he also worked through this
> type of delete event, though.  In his example a delete without a
> child table could rollback from a write-consistent conflict and
> restart - maybe the presence of the child makes that impossible
> for some reason.
> --
> Regards
> Jonathan Lewis
> Author: Cost Based Oracle: Fundamentals
> The Co-operative Oracle Users' FAQ
Received on Wed Feb 04 2009 - 09:36:35 CST

Original text of this message