Deadlocks and how to break them?

What is a deadlock?

Whenever you have competing DML running against the same data, you run the risk of a deadlock. This deadlock condition is an age-old issue known as the "perpetual embrace"! The doc note that a retry may work:

ORA-00060: deadlock detected while waiting for resource

Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

Deadlocks in Oracle result in this error:

ORA-00060: deadlock detected while waiting for resource

If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.

How to Break them?

SELECT a.object_name, b.process, b.session_id
FROM all_objects a, v$locked_object b
WHERE a.object_id = b.object_id

SELECT SID, serial#
FROM v$session
WHERE SID = '[SESSION_ID]'

ALTER SYSTEM KILL SESSION '[SID],[SERIAL#]'