Tuesday, 3 September 2013

Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys:There are two issues associated with unindexed foreign keys.The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child’s foreign key is not indexed.Locks in Oracle are managed at the row level. Concurrent disjoint transactions should not interfere with one another. Unindexed foreign keys are an exception, since it can result in a complete TABLE LOCK.

I am testing after how many ORA-00060 errors a recursive transaction aborts. This is a transactionwhich calls itself recursively when it fails, and is in a deadlock state with another transactionwhich also calls itself recursively when it fails.

That is, is there a limit when 2 such deadlocked procedures will give up? Is this because ofrecursive calls or deadlocks?

A script that can find foreign keys across all users that could cause blocking locks.

1 comment:

A deadlock occurs when 2 sessions block each other by attempting to update a row, which is already updated by another session but has not been yet committed or rolled back. There can be more than 2 sessions involved, but the main idea is the same.