Re: Integrity constraint

"Gerard H. Pille" <ghp_at_skynet.be> wrote in message news: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 Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html