Oracle 19c ORA-00060 데드락 trace 읽기: 원인 SQL을 찾고 재발을 막는 순서

Oracle 19c에서 ORA-00060이 발생했다면, 먼저 trace 파일의 데드락 그래프를 보며 서로 기다린 트랜잭션과 자원을 연결해야 합니다. 단순히 오류가 난 SQL만 재실행하면 잠깐 지나갈 수는 있어도, 두 업무 흐름이 반대 순서로 행을 잡는 구조는 그대로 남습니다.

Oracle은 데드락을 감지하면 관련 문장 하나를 롤백하고 ORA-00060을 반환합니다. 이미 같은 트랜잭션에서 성공한 앞선 변경까지 자동으로 취소되는 것은 아니므로, 애플리케이션은 재시도 전에 트랜잭션 경계와 중복 처리 방식을 확인해야 합니다.

ORA-00060에서 먼저 확인할 범위

데드락은 ‘행 두 개’보다 ‘접근 순서’ 문제에 가깝다

가령 주문 처리 A가 주문 행을 갱신한 뒤 재고 행을 갱신하고, 처리 B가 재고 행을 먼저 갱신한 뒤 같은 주문 행을 갱신한다고 가정해 보겠습니다. 두 작업이 겹치면 A는 B의 재고 행을, B는 A의 주문 행을 기다리게 됩니다. Oracle은 그 고리를 발견하면 한 문장에 ORA-00060을 돌려줍니다.

테스트 환경의 최소 재현 구조

CREATE TABLE order_header (
  order_id NUMBER PRIMARY KEY,
  status   VARCHAR2(20) NOT NULL
);

CREATE TABLE inventory_item (
  item_id  NUMBER PRIMARY KEY,
  qty      NUMBER NOT NULL
);

INSERT INTO order_header (order_id, status) VALUES (1001, 'READY');
INSERT INTO inventory_item (item_id, qty) VALUES (501, 10);
COMMIT;

아래 SQL은 서로 다른 세션에서 순서를 반대로 실행할 때만 데드락을 관찰하기 위한 예제입니다. 한 세션에서 모두 실행하면 데드락이 생기지 않습니다.

-- 세션 A: 먼저 실행하고 COMMIT하지 않는다.
UPDATE order_header
   SET status = 'PROCESSING'
 WHERE order_id = 1001;

-- 세션 B: 먼저 실행하고 COMMIT하지 않는다.
UPDATE inventory_item
   SET qty = qty - 1
 WHERE item_id = 501;

-- 세션 A: 다음에 실행하면 세션 B가 가진 행을 기다린다.
UPDATE inventory_item
   SET qty = qty - 1
 WHERE item_id = 501;

-- 세션 B: 다음에 실행하면 순환 대기가 완성될 수 있다.
UPDATE order_header
   SET status = 'PROCESSING'
 WHERE order_id = 1001;

trace 파일을 읽는 순서

Oracle 오류 도움말은 ORA-00060이 나면 trace 파일에서 참여 트랜잭션과 자원을 확인하라고 안내합니다. alert log에서 발생 시각을 좁힌 뒤 해당 시각의 trace 파일을 열고, 아래 순서로 업무 맥락을 복원합니다.

1. Deadlock graph에서 대기 방향이 닫힌 고리인지 확인합니다. 자원 종류와 대기·보유 관계를 먼저 읽고, SQL 텍스트만 보고 결론 내리지 않습니다.
2. 그래프의 세션·트랜잭션 정보를 오류 시각의 애플리케이션 로그와 연결합니다. 가능하면 MODULE, ACTION, CLIENT_IDENTIFIER를 함께 남겨 배치와 화면 요청을 구분합니다.
3. trace에 이어 나오는 오류 시점 SQL과 바인드 값 취급 방침을 확인합니다. 민감한 값은 복사하거나 공개 로그에 남기지 않습니다.
4. 두 코드 경로가 같은 테이블·행 집합을 어떤 순서로 갱신했는지 비교합니다. 공통 정렬 키를 정해 항상 같은 순서로 잠그는 수정이 우선입니다.

오류 시각의 세션 맥락을 보조로 조회하기

SELECT s.sid,
       s.serial#,
       s.username,
       s.module,
       s.action,
       s.client_identifier,
       s.status,
       s.sql_id
  FROM v$session s
 WHERE s.username = :schema_name
 ORDER BY s.logon_time DESC;

수정 전후에 확인할 것

재발을 줄이는 설계 원칙

가장 직접적인 해결책은 모든 코드 경로가 같은 순서로 행을 갱신하도록 정하는 것입니다. 예를 들어 주문과 재고를 함께 잠가야 하면 항상 주문 ID, 재고 ID 순으로 처리하고, 한 트랜잭션이 잡는 행 수와 외부 호출 시간을 줄입니다. 인덱스를 추가하거나 세션을 종료하는 조치는 증상과 원인에 따라 달라지므로, trace가 가리킨 자원과 실행 경로가 확인되기 전에는 만능 처방으로 쓰지 않습니다.

흔한 오해

• “데드락이면 트랜잭션 전체가 사라진다”: Oracle의 자동 해소는 데드락에 관련된 문장 하나의 롤백입니다. 애플리케이션의 전체 작업 단위는 별도로 정리합니다.
• “에러가 난 SQL만 무조건 재시도하면 된다”: 순환 접근이 남으면 재발합니다. 재시도는 짧고 멱등한 작업에서만 제한적으로 사용합니다.
• “블로킹 세션을 종료하면 원인이 해결된다”: 데드락은 이미 Oracle이 순환을 끊은 뒤일 수 있습니다. 종료보다 trace 보존과 접근 순서 수정이 먼저입니다.

마무리

ORA-00060 대응의 출발점은 trace에서 실제 대기 고리를 읽는 일입니다. 그다음 두 업무 흐름을 같은 잠금 순서로 맞추고, 오류 뒤 남을 수 있는 트랜잭션 상태와 재시도 중복을 분리해 다루면 일회성 해소가 재발 방지로 이어집니다.

공식 출처

• Oracle Database Error Messages: ORA-00060
• Oracle Database Concepts: Data Concurrency and Consistency
• Oracle Database Administrator’s Guide: Monitoring Errors with Trace Files and the Alert Log
• Oracle Database Reference: V$SESSION