ORA-00060: 데드락 발견 (Deadlock Detected)

ORA-00060: 데드락 발견 (Deadlock Detected)

ORA-00060 오류는 오라클 데이터베이스에서 발생하는 오류 중 하나로, "리소스 대기 중 교착 상태가 감지되었습니다(deadlock detected while waiting for resource)"라는 의미입니다. 이 오류는 두 개 이상의 트랜잭션이 서로가 점유하고 있는 리소스를 기다리면서 무한정 대기하는 상황, 즉 교착 상태(Deadlock)에 빠졌을 때 발생합니다. 마치 두 차량이 좁은 길에서 서로 지나가려고 하지만, 서로 막혀서 움직이지 못하는 상황과 같습니다.

 

ORA-00060 오류의 발생 원인

• 교착 상태는 주로 다음과 같은 네 가지 조건이 동시에 충족될 때 발생합니다.

1. 상호 배제(Mutual Exclusion) : 리소스는 한 번에 하나의 트랜잭션만 점유할 수 있습니다.
2. 점유 및 대기(Hold and Wait) : 트랜잭션이 이미 리소스를 점유하고 있으면서 다른 트랜잭션이 점유하고 있는 리소스를 기다립니다.
3. 비선점(No Preemption) : 트랜잭션이 점유한 리소스는 해당 트랜잭션이 자발적으로 해제할 때까지 강제로 빼앗을 수 없습니다.
4. 순환 대기(Circular Wait) : 두 개 이상의 트랜잭션이 서로 순환적으로 필요한 리소스를 점유하고 대기하는 상황입니다. 예를 들어, 트랜잭션 A는 리소스 X를 점유하고 리소스 Y를 기다리고, 트랜잭션 B는 리소스 Y를 점유하고 리소스 X를 기다리는 상황입니다.

• 오라클은 이러한 교착 상태를 자동으로 감지하고, 하나의 트랜잭션을 희생시켜(보통 가장 최근에 시작된 트랜잭션) 교착 상태를 해소합니다. 이때 희생된 트랜잭션은 ORA-00060 오류를 발생시키며 롤백됩니다.

ORA-00060 오류의 주요 발생 시나리오

• 동일 테이블에 대한 상반된 작업의 동시 수행 : 두 개의 세션이 동일한 테이블의 행을 동시에 수정하려고 시도할 때 발생할 수 있습니다. 예를 들어, 세션 A는 테이블의 행 1을 수정하고 행 2를 수정하려고 대기하고, 세션 B는 테이블의 행 2를 수정하고 행 1을 수정하려고 대기하는 경우 교착 상태가 발생합니다.
• 두 개 이상의 테이블에 대한 작업 순서 불일치 : 두 개의 세션이 두 개 이상의 테이블에 접근할 때, 접근 순서가 다를 경우 교착 상태가 발생할 수 있습니다. 예를 들어, 세션 A는 테이블 X를 먼저 접근하고 테이블 Y를 접근하려고 하고, 세션 B는 테이블 Y를 먼저 접근하고 테이블 X를 접근하려고 하는 경우 교착 상태가 발생할 수 있습니다.
• 인덱스 사용 방식의 문제 : 인덱스를 사용하는 방식에 따라 교착 상태가 발생할 가능성이 높아질 수 있습니다. 특히, 비트맵 인덱스를 사용하는 경우, 여러 세션에서 동시에 동일한 비트맵 인덱스를 수정하려고 하면 교착 상태가 발생할 가능성이 높습니다.

ORA-00060 오류 해결 방법

• ORA-00060 오류는 근본적으로 애플리케이션의 로직이나 데이터베이스 설계의 문제로 인해 발생하므로, 단순히 오류를 처리하는 것보다는 발생 원인을 분석하고 예방하는 것이 중요합니다.

1. 애플리케이션 로직 수정 : 가장 중요한 것은 애플리케이션의 로직을 수정하여 교착 상태가 발생하지 않도록 하는 것입니다.

• 트랜잭션 크기 최소화 : 트랜잭션의 범위를 가능한 한 작게 하여 락을 점유하는 시간을 최소화합니다.
• 객체 접근 순서 통일 : 여러 테이블에 접근하는 경우, 모든 트랜잭션에서 동일한 순서로 테이블에 접근하도록 합니다.
• 불필요한 락 획득 방지 : 필요한 경우에만 명시적으로 락을 획득하고, 사용이 끝나면 즉시 해제합니다.
• 낙관적 락(Optimistic Locking) 고려 : 데이터의 동시 수정이 빈번하지 않은 경우, 낙관적 락을 사용하여 락 경쟁을 줄일 수 있습니다.

2. 데이터베이스 설계 개선 : 데이터베이스 설계를 개선하여 교착 상태 발생 가능성을 줄일 수 있습니다.

• 정규화 : 데이터 정규화를 통해 데이터 중복을 줄이고, 여러 테이블에 걸친 작업의 필요성을 줄일 수 있습니다.
• 인덱스 설계 개선 : 인덱스를 적절히 사용하여 쿼리 성능을 향상하고, 락 경쟁을 줄일 수 있습니다. 특히, 비트맵 인덱스의 사용을 신중하게 고려해야 합니다.

3. 오라클 추적 파일 분석 : 오라클은 교착 상태가 발생할 때 추적 파일을 생성합니다. 이 파일을 분석하면 어떤 트랜잭션이 어떤 리소스를 점유하고 대기하고 있었는지 확인할 수 있습니다. 이를 통해 교착 상태의 원인을 정확하게 파악할 수 있습니다.

 

4. 세션 Kill (최후의 수단) : 교착 상태가 지속적으로 발생하고 원인 파악이 어려운 경우, DBA는 교착 상태에 연루된 세션을 강제로 종료(Kill)하여 문제를 해결할 수 있습니다. 하지만 이는 임시적인 조치이며, 근본적인 해결책은 아닙니다.

 

예시 (간단한 교착 상태 시나리오)

• 두 개의 세션이 다음과 같은 작업을 동시에 수행한다고 가정해 보겠습니다.

-- 세션 A
UPDATE EMP
SET SAL = 1000
WHERE EMPNO = 7369;
-- 후
UPDATE DEPT
SET LOC = 'NEW YORK'
WHERE DEPTNO = 20;

-- 세션 B
UPDATE DEPT
SET LOC = 'DALLAS'
WHERE DEPTNO = 20;
-- 후
UPDATE EMP
SET SAL = 2000
WHERE EMPNO = 7369;

 

• 이 경우, 세션 A는 EMP 테이블의 행을 락킹하고 DEPT 테이블의 행을 락킹 하려고 대기하고, 세션 B는 DEPT 테이블의 행을 락킹 하고 EMP 테이블의 행을 락킹 하려고 대기하면서 교착 상태가 발생할 수 있습니다.

※ ORA-00060 오류는 심각한 문제로 이어질 수 있으므로, 발생 원인을 정확히 파악하고 적절한 조치를 취해야 합니다. 애플리케이션 로직 수정과 데이터베이스 설계 개선을 통해 교착 상태 발생을 예방하는 것이 가장 중요하며, 발생 시에는 오라클 추적 파일을 분석하여 원인을 파악하고 필요한 조치를 취해야 합니다.