←

Serialization failures (40001) + retry

DB perf

Understand it step by step (한국어로 이해 → 영어로 말하기)

SERIALIZABLE(또는 REPEATABLE READ)에서 동시에 도는 두 트랜잭션이 직렬화 순서를 깰 것 같으면, Postgres는 둘 중 하나를 SQLSTATE 40001(serialization_failure)로 그냥 죽인다 — 데드락은 40P01. 이건 버그가 아니라 DB가 정합성을 지키는 정상 동작이다. 핵심은 앱이 40001을 잡아서 트랜잭션을 통째로 재시도해야 한다는 것, 그리고 재시도해도 안전하도록 트랜잭션이 멱등(idempotent)해야 한다는 것이다. 증상 → 원인(MVCC write-skew) → 눈으로 확인 → 재시도 루프 → 멱등성 → SERIALIZABLE vs 낙관적 버전 트레이드오프 순서로 올라간다.
  1. 1

    증상: 평소 멀쩡히 돌던 트랜잭션이 가끔 'ERROR: could not serialize access due to read/write dependencies among transactions'를 뱉으며 죽는다. SQLSTATE는 40001. 락 타임아웃도, 데드락(40P01)도 아니다 — 동시성이 올라간 피크에만, 무작위로, 같은 SQL이 터진다. 처음 보면 'DB가 고장났나?' 싶지만 이건 정상 동작이다.

    🔧 도구:SQLSTATE 40001 (serialization_failure)SQLSTATE 40P01 (deadlock_detected)Postgres SSI (Serializable Snapshot Isolation)

    -- isolation level set per-transaction or as a default
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

ERROR:  could not serialize access due to
        read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as
        a pivot, during write.
HINT:   The transaction might succeed if retried.
SQLSTATE: 40001
    🗣 영어로 말해

    “Under SERIALIZABLE, Postgres aborts one transaction with SQLSTATE 40001 — that's by design, not a bug.”

    checking microphone…

  2. 2

    원인 — write-skew. MVCC에서 각 트랜잭션은 시작 시점의 스냅샷을 본다. 두 트랜잭션이 '같은 조건을 SELECT로 읽고(겹치는 행 0개라 충돌 안 나 보임), 각자 다른 행을 INSERT/UPDATE'하면, 둘 다 따로 보면 규칙을 지키지만 합치면 불변식이 깨진다. 예: '한 매물에 ACCEPTED 오퍼는 최대 1건'. 두 요청이 동시에 '현재 ACCEPTED 0건'을 읽고 각자 다른 오퍼를 ACCEPTED로 만들면 → 2건. READ COMMITTED는 이걸 못 막는다. SERIALIZABLE은 이 위험한 읽기·쓰기 의존성을 추적하다가, 직렬 순서를 못 만들면 한쪽을 40001로 죽인다.

    ⚖️ Trade-off: write-skew는 두 트랜잭션이 같은 행을 동시에 UPDATE하는 게 아니라서 일반 행 락으로는 안 잡힌다. 읽은 '조건'에 대한 충돌이라 SERIALIZABLE의 술어(predicate) 추적이 필요하다.

    -- invariant: at most ONE accepted offer per home
-- T1 and T2 run concurrently, same home_id = 7
BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT count(*) FROM offers
   WHERE home_id = 7 AND status = 'ACCEPTED';   -- both read 0
  UPDATE offers SET status = 'ACCEPTED'
   WHERE id = :my_offer;                        -- T1 -> offer A, T2 -> offer B
COMMIT;
-- READ COMMITTED: both commit -> TWO accepted (invariant broken)
-- SERIALIZABLE: one commits, the other gets 40001
    🗣 영어로 말해

    “The classic case is write-skew: two transactions each read a condition that's still true and each write a row that, together, break an invariant.”

    checking microphone…

  3. 3

    눈으로 확인: 추측하지 말고 센다. 정확한 40001 횟수는 앱 로그에서 SQLSTATE로 카운트하고, pg_stat_database의 xact_rollback은 보조 지표로 본다 — xact_rollback은 명시적 ROLLBACK과 모든 에러를 다 합친 수라 40001만 따로 떼주지는 않으니, 추세(피크에 롤백 급증)를 보는 용도다. 40001이 피크에 몰리면 직렬화 충돌, 40P01이면 데드락(락 획득 순서 문제). 또 하나 — SERIALIZABLE의 충돌 감지는 SELECT가 읽은 범위를 SIReadLock(predicate lock)으로 추적한다. pg_locks에서 mode='SIReadLock'이 보이면 SSI가 일하고 있다는 신호다. 충돌률이 높으면 '재시도로 버틸지' vs '설계를 바꿀지' 판단의 근거가 된다.

    🔧 도구:pg_stat_database.xact_rollback (coarse, all rollbacks)pg_locks (mode = 'SIReadLock')app-side SQLSTATE counter / metric (precise 40001 rate)

    -- coarse signal: total rollbacks (includes ROLLBACK + all errors, not just 40001)
