Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Unindexed Foreign Keys and Locking Problems

If no index is found, two events occur that are somewhat troublesome:  Oracle will perform a table scan on the dependent table to identify matching foreign key values and in releases previous to Oracle9i Release 2, Oracle will issue a share lock on the entire dependent table while the foreign key search is being performed.

The table level share lock prevents transactions from completing DML operations on the child table until the foreign key search is complete. The table level share lock and the poor performance of the table scan combine to create an environment that is ideal for transaction deadlocks.

In Oracle9i Release 2, Oracle will issue and instantly release the table level share lock. This new locking strategy allows Oracle to identify any potential changes occurring on the child table, while the instant release means that DML can resume almost instantly once the parent table modification has started. If multiple rows are updated or deleted in the parent table, Oracle issues a share lock and release on the child table for each row being manipulated in the parent.

This new locking strategy does not completely solve the locking problem with unindexed foreign keys. It is highly recommended that indexes be created on all foreign keys that reference rows in parent tables that have the possibility of being accessed by update and delete statements.
 

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.