ORA-00060 Deadlock Detected in Oracle: Causes and Fixes

ORA-00060: deadlock detected while waiting for resource means two (or more) sessions were each waiting for a lock the other one held, so neither could ever continue. Oracle spots the cycle on its own and breaks it by rolling back one statement in one of the sessions. That session gets ORA-00060, and its earlier work in the same transaction is still there, still holding locks.

The fix is almost never a database parameter. Deadlocks come from the order in which your code locks rows, from unindexed foreign keys, or from an autonomous transaction fighting its own parent. This tutorial reproduces a deadlock step by step, shows how to read the trace file Oracle writes, and covers the code changes that stop it from happening again.

How a deadlock happens in Oracle

Oracle locks rows, not pages, and plain queries never take read locks. So a deadlock needs two sessions that both change data and grab their locks in a different order. Session A locks row 1 and then asks for row 2. Session B already holds row 2 and now asks for row 1. Both wait, and no amount of waiting will ever help.

This is different from ordinary blocking. With blocking, one session waits until the other commits or rolls back, and then it carries on. With a deadlock the wait is circular, so Oracle has to step in.

According to the Oracle Concepts guide, the statement that gets rolled back belongs to the session that detects the deadlock. Only one session gets the error, but you cannot predict which one. The rest of that session's transaction is not rolled back.

Sample setup

Create a small accounts table. A money transfer updates two rows, which is the classic place where deadlocks appear.

CREATE TABLE accounts (
  account_id  NUMBER PRIMARY KEY,
  owner_name  VARCHAR2(50) NOT NULL,
  balance     NUMBER(12,2) NOT NULL
);

INSERT INTO accounts VALUES (1, 'Asha',  5000);
INSERT INTO accounts VALUES (2, 'Ravi',  3000);
INSERT INTO accounts VALUES (3, 'Meena', 8000);
COMMIT;

You need two separate connections to follow along, for example two SQL*Plus or SQL Developer windows. Turn off autocommit in your GUI tool, otherwise every statement commits at once and no deadlock can form.

Reproduce ORA-00060 with two sessions

Run the statements in this exact order. Session 1 sends Asha's money to Ravi. Session 2 sends Ravi's money to Asha at the same moment.

Step 1: each session locks one row

-- Session 1
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;

-- Session 2
UPDATE accounts SET balance = balance - 200 WHERE account_id = 2;

1 row updated.

No problem yet. Session 1 holds a row lock on account 1 and session 2 holds a row lock on account 2.

Step 2: each session asks for the other row

-- Session 1 (hangs, waiting for session 2)
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- Session 2 (hangs, waiting for session 1)
UPDATE accounts SET balance = balance + 200 WHERE account_id = 1;

Now both sessions are stuck. After a short time Oracle detects the cycle, and one of them gets the error:

UPDATE accounts SET balance = balance + 500 WHERE account_id = 2
       *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

Step 3: understand what was rolled back

Only the failed UPDATE was undone. Session 1 still holds the lock on account 1 from step 1, so session 2 is still waiting. You can check it from session 1:

SELECT account_id, balance FROM accounts ORDER BY account_id;

Account 1 shows 4500 because session 1 sees its own uncommitted debit. The credit to account 2 never happened. If session 1 committed now, Asha would lose 500 and Ravi would get nothing. That is why the right reaction to ORA-00060 is a full ROLLBACK, not a COMMIT.

-- Session 1
ROLLBACK;

-- Session 2 continues immediately
-- 1 row updated.
COMMIT;

Find the deadlock details in the trace file

Every ORA-00060 writes a message to the alert log and a trace file in the diagnostic trace directory. The Oracle error reference says to look at that trace file to see the transactions and resources involved. Find the directory with:

SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

You need access to the database server (or a DBA) to read the files. On the server, search for the error:

cd /u01/app/oracle/diag/rdbms/orcl/orcl/trace   # use the path from the query
grep -l "ORA-00060" *.trc

What to look for in the trace

The trace contains a section called the deadlock graph, followed by the SQL being run by each session. Oracle's trace usually states that the deadlock is not an Oracle error but a result of application design or ad hoc SQL. Focus on these parts:

