// Oracle error reference

ORA-00060: deadlock detected while waiting for resource

Two sessions were each waiting for a row lock the other held. Oracle rolled back one statement (not the whole transaction) so the other could go on.

Error code
ORA-00060
Category
Transactions and locking
Error message
deadlock detected while waiting for resource

What it means

When Oracle detects a cycle of lock waits, it rolls back the current statement of one of the sessions and raises ORA-00060 there. Unlike some databases, it doesn't roll back that session's whole transaction: earlier work is still pending, and the session must decide whether to roll back or retry. Oracle writes a trace file with a deadlock graph showing the sessions, the rows and the SQL involved.

Common causes

  • Two transactions updating the same rows in opposite orders.
  • Unindexed foreign key columns: deleting a parent or updating its key locks the whole child table, which makes deadlocks much more likely.
  • Bitmap indexes on tables with concurrent DML.
  • SELECT ... FOR UPDATE taken in different orders in different code paths.

How to fix it

  1. Roll back (or retry from a savepoint) in the session that got the error. Retrying the whole transaction is the usual application response.

    ROLLBACK;
  2. Read the deadlock graph in the trace file named in the alert log to see both statements and the rows.

  3. Index foreign key columns that are joined to parents you update or delete.

    CREATE INDEX orders_customer_ix ON orders (customer_id);
  4. Lock rows in a consistent order everywhere.

    SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;

Related errors

More transactions and locking errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.