[PostgreSQL] Transaction: 격리 수준과 MVCC 아키텍처 완벽 가이드

1. 들어가며: 물류 데이터와 무결성 제어의 절대 조건

이커머스나 공급망 관리(SCM) 시스템에서 "재고 차감"이나 "창고 간 입출고"와 같은 물류 연산은 한 치의 오차도 허용되어서는 안 된다. 수천 명의 사용자가 동시에 인기 상품을 주문하거나 물류 센터에서 실시간으로 입출고 전표를 업데이트할 때, 일시적인 트래픽 폭증으로 인해 데이터베이스의 재고 정합성이 깨진다면 이는 오배송, 품절 오류 등 기업의 치명적인 신뢰도 하락과 금전적 손실로 직결된다.

관계형 데이터베이스(RDBMS)의 PostgreSQL은 이러한 고위험성 데이터를 보존하기 위해 매우 엄격한 ACID 트랜잭션 스펙과 독창적인 MVCC(Multi-Version Concurrency Control) 동시성 제어 아키텍처를 제공한다.

[PostgreSQL] 인덱스를 파괴하는 3대 안티 패턴과 Sargable 쿼리 최적화 기법

 

본 가이드에서는 실무에서 동시성 충돌이 가장 빈번하게 발생하는 물류 창고 재고 및 변동 로그 관리 시스템(product_stocks, stock_logs) 도메인을 바탕으로 트랜잭션의 동작 원리, 격리 수준(Isolation Level)별 읽기 현상, 동시성을 지휘하는 잠금(Lock)의 스펙, 그리고 MVCC 하에서 발생하는 데드 튜플(Dead Tuple)의 관리와 Vacuum의 내부 메커니즘을 상세히 정복한다.

 

 

2. 물류 데이터 수호의 시작: 트랜잭션과 ACID, 그리고 SAVEPOINT

트랜잭션은 데이터베이스의 논리적인 최소 작업 단위이다. 흔히 말하는 데이터의 무결성은 ACID라는 네 가지 기둥 위에 견고하게 서 있다.

• 원자성 (Atomicity): 트랜잭션 내의 모든 연산은 "모두 성공(Commit)"하거나 "모두 실패(Rollback)"해야 한다. 어설픈 중간 상태의 데이터 영속화는 허용되지 않는다.
• 일관성 (Consistency): 데이터베이스가 선언한 CHECK 제약 조건, 외래키, 기본키 무결성 등의 비즈니스 규칙은 트랜잭션 전후로 항상 완벽하게 유지되어야 한다. (예: 재고 수량 $quantity \ge 0$)
• 격리성 (Isolation): 동시에 실행되는 트랜잭션들은 서로의 연산 과정에 무분별하게 개입할 수 없다. 격리 수준에 따라 차단 경계가 조율된다.
• 지속성 (Durability): 성공적으로 커밋된 트랜잭션은 시스템 장애나 물리적인 전원 정전 사태가 발생하더라도 완전히 영속화되어야 한다. PostgreSQL은 이를 위해 디스크에 실제 데이터를 쓰기 전, 메모리 버퍼 상의 변경 이력을 고속 순차 디스크 쓰기 파일인 WAL(Write Ahead Log)에 먼저 기록하여 지속성을 보장한다.

 

2.1 실무 DDL 및 시드 데이터 셋업

가상의 물류 창고 재고 스키마를 다음과 같이 설계한다. 재고 수량(quantity) 필드에는 임의의 차감으로 인해 음수가 되는 것을 방지하는 강력한 도메인 정합성 수호 조건(CHECK quantity >= 0)이 장착되어 있다.

-- 1. 상품 재고 테이블 설계
CREATE TABLE product_stocks (
    stock_id     BIGSERIAL     PRIMARY KEY,
    product_id   BIGINT        NOT NULL,
    storage_type VARCHAR(20)   NOT NULL CHECK (storage_type IN ('정상재고', '반품재고', '임시보관')),
    quantity     INTEGER       NOT NULL DEFAULT 0 CHECK (quantity >= 0),
    updated_at   TIMESTAMPTZ   DEFAULT NOW()
);

