본문 바로가기
ORA DBDATA ORA DBDATA

ORA-00060 뒤에도 잠금은 남는다: 데드락 그래프를 SQL로 꺼내 읽은 기록

읽는 시간 약 14분
ORA-00060 데드락 이후에도 잠금이 남아 있는 상황과 SQL로 조회한 데드락 그래프를 요약한 이미지

자원 대기중 교착 상태가 검출되었습니다, 그다음에 남는 것

DBeaver 결과 창에 ORA-00060: 자원 대기중 교착 상태가 검출되었습니다가 떴습니다. 이 메시지를 보면 오라클이 한쪽을 정리해 줬으니 상황은 끝났다고 생각하기 쉽습니다. 그 직후 데이터베이스에 무엇이 남아 있는지를 SQL과 트레이스로 확인했습니다.

운영에서 데드락을 처음 알게 되는 경로는 대부분 Alert Log였습니다. On-Premise나 IaaS 환경에서는 Alert Log에 찍힌 .trc 경로를 보고 서버의 ADR trace 디렉터리에 직접 접속해 트레이스를 열었습니다. 원인은 대부분 동일 쿼리의 병렬 처리였고, 병렬 처리 빈도를 최소화하도록 로직 수정을 요청해 해결했습니다. 평소에는 V$LOCK과 V$SESSION 모니터링 쿼리로 특정 테이블에 락 대기가 5초 이상 지속되는 패턴을 정기적으로 추적했습니다.

그래서 두 가지가 궁금했습니다. 하나는 ORA-00060 직후 오류를 받은 세션의 트랜잭션은 어떤 상태인가입니다. 다른 하나는 서버 디렉터리에 들어가지 않고 SQL만으로 데드락 그래프를 읽을 수 있는가입니다.

창고 간 재고 이동 두 건으로 만든 순환 대기

재현은 Oracle Database 21c Express Edition의 XEPDB1에서 했습니다. 테스트 전용 USER DLK_LAB에 CREATE SESSION, CREATE TABLE, 그리고 V$ 뷰 조회용 SELECT_CATALOG_ROLE만 부여했습니다. 롤 권한이라 부여한 뒤 재접속해야 적용됩니다.

Oracle 21c Express Edition, CON_NAME XEPDB1, SESSION_USER와 CURRENT_SCHEMA가 DLK_LAB으로 조회된 DBeaver 결과
SYS나 SYSTEM이 아닌 테스트 USER로 XEPDB1에 접속한 상태에서 진행했습니다.

테이블은 창고 10곳과 품목 100개를 조합한 1,000건짜리 재고 테이블입니다. 데드락은 데이터 양이 아니라 잠금 순서로 생기므로 규모는 작게 잡았습니다. 블로그용 가상 시나리오입니다.

CREATE TABLE warehouse_stock (
  stock_id     NUMBER      PRIMARY KEY,
  warehouse_id VARCHAR2(4) NOT NULL,
  item_code    VARCHAR2(8) NOT NULL,
  qty          NUMBER      NOT NULL,
  CONSTRAINT uk_warehouse_stock UNIQUE (warehouse_id, item_code)
);

INSERT INTO warehouse_stock
SELECT LEVEL,
       'WH' || LPAD(MOD(LEVEL - 1, 10) + 1, 2, '0'),
       'IT' || LPAD(CEIL(LEVEL / 10), 3, '0'),
       1000
FROM dual CONNECT BY LEVEL <= 1000;
COMMIT;

DBeaver 편집기 세 개를 각각 별도 연결로 열었습니다. A와 B는 수동 커밋으로 두었고, C는 관찰용입니다. A와 B에는 DBMS_APPLICATION_INFO.SET_MODULE('DLK_LAB', 'SESSION_A')처럼 모듈과 액션 이름을 붙였습니다.

