ORA-00054 완벽 가이드: resource busy and acquire with NOWAIT specified
ORA-00054란?
ORA-00054는 Oracle 데이터베이스에서 특정 리소스(주로 테이블이나 행)에 대한 잠금(Lock)을 획득하려 했지만, 해당 리소스가 이미 다른 세션에 의해 점유되어 있을 때 발생하는 에러입니다. 특히 NOWAIT 옵션이 지정된 상태에서 잠금 획득에 실패했을 때 즉시 에러를 반환하며, DDL 문장(ALTER TABLE, DROP TABLE 등) 실행 시 해당 객체에 DML 트랜잭션이 진행 중인 경우에도 빈번하게 발생합니다. 실무 환경에서는 배치 작업, 스키마 변경 작업, 또는 애플리케이션의 동시 접근이 많은 환경에서 특히 자주 마주치는 에러입니다.
주요 발생 원인
DDL 작업 중 활성 트랜잭션 존재
운영 중인 테이블에ALTER TABLE,DROP TABLE,TRUNCATE TABLE등의 DDL 명령을 실행할 때, 해당 테이블에 커밋되지 않은 DML(INSERT, UPDATE, DELETE) 트랜잭션이 존재하면 Oracle은 배타적 잠금을 획득할 수 없어 이 에러를 반환합니다. Oracle DDL은 내부적으로NOWAIT방식으로 잠금을 시도하기 때문에 대기 없이 즉시 에러가 발생합니다.SELECT ... FOR UPDATE NOWAIT 사용 시 행 잠금 충돌
애플리케이션 코드에서SELECT ... FOR UPDATE NOWAIT구문을 사용할 때, 조회하려는 행이 이미 다른 세션에 의해 잠겨 있다면 대기 없이 즉시 ORA-00054가 발생합니다. 이는 애플리케이션의 낙관적 잠금(Optimistic Locking) 전략 또는 빠른 실패(Fail-Fast) 패턴에서 의도적으로 사용하기도 하지만, 예상치 못한 충돌 시에는 에러 처리가 반드시 필요합니다.장시간 실행되는 트랜잭션 방치
개발자 또는 운영자가 SQL*Plus나 SQL Developer에서 DML을 실행한 후 COMMIT 또는 ROLLBACK 없이 세션을 그대로 두는 경우, 해당 잠금이 계속 유지됩니다. 이 상태에서 다른 작업자가 동일한 테이블에 DDL을 시도하거나 NOWAIT 잠금을 요청하면 ORA-00054가 발생하며, 야간 배치 작업이나 스키마 변경 작업 시 특히 문제가 됩니다.자동화 배치 프로세스 간 충돌
여러 배치 프로세스가 동일한 테이블에 동시에 접근하는 구조에서 실행 시간이 겹치면 잠금 충돌이 발생할 수 있습니다. 특히 스케줄러 설정 오류로 인해 중복 실행되거나, 이전 배치가 완료되기 전에 다음 배치가 시작되는 경우에 ORA-00054가 빈번하게 나타납니다.온라인 스키마 변경 미지원 환경에서의 DDL 실행
Oracle 11g 이상에서는ONLINE옵션으로 일부 DDL을 무중단으로 처리할 수 있지만, 이를 모르거나 지원하지 않는 DDL을 실행할 때 잠금 충돌이 발생합니다. 특히 인덱스 생성/재빌드, 파티션 작업 등에서 배타적 잠금이 필요한 경우 활성 트랜잭션과 충돌하여 에러가 발생합니다.
해결 방법
1단계: 현재 잠금 보유 세션 파악
가장 먼저 어떤 세션이 해당 리소스를 점유하고 있는지 확인해야 합니다.
-- 현재 활성화된 잠금 정보 조회
SELECT
s.sid,
s.serial#,
s.username,
s.status,
s.osuser,
s.machine,
s.program,
s.logon_time,
l.type AS lock_type,
l.mode_held,
l.mode_requested,
o.object_name,
o.object_type
FROM
v$session s
JOIN v$lock l ON s.sid = l.sid
JOIN dba_objects o ON l.id1 = o.object_id
WHERE
l.type IN ('TM', 'TX')
AND s.status = 'ACTIVE'
ORDER BY
s.logon_time;
-- 특정 테이블에 대한 잠금 세션 조회
SELECT
s.sid,
s.serial#,
s.username,
s.status,
s.machine,
s.program,
s.sql_id,
s.last_call_et AS elapsed_seconds,
o.object_name
FROM
v$session s
JOIN v$lock l ON s.sid = l.sid
JOIN dba_objects o ON l.id1 = o.object_id
WHERE
o.object_name = 'YOUR_TABLE_NAME' -- 대상 테이블명 입력
AND l.type = 'TM';
2단계: 블로킹 세션과 대기 세션 연관 조회
-- 블로킹 세션과 대기 세션을 함께 조회
SELECT
blocker.sid AS blocker_sid,
blocker.serial# AS blocker_serial,
blocker.username AS blocker_user,
blocker.status AS blocker_status,
blocker.machine AS blocker_machine,
blocker.program AS blocker_program,
waiter.sid AS waiter_sid,
waiter.serial# AS waiter_serial,
waiter.username AS waiter_user,
waiter.status AS waiter_status,
waiter.sql_id AS waiter_sql_id
FROM
v$session blocker
JOIN v$session waiter ON blocker.sid = waiter.blocking_session
WHERE
waiter.blocking_session IS NOT NULL;
3단계: 해당 세션이 실행 중인 SQL 확인
-- 블로킹 세션의 현재/최근 실행 SQL 확인
SELECT
s.sid,
s.serial#,
s.username,
q.sql_text,
q.sql_fulltext
FROM
v$session s
JOIN v$sql q ON s.sql_id = q.sql_id
WHERE
s.sid = &블로킹_SID;
4단계: 세션 종료 또는 COMMIT/ROLLBACK 유도
-- 방법 1: 해당 세션에 COMMIT 또는 ROLLBACK 요청 (DBA가 직접 불가, 세션 소유자에게 요청)
-- 방법 2: DBA 권한으로 세션 강제 종료 (최후의 수단)
-- 반드시 비즈니스 영향도 확인 후 실행!
ALTER SYSTEM KILL SESSION '&SID,&SERIAL#' IMMEDIATE;
-- 예시
ALTER SYSTEM KILL SESSION '142,38291' IMMEDIATE;
-- OS 레벨에서도 종료가 필요한 경우 (DISCONNECT SESSION)
ALTER SYSTEM DISCONNECT SESSION '142,38291' IMMEDIATE;
5단계: NOWAIT 대신 WAIT 옵션 사용
애플리케이션 코드에서 NOWAIT 대신 일정 시간 대기하는 방식으로 변경합니다.
-- 기존 방식 (즉시 에러 발생)
SELECT *
FROM employees
WHERE department_id = 10
FOR UPDATE NOWAIT;
-- 개선 방식 1: 특정 시간(초) 대기 후 실패
SELECT *
FROM employees
WHERE department_id = 10
FOR UPDATE WAIT 5; -- 5초 대기 후 실패
-- 개선 방식 2: 무한 대기 (기본값, 주의 필요)
SELECT *
FROM employees
WHERE department_id = 10
FOR UPDATE;
6단계: DDL 작업 전 잠금 세션 점검 스크립트
-- DDL 실행 전 해당 테이블의 활성 세션 및 미완료 트랜잭션 점검
DECLARE
v_table_name VARCHAR2(100) := 'YOUR_TABLE_NAME';
v_count NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
JOIN dba_objects o ON l.id1 = o.object_id
WHERE o.object_name = UPPER(v_table_name)
AND l.type = 'TM'
AND s.status = 'ACTIVE';
IF v_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('경고: ' || v_table_name || ' 테이블에 ' || v_count || '개의 활성 잠금이 존재합니다.');
DBMS_OUTPUT.PUT_LINE('DDL 실행 전 해당 세션의 트랜잭션을 완료하십시오.');
ELSE
DBMS_OUTPUT.PUT_LINE('안전: 활성 잠금 없음. DDL 실행 가능합니다.');
END IF;
END;
/
7단계: 인덱스/DDL ONLINE 옵션 활용
-- 기존 방식 (배타적 잠금 필요, ORA-00054 위험)
CREATE INDEX idx_emp_dept ON employees(department_id);
-- 개선 방식: ONLINE 옵션으로 DML 허용하면서 인덱스 생성
CREATE INDEX idx_emp_dept ON employees(department_id) ONLINE;
-- 인덱스 재빌드도 ONLINE 옵션 활용
ALTER INDEX idx_emp_dept REBUILD ONLINE;
예방 방법
1. 트랜잭션 관리 정책 수립 및 자동 COMMIT 방지
모든 DML 작업은 반드시 명시적으로 COMMIT 또는 ROLLBACK을 수행하도록 개발 가이드라인을 수립해야 합니다. SQL*Plus나 SQL Developer 사용 시 세션을 종료하기 전 반드시 트랜잭션을 완료하는 습관을 팀 내 공유하고, 가능하면 자동 세션 종료 타임아웃을 설정합니다.
-- 유휴 세션 자동 종료 프로파일 설정 (30분 유휴 시 세션 종료)
CREATE PROFILE limited_session LIMIT
IDLE_TIME 30
CONNECT_TIME 480
SESSIONS_PER_USER 5;
-- 사용자에게 프로파일 적용
ALTER USER app_user PROFILE limited_session;
2. DDL 작업 전 표준 점검 절차(SOP) 수립
스키마 변경 작업을 수행하기 전에 반드시 활성 트랜잭션 여부를 확인하는 표준 절차를 만들고, 가능하면 저트래픽 시간대(새벽 또는 주말)에 DDL을 수행하도록 정책을 수립합니다. 또한 Oracle의 LOCK_TIMEOUT 관련 파라미터를 활용하거나, DDL 전에 DBMS_LOCK 패키지를 이용한 커스텀 잠금 관리를 검토합니다.
-- DDL 실행 전 활성 세션 전체 점검 스크립트 (DBA용)
SELECT
s.sid,
s.serial#,
s.username,
s.status,
s.last_call_et AS idle_seconds,
s.machine,
s.program,
t.used_ublk AS undo_blocks_used
FROM
v$session s
LEFT JOIN v$transaction t ON s.taddr = t.addr
WHERE
s.status = 'ACTIVE'
AND s.type = 'USER'
AND t.addr IS NOT NULL -- 활성 트랜잭션이 있는 세션만
ORDER BY
t.used_ublk DESC;
3. 애플리케이션 레벨 에러 처리 및 재시도 로직 구현
NOWAIT 옵션을 사용하는 코드에서는 ORA-00054 예외를 반드시 캐치하고, 적절한 재시도 로직 또는 사용자 친화적 메시지를 제공해야 합니다. Java, Python 등 애플리케이션 레이어에서 재시도 횟수와 대기 시간을 설정하는 패
Top comments (0)