• Resource name: a resource starting with TX is a row-level (transaction) lock. A resource starting with TM is a table-level lock, which very often points to an unindexed foreign key.
• holds / waits: which lock mode each session held and which one it was waiting for.
• Rows waited on: the object and row each session wanted. Map the object number to a name with SELECT object_name FROM dba_objects WHERE object_id = ....
• Current SQL: the statement of the session that got the error, and information for the other waiting sessions. These tell you which two code paths collide.

Two row lock (TX) deadlocks in the graph almost always mean the lock order problem from the example above. A TM lock in the graph sends you to the foreign key check in the next section.

Fix 1: always lock rows in the same order

The deadlock above happened because session 1 locked account 1 then 2, while session 2 locked 2 then 1. If every transfer locks the lower account_id first, the second session simply waits behind the first one. That is normal blocking, which ends on commit.

CREATE OR REPLACE PROCEDURE transfer (
  p_from   IN accounts.account_id%TYPE,
  p_to     IN accounts.account_id%TYPE,
  p_amount IN NUMBER
) AS
  v_balance accounts.balance%TYPE;
BEGIN
  -- Lock both rows in a fixed order: lowest id first
  SELECT balance INTO v_balance
    FROM accounts
   WHERE account_id = LEAST(p_from, p_to)
     FOR UPDATE;

  SELECT balance INTO v_balance
    FROM accounts
   WHERE account_id = GREATEST(p_from, p_to)
     FOR UPDATE;

  UPDATE accounts SET balance = balance - p_amount
   WHERE account_id = p_from;

  UPDATE accounts SET balance = balance + p_amount
   WHERE account_id = p_to;
END transfer;
/

Now run EXEC transfer(1, 2, 500) in session 1 and EXEC transfer(2, 1, 200) in session 2. Both calls lock account 1 first. Session 2 waits on its first SELECT ... FOR UPDATE until session 1 commits, then completes. No ORA-00060.

The same idea applies to batch jobs. If two jobs update overlapping sets of orders, have both process them sorted by the primary key, one row or one small batch at a time, so their lock order matches.

Fix 2: index your foreign keys

This is the deadlock cause people miss most. The Oracle Concepts guide explains that when a foreign key column has no index, deleting a parent row or changing its primary key takes a full table lock on the child table. Two sessions doing this on different parents while others change the child table can deadlock, and the graph shows TM locks.

CREATE TABLE payments (
  payment_id  NUMBER PRIMARY KEY,
  account_id  NUMBER NOT NULL REFERENCES accounts (account_id),
  amount      NUMBER(12,2) NOT NULL,
  paid_on     DATE DEFAULT SYSDATE
);
-- No index on payments.account_id yet

Find foreign keys in your schema whose columns are not covered by an index with the same leading columns:

SELECT c.table_name, c.constraint_name, cc.column_name, cc.position
  FROM user_constraints  c
  JOIN user_cons_columns cc
    ON cc.constraint_name = c.constraint_name
 WHERE c.constraint_type = 'R'
   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 = cc.position)
 ORDER BY c.table_name, c.constraint_name, cc.position;

(expected output; the constraint name is system generated and will differ on your database.) The fix is one statement:

CREATE INDEX payments_account_ix ON payments (account_id);

The documentation says foreign keys should almost always be indexed, with one exception: when the parent's primary key is never updated and parent rows are never deleted. The query above is a simple check that compares column positions. A multi-column foreign key needs an index whose leading columns are exactly the key columns, in any order, so review composite keys by hand.

Fix 3: retry the whole transaction, once or twice

Even with good design, rare deadlocks can happen under heavy load. Oracle's own advice for ORA-00060 is to retry if necessary. Retry the whole unit of work after a rollback, never just the failed statement on top of half-done work.

DECLARE
  e_deadlock EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_deadlock, -60);
BEGIN
  FOR attempt IN 1 .. 3 LOOP
    BEGIN
      transfer(1, 2, 500);
      COMMIT;
      EXIT;                       -- success, leave the loop
    EXCEPTION
      WHEN e_deadlock THEN
        ROLLBACK;                 -- undo the whole transfer
        IF attempt = 3 THEN
          RAISE;                  -- give up, report the error
        END IF;
        DBMS_SESSION.SLEEP(1);    -- 18c and later
    END;
  END LOOP;
END;
/