상황은 같은 재고 이동 로직이 방향만 반대로 동시에 도는 경우입니다. A는 WH01에서 WH02로, B는 WH02에서 WH01로 같은 품목 10개를 옮깁니다.

  1. A: WH01 행에서 10 차감
  2. B: WH02 행에서 10 차감
  3. A: WH02 행에 10 가산 → B가 쥔 행이라 대기
  4. B: WH01 행에 10 가산 → A가 쥔 행이라 대기, 순환 완성

4번을 실행하자, 먼저 기다리고 있던 A의 3번 문장에서 오류가 났습니다.

WH02 가산 UPDATE 문장에서 SQL Error 60, ORA-00060 자원 대기중 교착 상태가 검출되었습니다가 표시된 화면
오류를 받은 쪽은 순환에서 먼저 대기를 시작한 Session A였습니다.

오류를 받은 세션이 잠금을 그대로 쥐고 있었습니다

A와 B 모두 COMMIT이나 ROLLBACK을 하지 않은 상태에서, 관찰용 Session C로 V$SESSION과 V$LOCK을 함께 조회했습니다.

SELECT s.action, s.sid, s.status, s.event, s.blocking_session,
       l.type, l.lmode, l.request
FROM v$session s
LEFT JOIN v$lock l ON l.sid = s.sid AND l.type = 'TX'
WHERE s.module = 'DLK_LAB'
ORDER BY s.action, l.lmode DESC;
SESSION_A는 INACTIVE에 TX LMODE 6, SESSION_B는 enq TX row lock contention으로 BLOCKING_SESSION 2544를 기다리는 V$SESSION 조회 결과
ORA-00060 이후에도 A는 TX 잠금을 유지했고, B는 여전히 A를 기다렸습니다.

A(SID 2544)는 INACTIVE이고 이벤트는 SQL*Net message from client입니다. 데이터베이스 입장에서는 클라이언트가 다음 명령을 보내기만 기다리는 평범한 유휴 세션입니다. 그런데 TX 잠금은 LMODE 6으로 그대로 쥐고 있습니다.

B(SID 4)는 enq: TX - row lock contention으로 대기 중이고, BLOCKING_SESSION은 2544입니다. B는 자기 트랜잭션의 TX(LMODE 6)와 함께 A의 TX를 요청(REQUEST 6)한 상태로 멈춰 있었습니다.

A 세션에서 대상 두 행을 다시 읽자, 무엇이 되돌려졌는지가 분명해졌습니다.

WAREHOUSE_ID  QTY
WH01          990
WH02          1,000

WH01은 1번 문장대로 990으로 남아 있고, 오류가 난 3번 문장의 WH02 가산만 사라졌습니다. 오라클이 되돌린 범위는 오류를 받은 문장 하나였고, 같은 트랜잭션에서 앞서 바꾼 행은 잠긴 채 그대로였습니다.

그래서 순환은 끊겼지만 대기는 끝나지 않았습니다. A에서 ROLLBACK을 실행한 뒤에야 B의 4번 문장이 진행됐고, B는 그 뒤 COMMIT했습니다.

운영에서 이 차이는 큽니다. 애플리케이션이 ORA-00060을 잡고 로그만 남긴 채 트랜잭션을 정리하지 않으면, 오류를 받은 세션이 잠금을 쥔 채 유휴 상태로 남을 수 있습니다. 그러면 상대 세션은 계속 기다리게 됩니다. 이렇게 되면 Alert Log에는 데드락으로 기록되지만, V$SESSION에서는 평범한 락 대기로 이어져 보입니다. 락 대기 5초 이상 패턴을 정기적으로 추적하는 모니터링이 데드락 뒤처리 누락까지 함께 잡아낼 수 있는 이유입니다.

이번 재현은 두 세션, 두 행만으로 만든 단순한 순환입니다. 감지까지 걸린 시간은 따로 측정하지 않았습니다. 같은 순서로 갱신하도록 바꿨을 때 데드락이 사라지는지도 이번에는 실행하지 않았습니다.

서버에 들어가지 않고 트레이스를 따라간 순서

