Oracle ORA-00054 오류 원인과 해결 방법 완벽 가이드

#oracle #database #dba #sql

ORA-00054 resource busy and acquire with NOWAIT specified 는?

ORA-00054 에러는 특정 테이블이나 리소스에 대해 잠금(Lock)을 획득하려 할 때, 해당 리소스가 이미 다른 세션에 의해 점유된 상태에서 NOWAIT 옵션이 지정된 경우 즉시 발생하는 에러입니다. 일반적으로 ALTER TABLE, DROP TABLE, TRUNCATE, LOCK TABLE ... NOWAIT, 또는 DDL 작업 수행 시 다른 트랜잭션이 해당 객체를 사용 중이라면 Oracle은 대기하지 않고 곧바로 이 에러를 반환합니다. 실무에서는 배치 작업이나 테이블 구조 변경 작업 중 활성 세션이 해당 테이블에 DML을 수행 중일 때 빈번하게 발생하므로, 운영 환경에서 특히 주의가 필요합니다.

주요 발생 원인

1. DDL 작업 중 활성 DML 트랜잭션 충돌

ALTER TABLE, TRUNCATE TABLE, DROP TABLE 등 DDL 문은 내부적으로 해당 테이블에 대한 배타적 잠금(Exclusive Lock)을 요구합니다. 다른 세션이 해당 테이블에 INSERT, UPDATE, DELETE 등의 DML 작업을 수행하고 아직 커밋 또는 롤백하지 않은 상태라면, DDL 작업은 잠금을 획득하지 못하고 ORA-00054를 반환합니다. 특히 장시간 수행되는 배치 프로그램이 테이블을 점유하고 있을 때 운영자가 긴급 DDL 작업을 시도하는 상황에서 자주 목격됩니다.

2. NOWAIT 또는 짧은 WAIT 옵션이 설정된 락 요청

애플리케이션 레벨에서 SELECT ... FOR UPDATE NOWAIT 또는 LOCK TABLE ... IN EXCLUSIVE MODE NOWAIT와 같이 명시적으로 NOWAIT를 지정한 경우, 잠금을 즉시 획득하지 못하면 바로 에러가 발생합니다. 이는 교착 상태를 방지하기 위한 의도적인 설계일 수 있으나, 트래픽이 집중되는 시간대에 경합이 잦은 테이블에 대해서는 불필요한 에러를 유발할 수 있습니다. 애플리케이션 코드 리뷰 없이 무조건 NOWAIT를 사용하는 관행이 문제의 근원이 되기도 합니다.

3. 장기 미완료 트랜잭션(Long-running Transaction) 또는 락 좀비 세션

네트워크 단절, 애플리케이션 비정상 종료 등으로 인해 커밋/롤백 없이 트랜잭션이 열린 채로 방치된 세션이 존재할 경우, 해당 세션이 잠금을 계속 보유하게 됩니다. 이런 좀비 세션은 다른 정상적인 작업들을 모두 차단하며, 장시간 방치될 경우 연쇄적인 락 대기 현상을 일으켜 전체 서비스 장애로 이어질 수 있습니다. 실무에서는 정기적인 세션 모니터링과 임계치 기반 알림 시스템이 반드시 필요합니다.

해결 방법

원인 1 해결: 락을 보유한 세션 식별 및 처리

먼저 어떤 세션이 해당 객체에 락을 보유하고 있는지 확인합니다.

-- 현재 락을 보유 중인 세션과 대기 중인 세션 조회
SELECT
    l.sid,
    l.serial#,
    l.username,
    l.status,
    l.osuser,
    l.machine,
    l.program,
    l.logon_time,
    o.object_name,
    o.object_type,
    l2.type        AS lock_type,
    l2.lmode       AS lock_mode,
    l2.request     AS request_mode
FROM
    v$session  l,
    v$lock     l2,
    dba_objects o
WHERE
    l.sid      = l2.sid
    AND l2.id1 = o.object_id
    AND l2.type = 'TM'
ORDER BY
    l2.lmode DESC;

-- 특정 테이블에 걸린 락을 보유한 세션의 SQL 확인
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    q.sql_text,
    s.last_call_et AS elapsed_seconds
FROM
    v$session  s,
    v$sqlarea  q,
    v$lock     l,
    dba_objects o
WHERE
    s.sql_id    = q.sql_id(+)
    AND s.sid   = l.sid
    AND l.id1   = o.object_id
    AND l.type  = 'TM'
    AND o.object_name = UPPER('&테이블명')
    AND l.lmode > 0;

락을 보유한 세션을 확인한 후, 업무 영향도를 판단하여 강제 종료합니다.

