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

From: Mike heydon <pgbison_at_iafrica.com>
Date: 1997/10/27
Message-ID: <01bce2cf$534575e0$29af0bc4@mheydon>#1/1

	  I don't understand how does index on foreign key 'deptno' affect


>               locking parent table.

> 

>               Is it a bug or what?

>

No, this is not a bug, it is normal Oracle operation.

The rules for locks that apply to foreign key constraints are as follows:

NO INDEX ON FOREIGN KEY

---

1. An INSERT into the parent does NOT require any locks on the child table.
2. A DELETE or UPDATE on the parent cause the ENTIRE child table to be locked.
3. An INSERT,DELETE or UPDATE on the child table does not require any locks on the parent.

INDEX EXISTS ON FOREIGN KEY

---

1. A DELETE or UPDATE on the parent table does NOT place locks of any kind on the child table.
2. If the child table specifies ON DELETE CASCADE, deletes from the parent table may result in deletes from the child table. In this case, waiting and locking rules are the same as if you deleted from the child table yourself after deleting from the parent table.

Regards,
Mike Heydon   Received on Mon Oct 27 1997 - 00:00:00 CST