Un-indexed foreign keys - Part I

The question of whether or not foreign keys should be indexed comes up frequently. Un-indexed foreign keys were the biggest single cause of deadlocks that I encounter, due to the fact that an update to a parent tables primary key or the removal of a parent record will place a full table lock on the child table (no modifications to the child table will be allowed until the transaction commits) for the duration of transaction in 8i (until commit) and for the duration of the statement processed (not until commit, for just the statement process) from 9i. This was “better” , but the lock still exists.

The lock in child table locks many more rows then it should, and decrease concurrency. We can see this frequently when people are using tools that generate SQL to modify a table, this tool generates an UPDATE statement that updates every column in the table, regardless of whether  that value was modified or not.  This in effect updates the primary key and locks the child table.

We can see this behavior easily with the below scripts.

rajesh@ORA10G>

rajesh@ORA10G> create table p(x int primary key, y int);

 

Table created.

 

rajesh@ORA10G> create table c(x references p,y int);

 

Table created.

 

rajesh@ORA10G>

rajesh@ORA10G> insert into p values(1,null);

 

1 row created.

 

rajesh@ORA10G> insert into p values(2,null);

 

1 row created.

 

rajesh@ORA10G> commit;

 

Commit complete.

 

rajesh@ORA10G>

rajesh@ORA10G> insert into c values(1,null);

 

1 row created.

 

rajesh@ORA10G>

rajesh@ORA10G> declare

  2     pragma autonomous_transaction;

  3  begin

  4     delete from p where x = 2;

  5     rollback;

  6  end;

  7  /

declare

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at line 4

 

 

 

Now let us see what happens with index on foreign keys in place.

rajesh@ORA10G>

rajesh@ORA10G>

rajesh@ORA10G> set feedback off

rajesh@ORA10G> create table p(x int primary key, y int);

rajesh@ORA10G> create table c(x references p,y int);

rajesh@ORA10G> create index c_idx on c(x);

rajesh@ORA10G> insert into p values(1,null);

rajesh@ORA10G> insert into p values(2,null);

rajesh@ORA10G> commit;

rajesh@ORA10G> set feedback on

rajesh@ORA10G>

rajesh@ORA10G> insert into c values(1,null);

 

1 row created.

 

rajesh@ORA10G> declare

  2     pragma autonomous_transaction;

  3  begin

  4     delete from p where x = 2;

  5     rollback;

  6  end;

  7  /

 

PL/SQL procedure successfully completed.

 

rajesh@ORA10G>

 

With index in place on foreign key column, updates, and deletes on the parent table do not require full table lock on child tables.