Re: problem with foreign key

In article <3bc43555_at_news.iprimus.com.au>, "Howard says...
>
>Hi Thomas,
>
>When I was writing that paper, I was (inadvertently) connected to my 9i
>database -and I couldn't work out why my updates to the parent table
>*weren't* locking the child! I couldn't get myself prevented from updating
>the child for love nor money.
>
>And that is, of course, true for 9i alone when no indexes are present on the
>foreign key..
>
>Your test is slightly unfair (!) in that you are updating the *child* table
>first, causing a lock on the parent table, which then locks the third update

(one of my reasons for following this up is to keep the thread going -- to get more people to hopefully read this and see this quirk so they know about it...)

why is that unfair? (and its not the lock on the parent here that gets in the way -- its the fact the parent CANNOT lock the child for an instant that is the problem).

Since you do not control the order of transactions in your database, updating the child and then the parent and then other session updating the child is perfectly valid -- fair -- and in fact common.

Who says the parent will always be updated first? If someone updates the child, any parent update to the primary key will still block as will any delete on the parent. This in turn will block all other sessions who attempt to update the child.

>out. I agree absolutely that this indicates a continued issue, and an index
>on the child table prevents the problem arising at all, but it wasn't what
>was discussed in my paper (an initiating update to the parent), nor what the
>original poster was posting about. It's instructive nonetheless, and I'll
>include it in a revision to the paper as soon as possible, so thanks for
>pointing it out.
>

sure it is what you had in your paper. You had an update to the parent. I just added "oh yeah, someone else modified the child before that". its exactly the same -- its exactly the same set of circurmstances. If you say "well, you aren't updating the child first" then this entire discussion is moot cause then who cares that the child is locked. You aren't updating it. Since we have no control over the order of transactions -- the child could be updated FIRST which causes the whole problem.

The same guiding rules apply in 9i:

IF you ( update the parent primary key or delete from the parent )

   AND you ( modify the child in any way ) then

    index the foreign key
end if

This case is no different than the case in 8i. It only partially alleiviates the problem -- making it somewhat better but does not remove it by far.

>I agree also that if updates to the parent key are unlikely to happen, then
>there is no need for the index -in fact, I think we can safely agree that
>saying
>so much is a statement of the bleedin' obvious! But in the context of this
>thread, the need is there, as you say. And deletes from a parent table are
>not always as rare as all that.

agreed on that point.

>
>Regards
>HJR
>
>
>
>
>"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
>news:9put8i071g_at_drn.newsguy.com...
>> In article <3bc2c2f8_at_news.iprimus.com.au>, "Howard says...
>> >
>> >Not quite true: a lock of sorts is still taken, but it is a different
>sort
>> >of lock when an index is present than when it is not -and the new lock
>type
>> >is a share lock, so DML on the child table is permitted.
>> >
>> >The general rule is: every column that is declared to use a foreign key
>> >constraint ought to have an index slapped on it in double-quick time,
>> >otherwise complete, exclusive child table locking is the inevitable
>result.
>> >
>>
>> well, not so fast. (i despise rules of thumb as they seem to become laws
>and
>> they are only correct x% of the time)
>>
>> see
>>
>http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:292016138
>754
>>
>> There are cases where not having the index is JUST dandy.
>>
>> If you never update the parents primary key (and why would you) or never
>delete
>> from the parent -- this never happens. In this case, they are deleting --
>so it
>> is called for.
>>
>> Say I have a table of
>>
>> create table lookup_zip_to_state ( zip_code number primary key,
>> state varchar2(2) );
>>
>> and I have many child tables of this lookup. Since the lookup table is
>> virtually imutable (updates so rare), I would not suggest you need to
>index the
>> fkeys -- maintainance of this table is done during off hours. Also, we
>would
>> not update this table and zip codes are not deleted hence the maintanence
>of
>> this table consists of inserts. I might be able to save myself from
>having to
>> create MANY indexes on many large tables in this case.
>>
>> (i read your referenced paper. the issue is somewhat alleviated in 9i,
>not
>> resolved. The lock happens for a shorter duration but still has the
>> ramifications. Try this in 9i
>>
>> create table p ( x int primary key );
>> create table c ( y references p );
>>
>> insert into p values ( 1 );
>> insert into p values ( 2 );
>> commit;
>>
>> Now, in one session: insert into c values (1);
>> In another delete from p where x = 2;
>> In a third insert into c values (1);
>>
>> see what happens. So, in 9i I would say the need is still there)
>>
>>
>> >For other foreign key constraints, check out
>> >http://www.geocities.com/howardjr2000 and look at the "tips" page.
>> >
>> >Regards
>> >HJR
>> >
>> >
>> >"Peter Laursen" <pl_at_mail1.remove.this.stofanet.dk> wrote in message
>> >news:3bc1d6cd$0$11605$ba624c82_at_nntp01.dk.telia.net...
>> >>
>> >> "Rüdiger J. Schulz" <johannes.schulz_at_web.de> wrote in
>> >> message news:3bc1aa64$1_at_netnews.web.de...
>> >> > hi all,
>> >> >
>> >> >
>> >> <SNIP tables>
>> >> > transaction 1: delete a row in the parent table *without*
>> >> a commit
>> >> > transaction 2: wants to insert a row in the CHILD-table
>> >> > *problem*: transaction 2 wait until transaction 1 send a
>> >> commit!!!
>> >>
>> >> > is it true, that oracle lock the whole child-table, while
>> >> deleting a parent-
>> >> > key?
>> >>
>> >> Yes it is true that oracle will lock the table when there is
>> >> NO INDEX on the foreign key.
>> >> create an index on child(pid) and no locks will be taken.
>> >>
>> >> See http://govt.us.oracle.com/~tkyte/ Unindexed Foreign
>> >> Keys
>> >> or
>> >> http://technet.oracle.com/doc/server.815/a68003/01_05dta.htm
>> >> #1574
>> >>
>> >> /Peter
>> >>
>> >
>> >
>>
>> --
>> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
>> Expert one on one Oracle, programming techniques and solutions for Oracle.
>> http://www.amazon.com/exec/obidos/ASIN/1861004826/
>> Opinions are mine and do not necessarily reflect those of Oracle Corp
>>
>
>
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp