DB 롤백 세그먼트 고갈 시 트랜잭션 실패

증상 확인: 트랜잭션 실패와 함께 나타나는 특정 오류 메시지

데이터베이스에서 INSERT, UPDATE, DELETE 작업을 수행할 때 갑자기 트랜잭션이 실패하고, 다음과 유사한 오류 메시지가 반환되나요? “ORA-01555: snapshot too old”, “Could not allocate space for rollback segment”, “Rollback segment too small” 또는 “Out of space in rollback segment ‘segment_name'”. 이는 DB 롤백 세그먼트(Rollback Segment) 또는 언두(Undo) 공간이 고갈되어 트랜잭션 롤백 정보를 기록할 수 없음을 의미합니다. 장시간 실행되는 대용량 작업이나 동시에 많은 트랜잭션이 발생할 때 빈번히 나타납니다.

원인 분석: 왜 롤백 공간이 부족해지는가

롤백 세그먼트 또는 언두 테이블스페이스는 데이터 변경 전의 원본 데이터(Undo Data)를 임시 저장하는 공간입니다. 이 공간은 크게 두 가지 목적으로 사용됩니다. 첫째, 트랜잭션 롤백 시 데이터를 원상복구하기 위해. 둘째, 다른 세션이 아직 커밋되지 않은 변경 중인 데이터를 읽을 때 일관성 있는 읽기(Read Consistency)를 제공하기 위해. 문제는 하나의 대용량 트랜잭션이 너무 많은 언두 공간을 점유하거나, 많은 수의 작은 트랜잭션이 동시에 실행되어 공간을 빠르게 채울 때 발생합니다. 또한, 오래 걸리는 쿼리가 실행되는 동안 관련 언두 데이터가 다른 트랜잭션에 의해 덮어쓰여지면 ‘snapshot too old’ 오류의 직접적인 원인이 됩니다.

긴급 대응: 현재 실패 중인 트랜잭션 및 시스템 안정화 조치

시스템이 지속적으로 트랜잭션 실패를 보고한다면, 먼저 서비스의 추가 장애를 방지하기 위한 긴급 조치를 수행해야 합니다. 가장 쉽고 안전한 방법부터 시작합니다.

Method 1: 문제 세션 식별 및 최소한의 조치

가장 먼저, 어떤 세션이 언두 공간을 과도하게 사용하고 있는지 확인하여 상황을 파악해야 합니다. Oracle DB를 기준으로 한 진단 명령어입니다.

1. 관리자 권한으로 데이터베이스에 접속합니다: sqlplus / as sysdba
2. 언두 테이블스페이스 사용 현황을 확인합니다:
   
   SELECT tablespace_name, status, ROUND(used_ublk * block_size / 1024 / 1024, 2) AS “Undo Used (MB)”, ROUND(used_urec) AS “Undo Records” FROM v$transaction NATURAL JOIN dba_tablespaces WHERE tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents = ‘UNDO’);
   
3. 가장 많은 언두 블록을 사용하는 상위 세션을 찾습니다:
   
   SELECT s.sid, s.serial#, s.username, s.program, t.used_ublk, t.used_urec, t.start_time FROM v$session s, v$transaction t WHERE s.saddr = t.ses_addr ORDER BY t.used_ublk DESC;
   

위 쿼리 결과에서 used_ublk 값이 비정상적으로 높은 세션을 발견했다면, 해당 애플리케이션 로그를 확인하거나 개발자와 협의하여 트랜잭션을 중단(ROLLBACK)할 수 있는지 판단해야 합니다. 단, 운영 중인 중요한 트랜잭션을 함부로 중단해서는 안 됩니다.

근본적 해결 방법 1: 언두 테이블스페이스 공간 확장 및 관리

긴급 조치 후, 동일한 문제가 재발하지 않도록 인프라 수준에서 설정을 변경해야 합니다. 이는 가장 실질적이고 효과적인 기술 조치입니다.