PRAGMA EXCEPTION_INIT gives ORA-00060 a name so you can catch only that error. Everything else still fails normally. On Oracle releases before 18c, use DBMS_LOCK.SLEEP instead of DBMS_SESSION.SLEEP (it needs an EXECUTE grant on DBMS_LOCK).

Other causes worth checking

An autonomous transaction locking its parent's row

A deadlock can happen inside a single session. The PL/SQL Language Reference warns that if an autonomous transaction tries to use a resource held by the main transaction, a deadlock can occur. Here the main transaction locks account 3, then the autonomous procedure tries to update the same row and waits for a transaction that can never finish.

CREATE OR REPLACE PROCEDURE log_balance_change (p_id NUMBER) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE accounts SET balance = balance WHERE account_id = p_id;
  COMMIT;
END;
/

UPDATE accounts SET balance = balance + 100 WHERE account_id = 3;
EXEC log_balance_change(3);   -- raises ORA-00060

Autonomous transactions should write to their own tables (audit or log tables), not to rows the caller has already changed.

Bitmap indexes and very busy blocks

A bitmap index entry covers many rows, so concurrent DML on a table with bitmap indexes can lock far more than the rows you changed. Bitmap indexes suit data warehouses with bulk loads, not OLTP tables updated by many sessions. Deadlock graphs that show TX waits in share mode can also come from waiting for an interested transaction list (ITL) slot in a very busy block, or from two sessions inserting the same unique key value. In those cases the trace, not guesswork, should guide the fix.

Common mistakes

• Committing after ORA-00060. The earlier statements of the transaction are still there. Commit them and you save partial work. Roll back instead.
• Killing sessions or restarting the database. Oracle has already resolved the deadlock. Killing the other session only throws away its work.
• Raising a timeout parameter. No setting makes deadlocks go away. They come from the code's lock order and the schema.
• Retrying only the failed statement. Retry the whole transaction from the start after a rollback, with a small limit on attempts.
• Ignoring the trace file. It names both SQL statements and the locked objects. Read it before you change anything.
• Leaving foreign keys unindexed on child tables whose parents get deleted or updated.

Best practices to prevent deadlocks

• Lock rows in one fixed order everywhere, for example by ascending primary key.
• Keep transactions short. Do not wait for user input or call slow web services while holding row locks.
• Index foreign key columns on child tables.
• Use SELECT ... FOR UPDATE NOWAIT or FOR UPDATE WAIT n where waiting makes no sense. They fail fast with ORA-00054 or ORA-30006 instead of queuing.
• Avoid bitmap indexes on tables with concurrent DML.
• Monitor the alert log for ORA-00060 so recurring deadlocks get noticed and fixed.

Frequently asked questions

How do I resolve ORA-00060 deadlock detected while waiting for resource?

In the session that got the error, roll back the transaction and retry it if needed. To stop it coming back, read the trace file, find the two statements involved and make them lock rows in the same order, or index the foreign key if the graph shows TM locks.

Does Oracle roll back the whole transaction on a deadlock?

No. Oracle rolls back only the single statement that detected the deadlock. Earlier changes in that transaction stay in place and keep their locks until you commit or roll back.

Where can I find the ORA-00060 trace file?

In the directory returned by SELECT value FROM v$diag_info WHERE name = 'Diag Trace'. The alert log entry for the deadlock names the exact trace file.

Can a single session cause a deadlock in Oracle?

Yes, with an autonomous transaction that tries to lock a row its own main transaction already holds. The autonomous transaction waits for a parent that is waiting for it, and Oracle raises ORA-00060.

What is the difference between a deadlock and a blocking session?

A blocking session makes others wait until it ends its transaction, then they continue. A deadlock is a circular wait that never ends by itself, so Oracle breaks it with ORA-00060.

Summary

• ORA-00060 means sessions waited on each other in a cycle; Oracle rolled back one statement to break it.
• Roll back the whole transaction after the error. Never commit the half-done work.
• Read the trace file in the Diag Trace directory: TX resources point to lock order, TM resources to unindexed foreign keys.
• Lock rows in a fixed order, for example lowest primary key first.
• Index foreign key columns on child tables.
• Retry the full transaction a limited number of times with PRAGMA EXCEPTION_INIT(e, -60).
• Keep autonomous transactions away from rows the caller has locked.