Index on foreign key ---> parent table locking ?????? strange !!!

From: Ashish Sahasra <ashish_at_superlink.net>
Date: 1997/10/26
Message-ID: <6304md$mbb$2@earth.superlink.net>#1/1

I am facing a strange problem.

Let's assume I have two table (famous example)

dept table:

deptno		number			primary key
deptname	varchar2(50)

emp table :
emp 		number 			primary key
empname 	varchar2(50)
deptno 		number 			foreign key refrenceing to dept.deptno
					important : ***without Delete cascade

Scenerio 1:
There is "no index on foreign key" deptno in emp table

Step 1: one user let's say 'scott' tries to insert a employee in emp table with

              deptno 10.
              He fires :  insert into emp values (1001,'Test',10);

Step 2: other user let's say 'tom' tries to delete from dept table, deptno 20

              He fires : delete from dept where deptno = 20;
	  He waits as table dept is locked in share lock mode by 'scott'.
              I don't understand why table dept is 'fully' locked.
              This happens when there is "no index on foreign key" deptno

in emp table.

Scenerio 2:
Now there is a "index on foreign key" deptno in emp table.

Step 1 : one user let's say 'scott' tries to insert a employee in emp table with

              deptno 10.
              He fires :  insert into emp values (1001,'Test',10);

Step 2: other user let's say 'tom' tries to delete from dept table, deptno 20

              He fires : delete from dept where deptno = 20;
	  He is able to delete the row.
	  I don't understand how does index on foreign key 'deptno' affect
              locking parent table.

              Is it a bug or what?

Please answer.

Thanks in advance

Ashish
email: ashish_at_superlink.net Received on Sun Oct 26 1997 - 00:00:00 CDT