Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: performance when inserting into child tables

Re: performance when inserting into child tables

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 21 Apr 2004 19:55:14 +0100
Message-ID: <01eb01c427d2$2ff0c460$7102a8c0@Primary>

The lock is taken acquired only when all current transactions on the child have committed (or rolled back).

Once the lock has been acquired, Oracle knows that there can be no uncommitted transactions on the child that could break integrity, therefore can try to do the update/delete on the parent. As soon as that U/D has completed, the child lock can be released, because every I/U/D for the child from other sessions (which will have been queueing) will now be in the position of:

Session 1: uncommitted update/delete on parent

    Session 2: child row that could be illegal if the

        parent committed - therefore waits in TX/4
        on the undo slot of session 1
    Session 3: child row that could be illegal if the
        parent rolled back - therefore waits in TX/4
        on the undo slot of session 1

Session 1 commits:

    session 2 raises (e.g.) "no parent" error     session 3 continues

It works - but only after you realise that session 1 has to WAIT to acquire the lock. In other words, the logic works, but the practise doesn't (usually).

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

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

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar

Lock is released before commit? That would be something! So, what is to prevent child from inserting a record with an old value of the foreign key, the only one that it can see? It looks like a slight violation of the ACID property. What, exactly, do they mean by "immediately"? If it is "immediately
after the transaction ends", then it's not so new. If the lock is released before the transaction modifying the parent key ends, there are quite few nasty possibilities for breaking the integrity. Do you have any Metalink note? Was the instructor a former second hand car salesman or a real estate agent?



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Apr 21 2004 - 13:52:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US