Re: PK/FK and Locks

If your foreign key column is not indexed, Oracle locks the complete table. If the column is indexed only the affected rows are locked. Try creating an index .

Hth,

Sybrand Bakker, Oracle DBA

Gary Knopp <gknopp_at_ascend.com> schreef in berichtnieuws 8g1867$sdq_at_nntpa.cb.lucent.com...
>
>
> PK/FK and locks
>
> I create a Primary/Foreign key relationship between
> two tables.
>
> T1
> ===========
> key(PK) catg
> --- -----
> 1 Cars
> 2 Sports
> 3 Places
>
>
> T2
> ============
> key(FK) Values
> ------- -------
> 1 Toyota
> 1 Camry
> 2 Baseball
>
>
> + If UserA makes any change to table T2 then
> userB cannot delete any records in T1 until
> UserA commits changes. This seems to be
> true whether the FK in T2 has been modified
> or not.
>
> In the above example if UserA updates record field
> 'Camry' to 'FireBird' in table T2, UserB can't delete
> T1 record 'Places' until UserA commits, even though
> no T2 record references this key. Why?
>
> If the above is true, how can I inform the user that a delete
> cannot be performed on T1 since changes have not
> been committed on T2. I don't want the user
> sitting in a WAIT state forever.
>
> Do I have to do a 'select for update' on every table
> that has a FK constraint on this table?
>
>
>
>
Received on Thu May 18 2000 - 00:00:00 CDT