First page Back Continue Last page Graphics
Deadlocks: Cause and Fix
Common problem: missing index on foreign key
- Update parent primary key locks entire child table
- Delete parent row locks entire child table
The more rows you lock, the greater your chance of deadlock
Notes:
The cause of a deadlock is often a missing index on a foreign key column. When an index is not created on a foreign key, and an UPDATE or DELETE is issued on the parent table, the entire child table is locked. When an entire table is locked, you significantly increase the chances of a deadlock occurring.
Tom Kyte’s text book, “expert one-on-one Oracle” (ISBN 1-861004-82-6) has an excellent section on deadlocks, causes and fixes.