SELECT datname, xact_commit, xact_rollback
FROM pg_stat_database WHERE datname = current_database();

-- SSI's predicate locks (proof SERIALIZABLE is tracking reads)
SELECT mode, count(*) FROM pg_locks
WHERE mode = 'SIReadLock' GROUP BY mode;

-- the precise 40001 rate comes from the app log: 40001 = serialization, 40P01 = deadlock
    🗣 영어로 말해

    “I don't guess the rate — I count 40001s in the app log by SQLSTATE, and use xact_rollback and the SIReadLocks in pg_locks as supporting signals.”

    checking microphone…

  4. 4

    핵심 처방 — 트랜잭션을 통째로 재시도. 40001은 '지금 다시 하면 될 수도 있다'는 신호다(HINT가 그렇게 말한다). 잡아서 SQL 한 줄만 다시 치면 안 된다 — 트랜잭션 전체(BEGIN…COMMIT)를 새 스냅샷으로 처음부터 다시 돌려야 한다. 지수 백오프 + jitter로 3~5회 재시도하고, 그래도 안 되면 호출자에게 에러를 던진다. SQLSTATE로 분기하는 게 중요하다: 40001/40P01만 재시도, 23505(unique 위반) 같은 진짜 에러는 즉시 실패시켜야 무한 재시도를 피한다.

    ✅ Fix: 재시도 가능 코드(40001/40P01)와 진짜 실패(23505 등)를 SQLSTATE로 갈라야 한다. 백오프에 jitter를 넣어 여러 워커가 동시에 재충돌하는 thundering herd를 막는다. isolation level은 트랜잭션의 첫 문장 전에 set해야 한다(첫 SELECT/UPDATE 이후엔 못 바꾼다).

    🔧 도구:SQLState 분기 (40001/40P01)exponential backoff + jitterSpring @Retryable / 직접 재시도 래퍼

    // retry only on serialization (40001) / deadlock (40P01)
static final Set<String> RETRYABLE = Set.of("40001", "40P01");

<T> T runSerializable(SqlWork<T> work) throws SQLException, InterruptedException {
    int maxAttempts = 5;
    for (int attempt = 1; ; attempt++) {
        try (Connection c = ds.getConnection()) {
            c.setAutoCommit(false);
            // set isolation BEFORE any statement runs on this txn
            c.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            try {
                T result = work.run(c);   // ALL reads + writes happen here
                c.commit();
                return result;
            } catch (SQLException e) {
                c.rollback();
                if (RETRYABLE.contains(e.getSQLState()) && attempt < maxAttempts) {
                    long backoff = (1L << (attempt - 1)) * 10;   // 10,20,40,80ms
                    long jitter  = ThreadLocalRandom.current().nextLong(backoff);
                    Thread.sleep(backoff + jitter);
                    continue;                                    // RETRY whole txn
                }
                throw e;   // real error (e.g. 23505) or out of attempts
            }
        }
    }
}
    🗣 영어로 말해

    “The fix is to catch 40001 and retry the WHOLE transaction with exponential backoff — not re-run one statement, the entire BEGIN-to-COMMIT block on a fresh snapshot.”

    checking microphone…

  5. 5

    전제 조건 — 멱등성. 트랜잭션을 통째로 재시도하므로, 그 트랜잭션은 여러 번 실행돼도 한 번 실행한 것과 결과가 같아야 한다. DB 안의 쓰기는 롤백되니 안전하지만, 트랜잭션 안에서 외부 부수효과(이메일 발송, 결제 호출, 카프카 publish)를 하면 재시도할 때마다 그게 또 일어난다. 규칙: 부수효과는 트랜잭션 밖, COMMIT 성공 이후로 뺀다. 진짜로 정확히 한 번 보내야 하면 outbox 패턴(같은 트랜잭션 안 outbox 테이블에 INSERT → 커밋 후 별도 워커가 발송)을 쓴다. 그래야 재시도가 공짜가 된다.

    ⚖️ Trade-off: 트랜잭션 안에서 외부 호출을 하면 재시도가 그 부수효과를 중복 실행할 뿐 아니라, 락/스냅샷을 오래 쥐어 충돌률 자체를 올린다. 짧고 순수한(DB 쓰기만 하는) 트랜잭션이 재시도와 궁합이 가장 좋다.

    ✅ Fix: 트랜잭션은 DB 상태 변경만, 외부로 나가는 모든 것은 커밋 이후 또는 outbox로. 이렇게 분리하면 N번 재시도해도 최종 상태와 외부 효과가 1번과 같다.

    // WRONG: side effect inside the retried transaction
