Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Locking issues caused by self-referencing foreign key

Re: Locking issues caused by self-referencing foreign key

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Tue, 19 Apr 2005 22:25:44 +0100
Message-ID: <afsa615cucoljkqeb908175tre3ok2ph1v@4ax.com>


On 19 Apr 2005 06:38:20 -0700, "Mark D Powell" <Mark.Powell_at_eds.com> wrote:

>>I am probably stating the obvious - it appears that if:
>>1. A table has a self-referencing foreign key
>>2. There is no index on this foreign key
>>then Oracle would allow only one transaction against such table at a
>>time. Correct?
>
>Obviously the FK must reference a UK or a PK either of which would have
>an index used to enforce the constraint so if you perform DML against
>the table why would Oracle need an index on the FK column itself? It
>would be the PK or UK column value that Oracle would have to verify
>against.

 The foreign key must reference a UK or PK, but the column constrained by the foreign key may not be indexed. When the UK or PK column(s) change, Oracle is going to have to do a referential integrity check on any referencED columns; in the absence of an index on the child end of the foreign key, that would seem to inevitably result in a full table scan of the (same) table. Therefore, the question is presumably whether DML modifying values in the table effectively locks the entire table, serialising access to the table?

 Does the following demonstrate that? :

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 );

Table created.

SQL> insert into t values (1, null);

1 row created.

SQL> insert into t values (2, 1);

1 row created.

SQL> insert into t values (3, 1);

1 row created.

SQL> insert into t values (4, 2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t;

      T_ID PARENT_T_ID
---------- -----------

         1
         2           1
         3           1
         4           2

SQL> delete from t where t_id = 3;

1 row deleted.

SQL> delete from t where t_id = 2;

 ... This hangs until the first session is committed.

 That's the "on delete cascade" case. How about updates:

SQL> update t set t_id = 5 where t_id = 4;

1 row updated.

SQL> update t set t_id = 0 where t_id = 1;

 ... hangs until session 1 commits, at which point it fails due to FK violation. So session 1 was blocking the referential integrity check; without the FK, the two statements are independent so wouldn't block each other.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Received on Tue Apr 19 2005 - 16:25:44 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US