Increase Concurrency By Indexing Foreign Key

Concurrency Control, Indexes, and Foreign Keys

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.

No Index on the Foreign Key

In the following circumstances, the database acquires a table lock on the child table:

• No index exists on the foreign key column of the child table. For example, assume that hr.departments table is a parent of hr.employees, which contains the unindexed foreign key department_id.

• A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges data into the parent table. Inserts into the parent table do not acquire table locks on the child table. For example, a database session deletes row 3 from the departments table.

In above diagram, an unindexed foreign key column in the child table causes the deletion of row 3 in the parent to acquire a share table lock on the child table. This lock enables other transactions to query but not update the table. For example, phone numbers in employees cannot be updated while the departments row is being deleted. The table lock releases immediately after the DML on the departments table completes. If multiple rows are affected, then the lock is obtained and released once for each row.

Note: DML on a child table does not acquire a table lock on the parent table.


Index on the Foreign Key

If a foreign key column in the child table is indexed, then DML on the parent table acquires a table lock on the parent table. This lock prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or the child table while the DML on the parent table occurs. This situation is preferable if updates or deletions occur on the parent table while updates occur on the child table.

Bellow diagram shows a scenario in which the foreign key column in the child table is indexed. The parent table is departments and the child table is employees. A session updates row 3 in departments. The DML on departments does not prevent updates to employees, although updates and deletions of rows in departments must wait for row-level locks on the indexes of employees to clear.





If the child table specifies ON DELETE CASCADE, then deletions from the parent table can result in deletions from the child table. For example, a deletion of a record from departments can cause the deletion of records from employees for employees in the deleted department. In this case, waiting and locking rules are the same as if you deleted rows from the child table after deleting rows from the parent table.