Bookmark Fixed font
Go to End
Doc ID: Note:11828.1 Subject: FOREIGN KEYS, INDEXES AND PARENT TABLE LOCKING Type: BULLETIN Status: PUBLISHED Content Type: TEXT/PLAIN Creation Date: 18-APR-1994 Last Revision Date: 12-APR-2001 FOREIGN KEYS, INDEXES AND PARENT TABLE LOCKING ============================================== Environment: Category: Kernel Version: Subject: Generic RDBMS 7 Foreign keys, indexes and parent table locking
When setting up a master-detail relationship between tables, unless the parent (master) table is static or rarely changed, it is important to ensure that the foreign key of the child (detail) table is indexed. Without this index, any inserts, deletes or updates (foreign key or otherwise) made to the child table will result in a share lock being taken out on the parent table. Where another application wishes to modify the parent table, it will then have to wait for these locks to be released. This can lead to the application apparently 'hanging' or even to a deadlock situation (ORA-00060: deadlock detected while waiting for resource). So why, without this index, is a share lock taken out on the parent table? Clearly we must ensure that a parent row cannot be removed or have its primary key updated when a corresponding child row potentially exists. This implies some sort of locking must be undertaken. The alternative to locking the entire table in share mode would be to lock the corresponding parent ROW in exclusive mode (since there is no such thing as a shared row lock). However this would prevent any other applications modifying or adding further child records for this parent until the lock is released. Taking out a share lock on the table will allow other modifications and additions of child records since any number of transactions may hold a share lock. Only changes to the parent table (which would require the lock to be upgraded to an exclusive one) will be prevented. Why then, does an index on the foreign key mean that the shared lock
on the parent table is not required? When a row in the child table is inserted, deleted or has its foreign key updated, the corresponding index entry/entries is/are also locked. When an application attempts to delete or update the primary key of a parent row, it reads the FIRST corresponding entry in the child's foreign key index (uncommitted or otherwise) and, if locked, waits for that lock to be released. If the modified child row is NOT the first occurrence of the foreign key in the index then the parent modification must be prevented anyway, regardless of the outcome of uncommitted transactions on other child rows with this key. Hence the error can be flagged immediately and so the transaction is not forced to wait. This mechanism ensures the minimum reads and wait times to maintain data consistency. References: =========== See the ORACLE7 Server Application Developer's Guide, page 6-10 for more information. .