ORA-00060: deadlock detected while waiting for resource
ORA-00060: deadlock detected while waiting for resourceTwo 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
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
CREATE TABLE accounts (account_id NUMBER PRIMARY KEY, balance NUMBER);
INSERT INTO accounts VALUES (1, 1000);
INSERT INTO accounts VALUES (2, 1000);
COMMIT;Table created.
1 row created.
1 row created.
Commit complete.UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;1 row updated.UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;1 row updated.UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;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/ROLLBACK;Rollback complete.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.
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;1 row updated.UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;1 row updated.COMMIT;Commit complete.1 row updated.UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;1 row updated.COMMIT;Commit complete.SELECT account_id, balance FROM accounts ORDER BY account_id;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
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;Table created.
Table created.
4 rows created.
1 row created.
1 row created.
Commit complete.UPDATE emp SET name = 'Kim2' WHERE emp_id = 1;1 row updated.UPDATE emp SET name = 'Lee2' WHERE emp_id = 2;1 row updated.DELETE FROM dept WHERE dept_id = 3;DELETE FROM dept WHERE dept_id = 4;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/ROLLBACK;Rollback complete.1 row deleted.CREATE INDEX emp_dept_ix ON emp (dept_id);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.
UPDATE emp SET name = 'Kim2' WHERE emp_id = 1;1 row updated.UPDATE emp SET name = 'Lee2' WHERE emp_id = 2;1 row updated.DELETE FROM dept WHERE dept_id = 3;1 row deleted.DELETE FROM dept WHERE dept_id = 4;1 row deleted.COMMIT;Commit complete.COMMIT;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