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

-- 현재 잠금 상황 및 블로킹 세션 확인
SELECT
    l.session_id,
    s.serial#,
    s.username,
    s.program,
    l.oracle_username,
    l.os_user_name,
    o.object_name,
    o.object_type,
    DECODE(l.locked_mode,
        0, 'None',
        1, 'Null',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive', 'Unknown') AS lock_mode
FROM
    v$locked_object  l
    JOIN dba_objects  o ON l.object_id = o.object_id
    JOIN v$session    s ON l.session_id = s.sid
ORDER BY
    l.session_id;

-- 데드락 트레이스 파일 위치 확인
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- 혹은 알림 로그에서 데드락 발생 시각 확인
SELECT originating_timestamp, message_text
FROM   v$diag_alert_ext
WHERE  message_text LIKE '%deadlock%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

-- 나쁜 예: 순서가 보장되지 않아 데드락 발생 가능
-- 세션 A
UPDATE orders SET status = 'PROCESSING' WHERE order_id = 1001;
UPDATE order_items SET qty = qty - 1 WHERE item_id = 5001;

-- 세션 B (역순 접근 → 데드락 위험)
UPDATE order_items SET qty = qty - 1 WHERE item_id = 5001;
UPDATE orders SET status = 'PROCESSING' WHERE order_id = 1001;

-- 좋은 예: 항상 동일한 순서(orders → order_items)로 접근
-- 세션 A
BEGIN
    -- 1단계: 항상 부모 테이블 먼저
    UPDATE orders
    SET    status = 'PROCESSING'
    WHERE  order_id = 1001;

    -- 2단계: 자식 테이블은 항상 나중에, PK 오름차순 정렬
    UPDATE order_items
    SET    qty = qty - 1
    WHERE  order_id = 1001
    ORDER BY item_id;  -- 순서 보장

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

-- 외래 키가 있으나 인덱스가 없는 컬럼 찾기 (실무 필수 쿼리)
SELECT
    c.table_name,
    c.constraint_name,
    c.column_name,
    c.position
FROM
    user_cons_columns c
    JOIN user_constraints uc
        ON c.constraint_name = uc.constraint_name
        AND uc.constraint_type = 'R'  -- R = Foreign Key
WHERE
    NOT EXISTS (
        SELECT 1
        FROM   user_ind_columns ic
        WHERE  ic.table_name  = c.table_name
          AND  ic.column_name = c.column_name
    )
ORDER BY
    c.table_name, c.position;

-- 발견된 FK 컬럼에 인덱스 생성
-- 예시: ORDER_ITEMS 테이블의 ORDER_ID(FK) 컬럼
CREATE INDEX idx_order_items_order_id
    ON order_items (order_id)
    TABLESPACE users
    PARALLEL 4
    NOLOGGING;

-- 인덱스 생성 후 통계 수집
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => USER,
        tabname          => 'ORDER_ITEMS',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        cascade          => TRUE
    );
END;
/

-- OLTP 환경이라면 비트맵 인덱스를 B-Tree 인덱스로 교체
-- 기존 비트맵 인덱스 확인
SELECT index_name, index_type, table_name
FROM   user_indexes
WHERE  index_type = 'BITMAP';

-- 비트맵 인덱스 제거 후 B-Tree 인덱스로 대체
DROP INDEX bitmap_idx_status;

CREATE INDEX btree_idx_status
    ON orders (status)
    TABLESPACE users;

-- 대용량 배치 로딩 시 비트맵 인덱스가 필요한 경우:
-- 로딩 전 인덱스 비활성화 → 로딩 → 인덱스 재생성 패턴 사용
ALTER INDEX bitmap_idx_status UNUSABLE;

-- 데이터 로딩 수행
INSERT /*+ APPEND */ INTO orders SELECT * FROM orders_stg;
COMMIT;

-- 인덱스 재생성
ALTER INDEX bitmap_idx_status REBUILD PARALLEL 8 NOLOGGING;
ALTER INDEX bitmap_idx_status NOPARALLEL;
ALTER INDEX bitmap_idx_status LOGGING;

-- 실무에서 바로 활용 가능한 데드락 재시도 프로시저
CREATE OR REPLACE PROCEDURE process_order_with_retry(
    p_order_id  IN orders.order_id%TYPE,
    p_max_retry IN INTEGER DEFAULT 3
)
AS
    v_retry_count INTEGER := 0;
    deadlock_detected EXCEPTION;
    PRAGMA EXCEPTION_INIT(deadlock_detected, -60);  -- ORA-00060
BEGIN
    LOOP
        BEGIN
            -- 실제 비즈니스 로직
            UPDATE orders
            SET    status     = 'PROCESSING',
                   updated_dt = SYSDATE
            WHERE  order_id   = p_order_id;

            UPDATE order_items
            SET    status = 'PROCESSING'
            WHERE  order_id = p_order_id;

            COMMIT;
            EXIT;  -- 성공 시 루프 탈출

        EXCEPTION
            WHEN deadlock_detected THEN
                ROLLBACK;
                v_retry_count := v_retry_count + 1;

                IF v_retry_count >= p_max_retry THEN
                    -- 최대 재시도 횟수 초과 시 로그 기록 후 예외 전파
                    INSERT INTO error_log (
                        error_code, error_msg,
                        session_id, occurred_dt
                    ) VALUES (
                        -60, 'Deadlock exceeded max retry: ' || p_order_id,
                        SYS_CONTEXT('USERENV','SID'), SYSTIMESTAMP
                    );
                    COMMIT;
                    RAISE;
                END IF;

                -- 잠시 대기 후 재시도 (지수 백오프 적용)
                DBMS_LOCK.SLEEP(v_retry_count * 0.5);
        END;
    END LOOP;
END process_order_with_retry;
/