-- 2. 재고 변동 로그 테이블 설계 (SAVEPOINT 검증용)
CREATE TABLE stock_logs (
    log_id         BIGSERIAL   PRIMARY KEY,
    stock_id       BIGINT      NOT NULL REFERENCES product_stocks(stock_id),
    type           VARCHAR(10) NOT NULL CHECK (type IN ('입고', '출고')),
    amount         INTEGER     NOT NULL CHECK (amount > 0),
    description    TEXT,
    created_at     TIMESTAMPTZ DEFAULT NOW()
);

-- 3. 비즈니스 시뮬레이션을 위한 샘플 데이터 인입
INSERT INTO product_stocks (product_id, storage_type, quantity) VALUES
    (1, '정상재고', 1500),
    (1, '반품재고', 3000),
    (2, '정상재고',  250),
    (3, '임시보관',   80),
    (4, '정상재고', 5200),
    (4, '반품재고', 1800),
    (5, '반품재고', 1200),
    (5, '임시보관',    0);

 

2.2 SAVEPOINT를 활용한 트랜잭션 내 부분 복구 전략

실무 배치를 가동하거나 대규모 연속 재고 조정 작업을 제어할 때, 작업 중간에 예외가 발생했다고 해서 앞서 가동한 수천 개의 로직을 모두 처음부터 롤백하는 것은 엄청난 리소스 손실이다. 이때 트랜잭션 내부의 책갈피 역할을 수행하는 SAVEPOINT를 사용하면 실패한 서브 작업 구간만 영리하게 취소할 수 있다.

-- [SAVEPOINT 비즈니스 시나리오]: 1번 창고에서 연속 출고 처리를 단행하다가 오류가 발생했을 때
BEGIN;

-- 1. 첫 번째 정상 출고 처리 진행 및 이력 적재
UPDATE product_stocks SET quantity = quantity - 50 WHERE stock_id = 1;
INSERT INTO stock_logs (stock_id, type, amount, description)
VALUES (1, '출고', 50, '정상 출고 검수');

-- 2. 안전 구역 마크 (SAVEPOINT 선언)
SAVEPOINT sp_transfer_safe;

-- 3. 실수 혹은 정합성 오염으로 인해 유효하지 않은 비정상 연산 시도 (재고 한도 초과 출고)
UPDATE product_stocks SET quantity = quantity - 999999 WHERE stock_id = 1; 
-- ⚠️ [에러 발생]: CHECK (quantity >= 0) 제약 조건 위반으로 연산 실패!

-- 4. 전체 트랜잭션을 터뜨리지 않고, 안전 마크 구역까지만 부분 롤백을 단행
ROLLBACK TO SAVEPOINT sp_transfer_safe;

-- 5. 첫 번째 정상 출고 내역만 보존된 상태로 안전하게 영속화 반영
COMMIT;

-- [검증]: 1번 재고의 quantity는 안전하게 50개만 차감되었고 전체 트랜잭션 일관성은 유지되었다.

 

 

3. 격리 수준(Isolation Level)과 3대 이상 읽기 현상

여러 사용자가 공유 데이터에 동시에 접근할 때 발생하는 이상 읽기 현상은 격리 수준 설정에 따라 차단된다. SQL 표준에서 정의한 3대 이상 읽기 현상(Read Phenomena)의 실체는 다음과 같다.

1. Dirty Read (더티 리드): 트랜잭션 A가 수정 중이지만 아직 커밋하지 않은 미확정 상태의 값을 트랜잭션 B가 읽어버리는 현상이다. 트랜잭션 A가 최종 롤백되면 트랜잭션 B는 세상에 존재하지도 않는 가상(Ghost)의 데이터를 참조한 격리가 된다.
2. Non-Repeatable Read (반복 불가능한 읽기): 한 트랜잭션 내에서 동일한 단일 행을 두 번 조회했는데, 그 사이에 다른 트랜잭션이 값을 수정하고 커밋해 버려서 첫 번째 조회한 값과 두 번째 조회한 값이 다르게 읽히는 일관성 유실 현상이다.
3. Phantom Read (유령 읽기): 한 트랜잭션 내에서 동일한 조건의 범위 쿼리를 두 번 실행했는데, 그 사이에 다른 트랜잭션이 해당 범위 내에 속하는 새로운 행을 삽입(INSERT)하고 커밋하여 두 번째 조회 시 전에 없던 새로운 '유령(Phantom) 행'들이 불쑥 늘어나는 현상이다.

