Foreign Key Locking

I came across a very useful script from Metalink to Check for Foreign Key Locking Issues

Here is a test setup and execution:

create table p1(id1 int);
alter table p1 add constraint pk_p1 primary key(id1);

create table p2(id2 int);
alter table p2 add constraint pk_p2 primary key(id2);

create table p3(id3 int);
alter table p3 add constraint pk_p3 primary key(id3);

create table p4(id3 int, id1 int, id2 int);
alter table p4 add constraint pk_p4 primary key(id3,id1,id2);
alter table p4 add constraint fk_p1 foreign key (id1) references p1(id1);
alter table p4 add constraint fk_p2 foreign key (id2) references p2(id2);
alter table p4 add constraint fk_p3 foreign key (id3) references p3(id3);

create table c1 (id1 int, id2 int, id3 int);
alter table c1 add constraint fk_c1 foreign key (id1,id2,id3) references p4(id1,id2,id3);
create index xc1 on c1(id1,id2,id3);

DEV01:(SCOTT@orcl):PRIMARY> @tfsfkchk.sql
Table dropped.
Table created.
PL/SQL procedure successfully completed.

LINEMSG
----------------------------------------------------------------------------------------------------
Changing data in table P4 will lock table C1
Create an index on table C1 with the following columns to remove lock problem
Column = ID3 (1)
Column = ID1 (2)
Column = ID2 (3)
Changing data in table P4 will lock table C1
Create an index on table C1 with the following columns to remove lock problem
Column = ID3 (1)
Column = ID1 (2)
Column = ID2 (3)
Changing data in table P4 will lock table C1
Create an index on table C1 with the following columns to remove lock problem
Column = ID3 (1)
Column = ID1 (2)
Column = ID2 (3)
Changing data in table PARENT will lock table CHILD
Create an index on table CHILD with the following columns to remove lock problem
Column = ID (1)
Changing data in table DEPT will lock table EMP
Create an index on table EMP with the following columns to remove lock problem
Column = DEPTNO (1)
Changing data in table P1 will lock table P4
Create an index on table P4 with the following columns to remove lock problem
Column = ID1 (1)
Changing data in table P2 will lock table P4
Create an index on table P4 with the following columns to remove lock problem
Column = ID2 (1)
27 rows selected.

Looks like there is a bug where the same recommendation is repeated for table c1.

About these ads