1. 현재 언두 테이블스페이스 상태 진단:
   
   SELECT tablespace_name, file_name, bytes/1024/1024 AS size_mb, autoextensible, maxbytes/1024/1024 AS maxsize_mb FROM dba_data_files WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = ‘undo_tablespace’);
   
   이 쿼리로 현재 사용 중인 언두 테이블스페이스의 데이터 파일 크기와 자동 확장(AUTOEXTEND) 설정을 확인합니다.
2. 데이터 파일 크기 확장 (AUTOEXTEND ON):
   
   가장 일반적인 해결책입니다. 기존 데이터 파일에 자동 확장을 활성화하거나 최대 크기를 늘립니다. ALTER DATABASE DATAFILE ‘/path/to/your/undotbs01.dbf’ AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
   
   주의: MAXSIZE UNLIMITED는 디스크 공간이 고갈될 위험이 있으므로, 물리적 디스크 용량을 고려하여 적절한 한도(예: MAXSIZE 32767M)를 설정하는 것이 안전합니다.
3. 새로운 데이터 파일 추가:
   
   디스크 여유 공간이 다른 위치에 있다면 새로운 데이터 파일을 추가하는 것이 더 안전할 수 있습니다. ALTER TABLESPACE UNDOTBS1 ADD DATAFILE ‘/new_path/undotbs02.dbf’ SIZE 2048M AUTOEXTEND ON NEXT 256M MAXSIZE 16384M;
   
4. 언두 보존 시간(Undo Retention) 조정:
   
   인스턴스가 snapshot too old 예외에 취약하게 반응할 경우 언두 데이터가 유지되는 최소 임계치를 연장하는 조치가 요구됩니다. SHOW PARAMETER undo_retention 명령을 통해 현재 구성을 점검하되, 통상적인 기본값인 900초는 장시간 실행되는 쿼리가 존재하는 인프라에서 충분하지 않을 수 있습니다. ALTER SYSTEM SET undo_retention = 1800 SCOPE=BOTH; 구문을 활용해 해당 값을 조정하며, https://pastpresentproject.com 설계 구조에서 수치 정합성을 확보하기 위해 파라미터를 최적화하듯 운영 환경의 부하를 고려한 기준을 수립합니다. 이러한 설정 변경은 언두 테이블스페이스의 물리적 점유량에 즉각적인 변동을 야기하므로, 보관 주기에 비례하여 스토리지 가용 공간을 추가로 조달하는 프로세스가 병행되어야 합니다.

근본적 해결 방법 2: 애플리케이션 및 쿼리 최적화

인프라 확장만으로는 근본 문제가 해결되지 않을 수 있습니다. 가장 큰 언두 공간을 생성하는 주체는 애플리케이션의 비효율적인 트랜잭션 처리 방식입니다.

1. 대용량 작업 분할(Commit in Loop): 수백만 건의 데이터를 한 트랜잭션으로 업데이트하는 코드는 즉시 수정 대상입니다. 작업을 배치(Batch) 단위로 나누고 중간중간 커밋하여 언두 공간 점유를 해제해야 합니다.
   
   
   예시 (나쁜 사례): BEGIN FOR i IN 1..1000000 LOOP UPDATE large_table SET … 앞서 언급한 wHERE …; END LOOP; COMMIT; END;
   
   예시 (좋은 사례): BEGIN FOR i IN 1..1000000 LOOP UPDATE large_table SET … 이와 같은 wHERE …; IF MOD(i, 10000) = 0 THEN COMMIT; — 1만건마다 커밋 END IF; END LOOP; COMMIT; END;
   
   단, 분할 커밋 시 논리적 데이터 정합성은 개발자가 완전히 책임져야 합니다.
2. 불필요한 전체 테이블 잠금 방지: SELECT … FOR UPDATE와 같은 명시적 잠금은 필요한 최소한의 행에 대해서만 사용해야 합니다. 불필요한 잠금은 동시성 저하와 언두 데이터 증가를 유발합니다.
3. ‘snapshot too old’ 오류는 대규모 데이터를 처리하는 과정에서 실행 시간이 긴 쿼리를 수행할 때 주로 발생합니다. 데이터베이스의 안정적인 운영 표준을 정의하는 한국정보통신기술협회(TTA)의 기술 표준 가이드라인을 분석해 보면, 이러한 현상을 방지하기 위해 해당 쿼리의 실행 계획을 정밀하게 검토하여 적절한 인덱스를 구성하거나 비효율적인 전체 테이블 스캔(Full Table Scan)을 제거하는 최적화 작업이 권고됩니다. 시스템 자원의 효율적 활용을 위해 EXPLAIN PLAN 명령어를 활용한 사전 검증 절차를 수행해야 합니다.
4. 트랜잭션 설계 원칙 재검토: 트랜잭션의 시작(BEGIN)과 종료(COMMIT/ROLLBACK) 시점을 가능한 한 빠르게 설계합니다. 사용자 입력 대기, 외부 API 호출 등은 트랜잭션 외부로 분리하는 것이 바람직합니다.

고급 모니터링 및 예방 정책 수립

문제가 발생한 후 대응하는 것보다 사전에 예측하고 방지하는 시스템을 구축하는 것이 최선의 보안 관행입니다.

• 사전 경고 설정: 언두 테이블스페이스 사용률이 80%를 초과할 때마다 관리자에게 알림을 발송하는 모니터링 스크립트 또는 툴(예: Oracle Enterprise Manager, 사용자 정의 cron job)을 구현합니다, 사용률 확인 쿼리: select tablespace_name, round((1 – (free_blocks / total_blocks)) * 100, 2) as “used percent” from (select tablespace_name, sum(bytes) total_blocks, sum(decode(autoextensible, ‘yes’, maxbytes, bytes)) free_blocks from dba_data_files where tablespace_name like ‘%undo%’ group by tablespace_name);
• 정기적인 정리 작업 스케줄링: 필요 이상으로 오래 유지되는 언두 데이터는 정기적으로 정리해야 합니다. 그러나 대부분의 현대 Oracle 시스템에서는 자동 언두 관리(AUM)가 이를 처리합니다. UNDO_RETENTION 파라미터와 RETENTION GUARANTEE 설정의 균형을 유지하십시오. RETENTION GUARANTEE는 공간 고갈을 가속시킬 수 있으므로 신중하게 사용해야 합니다.
• 용량 계획 수립: 애플리케이션의 트랜잭션 볼륨 증가 추이를 분석하여 분기별 또는 연간 데이터베이스 스토리지 용량 계획에 언두 공간 요구사항을 반드시 포함시킵니다. 경험적으로, 피크 시간대의 최대 동시 트랜잭션 볼륨을 기준으로 언두 테이블스페이스 크기를 산정합니다.

롤백 세그먼트 고갈은 단순한 공간 부족이 아닌, 애플리케이션 결함 또는 잘못된 데이터베이스 설계의 징후입니다. 언두(Undo) 테이블스페이스를 무한정 확장하는 것은 임시방편에 불과하며, 특히 DB 컬럼 암호화 적용 시 검색 성능의 현저한 저하가 수반될 경우 인덱스 활용도가 떨어져 트랜잭션 시간이 길어지고 언두 사용량이 급증하는 악순환이 발생할 수 있습니다.

근본적인 해결을 위해 반드시 V$UNDOSTAT 뷰를 활용한 성능 분석을 정기적으로 수행하십시오. 다음 쿼리는 지난 24시간 동안의 언두 통계를 요약하여 문제 패턴을 찾는 데 도움을 줍니다.

SQL

SELECT TO_CHAR(begin_time, 'HH24:MI') as time, undoblks, txncount, maxquerylen 
FROM v$undostat 
ORDER BY begin_time DESC;

여기서 maxquerylen 값이 undo_retention 설정값을 지속적으로 초과한다면, ‘Snapshot too old’ 오류가 발생할 가능성이 매우 높습니다. 이 경우 해결 방법 2에 명시된 쿼리 최적화 작업이 가장 시급한 과제이며, 암호화된 컬럼에 대한 비효율적인 전체 스캔(Full Scan)이 발생하고 있지는 않은지 면밀히 검토해야 합니다.