3.1 PostgreSQL의 격리 수준별 방어 맵

PostgreSQL은 동시성 효율을 높이기 위해 독자적인 격리 수준 스펙을 운영한다.

ANSI 격리 수준	PostgreSQL 내부 구현	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	Read Committed로 자동 동작	차단 (PostgreSQL은 Dirty Read가 물리적으로 불가능)	발생 가능	발생 가능
Read Committed	Read Committed (기본 설정)	차단	발생 가능	발생 가능
Repeatable Read	Repeatable Read (Snapshot Isolation)	차단	차단	차단 (PostgreSQL은 이 단계에서 Phantom Read까지 차단함)
Serializable	Serializable (SSI)	차단	차단	차단 (완벽한 직렬성 보장)

 

3.2 실무 쿼리로 검증하는 격리성 검증 테스트

① Read Committed (기본 격리 수준) 테스트

가장 기본적인 격리 수준이다. 오직 "커밋된 데이터"만 읽는 것을 허용하지만, 한 트랜잭션 내에서 값이 계속 바뀔 수 있는 일관성 유실(Non-Repeatable Read)에 노출되어 있다.

-- [트랜잭션 A]: 정상재고 수량 업데이트 시도
BEGIN;
UPDATE product_stocks SET quantity = quantity - 100 WHERE stock_id = 1;
-- ⚠️ 아직 커밋을 내리지 않은 미완료 상태

-- [트랜잭션 B]: 다른 터미널에서 재고 조회 시도 (Read Committed)
BEGIN;
SELECT quantity FROM product_stocks WHERE stock_id = 1; 
-- 결과: 1500 (트랜잭션 A의 변경이 보이지 않는다. Dirty Read 자동 방어!)

-- [트랜잭션 A]: 변경 확정 및 커밋 실행
COMMIT;

-- [트랜잭션 B]: 두 번째 조회 수행
SELECT quantity FROM product_stocks WHERE stock_id = 1;
-- 결과: 1400 
-- ⚠️ [Non-Repeatable Read 발생]: 한 트랜잭션 공간 안에서 값의 변화가 관측되었다!
COMMIT;

② Repeatable Read (스냅샷 기반 격리 수준) 테스트

트랜잭션이 개시된 특정 시점의 전체 데이터 스냅샷을 박제해 유지하는 격리 수준이다.

-- [트랜잭션 B]: Repeatable Read 모드로 트랜잭션 시작
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT quantity FROM product_stocks WHERE stock_id = 1;
-- 결과: 1400 (스냅샷 기준값 박제)

-- [트랜잭션 A]: 그 사이 다른 곳에서 재고를 차감하고 커밋 처리
BEGIN;
UPDATE product_stocks SET quantity = quantity - 100 WHERE stock_id = 1;
COMMIT;

-- [트랜잭션 B]: 스냅샷 보존 상태에서 재고 다시 조회
SELECT quantity FROM product_stocks WHERE stock_id = 1;
-- 결과: 1400 
--  [Non-Repeatable Read 완벽 방어!]: 트랜잭션 A가 커밋했음에도 최초 스냅샷 값을 우직하게 유지한다.
COMMIT;

 

 

4. 동시성 제어의 양대 축: 잠금(Locking)과 데드락(Deadlock)

PostgreSQL은 격리 수준 제어만으로 충돌을 피할 수 없을 때, 특정 물리 자원의 점유권을 제어하는 Locking 메커니즘을 가동한다.

4.1 잠금의 수준과 격리성 타격 범위

• Row Level Lock (행 레벨 잠금): UPDATE, DELETE 연산 시 타겟 행에 대해 자동으로 걸리며 다른 트랜잭션이 동일 행을 변경하는 것을 완벽히 방어한다.
• Table Level Lock (테이블 레벨 잠금): 스키마를 변경하거나(ALTER TABLE), 테이블 전체 구조를 락업할 때 획득된다. 하위 세부 트랜잭션 인입 전체를 동결하므로 극도로 조심히 다뤄야 한다.

 

4.2 FOR UPDATE를 활용한 명시적 행 잠금 (비관적 락)

