14.4 트랜잭션 함정

PostgreSQL의 긴 트랜잭션과 잊혀진 트랜잭션은 운영 사고의 가장 큰 원인 중 하나. dead tuple 정리·vacuum·BLOAT가 모두 xmin horizon(3.2)에 잡혀 멈춥니다. 트랜잭션 운영의 흔한 함정과 방어 패턴을 정리합니다.

1. 긴 트랜잭션

-- 안티패턴
BEGIN;
SELECT ... ;     -- 5 ms
-- 사용자 입력 대기 5분
INSERT ... ;
COMMIT;

5분 동안 이 backend의 xmin이 잡혀 클러스터 전체 vacuum이 못 정리합니다.

대안:

트랜잭션은 짧고 좁게
사용자 입력 대기 같은 외부 작업을 트랜잭션 안에 두지 말 것
idle_in_transaction_session_timeout = '5min' 설정

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

이 timeout이 trigger되면 PostgreSQL이 자동으로 트랜잭션 끊습니다.

2. `idle in transaction` 누적

pg_stat_activity.state = 'idle in transaction'이 분 단위로 떠 있습니다. 거의 항상 application 버그입니다.

-- 진단
SELECT pid, usename, datname, state, now() - xact_start AS age, query
  FROM pg_stat_activity
 WHERE state LIKE 'idle in transaction%'
   AND xact_start < now() - interval '1 minute'
 ORDER BY xact_start;

원인 후보:

BEGIN 후 application에서 외부 API 호출
ORM이 connection을 트랜잭션 안에 보관
디버거가 일시 정지
exception 처리에 ROLLBACK 누락

대응: idle_in_transaction_session_timeout + application 로직 점검합니다.

3. SAVEPOINT 폭주

-- 안티패턴
BEGIN;
SAVEPOINT s1;
INSERT ... ;
SAVEPOINT s2;
INSERT ... ;
...
SAVEPOINT s100;

각 SAVEPOINT = subtransaction. 64개 초과 시 SubtransSLRU lock 경합 폭증 — 클러스터 전체 느려집니다.

ORM 일부가 모든 작업을 SAVEPOINT로 감싸는 경우가 있습니다. 코드 점검합니다.

대응:

트랜잭션을 더 작게 쪼개기
명시적 SAVEPOINT 남발 금지
결국 SAVEPOINT 필요하다면 전체 SQL을 한 번에 묶어 작성

4. 잊혀진 prepared transaction

PREPARE TRANSACTION 'order-42';
-- COMMIT PREPARED 또는 ROLLBACK PREPARED 안 함
-- 세션이 끊겨도 prepared transaction은 살아 있음

xmin·락·WAL이 모두 잡힘. 잊혀진 prepared transaction은 vacuum 영원히 차단합니다.

-- 진단
SELECT gid, prepared, owner, database FROM pg_prepared_xacts;

-- 정리
ROLLBACK PREPARED 'order-42';

운영 권장:

max_prepared_transactions = 0   -- 사용 안 함이 기본

XA 같은 분산 트랜잭션 매니저 없으면 굳이 사용 안 합니다.

5. autocommit OFF (어떤 클라이언트)

-- psql에서
\set AUTOCOMMIT off

SELECT 1;     -- 자동으로 BEGIN
-- 그 후 COMMIT 안 하면 트랜잭션 살아 있음

ORM에서도 autocommit off가 기본인 경우 있음 (예: Java/JDBC Connection.setAutoCommit(false)).

대응: autocommit ON이 안전 기본. autocommit OFF 쓸 경우 명시적 COMMIT 호출 점검합니다.

6. statement_timeout 미설정

-- 안티패턴 — 무한 실행
SELECT ... ;   -- 1시간 걸리는 쿼리

운영자가 알아채기 전에 다른 작업까지 느려집니다.

대응: 역할·세션 단위로 timeout 설정합니다.

ALTER ROLE app_user SET statement_timeout = '30s';
ALTER ROLE analytics_user SET statement_timeout = '10min';

-- 또는 세션
SET statement_timeout = '30s';

lock_timeout도 함께:

ALTER ROLE app_user SET lock_timeout = '5s';

7. DDL을 큰 트랜잭션 안에

-- 안티패턴
BEGIN;
ALTER TABLE big_table ADD COLUMN ...;   -- ACCESS EXCLUSIVE 잡음
-- 다음 작업 5분 걸림
-- 그 5분 동안 SELECT도 막힘
COMMIT;