파일 이름은 오류를 받은 세션이 알고 있습니다

데드락 트레이스는 오류를 받은 세션의 서버 프로세스가 자기 트레이스 파일에 기록합니다. 그래서 A 세션에서 V$DIAG_INFO를 조회하면 파일 위치가 바로 나옵니다.

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- 결과
C:\APP\...\diag\rdbms\xe\xe\trace\xe_ora_25488.trc

파일명의 25488은 뒤에서 볼 트레이스 안의 A 세션 ospid: 25488과 같은 값입니다. 다른 세션에서 찾아야 한다면 오류를 받은 세션의 OS 프로세스 번호로 파일을 추정할 수 있습니다.

파일 내용은 V$DIAG_TRACE_FILE_CONTENTS로 읽었습니다. 경로에서 파일명만 잘라 TRACE_FILENAME과 맞추고, DEADLOCK DETECTED가 처음 나오는 줄부터 80줄을 가져왔습니다.

WITH f AS (
  SELECT SUBSTR(value, INSTR(value, '\', -1) + 1) AS fname
  FROM v$diag_info WHERE name = 'Default Trace File'
), s AS (
  SELECT MAX(c.line_number) AS ln
  FROM v$diag_trace_file_contents c, f
  WHERE c.trace_filename = f.fname AND c.payload LIKE '%DEADLOCK DETECTED%'
)
SELECT c.line_number, RTRIM(c.payload, CHR(10)) AS payload
FROM v$diag_trace_file_contents c, f, s
WHERE c.trace_filename = f.fname
  AND c.line_number BETWEEN s.ln AND s.ln + 80
ORDER BY c.line_number;

21c XE에서는 SYS나 SYSTEM이 아니라 SELECT_CATALOG_ROLE만 받은 테스트 USER로, PDB 안에서 이 조회가 됐습니다. 결과는 DBeaver의 텍스트 보기로 확인했습니다.

Deadlock graph는 두 줄만 보면 됩니다

DEADLOCK DETECTED ORA-00060과 Transaction Deadlock, TX 리소스 두 줄에서 세션 2544와 4가 서로 holds X waits X로 맞물린 데드락 그래프
TX 리소스 두 개를 세션 2544와 4가 하나씩 쥐고, 서로 상대의 것을 기다린 순환이 그대로 기록됐습니다.

그래프의 각 줄은 리소스 하나입니다. 첫 줄 TX-000A000C-...는 세션 2544(A)가 X로 쥐고 있고, 세션 4(B)가 X로 기다립니다. 둘째 줄 TX-00090005-...는 반대로 B가 쥐고 A가 기다립니다. serial 값 46028과 26760도 함께 남아 있어 SID 재사용과 헷갈리지 않습니다.

양쪽 모두 holds X, waits X인 TX 데드락은 이번처럼 서로 다른 행을 반대 순서로 갱신했을 때 나타나는 모양입니다. 머리글의 [Transaction Deadlock]과 함께, 오라클 내부 오류가 아니라 애플리케이션 설계나 SQL 실행 순서에서 생긴 데드락이라는 안내 문구도 출력됐습니다.

누가 어떤 SQL로 들어왔는지

Information for waiting sessions 영역에 세션 2544와 4의 pdb, ospid, program, application name, action name, current SQL이 표시된 트레이스
대기 세션 정보에는 양쪽 세션의 모듈과 액션 이름, 실행 중이던 SQL이 모두 남았습니다.

Information for waiting sessions에는 두 세션의 PDB(3/XEPDB1), ospid, 접속 프로그램, application name과 action name, current SQL이 나옵니다.

SET_MODULE로 붙인 SESSION_A, SESSION_B가 action name에 그대로 찍혀 있어서, SID를 몰라도 어느 쪽이 어떤 로직인지 바로 구분됐습니다. current SQL에는 UPDATE 문장 위의 DBeaver 주석 줄(-- A2 [Session A] ...)까지 함께 기록됐습니다. 도구가 주석을 서버로 보내면 트레이스에도 남는다는 뜻입니다. 애플리케이션 SQL 주석이나 모듈 이름에 호출 위치를 넣어 두면, 동일 쿼리가 병렬로 돌다 생긴 데드락에서 원인 코드를 찾는 시간이 줄어듭니다.

여기서부터는 원인 분석에 필요 없습니다

End of information for waiting sessions 이후 덤프 시각, Error Stack Dump, sql_id 33hvqbrj8jd9b의 Current SQL Statement와 Call Stack Trace가 이어지는 트레이스
대기 세션 정보가 끝난 뒤에는 오류 세션의 SQL 한 번과 내부 호출 스택이 이어집니다.

End of information for waiting sessions 다음에는 덤프 시각(2026-10-27T23:19:18.226), 오류 세션의 Current SQL Statement(sql_id 33hvqbrj8jd9b), Call Stack Trace가 이어집니다. sql_id는 V$SQL과 연결할 때 쓸 수 있습니다. 하지만 호출 스택은 애플리케이션 데드락을 분석하는 데는 쓰이지 않습니다. 이번 트레이스에서 실제로 필요했던 범위는 DEADLOCK DETECTED부터 대기 세션 정보가 끝나는 곳까지였습니다.

Alert Log를 열기 전에 V$SESSION부터 봐야 하는 이유

서버의 trace 디렉터리로 들어가는 방법과 비교하면, SQL 조회는 OS 계정 없이 DB 권한만으로 같은 그래프를 볼 수 있다는 점이 다릅니다. 이번 21c XE에서는 SELECT_CATALOG_ROLE로 충분했습니다. 운영 버전이나 관리형 서비스에서 같은 권한으로 되는지는 확인하지 않았으므로, 환경마다 조회 권한부터 점검해야 합니다.

데드락이 났을 때 확인할 순서는 이번 결과를 근거로 이렇게 정리했습니다.

  • 오류를 받은 세션이 아직 TX 잠금을 쥔 채 INACTIVE로 남아 있는지, 그 세션을 기다리는 세션이 있는지 V$SESSION과 V$LOCK으로 먼저 봅니다.
  • 남아 있다면 애플리케이션이 ORA-00060 뒤에 ROLLBACK을 하는지 확인합니다. 문장만 되돌려졌으므로 나머지 정리는 애플리케이션의 몫입니다.
  • 트레이스에서는 Deadlock graph의 holds/waits와 대기 세션의 action name, current SQL까지만 읽습니다.
  • 같은 SQL이 양쪽에 보이면 동일 로직의 병렬 실행을 의심하고, 처리 순서나 병렬 처리 빈도를 조정합니다.

잠금과 대기를 다룬 앞선 글 FOR UPDATE NOWAIT, WAIT, SKIP LOCKED로 작업 큐 대기를 다룬 글이 대기를 어떻게 피할지에 관한 내용이라면, 여기서 본 것은 피하지 못한 대기가 순환했을 때 무엇이 남는지입니다.

ORA-00060이 끊어 준 것은 순환 하나였고, A의 트랜잭션은 잠금과 함께 그대로 남았습니다. 이번 재현에서 데드락 그래프보다 먼저 확인할 가치가 있었던 것은, 오류를 받고도 유휴 상태로 잠금을 쥐고 있던 Session A였습니다.

ORA DBDATA
16년 차 Oracle DBA로, 온프레미스 RAC·ASM 환경과 AWS RDS for Oracle(19c SE2)을 함께 운영하고 있습니다. 블로그의 예제는 운영 DB가 아닌 Oracle 21c XE에서 직접 실행해 확인한 결과입니다.
함께 보면 좋은 글

댓글 0

첫 댓글을 남겨보세요.

광고 차단 알림

광고 클릭 제한을 초과하여 광고가 차단되었습니다.

단시간에 반복적인 광고 클릭은 시스템에 의해 감지되며, IP가 수집되어 사이트 관리자가 확인 가능합니다.