| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: performance when inserting into child tables
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?
 |
 |