PK/FK and Locks

From: Gary Knopp <gknopp_at_ascend.com>
Date: 2000/05/18
Message-ID: <8g1867$sdq@nntpa.cb.lucent.com>#1/1

PK/FK and locks

I create a Primary/Foreign key relationship between two tables.

key(PK) catg

----- 1 Cars 2 Sports 3 Places

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