Re: RI Table Locking Problem

In article K1x_at_netcom.com, chrisc_at_netcom.com (Chris Cummings) writes:

> I have a Forms application that maintains table A. Table A has several
> foreign key columns that reference parent records in tables B, C, D and
> E. My problem occurs when a user modifies a record in table A, using
> the Forms application, thus acquiring a lock on that record.
> Oracle subsequently places an exclusive table lock on tables B, C, D and
> E. Therefore any user using any application that attempts to lock a row
> of any table that references one of these tables gets hung out to dry
> until the first user commits or rolls back.
>

We tend to use triggers rather than constraints in cases like this to check for record existence in the parent table. This works fine in cases where you are sure that the parent record won't disappear while you're in the middle of the transaction.

Avoid indexing the foreign keys unless the table is very stable (or there is some other justification). In my experience there is a considerable performance penalty in some cases, as you suspect.

Hope this helps!

John Received on Tue Nov 15 1994 - 20:15:35 CET