ACCESS EXCLUSIVE를 트랜잭션 끝까지 잡고 있어 다른 모든 쿼리 차단합니다.

대응:

DDL은 별도 짧은 트랜잭션
가능하면 CONCURRENTLY 옵션 사용 (CREATE INDEX CONCURRENTLY)
큰 ALTER는 점검 시간

8. ROLLBACK 누락

# 안티패턴
try:
    cur.execute("BEGIN")
    cur.execute("UPDATE ...")
    cur.execute("INSERT ...")    # raises
    cur.execute("COMMIT")
except Exception:
    pass   # ROLLBACK 안 함 — connection이 'idle in transaction (aborted)' 상태

대응: try/finally 또는 context manager.

with conn.transaction():
    cur.execute("UPDATE ...")
    cur.execute("INSERT ...")
# exception 시 자동 ROLLBACK

9. 트랜잭션 안에서 외부 시스템 호출

-- 안티패턴
BEGIN;
UPDATE orders ... ;
-- application: HTTP API 호출 (10초)
INSERT INTO audit_log ... ;
COMMIT;

API 시간 동안 row lock·xmin 잡고 있습니다. 사고 상시.

대응:

외부 호출은 트랜잭션 밖
queue 패턴 — DB에 작업만 기록, 외부 호출은 worker가 별도

-- 트랜잭션 안에는 DB 작업만
BEGIN;
UPDATE orders ... ;
INSERT INTO pending_tasks(type, payload) VALUES ('send_email', ...);
COMMIT;
-- worker가 별도로 pending_tasks를 처리

10. lock 순서가 일관되지 않음

-- session A
BEGIN;
UPDATE accounts WHERE id = 1;
UPDATE accounts WHERE id = 2;

-- session B (동시)
BEGIN;
UPDATE accounts WHERE id = 2;
UPDATE accounts WHERE id = 1;
-- → deadlock

대응: 모든 트랜잭션이 같은 순서로 row를 잡기.

-- 항상 id 작은 순서로
UPDATE accounts WHERE id IN (1, 2)
ORDER BY id   -- 또는 명시 sort 후 UPDATE

11. 큰 UPDATE/DELETE 한 트랜잭션에

-- 안티패턴
BEGIN;
DELETE FROM events WHERE created_at < '2025-01-01';   -- 1억 row
COMMIT;

락 보유 시간 길어짐
xmin 오래 잡힘
WAL 폭증 (commit 시 fsync 폭증)
ROLLBACK 시 긴 undo

대안: 배치 처리합니다.

-- 한 번에 10000 row씩
DELETE FROM events
 WHERE id IN (
   SELECT id FROM events
    WHERE created_at < '2025-01-01'
    LIMIT 10000
 );
-- 반복

또는 파티션 단위 DETACH + DROP(7.5)가 더 효율적.

12. `SERIALIZABLE`을 디폴트로

ALTER SYSTEM SET default_transaction_isolation = 'serializable';

모든 트랜잭션이 SSI 비용 + serialization failure 처리 필요합니다. 대부분 application은 re-try 로직 없음 → 에러 노출합니다.

대응: Read Committed 디폴트. 필요한 트랜잭션만 SERIALIZABLE.

13. CHECK 없는 무한 재시도

# 안티패턴
while True:
    try:
        do_transaction()
        break
    except SerializationFailure:
        continue   # 무한 재시도

영원히 fail할 수 있는 트랜잭션은 재시도 한도 + 백오프.

for attempt in range(3):
    try:
        do_transaction()
        break
    except SerializationFailure:
        time.sleep(0.1 * (2 ** attempt))
else:
    raise

정리

긴 트랜잭션은 모든 사고의 시작
idle_in_transaction_session_timeout·statement_timeout·lock_timeout 설정 표준
SAVEPOINT 64+ 폭주 회피
잊혀진 prepared transaction 정기 점검
DDL은 짧은 트랜잭션, 큰 변경은 배치
외부 호출은 트랜잭션 밖
lock 순서 일관성으로 deadlock 회피
SERIALIZABLE은 명시적 트랜잭션만

다음 절(14.5)에서는 운영 명령의 흔한 실수 — 운영 실수를 봅니다.

14.3 인덱스 안티패턴 14.5 운영 실수