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

#oracle #database #dba #sql

ORA-00060 deadlock detected while waiting for resource 는?

ORA-00060 에러는 두 개 이상의 세션이 서로 상대방이 보유한 리소스(주로 행 잠금)를 기다리면서 교착 상태(Deadlock)에 빠졌을 때 Oracle이 자동으로 감지하여 발생시키는 에러입니다. 예를 들어, 세션 A가 테이블 T1의 행을 잠근 채 T2의 행을 기다리고, 세션 B는 반대로 T2의 행을 잠근 채 T1의 행을 기다리는 순환 대기 상황이 이에 해당합니다. Oracle은 이 교착 상태를 감지하면 두 세션 중 하나를 희생자(Victim)로 선택하여 해당 트랜잭션을 롤백시키고 ORA-00060 에러를 반환하며, 나머지 세션은 정상적으로 처리를 계속할 수 있게 됩니다.

주요 발생 원인

1. 서로 다른 순서로 동일한 리소스에 접근하는 트랜잭션

가장 흔한 원인으로, 두 트랜잭션이 동일한 테이블의 행들을 서로 다른 순서로 UPDATE 또는 DELETE할 때 발생합니다. 예를 들어 트랜잭션 A가 행 1 → 행 2 순서로 잠그는 동안, 트랜잭션 B는 행 2 → 행 1 순서로 잠그려 하면 교착 상태가 발생합니다. 이는 배치 처리나 대량 데이터 업데이트 시 특히 자주 나타나는 패턴입니다.

2. 인덱스 부재로 인한 불필요한 테이블 전체 잠금

외래 키(Foreign Key) 컬럼에 인덱스가 없는 경우, 자식 테이블에서 부모 테이블의 행을 수정하거나 삭제할 때 Oracle이 자식 테이블 전체에 테이블 레벨 잠금(Share Lock)을 획득하려 시도합니다. 이 상황에서 다른 세션이 동일한 자식 테이블의 다른 행을 수정하고 있다면 교착 상태가 매우 쉽게 발생합니다. 외래 키 인덱스 누락은 Oracle DBA가 가장 먼저 점검해야 할 항목 중 하나입니다.

3. 비트맵 인덱스(Bitmap Index) 사용 환경에서의 동시 DML

비트맵 인덱스는 OLAP 환경에서 조회 성능을 높이는 데 유리하지만, 동시에 여러 세션이 DML 작업을 수행할 경우 인덱스 블록 수준에서 교착 상태를 유발할 수 있습니다. 비트맵 인덱스의 특성상 하나의 인덱스 엔트리가 여러 행을 가리키기 때문에, 서로 다른 행을 수정하는 두 세션이 동일한 인덱스 블록을 경쟁하게 됩니다. OLTP 환경에서는 비트맵 인덱스 사용을 지양해야 하는 핵심 이유 중 하나입니다.

해결 방법

원인 1 해결 - 트랜잭션 내 리소스 접근 순서 통일

모든 트랜잭션에서 동일한 순서로 데이터에 접근하도록 코드를 수정합니다. 배치 작업의 경우 ORDER BY를 활용하여 항상 동일한 순서로 행을 처리하도록 강제합니다.

-- 문제가 되는 패턴: 서로 다른 순서로 UPDATE
-- 세션 A
UPDATE orders SET status = 'PROCESSING' WHERE order_id = 1001;
UPDATE orders SET status = 'PROCESSING' WHERE order_id = 1002;

-- 세션 B (반대 순서 - 데드락 유발)
UPDATE orders SET status = 'CANCELLED' WHERE order_id = 1002;
UPDATE orders SET status = 'CANCELLED' WHERE order_id = 1001;

-- 해결책: 항상 order_id 오름차순으로 정렬하여 처리
-- 배치 처리 시 커서를 통해 순서 보장
DECLARE
    CURSOR c_orders IS
        SELECT order_id
        FROM orders
        WHERE status = 'PENDING'
        ORDER BY order_id ASC  -- 반드시 동일한 순서로 잠금
        FOR UPDATE;