출고 연산의 경우, "재고 수량 조회" 시점과 "재고 수량 변경" 시점 사이에 다른 비즈니스 코드가 끼어들면 잘못된 재고 예측이 일어나기 쉽다. 이때 조회 시점에 즉각 락을 거는 FOR UPDATE 구문을 활용해 경쟁 상태(Race Condition)를 차단한다.

-- [트랜잭션 A]: 1번 재고를 선점 조회하며 Row Exclusive Lock 획득
BEGIN;
SELECT quantity FROM product_stocks WHERE stock_id = 1 FOR UPDATE;
-- [TID 1번 행 잠금 완료]: 다른 트랜잭션은 트랜잭션 A가 COMMIT을 내리기 전까지 대기 상태에 돌입한다.

-- [트랜잭션 B]: 다른 터미널에서 1번 재고에 대해 수량 차감 시도
BEGIN;
UPDATE product_stocks SET quantity = quantity - 50 WHERE stock_id = 1;
-- ⚠️ [대기 상태(Lock Contention)]: 트랜잭션 A가 점유한 락이 해제되기를 기다리며 무기한 블로킹된다.

-- [트랜잭션 A]: 모든 재고 실사 연산을 마친 후 최종 커밋
COMMIT; 
-- [해제]: 락이 풀리는 즉시 대기 중이던 트랜잭션 B의 UPDATE 연산이 안전하게 바통을 이어받아 구동된다.

 

4.3 교착 상태(Deadlock)의 발생 조건과 예방 대책

교착 상태는 두 개 이상의 트랜잭션이 서로 상대방이 점유한 자원의 잠금이 풀리기만을 대기하며 영원한 블로킹에 빠지는 일종의 순환 대기 사태이다.

-- 🛡️ [교착 상태 예방 대책]: 자원 접근 순서 동기화
-- 데드락을 원천 예방하는 가장 확실하고 단순한 방법은 모든 트랜잭션 코드에서 자원에 접근(UPDATE/FOR UPDATE)하는 
-- 물리적 순서를 완벽히 일치시키는 것이다. 
-- 예: 언제나 stock_id가 정렬된 오름차순 순서로만 Lock을 획득하도록 규약 수립.

 

 

5. PostgreSQL의 숙명: MVCC와 Vacuum 아키텍처

PostgreSQL 동시성 제어 기술의 정수는 MVCC(Multi-Version Concurrency Control)에 있다. MVCC는 "읽기 작업은 쓰기 작업을 막지 않고, 쓰기 작업 또한 읽기 작업을 막지 않는다"는 대명제를 달성하기 위해 고안된 구조다.

5.1 MVCC의 내부 동작: 덮어쓰지 않는 변경의 원리

오라클이나 MySQL(InnoDB)은 원본 레코드를 덮어쓰고 언두 로그(Undo Log) 영역에 이전 버전을 보관해 격리성을 구현한다. 그러나 PostgreSQL은 원본 힙 메모리 공간에 직접 쓰기를 가동하되, 기존 데이터를 절대로 물리적으로 덮어쓰거나 지우지 않는다.

• INSERT: 새로운 물리 데이터 튜플을 페이지 공간에 추가한다. 이때 해당 튜플 메타데이터 영역에 xmin(생성한 트랜잭션 ID)을 기록한다.
• DELETE: 데이터를 지우지 않는다. 대신 대상 튜플 메타데이터 영역에 xmax(삭제를 결정한 트랜잭션 ID)를 태깅하여 더 이상 다른 트랜잭션의 스냅샷 검색망에 걸리지 않도록 무효화 처리한다.
• UPDATE: 내부적으로 DELETE + INSERT 연산의 결합으로 구현된다. 기존 버전 튜플의 xmax를 켜서 구버전으로 밀어두고, 새로운 페이지 공간에 변경된 새 버전의 튜플을 새로 삽입한 후 xmin을 기록한다.

 

5.2 유령 행의 관측: Dead Tuple 통계 측정하기

트랜잭션이 완전히 종료되고 나면, xmax가 마킹된 구버전의 행(데드 튜플)들은 데이터베이스 내의 그 어떤 트랜잭션 스냅샷에도 조회가 허용되지 않는 완벽한 쓰레기 데이터로 전락한다. 이러한 데드 튜플이 누적되면 테이블 크기가 불필요하게 뚱뚱해지는 '테이블 블로팅(Table Bloat)' 현상이 생겨 시퀀셜 스캔 및 전체 버퍼 조회 효율이 곤두박질천다.

