ORA-00060 Deadlock Detected - Deadlock Resolution and Prevention

ORA-00060: Deadlock Detected

Error Overview

Error Text: ORA-00060: deadlock detected while waiting for resource

This error occurs when two or more sessions are waiting for resources locked by each other, creating a circular dependency that cannot be resolved automatically. Oracle detects the deadlock and terminates one of the transactions to break the cycle, rolling back that transaction and returning this error.

Understanding Deadlocks

Deadlock Scenario Example

Session A: Locks Table1 → Waits for Table2
     ↓                         ↑
Session B: Locks Table2 → Waits for Table1

Types of Deadlocks

• TX/TX Deadlocks - Transaction vs transaction (most common)
• TX/TM Deadlocks - Transaction vs table lock
• TM/TM Deadlocks - Table lock vs table lock
• UL Deadlocks - User-defined lock conflicts

Diagnostic Steps

1. Examine Deadlock Trace Files

# Find recent deadlock trace files
find $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace -name "*trc" -mtime -1 -exec grep -l "DEADLOCK DETECTED" {} \;

# View deadlock details in trace file
grep -A 50 -B 10 "DEADLOCK DETECTED" /path/to/trace/file.trc

# Check alert log for deadlock information
grep -i deadlock $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log | tail -10

2. Query Deadlock Information

-- Check for recent deadlocks in alert log
SELECT
  originating_timestamp,
  message_text
FROM x$dbgalertext
WHERE message_text LIKE '%deadlock%'
  AND originating_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY originating_timestamp DESC;

