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

In article <6304md$mbb$2_at_earth.superlink.net>,   "Ashish Sahasra" <ashish_at_superlink.net> wrote:
>
> 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

Hi Ashish,

   That's EXACTLY the way ORacle is supposed to function.

1. When no index on Foreign Key in child table Share Lock on the ENTIRE Parent table till insert on child table is commited. Inserts, Updates & Deletes not allowed on Parent table. but Query allowed.
2. When no index on foreign key. No locks placed on parent table.

  For further details U can refer to Oracle7 Server Application Developers Manual - Integrity Constraints Chapter.

  But I don't know WHY Index is important here - If U come to know, Please tell me.

 Hope this helps.

 Krish.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet