PostgreSQL에서 중첩 트랜잭션 작업 시 흔한 실수 분석

Postgres로 프로그래밍하는 건 진짜 모험이야. 가끔은 "내 실수 찾아라"라는 퀘스트가 되기도 하지. 이번 블록에서는 중첩 트랜잭션을 다루면서 만날 수 있는 흔한 실수와 함정에 대해 얘기해볼게. 가자!

함수와 프로시저 내부에서 트랜잭션 명령을 잘못 사용하는 경우

실수: COMMIT, ROLLBACK 또는 SAVEPOINT를 FUNCTION 안에서 사용하려고 하는 경우.

이유: PostgreSQL에서 함수(CREATE FUNCTION ... LANGUAGE plpgsql)는 항상 하나의 외부 트랜잭션 안에서 실행돼. 그래서 함수 내부에서 트랜잭션 명령을 쓰면 안 돼. 시도하면 문법 에러가 나.

실수 예시:

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- 실수: 트랜잭션 명령은 금지야
END;
$$ LANGUAGE plpgsql;

올바른 방법:

원자적으로 "모두 아니면 아무것도" 식으로 동작해야 하는 작업은 트랜잭션 명령 없이 함수로 만들어. 단계별로 변경사항을 저장하고 싶으면 프로시저를 써야 해.

실수: PL/pgSQL 프로시저에서 ROLLBACK TO SAVEPOINT를 쓰려고 하는 경우.

이유: PostgreSQL 17에서는 프로시저(CREATE PROCEDURE ... LANGUAGE plpgsql) 내부에서 COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT만 허용돼. 하지만 ROLLBACK TO SAVEPOINT는 PL/pgSQL에서 쓸 수 없어! 시도하면 문법 에러가 나.

실수 예시:

CREATE PROCEDURE p_bad()
LANGUAGE plpgsql
AS $$
BEGIN
    SAVEPOINT sp1;
    -- ...
    ROLLBACK TO SAVEPOINT sp1; -- 실수! 사용 불가
END;
$$;

올바른 방법:

“부분 롤백”이 필요하면 BEGIN ... EXCEPTION ... END 블록을 써. 이 블록은 자동으로 savepoint를 만들어주고, 블록 안에서 에러가 나면 그 시점으로 롤백돼.

CREATE PROCEDURE p_good()
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- 에러가 날 수 있는 작업들
        ...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'BEGIN ... EXCEPTION ... END 블록 안에서 롤백됨';
    END;
END;
$$;

중첩 프로시저 호출: 제한사항과 흔한 실수

실수: 이미 열린 클라이언트 트랜잭션 안에서 COMMIT/ROLLBACK이 있는 프로시저를 호출하는 경우.

이유: 트랜잭션 제어가 있는 프로시저는 autocommit 모드(프로시저 하나당 트랜잭션 하나)에서만 제대로 동작해. 그렇지 않으면 프로시저 내부에서 COMMIT이나 ROLLBACK을 쓰려고 할 때 에러가 나. 이미 클라이언트에서 트랜잭션이 열려 있으니까.

예시:

# Python에서 psycopg2는 기본적으로 autocommit=False
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- my_proc 안에서 COMMIT 시도 시 에러

올바른 방법:

• 프로시저 호출 전에 연결을 autocommit 모드로 바꿔.
• 프로시저를 함수나 SELECT로 호출하지 마.

실수: 트랜잭션 제어(COMMIT, ROLLBACK)가 있는 프로시저를 CALL이 아닌 다른 방법(예: SELECT)으로 호출하는 경우 동작하지 않아.

이유: 트랜잭션 제어는 CALL(또는 익명 DO 블록)로 호출할 때만 가능해. 함수에서 호출하면 안 돼.

락과 데드락(Deadlock) 문제

락은 불청객 같아. 처음엔 별거 아닌 것 같다가 나중엔 혼란을 일으키지. 데드락은 트랜잭션들이 서로를 무한정 기다릴 때 발생해. 대표적인 예시:

