RI Table Locking Problem

Hi,

I have an interesting problem at one of my client sites that I hope you can help me with. We are developing a large retail application using Forms 4.0.13 and Oracle 7.1.3. The problem occurs as a result of the referential integrity definition in the database.

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.

After much frustration digging through the Oracle7 manuals, our DBA found that this behavior is documented in section 6-10 of the Application Developer's Guide and occurs when the foreign key columns of table A are not indexed.

Okay, it is good to know why this is happening, but I fear that we could experience performance problems due to index maintenance if we index all foreign key columns in our database. We have some tables with more than 10 foreign key columns.

Has anyone out there experienced this problem and created the indexes on the foreign key columns? Have you experience performance degradation if so? I would be very grateful for any suggestions/experiences.

Thanks in advance,

Chris Cummings                 ** I NEED Oracle Forms 4.0 developers for
Dallas Softworks, Inc.         ** immediate $$contract$$ positions in
Fax (214) 351-2139             ** Dallas.  Please E-mail or fax.