BEGIN
    FOR r IN c_orders LOOP
        UPDATE orders
        SET    status = 'PROCESSING',
               updated_at = SYSDATE
        WHERE  order_id = r.order_id;
    END LOOP;
    COMMIT;
END;
/

-- FOR UPDATE SKIP LOCKED 활용: 잠긴 행은 건너뛰고 처리 (Oracle 11g R2 이상)
-- 큐 방식의 작업 처리에서 데드락을 원천 차단
DECLARE
    CURSOR c_jobs IS
        SELECT job_id, job_data
        FROM   job_queue
        WHERE  status = 'READY'
        ORDER BY job_id
        FOR UPDATE SKIP LOCKED;
BEGIN
    FOR r IN c_jobs LOOP
        -- 잠금 획득한 행만 처리 (다른 세션과 충돌 없음)
        UPDATE job_queue
        SET    status = 'RUNNING',
               started_at = SYSDATE
        WHERE  job_id = r.job_id;

        -- 실제 작업 처리 로직
        process_job(r.job_id, r.job_data);

        UPDATE job_queue
        SET    status = 'DONE'
        WHERE  job_id = r.job_id;

        COMMIT; -- 행 단위로 즉시 커밋하여 잠금 해제
    END LOOP;
END;
/

원인 2 해결 - 외래 키 인덱스 누락 확인 및 생성

아래 쿼리로 외래 키 인덱스 누락 여부를 즉시 확인하고, 누락된 인덱스를 생성합니다.

-- 외래 키 인덱스 누락 점검 쿼리 (실무 필수 쿼리)
SELECT
    c.table_name,
    c.constraint_name,
    c.column_name,
    c.position,
    'MISSING INDEX' AS status
FROM
    user_cons_columns c
    JOIN user_constraints fk
        ON c.constraint_name = fk.constraint_name
       AND fk.constraint_type = 'R'
WHERE
    NOT EXISTS (
        SELECT 1
        FROM   user_ind_columns i
        WHERE  i.table_name  = c.table_name
          AND  i.column_name = c.column_name
          AND  i.column_position = c.position
    )
ORDER BY
    c.table_name, c.constraint_name, c.position;

-- 누락된 외래 키 인덱스 생성 예시
-- 예: ORDER_ITEMS 테이블의 CUSTOMER_ID FK 컬럼에 인덱스 누락된 경우

-- 인덱스 생성 (온라인 방식으로 서비스 중단 없이 가능)
CREATE INDEX idx_order_items_cust_id
    ON order_items (customer_id)
    TABLESPACE idx_tbsp
    ONLINE;  -- 운영 중에도 안전하게 생성

-- 생성 후 FK 인덱스 활용 여부 재점검
SELECT
    i.index_name,
    i.table_name,
    ic.column_name,
    i.status
FROM
    user_indexes i
    JOIN user_ind_columns ic ON i.index_name = ic.index_name
WHERE
    i.table_name = 'ORDER_ITEMS'
ORDER BY
    i.index_name, ic.column_position;

원인 3 해결 - 비트맵 인덱스를 B-Tree 인덱스로 전환

-- 현재 비트맵 인덱스 현황 확인
SELECT
    index_name,
    table_name,
    index_type,
    status
FROM
    user_indexes
WHERE
    index_type = 'BITMAP'
ORDER BY
    table_name;

-- OLTP 환경에서 비트맵 → B-TREE 전환
-- 1단계: 기존 비트맵 인덱스 삭제
DROP INDEX bitmap_idx_sales_region;

-- 2단계: B-TREE 인덱스로 재생성
CREATE INDEX btree_idx_sales_region
    ON sales (region_code)
    TABLESPACE idx_tbsp
    ONLINE;

