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
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 UPDATEtaken in different orders in different code paths.
How to fix it
-
Roll back (or retry from a savepoint) in the session that got the error. Retrying the whole transaction is the usual application response.
ROLLBACK;
-
Read the deadlock graph in the trace file named in the alert log to see both statements and the rows.
-
Index foreign key columns that are joined to parents you update or delete.
CREATE INDEX orders_customer_ix ON orders (customer_id);
-
Lock rows in a consistent order everywhere.
SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE;
Related errors
ORA-00054resource busy and acquire with NOWAIT specifiedORA-08177can't serialize access for this transaction1205SQL Server: Deadlocked and chosen as the victim40P01PostgreSQL: deadlock detected1213MySQL: Deadlock found when trying to get lock
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.
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.