Re: problem with foreign key

"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message news:9q1h1n0e2t_at_drn.newsguy.com...
> 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...)
>

Good idea, actually.

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

It's unfair in the sense that it wasn't the issue I was addressing in my paper, nor the issue that the original poster was talking about. It's highly instructive of course, but its a separate problem from what was actually being discussed originally.

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

Yes, but it wasn't what was being discussed. That's my only point.

>
> Who says the parent will always be updated first?

The original poster *was* updating the parent first, and so was my paper!

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

No it's not. The fact that you "added" something makes it different! The paper states that an index on a child table is no longer necessary in order to prevent an update to a parent table locking the entire child table. In that order, and without any additional complications. That happens to be because there is a great splodge of material on the DBA course for 8i which states that an index is required to avoid just *that* situation, and that is what I was addressing (and happens to be precisely what our original poster was experiencing).

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

The person who approaches the database second and expects to be able to do DML on the child! Scenario: Mary updates customer parent table, gets overcome with emotion and decides to pop out for a smoke for ten minutes. During that 10 minutes, the 300 people who have just arrived at work intending to raise sales orders experience wailing and gnashing of teeth because the system appears to have hung. With 9i, their is no need to visit their dentist afterwards.

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

Not in the Oracle 9i DBA Fundamentals Part I and Oracle 9i New Features course material they don't! (I suspect we may be talking real world versus class room stuff here. Fair enough -I learn from the real world and try and make the classroom better as a result, so I *am* taking all this on board!).

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

That sounds suspiciously like a rule of thumb! I'd buy it if we were anticipating simultaneous (or near-simultaneous) and continuous updates of both tables. The scenario I was addressing was a rare-ish update to the parent which is inadvertently left uncommitted for a time -at enormous expense in 8i, at practically no expense in 9i.

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

Our scenarios *are* different, but I agree that I have addressed a special case, and not the general, and I'll be taking that into account ASAP (in fact, I have the Foreign Key section to teach on a 9i New Features course just this morning!).

Many Thanks
HJR
> >
> >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:29201613
8
> >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
>
Received on Wed Oct 10 2001 - 16:23:24 CDT