Re: Locking issues caused by self-referencing foreign key

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Sat, 23 Apr 2005 15:15:22 +0100
Message-ID: <jklk6153rrs7qmetj245n48qphamr6chpa@4ax.com>

On 23 Apr 2005 06:45:29 -0700, "Mark D Powell" <Mark.Powell_at_eds.com> wrote:

>Andy, Vsevolod was referring to "A table has a self-referencing foreign
>key ". The FK is to the same table so it has to be pointing to the
>table's PK or a UK constraint which in turn is indexed.

Yes, that's exactly what I'm talking about too.

SQL> create table t (

  2      t_id number not null,
  3      parent_t_id null,
  4      constraint t_pk primary key (t_id),
  5      constraint t_fk1 foreign key (parent_t_id)
  6          references t (t_id)
  7          on delete cascade

8 );

That's a self-referencing FK, where the _child_ end is not indexed - no index on parent_t_id there.

Clearly the parent end must be indexed as it's a UK or PK, so there's no issue on that end.

But there's no index on parent_t_id above.

e.g. see
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10752/data_acc.htm#10885

"
Choosing Columns and Expressions to Index

Consider indexing foreign keys of referential integrity constraints in cases in which a large number of concurrent INSERT, UPDATE, and DELETE statements access the parent and child tables. Such an index allows UPDATEs and DELETEs on the parent table without share locking the child table. "

OK, but since the foreign key is self-referencing, the parent table _is_ the child table; DML on the parent row involves locking that row in the table, and the referential integrity constraint without indexing the child end results in the whole child _table_ being locked. This results in the OP's question as to whether this situation results in DML on that table always resulting in the whole table being locked, effectively serialising access to the table.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Sat Apr 23 2005 - 09:15:22 CDT