Re: Integrity constraint
Date: Wed, 4 Feb 2009 07:36:35 -0800 (PST)
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" <jonat..._at_jlcomp.demon.co.uk>
> "Gerard H. Pille" <g..._at_skynet.be> wrote in messagenews:49887dab$0$2870$ba620e4c_at_news.skynet.be...
> >>>> 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.
> Jonathan Lewishttp://jonathanlewis.wordpress.com
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Wed Feb 04 2009 - 09:36:35 CST