ORA-00054 Resource Busy - Lock Resolution and Conflict Management

ORA-00054: Resource Busy and Acquire with NOWAIT Specified

Error Overview

Error Text: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

This error occurs when a session tries to acquire a lock on a resource (table, row, or object) that is already locked by another session, and the operation was specified with NOWAIT or a timeout that has expired. It’s a common concurrency control error in high-transaction environments.

Understanding Oracle Locking

Lock Types and Hierarchy

Oracle Lock Hierarchy
├── TX (Transaction) Locks - Row-level locks
├── TM (Table) Locks - Table-level locks
├── UL (User) Locks - User-defined locks
└── DDL Locks - Schema object locks

Common Lock Scenarios

• DML Operations - INSERT, UPDATE, DELETE causing row locks
• DDL Operations - ALTER TABLE, DROP TABLE requiring exclusive locks
• SELECT FOR UPDATE - Explicit row locking
• Index Maintenance - Concurrent index operations

Diagnostic Steps

1. Identify Blocking Sessions

-- Find blocking and waiting sessions
SELECT
  s1.sid as blocking_sid,
  s1.serial# as blocking_serial,
  s1.username as blocking_user,
  s1.program as blocking_program,
  s1.machine as blocking_machine,
  s1.sql_id as blocking_sql_id,
  s2.sid as waiting_sid,
  s2.serial# as waiting_serial,
  s2.username as waiting_user,
  s2.program as waiting_program,
  s2.sql_id as waiting_sql_id,
  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 lock_held,
  DECODE(l2.request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive') as lock_requested
FROM v$lock l1, v$lock l2, v$session s1, v$session s2
WHERE l1.block = 1
  AND l2.request > 0
  AND l1.id1 = l2.id1
  AND l1.id2 = l2.id2
  AND l1.sid = s1.sid
  AND l2.sid = s2.sid;

-- Alternative comprehensive blocking query
SELECT
  blocking_session,
  sid as waiting_session,
  serial# as waiting_serial,
  username,
  program,
  machine,
  sql_id,
  event,
  wait_time_micro/1000000 as wait_seconds,
  CASE
    WHEN blocking_session IS NOT NULL THEN 'BLOCKED'
    ELSE 'NOT BLOCKED'
  END as status
FROM v$session
WHERE blocking_session IS NOT NULL
   OR sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
ORDER BY blocking_session NULLS FIRST, sid;

2. Analyze Lock Details

-- Detailed lock information
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  l.type as lock_type,
  DECODE(l.lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive') as mode_held,
  DECODE(l.request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive') as mode_requested,
  l.id1,
  l.id2,
  l.ctime as lock_time_seconds,
  o.owner,
  o.object_name,
  o.object_type
FROM v$lock l, v$session s, dba_objects o
WHERE l.sid = s.sid
  AND l.id1 = o.object_id(+)
  AND l.type IN ('TX', 'TM')
ORDER BY l.ctime DESC;

-- Check for DDL locks
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.sql_id,
  l.type,
  l.mode_held,
  l.mode_requested,
  o.owner,
  o.name as object_name,
  o.type as object_type
FROM dba_ddl_locks l, v$session s, dba_objects o
WHERE l.session_id = s.sid
  AND l.owner = o.owner(+)
  AND l.name = o.object_name(+)
ORDER BY l.mode_held DESC;

3. Identify Locked Objects and Rows

-- Find locked rows (for TX locks)
SELECT
  s.sid,
  s.serial#,
  s.username,
  t.xidusn,
  t.xidslot,
  t.xidsqn,
  r.object_id,
  o.owner,
  o.object_name,
  r.file#,
  r.block#,
  r.row#
FROM v$session s, v$transaction t, v$locked_object r, dba_objects o
WHERE s.saddr = t.ses_addr
  AND t.addr = r.xidusn
  AND r.object_id = o.object_id
ORDER BY s.sid;

-- Find table locks
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  o.owner,
  o.object_name,
  o.object_type,
  l.oracle_username,
  l.os_user_name,
  l.process,
  DECODE(l.locked_mode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive') as lock_mode
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
  AND l.session_id = s.sid
ORDER BY o.owner, o.object_name;

4. Check Current SQL and Wait Events

-- Current SQL for blocking and waiting sessions
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.status,
  s.event,
  s.wait_time,
  s.seconds_in_wait,
  s.sql_id,
  sq.sql_text
FROM v$session s, v$sqlarea sq
WHERE s.sql_address = sq.address(+)
  AND s.sql_hash_value = sq.hash_value(+)
  AND (s.blocking_session IS NOT NULL
       OR s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL))
ORDER BY s.blocking_session NULLS FIRST;

-- Wait events related to locks
SELECT
  sid,
  serial#,
  username,
  event,
  wait_class,
  state,
  wait_time_micro/1000000 as wait_seconds,
  time_waited_micro/1000000 as total_wait_seconds
FROM v$session_wait
WHERE event LIKE '%enq%'
   OR event LIKE '%lock%'
   OR event LIKE '%busy%'
ORDER BY wait_time_micro DESC;

Immediate Solutions

Solution 1: Kill Blocking Sessions

Identify and Terminate Blocking Sessions

-- Find long-running blocking sessions
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.machine,
  s.logon_time,
  ROUND((SYSDATE - s.logon_time) * 24, 2) as hours_connected,
  s.last_call_et as seconds_since_last_call,
  s.status,
  s.sql_id,
  COUNT(w.sid) as sessions_blocked
FROM v$session s
LEFT JOIN v$session w ON s.sid = w.blocking_session
WHERE s.sid IN (SELECT DISTINCT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
GROUP BY s.sid, s.serial#, s.username, s.program, s.machine, s.logon_time, s.last_call_et, s.status, s.sql_id
ORDER BY sessions_blocked DESC, hours_connected DESC;

-- Kill specific blocking session (be very careful!)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Cancel specific SQL statement
ALTER SYSTEM CANCEL SQL 'sid,serial#,sql_id';

-- For long-running sessions, try disconnect first
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

Solution 2: Force Unlock Objects

Handle Specific Lock Types

-- For table locks, check if we can work around them
-- Enable row movement to avoid some lock conflicts
ALTER TABLE schema.table_name ENABLE ROW MOVEMENT;

-- For DDL operations, check for long-running transactions
SELECT
  s.sid,
  s.serial#,
  s.username,
  t.start_time,
  t.used_ublk,
  t.used_urec,
  ROUND((SYSDATE - t.start_date) * 24 * 60, 2) as minutes_active
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
  AND (SYSDATE - t.start_date) * 24 * 60 > 30  -- Running > 30 minutes
ORDER BY minutes_active DESC;

-- Commit or rollback long transactions if safe
-- COMMIT; -- Only if you're sure about the transaction
-- ROLLBACK; -- To release locks

Solution 3: Retry with Different Strategy

Modify Application Logic

-- Instead of NOWAIT, use timeout
-- BAD: Immediate failure
SELECT * FROM table_name WHERE id = 123 FOR UPDATE NOWAIT;

-- GOOD: Wait up to 30 seconds
SELECT * FROM table_name WHERE id = 123 FOR UPDATE WAIT 30;

-- Alternative: Use SKIP LOCKED for queue-like processing
SELECT * FROM work_queue
WHERE status = 'PENDING'
ORDER BY priority DESC
FOR UPDATE SKIP LOCKED;

-- For batch operations, process in smaller chunks
-- Instead of locking entire table
UPDATE large_table SET status = 'PROCESSED'
WHERE batch_id = 12345;

-- Process in chunks
UPDATE large_table SET status = 'PROCESSED'
WHERE batch_id = 12345
  AND ROWNUM <= 1000;

Long-Term Solutions

1. Implement Lock Monitoring

Create Lock Monitoring System

-- Create lock monitoring table
CREATE TABLE dba_lock_alerts (
  alert_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  blocking_sid NUMBER,
  blocking_user VARCHAR2(30),
  waiting_sid NUMBER,
  waiting_user VARCHAR2(30),
  lock_type VARCHAR2(10),
  object_owner VARCHAR2(30),
  object_name VARCHAR2(30),
  lock_duration_seconds NUMBER,
  alert_level VARCHAR2(20),
  action_taken VARCHAR2(500)
);

-- Lock monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_locks AS
  CURSOR lock_cursor IS
    SELECT
      s1.sid as blocking_sid,
      s1.username as blocking_user,
      s1.program as blocking_program,
      s2.sid as waiting_sid,
      s2.username as waiting_user,
      l1.type as lock_type,
      l1.ctime as lock_duration,
      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.block = 1
      AND l2.request > 0
      AND l1.id1 = l2.id1
      AND l1.id2 = l2.id2
      AND l1.sid = s1.sid
      AND l2.sid = s2.sid
      AND l1.id1 = o.object_id(+)
      AND l1.ctime > 300;  -- Locks held > 5 minutes

  v_alert_level VARCHAR2(20);
BEGIN
  FOR lock_rec IN lock_cursor LOOP
    -- Determine alert level based on lock duration
    IF lock_rec.lock_duration > 3600 THEN  -- > 1 hour
      v_alert_level := 'CRITICAL';
    ELSIF lock_rec.lock_duration > 1800 THEN  -- > 30 minutes
      v_alert_level := 'WARNING';
    ELSE
      v_alert_level := 'INFO';
    END IF;

    -- Log the lock situation
    INSERT INTO dba_lock_alerts (
      blocking_sid, blocking_user, waiting_sid, waiting_user,
      lock_type, object_owner, object_name, lock_duration_seconds, alert_level
    ) VALUES (
      lock_rec.blocking_sid, lock_rec.blocking_user,
      lock_rec.waiting_sid, lock_rec.waiting_user,
      lock_rec.lock_type, lock_rec.object_owner, lock_rec.object_name,
      lock_rec.lock_duration, v_alert_level
    );

    -- Auto-kill critical long-running locks (be very careful!)
    IF v_alert_level = 'CRITICAL' AND lock_rec.blocking_program LIKE '%background%' THEN
      BEGIN
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' ||
                         lock_rec.blocking_sid || ',0'' IMMEDIATE';

        UPDATE dba_lock_alerts
        SET action_taken = 'Killed blocking session automatically'
        WHERE blocking_sid = lock_rec.blocking_sid
          AND waiting_sid = lock_rec.waiting_sid
          AND alert_time = (SELECT MAX(alert_time) FROM dba_lock_alerts
                           WHERE blocking_sid = lock_rec.blocking_sid);
      EXCEPTION
        WHEN OTHERS THEN
          UPDATE dba_lock_alerts
          SET action_taken = 'Failed to kill session: ' || SQLERRM
          WHERE blocking_sid = lock_rec.blocking_sid
            AND waiting_sid = lock_rec.waiting_sid
            AND alert_time = (SELECT MAX(alert_time) FROM dba_lock_alerts
                             WHERE blocking_sid = lock_rec.blocking_sid);
      END;
    END IF;
  END LOOP;

  COMMIT;
END;
/

-- Schedule lock monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_LOCKS',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_locks',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
    enabled => TRUE
  );
END;
/

2. Optimize Application Design

Reduce Lock Contention

-- Use optimistic locking instead of pessimistic
-- BAD: Locks row immediately
SELECT * FROM accounts WHERE account_id = 123 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;

-- GOOD: Optimistic concurrency control
SELECT account_id, balance, version FROM accounts WHERE account_id = 123;
-- Application logic checks version
UPDATE accounts
SET balance = balance - 100, version = version + 1
WHERE account_id = 123 AND version = :old_version;
-- Check if update affected 1 row, retry if not

-- Implement row-level versioning
ALTER TABLE critical_table ADD (
  version_number NUMBER DEFAULT 1,
  last_updated_by VARCHAR2(30),
  last_updated_date TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- Create trigger to manage versioning
CREATE OR REPLACE TRIGGER trg_critical_table_version
BEFORE UPDATE ON critical_table
FOR EACH ROW
BEGIN
  :NEW.version_number := :OLD.version_number + 1;
  :NEW.last_updated_by := USER;
  :NEW.last_updated_date := SYSTIMESTAMP;
END;
/

3. Implement Lock-Free Alternatives

Queue-Based Processing

-- Create work queue table with lock-free processing
CREATE TABLE work_queue (
  queue_id NUMBER PRIMARY KEY,
  status VARCHAR2(20) DEFAULT 'PENDING',
  priority NUMBER DEFAULT 1,
  payload CLOB,
  created_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  processed_date TIMESTAMP,
  processed_by VARCHAR2(30)
);

-- Lock-free queue processing
CREATE OR REPLACE PROCEDURE process_queue_items AS
  CURSOR queue_cursor IS
    SELECT queue_id, payload
    FROM work_queue
    WHERE status = 'PENDING'
    ORDER BY priority DESC, created_date
    FOR UPDATE SKIP LOCKED;  -- Skip locked rows

  v_processed_count NUMBER := 0;
BEGIN
  FOR queue_rec IN queue_cursor LOOP
    BEGIN
      -- Process the item
      process_work_item(queue_rec.payload);

      -- Mark as completed
      UPDATE work_queue
      SET status = 'COMPLETED',
          processed_date = SYSTIMESTAMP,
          processed_by = USER
      WHERE queue_id = queue_rec.queue_id;

      v_processed_count := v_processed_count + 1;

      -- Commit every 100 items
      IF MOD(v_processed_count, 100) = 0 THEN
        COMMIT;
      END IF;

    EXCEPTION
      WHEN OTHERS THEN
        -- Mark as failed
        UPDATE work_queue
        SET status = 'FAILED',
            processed_date = SYSTIMESTAMP,
            processed_by = USER
        WHERE queue_id = queue_rec.queue_id;

        -- Log error but continue processing
        INSERT INTO error_log (error_date, error_message, queue_id)
        VALUES (SYSTIMESTAMP, SQLERRM, queue_rec.queue_id);
    END;
  END LOOP;

  COMMIT;

  DBMS_OUTPUT.PUT_LINE('Processed ' || v_processed_count || ' items');
END;
/

Prevention Strategies

1. Connection and Transaction Management

-- Set appropriate timeout values
ALTER SYSTEM SET distributed_lock_timeout = 60 SCOPE=BOTH;

-- Configure automatic deadlock detection
ALTER SYSTEM SET "_deadlock_resolution_signal_frequency" = 1 SCOPE=BOTH;

-- Monitor long-running transactions
CREATE OR REPLACE VIEW v_long_transactions AS
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.program,
  t.start_time,
  ROUND((SYSDATE - t.start_date) * 24 * 60, 2) as minutes_active,
  t.used_ublk as undo_blocks,
  t.status
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
  AND (SYSDATE - t.start_date) * 24 * 60 > 15  -- > 15 minutes
ORDER BY minutes_active DESC;

2. Application Best Practices

// Java example of proper connection management
public class DatabaseManager {
    private static final int LOCK_TIMEOUT = 30; // seconds

    public void updateAccountBalance(Connection conn, int accountId, double amount)
            throws SQLException {
        // Set lock timeout at connection level
        try (Statement stmt = conn.createStatement()) {
            stmt.execute("ALTER SESSION SET ddl_lock_timeout = " + LOCK_TIMEOUT);
        }

        // Use timeout instead of NOWAIT
        String sql = "SELECT balance FROM accounts WHERE account_id = ? FOR UPDATE WAIT " + LOCK_TIMEOUT;

        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, accountId);

            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    double currentBalance = rs.getDouble("balance");

                    // Perform business logic
                    double newBalance = currentBalance + amount;

                    // Update with minimal lock time
                    String updateSql = "UPDATE accounts SET balance = ? WHERE account_id = ?";
                    try (PreparedStatement updateStmt = conn.prepareStatement(updateSql)) {
                        updateStmt.setDouble(1, newBalance);
                        updateStmt.setInt(2, accountId);
                        updateStmt.executeUpdate();
                    }

                    // Commit quickly to release locks
                    conn.commit();
                }
            }
        } catch (SQLException e) {
            if (e.getErrorCode() == 54) { // ORA-00054
                // Handle resource busy error
                throw new ResourceBusyException("Account is currently being updated. Please try again.", e);
            }
            throw e;
        }
    }
}

