Oracle Database - Locks and Foreign Keys - Concurrency control of parent keys
About
Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys.
Locking behaviour 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.
Articles Related
Type of Foreign Keys
Unindexed
When both of the following conditions are true, the database acquires a full table lock on the child table:
- No index exists on the foreign key column of the child table.
- A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table. Inserts into the parent table do not acquire table locks on the child table.
DML on a child table does not acquire a table lock on the parent table.
Example:
- The database acquires a full table lock on employees during the primary key modification of department 60.
- This lock enables other sessions to query but not update the employees table. For example, employee phone numbers cannot be updated.
- The table lock on employees releases immediately after the primary key modification on the departments table completes.
- If multiple rows in departments undergo primary key modifications, then a table lock on employees is obtained and released once for each row that is modified in departments.
Indexed
When both of the following conditions are true, the database does not acquire a full table lock on the child table:
- A foreign key column in the child table is indexed.
- A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.
A lock on the parent table prevents transactions from acquiring exclusive table locks, but does not prevent DML on the parent or child table during the primary key modification. This situation is preferable if primary key modifications occur on the parent table while updates occur on the child table.
The figure below shows child table employees with an indexed department_id column. A transaction deletes department 280 from departments. This deletion does not cause the database to acquire a full table lock on the employees table as in the unindexed above scenario.
If the child table specifies ON DELETE CASCADE, then deletions from the parent table can result in deletions from the child table. For example, the deletion of department 280 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.