PK/FK and locks

From: Gary Knopp <gknopp_at_ascend.com>
Date: 2000/05/18
Message-ID: <8g19gf$skr_at_nntpa.cb.lucent.com>#1/1

[Quoted] 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 CEST