ORA-00060: deadlock detected while waiting for resource

Two sessions were each waiting for a lock the other held. Oracle broke the cycle by rolling back one statement in one of them. Make sessions lock rows in the same order, and index foreign key columns.

What it means

A deadlock happens when session A waits for a row locked by session B while B waits for a row locked by A. Neither can continue, so Oracle detects the cycle, rolls back the statement (not the whole transaction) in one session and returns ORA-00060 to it. The rest of that transaction is still open — the application should roll back or retry. Oracle also writes a trace file with the deadlock graph and notes it in the alert log.

Common causes

Two code paths updating the same rows in a different order.
Unindexed foreign key columns: changes to the parent table then lock the whole child table.
Bitmap indexes on tables with concurrent DML.
Too few transaction slots (ITL) in heavily updated blocks.

How to fix it

Open the trace file named in the alert log; the deadlock graph shows both sessions and the SQL they ran.
Make every code path lock rows in the same order (for example, ordered by primary key).
Index foreign key columns on child tables, especially if parent rows are updated or deleted.
After ORA-00060, roll back the transaction and retry it in the application.

Example

Find foreign key columns without an index (first column check):

SELECT c.table_name, cc.column_name, c.constraint_name
  FROM user_constraints c
  JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
 WHERE c.constraint_type = 'R'
   AND cc.position = 1
   AND NOT EXISTS (
       SELECT 1 FROM user_ind_columns ic
        WHERE ic.table_name = cc.table_name
          AND ic.column_name = cc.column_name
          AND ic.column_position = 1);

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-00060. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.