Re: problem with foreign key

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 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.

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.

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
>
Received on Wed Oct 10 2001 - 06:43:53 CDT