1. 트랜잭션 A가 orders 테이블의 행을 락하고, products 테이블의 행을 업데이트하려고 해.
2. 트랜잭션 B가 products 테이블의 행을 락하고, orders 테이블의 행을 업데이트하려고 해.

결국 둘 다 진행을 못 해. 마치 두 대의 차가 좁은 골목에서 동시에 돌려고 하다가 막히는 것처럼.

예시:

-- 트랜잭션 A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;

-- 트랜잭션 B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- 이제 트랜잭션 A가 `products`의 같은 행을 업데이트하려 하고,
-- 트랜잭션 B는 `orders`의 행을 바꾸려 해.
-- 데드락!

어떻게 피할까?

1. 항상 같은 순서로 데이터를 업데이트해. 예를 들어, 먼저 orders, 그 다음 products.
2. 트랜잭션을 너무 오래 끌지 마.
3. LOCK을 쓸 땐 최소한의 락 레벨로만 써.

동적 SQL(EXECUTE)을 잘못 사용하는 경우

동적 SQL을 조심 없이 쓰면 진짜 골치 아픈 일이 생길 수 있어. 제일 흔한 실수는 SQL 인젝션이야. 예를 들어:

EXECUTE 'SELECT * FROM orders WHERE id = ' || user_input;

만약 user_input에 1; DROP TABLE orders; 같은 게 들어가면 orders 테이블은 사라질 수도 있어.

어떻게 피할까? 준비된 쿼리를 써:

EXECUTE 'SELECT * FROM orders WHERE id = $1' USING user_input;

이렇게 하면 SQL 인젝션으로부터 앱을 지킬 수 있어.

에러 처리가 잘못되어 트랜잭션이 롤백되지 않는 경우

에러를 제대로 처리하지 않으면 트랜잭션이 이상한 상태로 남을 수 있어. 예를 들어:

BEGIN;

INSERT INTO orders (order_id, status) VALUES (1, 'Pending');

BEGIN;
-- 에러를 일으키는 작업
INSERT INTO non_existing_table VALUES (1);
-- 에러가 났지만 트랜잭션이 끝나지 않음

COMMIT; -- 에러: 현재 트랜잭션이 중단됨

에러 때문에 전체 코드가 멈춰버려.

어떻게 피할까? EXCEPTION 블록으로 제대로 롤백해:

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, 'Pending');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE '에러 발생, 트랜잭션 롤백됨.';
END;

실수 피하기: 팁과 추천

• 복잡한 프로시저를 쓸 땐 항상 먼저 의사코드로 단계와 에러 포인트를 정리해.
• SAVEPOINT로 트랜잭션을 부분적으로 롤백할 수 있어. 다 썼으면 꼭 해제해.
• 트랜잭션을 오래 끌지 마. 길어질수록 락 걸릴 확률이 높아져.
• 중첩 프로시저 호출 시 외부와 내부 트랜잭션 컨텍스트가 잘 맞는지 확인해.
• 항상 EXPLAIN ANALYZE로 프로시저 성능을 체크해.
• 에러는 테이블이나 텍스트 파일에 로깅해두면 디버깅이 쉬워져.

실수 예시와 수정 방법

예시 1: 중첩 프로시저 호출 시 에러

에러가 있는 코드:

BEGIN;

CALL process_order(5);

-- process_order 안에서 ROLLBACK 발생
-- 전체 트랜잭션이 무효화됨
COMMIT; -- 에러

수정된 코드:

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- 에러 시에만 롤백
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

예시 2: 데드락 문제

에러가 있는 코드:

-- 트랜잭션 A
BEGIN;
UPDATE orders SET status = 'Processing' WHERE id = 1;
-- `products`를 기다림

-- 트랜잭션 B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- `orders`를 기다림

수정:

-- 두 쿼리를 같은 순서로 실행:
-- 먼저 `products`, 그 다음 `orders`.
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'Processing' WHERE id = 1;
COMMIT;

이런 실수들은 트랜잭션 작업이 얼마나 신경 써야 하는지 보여줘. 근데 연습을 많이 하면 실제로 ROLLBACK 당할 확률이 점점 줄어들 거야(실생활에서도, 커리어에서도).