Re: Integrity constraint

From: vm22 <vivekmarwaha_at_gmail.com>
Date: Wed, 4 Feb 2009 07:36:35 -0800 (PST)
Message-ID: <00c5c91c-15bc-4c8d-b835-d04c7b939caa_at_e1g2000pra.googlegroups.com>


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.

Regards,
Vivek

On Feb 3, 6:08 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "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.
>
> --
> Regards
>
> 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

Original text of this message