runSerializable(c -> {
    acceptOffer(c, offerId);
    emailClient.send(buyer, "accepted");  // re-sent on EVERY retry!
    return null;
});

// RIGHT: side effect after a successful commit (or via outbox)
runSerializable(c -> {
    acceptOffer(c, offerId);
    insertOutbox(c, "offer.accepted", offerId);  // committed atomically
    return null;
});
// a separate worker reads outbox after commit and sends exactly once
    🗣 영어로 말해

    “Retry only works if the transaction is idempotent — so any side effect like sending an email goes outside, after commit, or through an outbox row.”

    checking microphone…

  6. 6

    트레이드오프 — SERIALIZABLE vs 낙관적 버전(version 컬럼). SERIALIZABLE은 DB가 모든 write-skew를 자동으로 잡아준다(앱이 불변식을 일일이 SQL로 안 써도 됨). 대신 SSI의 predicate lock 추적 오버헤드가 있고, 충돌이 잦으면 40001이 많아져 재시도 비용·지연이 커진다. 낙관적 버전은 가볍고(READ COMMITTED로 충분) 충돌 지점이 명확하지만, 보호할 불변식마다 version 컬럼과 'WHERE version = ?' 체크를 사람이 정확히 넣어야 하고, 한 행 단위 충돌만 잡는다 — 여러 행에 걸친 write-skew는 못 막는다. 둘 다 결국 '충돌 시 재시도'라는 같은 앱 코드를 요구한다. 충돌이 드물고 불변식이 단순하면 낙관적 버전, 불변식이 여러 행에 걸치고 정확성이 최우선이면 SERIALIZABLE을 쓴다.

    ⚖️ Trade-off: SERIALIZABLE costs predicate-lock overhead and a higher 40001 rate under contention; optimistic versioning is lighter but only protects per-row invariants and must be hand-written for each rule — both still need the same retry loop. SERIALIZABLE은 정확성을 사면서 충돌률·오버헤드를 지불하고, 낙관적 버전은 가벼움을 사면서 다중 행 불변식 보호와 자동성을 포기한다. 어느 쪽이든 재시도 루프는 똑같이 있어야 한다 — 충돌은 못 막는 게 아니라 정상이라 '재시도로 처리'하는 것이다.

    🔧 도구:SERIALIZABLE (SSI 자동 충돌 감지)optimistic version 컬럼 (WHERE version = ?)UNIQUE/EXCLUDE 제약 (단일 행 불변식 보강)

    -- optimistic alternative: explicit version check, READ COMMITTED is fine
UPDATE offers
   SET status = 'ACCEPTED', version = version + 1
 WHERE id = :id AND version = :seen_version;
-- 0 rows updated  ==  someone else won  ->  reload + retry (same retry loop)

-- but a multi-row invariant (one ACCEPTED per home) still needs either
-- SERIALIZABLE, or a unique constraint, or a guard row to lock.
    🗣 영어로 말해

    “SERIALIZABLE lets the database catch every write-skew for me; optimistic versioning is cheaper but I have to encode each invariant myself and it only guards single rows.”

    checking microphone…

6단계 영어를 다 말하면 → 이 메커니즘 전체를 영어로 설명할 수 있게 된다.