ORA-00060: deadlock detected while waiting for resource

ORA-00060: deadlock detected while waiting for resource

Two sessions were each waiting for a lock the other one held, so Oracle broke the loop with an error.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-12

ORA-00060 happens when session A waits for session B and session B waits for session A at the same time. Neither can ever continue, so Oracle picks one waiting statement and fails it with ORA-00060.

In both tests on this page, the error came in session A, the one that started waiting first. Only that one statement failed. Session A's transaction was still open, and session B stayed blocked until session A ran ROLLBACK. So after ORA-00060, the application must still end the transaction with ROLLBACK (or COMMIT).

Causes and fixes

  1. Two sessions update the same rows in opposite order
  2. Deleting parent rows when the foreign key column has no index

1Two sessions update the same rows in opposite order

A transfer between accounts: session A updates account 1 and then account 2, while session B updates account 2 and then account 1. Each holds the row the other wants next. Shortly after session B started waiting, session A got ORA-00060. Session B's update finished only after session A rolled back.

The fix is to always lock rows in the same order, for example by smallest account_id first. When session B also starts with account 1, it simply waits for session A's COMMIT and then continues. Both transfers succeeded, and the final balances were 950 and 1050.

Setup
Setuprun as lab_a
CREATE TABLE accounts (account_id NUMBER PRIMARY KEY, balance NUMBER);
INSERT INTO accounts VALUES (1, 1000);
INSERT INTO accounts VALUES (2, 1000);
COMMIT;
OUTPUT
Table created.


1 row created.


1 row created.


Commit complete.
Reproduce2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
OUTPUT
1 row updated.
Session Brun as lab_a
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
OUTPUT
1 row updated.
Session Arun as lab_a⏳ Waiting for the lock…
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
Session Brun as lab_a⏳ Waiting for the lock…
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
Session A: the waiting statement finishesrun as lab_a
OUTPUT
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2
                              *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Help: https://docs.oracle.com/error-help/db/ora-00060/
Session Arun as lab_a
ROLLBACK;
OUTPUT
Rollback complete.
Session B: the waiting statement finishesrun as lab_a
OUTPUT
1 row updated.

This is the most common deadlock in application code: batch jobs or screens that touch the same rows in different orders. Sorting the keys before updating removes it.

After the fix3 sessions open at the same time (A, B, S), run in this order
Session Arun as lab_a
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
OUTPUT
1 row updated.
Session B · Fixrun as lab_a⏳ Waiting for the lock…
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;
Session Arun as lab_a
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
OUTPUT
1 row updated.
Session Arun as lab_a
COMMIT;
OUTPUT
Commit complete.
Session B: the waiting statement finishesrun as lab_a
OUTPUT
1 row updated.
Session B · Fixrun as lab_a
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
OUTPUT
1 row updated.
Session Brun as lab_a
COMMIT;
OUTPUT
Commit complete.
Session S · Checkrun as lab_a
SELECT account_id, balance FROM accounts ORDER BY account_id;
OUTPUT
ACCOUNT_ID    BALANCE
---------- ----------
         1        950
         2       1050

2 rows selected.

2Deleting parent rows when the foreign key column has no index

emp.dept_id references dept, but has no index. Sessions A and B each update a different emp row (not committed). Then session A deletes dept 3 and session B deletes dept 4. These are different rows and no employee belongs to them, but session A got ORA-00060 anyway.

Without an index on the foreign key, deleting a parent row needs a lock on the whole child table, so each delete waits for the other session's uncommitted emp update. After CREATE INDEX emp_dept_ix ON emp (dept_id), the same four statements ran without any waiting and both sessions committed.

Setup
Setuprun as lab_a
CREATE TABLE dept (dept_id NUMBER PRIMARY KEY, name VARCHAR2(20));
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_id NUMBER REFERENCES dept);
INSERT INTO dept SELECT LEVEL, 'D' || LEVEL FROM dual CONNECT BY LEVEL <= 4;
INSERT INTO emp VALUES (1, 'Kim', 1);
INSERT INTO emp VALUES (2, 'Lee', 2);
COMMIT;
OUTPUT
Table created.


Table created.


4 rows created.


1 row created.


1 row created.


Commit complete.
Reproduce2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a
UPDATE emp SET name = 'Kim2' WHERE emp_id = 1;
OUTPUT
1 row updated.
Session Brun as lab_a
UPDATE emp SET name = 'Lee2' WHERE emp_id = 2;
OUTPUT
1 row updated.
Session Arun as lab_a⏳ Waiting for the lock…
DELETE FROM dept WHERE dept_id = 3;
Session Brun as lab_a⏳ Waiting for the lock…
DELETE FROM dept WHERE dept_id = 4;
Session A: the waiting statement finishesrun as lab_a
OUTPUT
DELETE FROM dept WHERE dept_id = 3
                       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
Help: https://docs.oracle.com/error-help/db/ora-00060/
Session Arun as lab_a
ROLLBACK;
OUTPUT
Rollback complete.
Session B: the waiting statement finishesrun as lab_a
OUTPUT
1 row deleted.
Fixrun as lab_a
CREATE INDEX emp_dept_ix ON emp (dept_id);
OUTPUT
Index created.

This deadlock is hard to spot because the sessions touch different rows. If deadlocks involve a parent table, check whether every foreign key column on the child tables has an index.

After the fix2 sessions open at the same time (A, B), run in this order
Session Arun as lab_a
UPDATE emp SET name = 'Kim2' WHERE emp_id = 1;
OUTPUT
1 row updated.
Session Brun as lab_a
UPDATE emp SET name = 'Lee2' WHERE emp_id = 2;
OUTPUT
1 row updated.
Session Arun as lab_a
DELETE FROM dept WHERE dept_id = 3;
OUTPUT
1 row deleted.
Session Brun as lab_a
DELETE FROM dept WHERE dept_id = 4;
OUTPUT
1 row deleted.
Session Arun as lab_a
COMMIT;
OUTPUT
Commit complete.
Session Brun as lab_a
COMMIT;
OUTPUT
Commit complete.

FAQ

Which session gets ORA-00060?

One of the waiting statements is chosen. In both tests here it was session A, the one that had been waiting longer. The other session keeps waiting until session A ends its transaction.

Is the whole transaction rolled back on ORA-00060?

No. Only the statement that got the error failed. Session A still held its earlier locks, and session B was released only after session A ran ROLLBACK. Your code must ROLLBACK (or COMMIT) after catching ORA-00060.

Why do I get a deadlock when two sessions delete different rows?

Check the foreign keys. When the child table's foreign key column has no index, deleting a parent row has to lock the whole child table, so sessions changing different rows can block each other. An index on the foreign key column fixed it on this page.

How do I prevent deadlocks in update batches?

Update rows in the same order in every session, for example sorted by primary key. With the same order, the second session just waits instead of deadlocking.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors