끝나지 않는 트랜잭션의 굴레 😫
RDBMS에서 가장 중요한 원칙은 여럿 있겠지만,
트랜잭션의 ACID 원칙은 놓칠 수 없는 핵심 중 핵심이다.
개발을 하다 보면 데이터베이스, 특히 Oracle의 트랜잭션 처리 방식 때문에 얘기치 못한 난관에 부딪히곤 한다.
오늘은 회사에서 프로젝트를 진행하며 트리거(Trigger)와 프로시저(Procedure)를 이용한 데이터 처리 과정에서 겪었던 "뮤테이팅 테이블(Mutating Table)" 에러와
"독립 트랜잭션(Autonomous Transaction)" 의 데이터 가시성 문제를 해결한 방식을 회고하겠다.
만약 트리거를 연쇄적으로 사용하다가 원인 모를 에러를 마주했거나,
트랜잭션의 원자성(Atomicity)을 몸소 체험하고 있다면 좋은 참고가 될 것이다.
문제 상황
두 가지 선택, 두 가지 실패 😱
먼저 시스템의 데이터 흐름은 다음과 같았다.
TABLE_A에INSERT→TRIGGER_AtoB발동 →TABLE_B에INSERT→TRIGGER_BtoC발동 →PROCEDURE_MAIN호출 →TABLE_C에 데이터 적재
여기서 핵심 문제는 바로 TRIGGER_BtoC에서 발생했다.
트랜잭션 처리 방식을 어떻게 가져가느냐에 따라 서로 다른 두 가지 문제에 직면했다.
1. TRIGGER_BtoC를 독립 트랜잭션으로 처리한 경우
독립 트랜잭션(PRAGMA AUTONOMOUS_TRANSACTION)을 사용하면 부모 트랜잭션과 별개로 동작하여 COMMIT과 ROLLBACK을 독자적으로 수행할 수 있다.
Mutating Table ERROR를 피하기 위해 이 방법을 시도했지만, 새로운 문제가 발생했다.
- 문제점:
PROCEDURE_MAIN이 새로운 트랜잭션으로 실행되다 보니, 아직COMMIT되지 않은TABLE_A의 새로운 데이터를 읽지 못했다. - 결과: 데이터 동기화 실패.
TABLE_C에 타겟 데이터는 들어가지 않았다. 😭
2. TRIGGER_BtoC를 일반 트랜잭션으로 처리한 경우
그렇다면 독립 트랜잭션을 사용하지 않고, 전체를 하나의 트랜잭션으로 묶으면 어떨까?
이번엔 Oracle의 강력한 제약사항과 마주했다.
- 문제점:
TABLE_A에INSERT된 후, 연쇄적으로 동작하는 트리거가 아직COMMIT되지 않은TABLE_A의 데이터를 다시 조회하려고 시도했다. - 결과:
ORA-04091: table is mutating, trigger/function may not see it에러가 발생하며 프로시저 실행 자체가 실패했다. 🚫
🔄 문제점 종합 분석
| 상황 | 독립 트랜잭션 사용 | 독립 트랜잭션 미사용 |
|---|---|---|
| 문제 | COMMIT 이전 데이터 읽기 불가 | ORA-04091 뮤테이팅 테이블 에러 |
| 결과 | 데이터 동기화 실패 | 프로시저 실행 실패 |
| 원인 | 독립 트랜잭션의 격리 수준 | Oracle의 데이터 일관성 보장을 위한 제약 |
🔒 뮤테이팅 테이블(Mutating Table)이란?
DML (
INSERT,UPDATE,DELETE) 문에 의해 변경되고 있는 테이블을 해당 DML 문을 발생시킨 트리거 안에서 다시SELECT하려고 할 때 발생하는 문제다.
Oracle은 데이터의 읽기 일관성(Read Consistency)을 보장하기 위해 이를 엄격히 금지한다.
🔒 독립 트랜잭션(PRAGMA AUTONOMOUS_TRANSACTION)이란?
PRAGMA AUTONOMOUS_TRANSACTION는 새로운 트랜잭션 컨텍스트를 여는 기능으로,
호출한 PL/SQL 서브프로그램(OR 트리거) 안에서 독립적인COMMIT/ROLLBACK을 수행하고,
종료 후 바로 본 트랜잭션으로 복귀한다.
- 메인 트랜잭션을 일시정지(
suspend)했다가 돌아오는 구조- 동일 세션, 프로세스 안에서 트랜잭션 컨텍스트만 분리해 실행되는 동기식 구조이다.
해결을 위한 두 가지 접근법 🛠️
이 복잡한 트랜잭션 문제를 해결하기 위해 두 가지 방안을 고려했다.
1️⃣ 스케줄러 기반의 배치 처리 접근
첫 번째는 트랜잭션 자체를 분리하는 것이다.
기존 시스템의 설계 상, 매 정시 25분마다 호출되는 PROCEDURE_MAIN을 정시 57분 호출로 변경하고,
트리거에서 즉시 호출해 변경 사항을 바로 반영하는 과정을 제거하는 것이다.
왜냐하면, 매 정시에 실행되는 가장 핵심 프로시저인
PROCEDURE_CORE가 최신 데이터를 기준으로 동작해야 하기 때문이다.
이는 Oracle Job Scheduler를 이용해 특정 시간마다 주기적으로 실행하는 방식이다.
- 방법: 매시 57분마다
PROCEDURE_MAIN을 실행하도록 스케줄링. - 장점:
- 트랜잭션 충돌 문제를 원천적으로 회피하여 안정성을 확보할 수 있다.
- 배치(Batch) 처리로 리소스 사용을 예측하고 관리하기 용이하다.
- 단점:
- 데이터가 실시간으로 동기화되지 않는다.
- 한 마디로, 지금의 변경 사항이
TABLE_C로 반영되지 않는다는 것.
- 한 마디로, 지금의 변경 사항이
- 데이터가 실시간으로 동기화되지 않는다.
2️⃣ 애플리케이션 레벨에서의 명시적 처리
두 번째는 데이터베이스의 책임을 애플리케이션으로 옮기는 것이다.
트리거에서는 프로시저 호출 로직을 제거하고, 애플리케이션이 TABLE_A에 데이터를 INSERT한 후 명시적으로 PROCEDURE_MAIN을 호출하도록 변경한다.
- 방법: 트리거 로직 간소화, 비즈니스 로직을 애플리케이션 레이어로 이동.
- 장점:
- 뮤테이팅 테이블 문제를 근본적으로 해결한다.
- 트랜잭션 제어권을 애플리케이션이 갖게 되어 디버깅과 모니터링이 용이하다.
- 사용자 액션에 따른 즉시 처리가 가능하다.
- 단점:
- 애플리케이션의 로직 복잡도가 소폭 증가할 수 있다.
🎯 권장 접근법 및 최종 결론
두 방법 모두 장단점이 있지만, 현대적인 아키텍처 관점에서는 애플리케이션 레벨 처리가 더 효과적이다.
(다만, 현재 회사의 시스템이 현대적이지 않다는게 문제...)
DB는 데이터 저장 및 무결성 유지에 집중하고, 비즈니스 로직 처리는 애플리케이션이 담당하는 것이 책임 분리 원칙(Separation of Concerns)에 부합하기 때문이다.
물론, 비즈니스 요구사항에 따라 하이브리드 접근법도 훌륭한 대안이 될 수 있다.
- Critical 데이터: 애플리케이션에서 실시간 처리.
- 일반 데이터: 스케줄러를 통해 배치 처리.
- 보완 메커니즘: 실시간 처리에 실패한 건을 배치에서 재처리.
What I learned ✍️
이번 트러블슈팅을 통해 트랜잭션과 데이터베이스 아키텍처에 대해 많은 것을 배울 수 있었다.
✔ 기술적 관점
- 트랜잭션의 원자성(Atomicity)을 이론이 아닌 실무로 체감했다.
- 독립 트랜잭션이 주는 격리성의 편리함 이면에는
데이터 가시성이라는 큰 제약이 있음을 깨달았다. - 트랜잭션 경계 설정이 시스템 설계에서 얼마나 중요한지 다시 한번 확인했다.
- 트리거는 자동화의 편리함을 주지만, 복잡성을 기하급수적으로 늘릴 수 있는 양날의 검이다.
✔ 아키텍처 관점
- 책임 분리 원칙: DB는 데이터 저장, 애플리케이션은 비즈니스 로직 처리에 집중해야 한다.
- 장애 격리(Fault Isolation): 트리거와 같은 컴포넌트의 문제가 시스템 전체에 미치는 영향을 최소화해야 한다.
- 관찰 가능성(Observability): 트리거 내부 로직은 디버깅이 어렵다.
로직은 투명하게 관리되어야 한다.
✔ 운영 관점
- 실시간성 vs 안정성: 모든 것을 실시간으로 처리할 필요는 없다.
비즈니스 요구에 따른 우선순위를 정해야 한다. - 기술 부채 관리: 복잡한 트리거 체인은 결국 유지보수 비용이라는 기술 부채로 돌아온다.
마치며...
단순한 데이터 처리 문제로 시작했지만, 결국 트랜잭션의 깊은 이해와 아키텍처 재설계까지 고민하게 된 값진 경험이었다.
복잡한 데이터베이스 로직과 오래된 시스템 구조로 어려움을 겪는 누군가에게 작은 도움이 되기를 바란다.
사실, 프론트엔드, 백엔드, DB 단을 별도로 구성하기만 해도 고민을 아에 안할 문제이긴 하다...
데이터 저장과 프로세스 로직을 구분하기만 해도 해결되는 부분이기 때문.
다만, 모든 개발 환경이 동일하지 않고 비즈니스 환경에 맞는 기술적 발전이 필요하니까... 함께 발전해보겠다.
(해당 글은 Claude Sonnet 4, Gemini 2.5 pro를 통해 작성되고, 게시되었습니다.)