-- 세션 강제 종료 (SID, SERIAL# 는 위 조회 결과 참조)
ALTER SYSTEM KILL SESSION '1234,56789' IMMEDIATE;

-- RAC 환경에서는 INST_ID 포함
ALTER SYSTEM KILL SESSION '1234,56789,@1' IMMEDIATE;

원인 2 해결: NOWAIT 대신 WAIT 절 사용

NOWAIT 대신 적절한 대기 시간을 설정하여 일시적인 경합 상황을 자연스럽게 해소합니다.

-- NOWAIT 대신 WAIT N 초 지정 (최대 N초간 대기 후 획득 실패 시 에러)
SELECT *
FROM   orders
WHERE  order_id = 1001
FOR UPDATE WAIT 10;  -- 10초 대기

-- LOCK TABLE에 WAIT 절 적용
LOCK TABLE orders IN EXCLUSIVE MODE WAIT 30;  -- 30초 대기

DDL 작업 시 Oracle 11g 이상에서는 DDL_LOCK_TIMEOUT 파라미터 활용이 가능합니다.

-- 세션 레벨에서 DDL 락 대기 시간 설정 (초 단위)
ALTER SESSION SET DDL_LOCK_TIMEOUT = 60;

-- 이후 DDL 수행 시 최대 60초 대기 후 실행
ALTER TABLE orders ADD (memo VARCHAR2(200));

-- 시스템 레벨 설정 (영구 적용)
ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 30 SCOPE = BOTH;

-- 현재 설정 확인
SHOW PARAMETER DDL_LOCK_TIMEOUT;

원인 3 해결: 장기 미완료 트랜잭션 감지 및 정리

-- 오래된 미완료 트랜잭션 세션 조회 (30분 이상 경과)
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    ROUND((SYSDATE - l.start_time) * 24 * 60, 2) AS elapsed_min,
    l.used_ublk,
    l.used_urec
FROM
    v$session     s,
    v$transaction l
WHERE
    s.taddr = l.addr
    AND (SYSDATE - l.start_time) * 24 * 60 > 30
ORDER BY
    elapsed_min DESC;

-- 락 체인(Lock Chain) 전체 구조 파악: 블로커와 웨이터 한눈에 확인
SELECT
    DECODE(l.block, 0, '대기중', 1, '락보유(블로커)', '기타') AS lock_status,
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.last_call_et,
    o.object_name
FROM
    v$lock     l,
    v$session  s,
    dba_objects o
WHERE
    l.sid  = s.sid
    AND l.id1 = o.object_id(+)
    AND l.type = 'TM'
ORDER BY
    l.block DESC, l.id1;

예방 방법

1. DDL 작업 전 사전 점검 및 유지보수 윈도우 활용

운영 환경에서 DDL 작업은 반드시 서비스 트래픽이 최소화된 유지보수 윈도우(Maintenance Window) 시간대에 수행해야 합니다. 작업 전에는 아래 스크립트로 해당 테이블에 활성 세션이 없는지 반드시 확인하고, DDL_LOCK_TIMEOUT 파라미터를 적절히 설정하여 일시적인 경합에 유연하게 대처하는 것이 Best Practice입니다.

-- DDL 작업 전 활성 세션 사전 점검 스크립트
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    s.last_call_et AS elapsed_sec,
    t.start_time   AS tx_start
FROM
    v$session     s,
    v$transaction t
WHERE
    s.taddr = t.addr(+)
    AND s.username IS NOT NULL
    AND s.status   = 'ACTIVE'
ORDER BY
    elapsed_sec DESC;

2. 정기적인 락 모니터링 및 자동 알림 체계 구축

장기 락 세션을 실시간으로 탐지할 수 있는 모니터링 잡을 구성하고, 임계치 초과 시 DBA에게 알림이 발송되도록 자동화해야 합니다. Oracle Enterprise Manager(OEM) 또는 커스텀 DBMS_SCHEDULER 잡을 활용하여 5분 단위로 장기 락 세션을 점검하고 로그를 남기면, 문제 발생 시 신속한 원인 분석이 가능합니다.

-- DBMS_SCHEDULER를 이용한 정기 락 모니터링 잡 생성 예시
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_LOCK_MONITOR',
        job_type        => 'PLSQL_BLOCK',
        job_action      => '
            BEGIN
                -- 10분 이상 락 보유 세션 감지 시 로그 테이블에 기록
                INSERT INTO dba_lock_log (sid, serial#, username, elapsed_min, log_time)
                SELECT s.sid, s.serial#, s.username,
                       ROUND((SYSDATE - t.start_time)*24*60, 2),
                       SYSDATE
                FROM   v$session s, v$transaction t
                WHERE  s.taddr = t.addr
                AND    (SYSDATE - t.start_time)*24*60 > 10;
                COMMIT;
            END;
        ',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
        enabled         => TRUE,
        comments        => '장기 락 세션 5분 주기 모니터링'
    );
END;
/