중첩 트랜잭션 실전 예제

오늘 우리의 목표는 이런 함수를 만드는 거야:

1. 클라이언트 잔고 확인. 금액을 차감하기 전에 돈이 충분한지 체크해야 해.
2. 잔고에서 금액 차감. 잔고가 충분하면 차감이 일어나.
3. 성공/실패한 작업 모두 로그 남기기. 모든 동작은 나중에 분석할 수 있도록 로그 테이블에 기록돼.

이건 그냥 단순한 차감 함수가 아니야. 여기선 중첩 트랜잭션을 써서 뭔가 잘못되면(예: 잔고 부족, 로그 기록 에러 등) 변경사항을 롤백할 거야. SAVEPOINT의 장점을 알아보고, 에러에 강한 프로시저 만드는 법을 배울 거야.

초기 테이블 만들기

함수 만들기 전에 데이터베이스를 준비하자. 세 개의 테이블이 필요해:

1. clients — 클라이언트 정보와 잔고 저장용.
2. payments — 성공한 트랜잭션 기록용.
3. logs — 결제 시도(성공/실패 모두) 정보 저장용.

-- 클라이언트 테이블
CREATE TABLE clients (
    client_id SERIAL PRIMARY KEY,
    full_name TEXT NOT NULL,
    balance NUMERIC(10, 2) NOT NULL DEFAULT 0
);

-- 성공한 결제 테이블
CREATE TABLE payments (
    payment_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    amount NUMERIC(10, 2) NOT NULL,
    payment_date TIMESTAMP DEFAULT NOW()
);

-- 로그 테이블
CREATE TABLE logs (
    log_id SERIAL PRIMARY KEY,
    client_id INT NOT NULL REFERENCES clients(client_id),
    message TEXT NOT NULL,
    log_date TIMESTAMP DEFAULT NOW()
);

clients 테이블에 테스트 데이터 넣자

INSERT INTO clients (full_name, balance)
VALUES 
    ('Otto Song', 100.00),
    ('Maria Chi', 50.00),
    ('Anna Vel', 0.00);

이제 우리에겐 세 명의 클라이언트가 있어: Otto는 100, Maria는 50, Anna는 0이야.

비즈니스 로직 구현: 프로시저 vs 함수

요약:

• 비즈니스 작업이 "올 오어 낫싱"이면 함수로 충분해.
• 트랜잭션을 단계별로 관리하거나, 부분 커밋/롤백, 에러 로그 남기려면 — 프로시저(CREATE PROCEDURE)를 써.

왜 함수가 아니냐고? PostgreSQL 17에선 함수 안에서 COMMIT, SAVEPOINT, ROLLBACK을 쓸 수 없어. 모든 변경은 외부 트랜잭션 안에서 원자적으로 처리돼.

프로시저(CREATE PROCEDURE ... LANGUAGE plpgsql)만 SAVEPOINT, COMMIT, ROLLBACK을 쓸 수 있는데, 중요한 제한이 있어:

• 프로시저 안에선 SAVEPOINT, COMMIT, RELEASE SAVEPOINT만 허용돼.
• ROLLBACK TO SAVEPOINT는 PL/pgSQL에서 금지야(에러남). 대신 BEGIN ... EXCEPTION ... END 블록을 써서 "가상 세이브포인트"를 만들어.

부분 코드 롤백의 핵심 테크닉:

BEGIN
    -- 네 코드
EXCEPTION
    WHEN OTHERS THEN
        -- 이 블록에서 에러나면 블록 안의 모든 변경이 롤백돼!
        -- 로그에 정보 남기기:
        INSERT INTO logs (...) VALUES (...);
END;

부분 롤백과 로그 남기는 결제 프로시저 만들기

CREATE OR REPLACE PROCEDURE process_payment(
    in_client_id INT,
    in_payment_amount NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
    current_balance NUMERIC;
BEGIN
    -- 클라이언트 잔고 가져오기
    SELECT balance INTO current_balance
    FROM clients
    WHERE client_id = in_client_id;

    IF NOT FOUND THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, '클라이언트 못 찾음, 작업 거부됨');
        RAISE EXCEPTION 'ID %인 클라이언트 못 찾음', in_client_id;
    END IF;

    -- 잔고 충분한지 체크
    IF current_balance < in_payment_amount THEN
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, '차감할 금액 ' || in_payment_amount || '원이 부족함');
        -- 프로시저 종료
        RETURN;
    END IF;

    -- 원자적 변경 블록; 에러시 롤백(가상 savepoint)
    BEGIN
        -- 잔고 차감
        UPDATE clients
        SET balance = balance - in_payment_amount
        WHERE client_id = in_client_id;

        -- 성공한 결제 기록 추가
        INSERT INTO payments (client_id, amount)
        VALUES (in_client_id, in_payment_amount);

        -- 성공 로그 남기기
        INSERT INTO logs (client_id, message)
        VALUES (in_client_id, '성공적으로 ' || in_payment_amount || '원 차감됨');

    EXCEPTION
        WHEN OTHERS THEN
            -- 이 블록 안의 모든 변경이 취소됨
            INSERT INTO logs (client_id, message)
            VALUES (in_client_id, '결제 중 에러: ' || SQLERRM);
            -- (명시적 ROLLBACK TO SAVEPOINT 필요 없음 — 금지되어 있고 필요도 없음)
    END;
END;
$$;

요약:

• 잔고 부족/클라이언트 없음 — 로그 남기고 바로 종료.
• 중요한 코드는 BEGIN ... EXCEPTION ... END 블록에 다 들어가.
• 이 블록에서 에러나면 모든 변경이 자동 롤백되고, 에러 로그 남겨.
• SAVEPOINT나 ROLLBACK TO SAVEPOINT 직접 쓰지 않아 — PL/pgSQL에선 EXCEPTION 블록으로만 처리하는 게 맞아.

프로시저 호출

중요: 프로시저는 CALL ... 명령으로 호출해야 하고, 외부 DB 연결은 autocommit 모드거나 명시적 대형 트랜잭션 밖이어야 해!

CALL process_payment(1, 30.00);   -- 성공 결제
CALL process_payment(2, 100.00);  -- 잔고 부족
CALL process_payment(999, 50.00); -- 클라이언트 없음

결과 확인

• 클라이언트 잔고 변경 — 결제 성공시에만.
• payments 테이블 — 성공한 차감만 기록됨.
• logs — 모든 시도(에러 포함) 기록됨.

SELECT * FROM clients;
SELECT * FROM payments;
SELECT * FROM logs;

실전 적용

트랜잭션 처리 프로시저는 핀테크, 이커머스, 게임 플랫폼 같은 시스템의 핵심이야. 예를 들어, 온라인 쇼핑몰에서 기프트카드 잔고를 관리하고, 결제 시 차감해야 하거나, 초당 수천 건의 거래가 일어나는 은행 시스템을 생각해봐.

이런 지식은 실제로 엄청 유용하고, 네 고객의 데이터를 안전하게 지키고 결제 처리에서 치명적인 실수를 막아줄 거야.