Parent Table Not Locked While Foreign Key Constraint Creeted in Oracle 8.0

From: Tom Pall <tom_at_cdproc.com>
Date: Tue, 12 Sep 2000 13:47:05 -0500
Message-Id: <10617.116755@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0018_01C01CBF.EFDF1640 Content-Type: text/plain;

charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

I needed to create a new foreign key constraint between a very volatile = parent table and child table
only changed during an off hours batch job.

I was surprised when I viewed the locks in the system:

A share lock on the child table. A row lock on the exceptions table. But no lock on the parent table.

OK, if Oracle has a share lock against the child and is using a read = consistent view of the parent's primary
key index, cool. Except what if while the constraint is being built = someone deletes or updates the parent table? Would I have a point in time consistent foreign key which is now now = longer consistent? Or does Oracle do some quick checks against the primary key right before finishing the = constraint build?

------=_NextPart_000_0018_01C01CBF.EFDF1640 Content-Type: text/html;

charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Dwindows-1252" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2314.1000" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>I needed to create a new foreign key constraint =
between a very=20
volatile parent table and child table</FONT></DIV>
<DIV><FONT size=3D2>only changed during an off hours batch =
job.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2>I was surprised when I viewed the locks in the=20
system:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2>A share lock on the child table.  A row lock on =
the=20
exceptions table.</FONT></DIV>
<DIV><FONT size=3D2>But no lock on the parent table.</FONT></DIV>
<DIV> </DIV>
<DIV><FONT size=3D2>OK, if Oracle has a share lock against the child and =
is using=20
a read consistent view of the parent's primary</FONT></DIV>
<DIV><FONT size=3D2>key index, cool.  Except what if while the =
constraint is=20
being built someone deletes or updates the parent table?</FONT></DIV>
<DIV><FONT size=3D2>Would I have a point in time consistent foreign key =
which is=20
now now longer consistent?  Or does Oracle do some</FONT></DIV>
<DIV><FONT size=3D2>quick checks against the primary key right before =
finishing=20
the constraint build?</FONT></DIV></BODY></HTML> Received on Tue Sep 12 2000 - 13:47:05 CDT