3. Database Configuration

-- Optimize lock-related parameters
ALTER SYSTEM SET lock_sga = TRUE SCOPE=SPFILE;
ALTER SYSTEM SET pre_page_sga = TRUE SCOPE=SPFILE;

-- Configure appropriate lock timeout
ALTER SYSTEM SET ddl_lock_timeout = 60 SCOPE=BOTH;

-- Enable lock monitoring
ALTER SYSTEM SET timed_statistics = TRUE SCOPE=BOTH;

-- For high concurrency, consider
ALTER SYSTEM SET "_row_locking" = always SCOPE=BOTH;

Related Errors

• ORA-00060 - Deadlock detected
• ORA-01000 - Maximum open cursors exceeded
• ORA-30006 - Resource busy, acquire with WAIT timeout
• ORA-04068 - Existing state of packages discarded

Quick Reference

Emergency Response Steps

1. ✓ Identify blocking and waiting sessions
2. ✓ Determine lock type and duration
3. ✓ Analyze impact of killing blocking session
4. ✓ Kill blocking session if safe to do so
5. ✓ Monitor for lock pattern recurrence
6. ✓ Implement preventive measures

Quick Commands

-- Find blocking sessions
SELECT blocking_session, sid, username, program, sql_id
FROM v$session
WHERE blocking_session IS NOT NULL;

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

-- Check lock waits
SELECT event, count(*) FROM v$session_wait
WHERE wait_class = 'Concurrency'
GROUP BY event ORDER BY count(*) DESC;

-- Monitor lock duration
SELECT type, ctime, count(*)
FROM v$lock
WHERE ctime > 60
GROUP BY type, ctime
ORDER BY ctime DESC;

Lock Prevention Guidelines

• Keep transactions short - Minimize lock hold time
• Use appropriate isolation levels - READ COMMITTED for most cases
• Process in smaller batches - Avoid large bulk operations
• Use SKIP LOCKED - For queue processing
• Implement timeouts - Don’t use NOWAIT unless necessary
• Monitor regularly - Set up automated lock detection