-- 데드락 발생 시 트레이스 파일에서 원인 확인하는 쿼리
-- (에러 발생 후 즉시 실행하여 deadlock 그래프 위치 파악)
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.sql_id,
    s.blocking_session,
    s.wait_class,
    s.event,
    s.seconds_in_wait
FROM
    v$session s
WHERE
    s.blocking_session IS NOT NULL
    OR s.event LIKE '%enq%'
ORDER BY
    s.blocking_session NULLS LAST;

-- alert log 및 trace 파일 위치 확인 (데드락 발생 시 그래프 분석용)
SELECT
    value AS trace_directory
FROM
    v$parameter
WHERE
    name = 'user_dump_dest';

-- 최근 데드락 발생 이력 조회 (AWR 활용, Oracle Enterprise Edition)
SELECT
    snap_id,
    instance_number,
    begin_interval_time,
    end_interval_time
FROM
    dba_hist_snapshot
WHERE
    begin_interval_time >= SYSDATE - 1
ORDER BY
    snap_id DESC;

예방 방법

1. 트랜잭션을 짧고 일관된 순서로 설계하고 주기적으로 데드락 모니터링 자동화

트랜잭션 내에서 잠금을 보유하는 시간을 최소화하고, 여러 테이블이나 행에 접근할 때는 반드시 애플리케이션 전체에서 동일한 순서를 강제하는 개발 가이드라인을 수립해야 합니다. 또한 아래와 같이 데드락 발생을 자동으로 감지하는 모니터링 스크립트를 스케줄러에 등록하여 발생 즉시 알림을 받도록 구성하면 신속한 대응이 가능합니다.

-- 데드락 모니터링용 뷰 생성 (주기적 점검용)
CREATE OR REPLACE VIEW v_deadlock_monitor AS
SELECT
    w.sid              AS waiting_sid,
    ws.username        AS waiting_user,
    ws.sql_id          AS waiting_sql_id,
    h.sid              AS holding_sid,
    hs.username        AS holding_user,
    hs.sql_id          AS holding_sql_id,
    w.type             AS lock_type,
    SYSDATE            AS check_time
FROM
    v$lock w
    JOIN v$lock h
        ON  w.id1 = h.id1
        AND w.id2 = h.id2
        AND w.block = 0
        AND h.block = 1
    JOIN v$session ws ON w.sid = ws.sid
    JOIN v$session hs ON h.sid = hs.sid
WHERE
    w.sid != h.sid;

-- DBMS_SCHEDULER로 5분마다 모니터링 실행 예시
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'JOB_DEADLOCK_MONITOR',
        job_type        => 'PLSQL_BLOCK',
        job_action      => '
            DECLARE
                v_cnt NUMBER;
            BEGIN
                SELECT COUNT(*) INTO v_cnt FROM v_deadlock_monitor;
                IF v_cnt > 0 THEN
                    -- 실무에서는 이 위치에 이메일/슬랙 알림 로직 추가
                    INSERT INTO deadlock_log (log_time, lock_count)
                    VALUES (SYSDATE, v_cnt);
                    COMMIT;
                END IF;
            END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
        enabled         => TRUE,
        comments        => '데드락 발생 여부 주기적 점검'
    );
END;
/

2. 애플리케이션 레벨에서 재시도(Retry) 로직 구현 및 외래 키 인덱스 정기 점검 자동화

ORA-00060은 Oracle이 희생자 세션을 자동 선택하여 롤백하기 때문에, 애플리케이션에서 이 에러를 명시적으로 catch하여 일정 횟수(보통 3회) 재시도하는 로직을 반드시 구현해야 합니다. 동시에 신규 테이블이 추가될 때마다 외래 키 인덱스 누락 점검 쿼리를 CI/CD 파이프라인에 포함시켜, 배포 단계에서 누락 인덱스를 사전에 차단하는 것이 데드락 예방의 핵심 Best Practice입니다.