실무 데이터 가용성을 위해 수시로 아래의 카탈로그 통계 뷰를 스캔하여 데드 튜플의 누적 상황을 체크해야 한다.

-- product_stocks 테이블 내 활성 튜플(n_live_tup) 대비 유령 행 데드 튜플(n_dead_tup) 비율 확인
SELECT 
    relname AS 테이블명, 
    n_dead_tup AS 데드_튜플_개수, 
    n_live_tup AS 라이브_튜플_개수,
    ROUND(n_dead_tup::numeric / NULLIF(n_dead_tup + n_live_tup, 0) * 100, 2) AS 데드_튜플_비율_PCT
FROM pg_stat_user_tables
WHERE relname = 'product_stocks';

 

5.3 데이터베이스의 정화 의식: VACUUM과 Autovacuum

이렇게 누적된 데드 튜플 공간을 정리하여 새로운 가용 튜플을 위한 디스크 페이지 빈 곳으로 환원시켜주는 필수 정화 프로세스가 바로 VACUUM이다.

• 일반 VACUUM: 테이블에 락을 거의 걸지 않고(Read/Write 유효) 백그라운드에서 데드 튜플 메모리 공간만 확보하여 PostgreSQL 페이지 프리 리스트(FSM: Free Space Map)로 반환한다. OS 파일 시스템으로 크기를 완전히 돌려주지는 못하지만 새로운 인서트를 담을 충분한 여유 공간을 확보해 준다.
• VACUUM FULL: 테이블 전체에 강한 배타적 잠금(Access Exclusive Lock)을 행사하여 디스크 전체를 새로운 압축 데이터 파일로 재구축한다. 디스크 물리 용량 자체가 완전히 OS로 회수되지만, 가동 중 서비스가 일체 마비되므로 절대 프로덕션 가동 중에 수행해서는 안 된다.
• Autovacuum: PostgreSQL 백그라운드 가디언 데몬이 특정 데드 튜플 임계치(기본 설정: 데드 튜플 수 > 테이블 크기 * 20% + 50개)를 초과하는 순간 자동으로 VACUUM 및 통계 갱신(ANALYZE)을 구동해 준다.

-- [실무 자동 진단 팁]: 잦은 변경이 일어나는 재고 테이블은 autovacuum 임계치를 
-- 테이블 레벨 커스텀 속성 파라미터 설정을 통해 아주 타이트하게(예: 5% ~ 10%) 유지하는 것이 블로팅을 조기 방어하는 왕도이다.
ALTER TABLE product_stocks SET (autovacuum_vacuum_scale_factor = 0.05);

 

 

6. 마치며: 안전하고 막강한 무결성 아키텍처를 향한 체크리스트

PostgreSQL 트랜잭션 제어 기법과 MVCC 및 자원 무결성을 도모하기 위한 궁극의 핵심 전술 체크리스트를 인포그래픽 카드로 도식화하여 총정리해 본다.

실무 데이터 아키텍트 가이드라인 3종

1. 원자적 결합과 부분 복구 설계: 연속적인 다중 창고 입출고 연산 시에는 수동 예외 복구 오버헤드를 막기 위해 비즈니스 단계마다 SAVEPOINT를 적절히 도입하여 시스템의 트랜잭션 회복력(Resiliency)을 확보할 것.
2. 동시성 선점과 교착 방지: 대규모 주문 폭주가 일어나는 재고 수량 연산 로직은 선점 단계에서 SELECT FOR UPDATE를 활용해 경쟁 상태를 원천 차단하되, 데드락 방지를 위해 다중 자원 갱신 시 반드시 정렬된 ID 순서(오름차순)로 순차 락을 점유하는 규칙을 단일화할 것.
3. 지속 가능한 리소스 정화: MVCC로 인해 발생하는 필연적인 데드 튜플(Dead Tuple) 누적을 모니터링하기 위해 pg_stat_user_tables 통계 수집 뷰를 주기적으로 확인하고, 고부하 변경 테이블은 autovacuum_vacuum_scale_factor 비율을 세밀하게 하향 조정하여 디스크 단편화와 테이블 블로팅을 사전에 통제할 것.

 

 

# 본 이미지들은 AI를 이용하여 생성하였습니다.