-- Current lock information
SELECT
  s1.sid as holder_sid,
  s1.serial# as holder_serial,
  s1.username as holder_user,
  s1.program as holder_program,
  s2.sid as waiter_sid,
  s2.serial# as waiter_serial,
  s2.username as waiter_user,
  s2.program as waiter_program,
  l1.type as lock_type,
  DECODE(l1.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive') as mode_held,
  DECODE(l2.request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive') as mode_requested,
  o.owner,
  o.object_name
FROM v$lock l1, v$lock l2, v$session s1, v$session s2, dba_objects o
WHERE l1.id1 = l2.id1
  AND l1.id2 = l2.id2
  AND l1.block = 1
  AND l2.request > 0
  AND l1.sid = s1.sid
  AND l2.sid = s2.sid
  AND l1.id1 = o.object_id(+);

-- Check for lock waits
SELECT
  sid,
  serial#,
  username,
  program,
  machine,
  sql_id,
  event,
  p1text,
  p1,
  p2text,
  p2,
  wait_time,
  seconds_in_wait,
  state
FROM v$session_wait
WHERE event LIKE '%enq%'
   OR event LIKE '%TX%'
ORDER BY seconds_in_wait DESC;

3. Analyze Deadlock Patterns

-- Check for deadlock-prone objects
SELECT
  owner,
  object_name,
  object_type,
  COUNT(*) as lock_requests
FROM v$locked_object lo, dba_objects o
WHERE lo.object_id = o.object_id
GROUP BY owner, object_name, object_type
HAVING COUNT(*) > 1
ORDER BY lock_requests DESC;

-- Find sessions with multiple locks
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  COUNT(*) as lock_count,
  LISTAGG(o.object_name, ', ') WITHIN GROUP (ORDER BY o.object_name) as locked_objects
FROM v$session s, v$locked_object lo, dba_objects o
WHERE s.sid = lo.session_id
  AND lo.object_id = o.object_id
GROUP BY s.sid, s.serial#, s.username, s.program
HAVING COUNT(*) > 1
ORDER BY lock_count DESC;

-- Check transaction isolation levels
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  t.start_time,
  t.status,
  t.isolation_level
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY t.start_time;

4. SQL Analysis for Deadlock Candidates

-- Find SQL statements that commonly cause deadlocks
SELECT
  sql_id,
  child_number,
  executions,
  sorts,
  rows_processed,
  elapsed_time/1000000 as elapsed_seconds,
  cpu_time/1000000 as cpu_seconds,
  SUBSTR(sql_text, 1, 100) as sql_preview
FROM v$sql
WHERE sql_text LIKE '%UPDATE%'
   OR sql_text LIKE '%DELETE%'
   OR sql_text LIKE '%INSERT%'
   OR sql_text LIKE '%FOR UPDATE%'
ORDER BY executions DESC, elapsed_time DESC;

-- Check for foreign key constraints without indexes
SELECT
  c.owner,
  c.table_name,
  c.constraint_name,
  c.constraint_type,
  cc.column_name,
  'Missing index on ' || c.table_name || '(' || cc.column_name || ')' as recommendation
FROM dba_constraints c, dba_cons_columns cc
WHERE c.constraint_type = 'R'  -- Foreign key
  AND c.owner = cc.owner
  AND c.constraint_name = cc.constraint_name
  AND NOT EXISTS (
    SELECT 1 FROM dba_ind_columns ic
    WHERE ic.table_owner = cc.owner
      AND ic.table_name = cc.table_name
      AND ic.column_name = cc.column_name
      AND ic.column_position = 1
  )
ORDER BY c.owner, c.table_name;

Immediate Solutions

Solution 1: Identify and Break Deadlock Cycle

Analyze Current Deadlock

-- Create procedure to capture deadlock information
CREATE OR REPLACE PROCEDURE capture_deadlock_info AS
  v_deadlock_count NUMBER;
BEGIN
  -- Check if deadlock is currently happening
  SELECT COUNT(*) INTO v_deadlock_count
  FROM v$lock l1, v$lock l2
  WHERE l1.id1 = l2.id1
    AND l1.id2 = l2.id2
    AND l1.block = 1
    AND l2.request > 0
    AND l1.sid != l2.sid;

  IF v_deadlock_count > 0 THEN
    -- Log deadlock information
    INSERT INTO deadlock_log (
      log_time, holder_sid, holder_user, waiter_sid, waiter_user,
      lock_type, object_name
    )
    SELECT
      SYSTIMESTAMP,
      s1.sid,
      s1.username,
      s2.sid,
      s2.username,
      l1.type,
      o.object_name
    FROM v$lock l1, v$lock l2, v$session s1, v$session s2, dba_objects o
    WHERE l1.id1 = l2.id1
      AND l1.id2 = l2.id2
      AND l1.block = 1
      AND l2.request > 0
      AND l1.sid = s1.sid
      AND l2.sid = s2.sid
      AND l1.id1 = o.object_id(+);

    COMMIT;
  END IF;
END;
/

-- Create deadlock log table
CREATE TABLE deadlock_log (
  log_time TIMESTAMP,
  holder_sid NUMBER,
  holder_user VARCHAR2(30),
  waiter_sid NUMBER,
  waiter_user VARCHAR2(30),
  lock_type VARCHAR2(10),
  object_name VARCHAR2(128),
  resolution_action VARCHAR2(500)
);

Solution 2: Resolve Current Deadlocks

Manual Deadlock Resolution

-- Kill one of the deadlocked sessions (choose carefully)
-- Generally kill the session that started later or has less work done
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.logon_time,
  t.start_time as transaction_start,
  t.used_ublk as undo_blocks_used,
  'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' as kill_command
FROM v$session s, v$transaction t, v$lock l
WHERE s.taddr = t.addr
  AND s.sid = l.sid
  AND l.block = 1
ORDER BY t.start_time DESC, t.used_ublk ASC;

-- Execute the kill command for the appropriate session
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Force rollback of specific transaction
-- Only if you can identify the problematic transaction
-- ROLLBACK FORCE 'transaction_id';

Solution 3: Application-Level Retry Logic

Implement Deadlock Retry Pattern

// Java example of deadlock retry logic
public class DeadlockRetryManager {
    private static final int MAX_RETRIES = 3;
    private static final int BASE_DELAY_MS = 100;

    public void executeWithDeadlockRetry(Runnable operation) throws SQLException {
        int attempts = 0;
        while (attempts < MAX_RETRIES) {
            try {
                operation.run();
                return; // Success, exit retry loop

            } catch (SQLException e) {
                if (e.getErrorCode() == 60 && attempts < MAX_RETRIES - 1) { // ORA-00060
                    attempts++;

                    // Exponential backoff with jitter
                    int delay = BASE_DELAY_MS * (int) Math.pow(2, attempts - 1);
                    delay += (int) (Math.random() * delay * 0.1); // Add 10% jitter

                    try {
                        Thread.sleep(delay);
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt();
                        throw new SQLException("Interrupted during deadlock retry", ie);
                    }

                    System.out.println("Deadlock detected, retrying attempt " + attempts +
                                     " after " + delay + "ms delay");
                } else {
                    throw e; // Re-throw if not a deadlock or max retries exceeded
                }
            }
        }
        throw new SQLException("Maximum deadlock retry attempts exceeded");
    }
}

Long-Term Solutions

1. Prevent Deadlocks Through Design

Consistent Lock Ordering

-- BAD: Inconsistent lock ordering can cause deadlocks
-- Session A:
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

-- Session B (same time):
UPDATE accounts SET balance = balance - 50 WHERE account_id = 2;
UPDATE accounts SET balance = balance + 50 WHERE account_id = 1;

-- GOOD: Always lock in consistent order (by account_id)
CREATE OR REPLACE PROCEDURE transfer_funds(
  p_from_account NUMBER,
  p_to_account NUMBER,
  p_amount NUMBER
) AS
  v_first_account NUMBER;
  v_second_account NUMBER;
  v_first_amount NUMBER;
  v_second_amount NUMBER;
BEGIN
  -- Determine lock order by account ID
  IF p_from_account < p_to_account THEN
    v_first_account := p_from_account;
    v_second_account := p_to_account;
    v_first_amount := -p_amount;
    v_second_amount := p_amount;
  ELSE
    v_first_account := p_to_account;
    v_second_account := p_from_account;
    v_first_amount := p_amount;
    v_second_amount := -p_amount;
  END IF;

  -- Lock accounts in consistent order
  UPDATE accounts SET balance = balance + v_first_amount
  WHERE account_id = v_first_account;

  UPDATE accounts SET balance = balance + v_second_amount
  WHERE account_id = v_second_account;

  COMMIT;
END;
/

Reduce Lock Duration

-- BAD: Long-running transaction holds locks
BEGIN
  SELECT balance INTO v_balance FROM accounts WHERE id = 123 FOR UPDATE;

  -- Long processing time
  complex_business_logic(v_balance);

  UPDATE accounts SET balance = v_new_balance WHERE id = 123;
  COMMIT;
END;

-- GOOD: Minimize lock hold time
BEGIN
  -- Do processing first
  v_new_balance := complex_business_logic(v_current_balance);

  -- Quick lock and update
  UPDATE accounts
  SET balance = v_new_balance
  WHERE id = 123 AND balance = v_current_balance;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Balance changed, retry required');
  END IF;

  COMMIT;
END;

2. Implement Deadlock Monitoring

Comprehensive Deadlock Tracking

-- Create deadlock monitoring system
CREATE TABLE deadlock_history (
  deadlock_id NUMBER PRIMARY KEY,
  occurrence_time TIMESTAMP,
  victim_sid NUMBER,
  victim_user VARCHAR2(30),
  victim_program VARCHAR2(100),
  victim_sql_id VARCHAR2(13),
  blocker_sid NUMBER,
  blocker_user VARCHAR2(30),
  blocker_program VARCHAR2(100),
  lock_type VARCHAR2(10),
  object_owner VARCHAR2(30),
  object_name VARCHAR2(128),
  resolution_time NUMBER, -- seconds to resolve
  auto_resolved CHAR(1)
);

-- Create sequence for deadlock tracking
CREATE SEQUENCE deadlock_seq START WITH 1 INCREMENT BY 1;

-- Deadlock monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_deadlocks AS
  CURSOR deadlock_cursor IS
    SELECT
      s1.sid as blocker_sid,
      s1.username as blocker_user,
      s1.program as blocker_program,
      s2.sid as victim_sid,
      s2.username as victim_user,
      s2.program as victim_program,
      s2.sql_id as victim_sql_id,
      l1.type as lock_type,
      o.owner as object_owner,
      o.object_name
    FROM v$lock l1, v$lock l2, v$session s1, v$session s2, dba_objects o
    WHERE l1.id1 = l2.id1
      AND l1.id2 = l2.id2
      AND l1.block = 1
      AND l2.request > 0
      AND l1.sid = s1.sid
      AND l2.sid = s2.sid
      AND l1.id1 = o.object_id(+);

  v_start_time TIMESTAMP := SYSTIMESTAMP;
BEGIN
  FOR deadlock_rec IN deadlock_cursor LOOP
    -- Log potential deadlock
    INSERT INTO deadlock_history (
      deadlock_id, occurrence_time, victim_sid, victim_user, victim_program, victim_sql_id,
      blocker_sid, blocker_user, blocker_program, lock_type,
      object_owner, object_name, auto_resolved
    ) VALUES (
      deadlock_seq.NEXTVAL, v_start_time, deadlock_rec.victim_sid, deadlock_rec.victim_user,
      deadlock_rec.victim_program, deadlock_rec.victim_sql_id, deadlock_rec.blocker_sid,
      deadlock_rec.blocker_user, deadlock_rec.blocker_program, deadlock_rec.lock_type,
      deadlock_rec.object_owner, deadlock_rec.object_name, 'N'
    );
  END LOOP;

  COMMIT;
END;
/

-- Schedule deadlock monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_DEADLOCKS',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_deadlocks',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=1',
    enabled => TRUE
  );
END;
/

3. Database Configuration for Deadlock Prevention

Optimize Deadlock Detection

-- Tune deadlock detection frequency (Oracle internal parameter)
-- Default is usually adequate, but can be tuned in high-concurrency environments
ALTER SYSTEM SET "_deadlock_resolution_signal_frequency" = 1 SCOPE=BOTH;

-- Configure distributed deadlock resolution timeout
ALTER SYSTEM SET distributed_lock_timeout = 60 SCOPE=BOTH;

-- Enable detailed deadlock tracing
ALTER SYSTEM SET events '10046 trace name context forever, level 8';

-- For high-frequency deadlocks, consider
ALTER SYSTEM SET "_lm_deadlock_detection_timeout" = 5 SCOPE=BOTH;

4. Application Design Patterns

Lock-Free Programming Patterns

-- Use MERGE statements instead of SELECT + INSERT/UPDATE
-- Reduces lock time and deadlock potential
MERGE INTO accounts a
USING (SELECT 123 as account_id, 100 as amount FROM dual) s
ON (a.account_id = s.account_id)
WHEN MATCHED THEN
  UPDATE SET balance = balance + s.amount
WHEN NOT MATCHED THEN
  INSERT (account_id, balance) VALUES (s.account_id, s.amount);

-- Use bulk operations to reduce lock frequency
FORALL i IN 1..account_ids.COUNT
  UPDATE accounts
  SET balance = balance + amounts(i)
  WHERE account_id = account_ids(i);

-- Implement optimistic concurrency control
UPDATE accounts
SET balance = :new_balance, version = version + 1
WHERE account_id = :account_id
  AND version = :expected_version;

-- Check if update was successful
IF SQL%ROWCOUNT = 0 THEN
  RAISE_APPLICATION_ERROR(-20002, 'Concurrent modification detected');
END IF;

Queue-Based Processing

-- Replace direct updates with queue-based processing
CREATE TABLE transaction_queue (
  queue_id NUMBER PRIMARY KEY,
  account_id NUMBER,
  transaction_type VARCHAR2(20),
  amount NUMBER,
  status VARCHAR2(20) DEFAULT 'PENDING',
  created_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  processed_date TIMESTAMP
);

-- Queue processor (single thread processes queue sequentially)
CREATE OR REPLACE PROCEDURE process_transaction_queue AS
  CURSOR queue_cursor IS
    SELECT queue_id, account_id, transaction_type, amount
    FROM transaction_queue
    WHERE status = 'PENDING'
    ORDER BY created_date
    FOR UPDATE SKIP LOCKED;

BEGIN
  FOR queue_rec IN queue_cursor LOOP
    BEGIN
      -- Process transaction
      IF queue_rec.transaction_type = 'CREDIT' THEN
        UPDATE accounts
        SET balance = balance + queue_rec.amount
        WHERE account_id = queue_rec.account_id;
      ELSIF queue_rec.transaction_type = 'DEBIT' THEN
        UPDATE accounts
        SET balance = balance - queue_rec.amount
        WHERE account_id = queue_rec.account_id;
      END IF;

      -- Mark as processed
      UPDATE transaction_queue
      SET status = 'COMPLETED', processed_date = SYSTIMESTAMP
      WHERE queue_id = queue_rec.queue_id;

      COMMIT;

    EXCEPTION
      WHEN OTHERS THEN
        UPDATE transaction_queue
        SET status = 'FAILED'
        WHERE queue_id = queue_rec.queue_id;
        COMMIT;
    END;
  END LOOP;
END;
/

Analysis and Reporting

Deadlock Pattern Analysis

-- Analyze deadlock patterns over time
SELECT
  TO_CHAR(occurrence_time, 'YYYY-MM-DD HH24') as hour,
  COUNT(*) as deadlock_count,
  COUNT(DISTINCT object_name) as affected_objects,
  COUNT(DISTINCT victim_user) as affected_users
FROM deadlock_history
WHERE occurrence_time > SYSTIMESTAMP - INTERVAL '7' DAY
GROUP BY TO_CHAR(occurrence_time, 'YYYY-MM-DD HH24')
ORDER BY deadlock_count DESC;

-- Most deadlock-prone objects
SELECT
  object_owner,
  object_name,
  COUNT(*) as deadlock_count,
  COUNT(DISTINCT victim_user) as affected_users,
  MIN(occurrence_time) as first_occurrence,
  MAX(occurrence_time) as last_occurrence
FROM deadlock_history
WHERE occurrence_time > SYSTIMESTAMP - INTERVAL '30' DAY
GROUP BY object_owner, object_name
HAVING COUNT(*) > 1
ORDER BY deadlock_count DESC;

-- Deadlock frequency by application
SELECT
  victim_program,
  COUNT(*) as deadlock_count,
  ROUND(AVG(resolution_time), 2) as avg_resolution_seconds,
  MAX(resolution_time) as max_resolution_seconds
FROM deadlock_history
WHERE occurrence_time > SYSTIMESTAMP - INTERVAL '7' DAY
  AND resolution_time IS NOT NULL
GROUP BY victim_program
ORDER BY deadlock_count DESC;

Related Errors

• ORA-00054 - Resource busy and acquire with NOWAIT
• ORA-04020 - Deadlock detected while trying to lock object
• ORA-00051 - Timeout occurred while waiting for resource
• ORA-30006 - Resource busy; acquire with WAIT timeout

Quick Reference

Emergency Response Steps

1. ✓ Check current deadlock status and affected sessions
2. ✓ Analyze deadlock trace files for root cause
3. ✓ Identify which session to terminate (if manual intervention needed)
4. ✓ Kill appropriate session to break deadlock cycle
5. ✓ Implement application retry logic
6. ✓ Review and fix deadlock-prone code patterns

Quick Commands

-- Check for current deadlocks
SELECT COUNT(*) as potential_deadlocks
FROM v$lock l1, v$lock l2
WHERE l1.id1 = l2.id1 AND l1.id2 = l2.id2
  AND l1.block = 1 AND l2.request > 0;

-- Find recent deadlock traces
!find $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace -name "*trc" -mtime -1 -exec grep -l "DEADLOCK" {} \;

-- Kill deadlocked session
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Check deadlock-prone tables
SELECT object_name, COUNT(*) as lock_count
FROM v$locked_object lo, dba_objects o
WHERE lo.object_id = o.object_id
GROUP BY object_name
HAVING COUNT(*) > 1;

Prevention Best Practices

• Consistent lock ordering - Always acquire locks in the same order
• Minimize lock duration - Keep transactions as short as possible
• Use timeout values - Avoid NOWAIT, use reasonable timeouts
• Implement retry logic - Handle deadlocks gracefully in applications
• Consider lock-free algorithms - Use optimistic concurrency control
• Monitor regularly - Track deadlock patterns and trends