오라클 오류 ORA-00060

오늘 아침에 출근과 동시에 나를 반겨준 것은 다름아닌 오라60  오류! 반갑다 친구야

오늘은 오라60오류에 대해 살펴보자.

오라클 오류 ORA-00060은 "deadlock detected while waiting for resource", 즉 교착 상태(Deadlock) 가 발생했을 때 나타나는 오류입니다.

원인은 두 개 이상의 세션이 서로가 가진 자원을 기다리는 상황이 되어, 더 이상 진행할 수 없게 되었을 때 발생합니다.

예)
세션 A가 행 1을 잠금(Lock) → 세션 B가 행 2를 잠금

이후 세션 A가 행 2를 요청하고, 세션 B는 행 1을 요청하면
→ 서로 자원을 기다리면서 무한 대기 상태가 발생

오라클은 이 상황을 감지하면, 둘 중 하나의 트랜잭션을 강제로 종료시키고 ORA-00060 오류를 발생시킵니다.

해결 방법은
1. 트랜잭션 순서 통일
여러 세션에서 동일한 순서로 자원(테이블/행)을 액세스하도록 합니다.

2. 짧은 트랜잭션 유지
트랜잭션을 짧고 빠르게 처리하면 교착 상태 발생 가능성이 줄어듭니다.

3. 명시적 Lock 최소화
SELECT ... FOR UPDATE 같은 명시적 락 사용 시 신중하게 접근합니다.

4. Deadlock 트레이스 확인
alert.log 또는 trace 파일을 통해 어떤 세션/쿼리에서 문제가 발생했는지 확인할 수 있습니다.
일반적으로 trace 파일은 $ORACLE_HOME/diag/... 경로에 생성됩니다.

현재 락 세션 확인 할수도 있겠지만 접근권한이나 이미 상황이 종료된 경우도 있다.

--현재 락 상태 보기
SELECT
    l.session_id,
    l.locked_mode,
    o.object_name,
    o.object_type
FROM
    v$locked_object l,
    dba_objects o
WHERE
    l.object_id = o.object_id;

다른 트랜젝션에서 동시에 테이블 업데이트 되는게 원인이였고 어떻게 해결할지는 아직 고민이다. 🤔

1. 트랜잭션 간 락 순서 통일 (락 순서 전략)
트랜잭션 A: UPDATE table1 → UPDATE table2
트랜잭션 B: UPDATE table2 → UPDATE table1

모든 트랜잭션이 동일한 순서로 테이블을 업데이트하도록 강제

-- 모두 table1 먼저, 그다음 table2
BEGIN
  UPDATE table1 SET ... WHERE ...;
  UPDATE table2 SET ... WHERE ...;
  COMMIT;
END;

2. SELECT FOR UPDATE 사용 시 NOWAIT 또는 SKIP LOCKED 추가
A 세션: SELECT FOR UPDATE로 잠금
B 세션: 동일한 행을 SELECT FOR UPDATE 하려고 대기 → 교착 상태 발생 가능

-- NOWAIT: 잠금 못 잡으면 즉시 에러 반환
SELECT * FROM employees WHERE id = 101 FOR UPDATE NOWAIT;

-- SKIP LOCKED: 잠긴 행은 건너뜀
SELECT * FROM employees WHERE status = 'READY' FOR UPDATE SKIP LOCKED;

3. 인덱스 최적화로 풀스캔 방지
UPDATE 문에서 WHERE 절에 인덱스가 없어 다수 행에 락 발생

WHERE 절에 맞는 인덱스를 생성하여 대상 행만 빠르게 잠금

CREATE INDEX idx_orders_status ON orders(status);

4. 커밋을 빠르게 하여 락 지속 시간 단축
사용자 입력 후 커밋하지 않고 대기 → 긴 락 보유

트랜잭션은 빠르게 처리하고 즉시 커밋/롤백

BEGIN
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  COMMIT;  -- 락 오래 잡지 않도록 즉시 커밋
END;

5. 애플리케이션에서 재시도 로직 구현
드물게 교착 상태가 발생하지만 비즈니스에 큰 문제는 아님

int attempts = 3;
while (attempts-- > 0) {
  try {
    // 데이터베이스 트랜잭션 로직
    break;  // 성공 시 탈출
  } catch (SQLException e) {
    if (e.getErrorCode() == 60) { // ORA-00060
      Thread.sleep(1000); // 대기 후 재시도
    } else {
      throw e;
    }
  }
}

* Oracle 트레이스로 원인 분석
설정 방법

ALTER SESSION SET EVENTS '60 trace name errorstack level 3';

*오류 발생 시 trace 파일을 통해 어떤 세션이 어떤 자원을 점유하다 충돌이 발생했는지 확인 가능

*DB 서버의 user_dump_dest 경로에 trace 파일 생성됨