PostgreSQL 동시성 제어

PostgreSQL 동시 쓰기

핵심. "코드에서 읽고 확인한 후 쓰기"는 하지 마세요. Read Committed에서는 다른 트랜잭션의 변경 사항이 손실됩니다. 불변 조건의 종류에 따라 적절한 수단을 선택하세요:

불변 조건	수단
조건이 여전히 참일 때 행 변경	하나의 UPDATE ... WHERE <조건> + 영향받은 행 수 확인
읽고, 코드에서 결정하고, 같은 행 쓰기	하나의 트랜잭션에서 SELECT ... FOR NO KEY UPDATE 후 쓰기
중복 없음	고유 인덱스 + INSERT ... ON CONFLICT
범위가 겹치지 않음	EXCLUDE USING gist (btree_gist)
제약 조건으로 표현할 수 없는 행 집합 규칙	모든 참여 트랜잭션의 직렬화(Serializable) + 재시도
작업 큐	FOR UPDATE SKIP LOCKED
모든 핸들러에 대해 단일 처리기	pg_advisory_xact_lock

코드보다 데이터베이스 제약 조건이 더 안정적입니다. 어떤 격리 수준과 어떤 클라이언트에서도 불변 조건을 유지합니다. Go 트랜잭션 및 재시도 코드는 pgx-txmanager 스킬을 참조하세요. 단언 검사는 CHECKS.md를 참조하세요.

격리 수준

• Read Committed (기본): 각 명령은 최신 스냅샷을 봅니다. 점유된 행에 대한 UPDATE는 다른 트랜잭션을 기다린 후 행을 다시 읽고 WHERE를 다시 확인합니다.
• Repeatable Read 및 Serializable은 다시 읽지 않습니다. 충돌 시 40001 오류가 발생합니다(Serializable은 더 자주 발생). 격리 수준을 올렸다면 즉시 전체 트랜잭션 재시도를 구현하세요. 그렇지 않으면 사용자가 오류를 보게 됩니다. 40001은 COMMIT 시에도 발생할 수 있습니다.
• Repeatable Read 스냅샷은 BEGIN 이후 첫 번째 명령에서 생성되며, BEGIN 자체가 아닙니다. 중요한 점은 첫 번째 요청으로 읽기를 수행해야 한다는 것입니다.
• Serializable은 Serializable 트랜잭션 간의 결과만 보장합니다. 불변 조건을 보호하려면 해당 조건에 영향을 주는 모든 트랜잭션이 Serializable이어야 합니다.
• Serializable 내에서는 EXPLAIN을 확인하세요. Seq Scan은 테이블 전체에 술어 잠금을 걸어 40001 비율을 높입니다. 인덱스로 읽으면 읽은 행과 인덱스 페이지만 잠급니다. 필요한 인덱스를 생성하세요. 작은 테이블에서는 Seq Scan이 정상입니다.

조건이 참일 때 변경: UPDATE의 조건

계산된 값을 전달하지 말고 동작을 데이터베이스에 전달하세요: SET sent = sent + 1, SET left = left - $1 WHERE left >= $1, WHERE id = $1 AND status = 'free'. 영향받은 행이 0개이면 (RowsAffected() == 0 또는 RETURNING의 pgx.ErrNoRows) 조건이 충족되지 않았거나 행이 없는 것입니다. 이는 성공이 아닌 도메인 오류입니다.

tag, err := q.Exec(ctx,
	`UPDATE seats SET status = 'booked', customer_id = $2
	 WHERE id = $1 AND status = 'free'`,
	seatID, customerID,
)
if err != nil {
	return fmt.Errorf("book seat %d: %w", seatID, err)
}
if tag.RowsAffected() == 0 { // 좌석이 이미 점유되었거나 없음
	return fmt.Errorf("book seat %d: %w", seatID, ErrConflict)
}

Repeatable Read 및 Serializable에서는 동일한 충돌이 40001로 끝나며 재시도가 필요합니다.

같은 행 읽고 쓰기

SELECT ... FOR NO KEY UPDATE (모드는 "잠금 모드" 섹션 참조)와 쓰기는 하나의 트랜잭션(trManager.Do) 내에서 수행하세요. Auto-commit에서는 SELECT 직후 잠금이 해제됩니다. 아직 존재하지 않는 행은 잠금으로 보호되지 않습니다. 중복 삽입을 막는 유일한 방법은 고유 인덱스 또는 제약 조건입니다.

행 집합에 대한 결정 (Write Skew)

"담당자 없음", "예약이 범위 내에 들어감", "교대에 남아 있는 당직자" 등의 경우: 두 트랜잭션이 동일한 스냅샷을 읽고 각자 자신의 행을 쓰며 둘 다 커밋합니다. Read Committed에서는 두 개의 겹치는 예약이 발생할 수 있습니다. Repeatable Read도 도움이 되지 않습니다. 같은 행에 대한 쓰기만 감지하기 때문입니다.

• 기존 행에 대한 결정 — 결정 기준이 되는 행을 정확히 잠급니다(FOR NO KEY UPDATE / FOR UPDATE).
• 행 부재에 대한 결정 ("겹치는 예약 없음") — 잠글 것이 없습니다. 제약 조건이 필요합니다:

  • 값 중복 — 고유 인덱스;

  • 범위 겹침 — Exclusion 제약 조건. Read Committed에서는 두 번째 트랜잭션이 첫 번째를 기다린 후 23P01을 받습니다. 직렬화나 재시도가 필요 없습니다:

    CREATE EXTENSION IF NOT EXISTS btree_gist; -- trusted: 데이터베이스에 대한 CREATE 권한 필요
    ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
        EXCLUDE USING gist (resource_id WITH =, during WITH &&); -- during tstzrange
    

    23P01 (exclusion_violation)을 도메인 충돌 오류로 변환하세요.

• 제약 조건으로 표현할 수 없는 규칙 — 모든 참여 트랜잭션에 Serializable을 사용하고 40001 발생 시 재시도합니다.

행 잠금 모드

행을 잠가서 읽고 업데이트하지만, 외래 키에 사용될 수 있는 고유 인덱스(PK, 고유 키; 부분 인덱스 및 표현식 인덱스는 제외)가 있는 열을 변경하지 않는다면 FOR NO KEY UPDATE를 사용하세요. FOR UPDATE는 DELETE와 그러한 열의 변경을 위해 남겨두세요. 이유: 자식 행 삽입 시 외래 키 검사는 부모에 대해 FOR KEY SHARE를 사용합니다. 이는 FOR UPDATE와 충돌하지만 FOR NO KEY UPDATE와는 충돌하지 않습니다. 부모 행(클라이언트, 리소스, 주문)에 불필요한 FOR UPDATE를 사용하면 해당 행을 참조하는 자식 행의 삽입이 차단됩니다.

작업 큐

여러 워커가 하나의 명령문으로 작업을 가져옵니다:

UPDATE tasks
SET status = 'running', taken_at = now()
WHERE id = (
	SELECT id FROM tasks
	WHERE status = 'ready'
	ORDER BY id
	LIMIT 1
	FOR UPDATE SKIP LOCKED
)
RETURNING id

• SKIP LOCKED는 점유된 행을 건너뛰므로 워커가 서로 기다리지 않습니다. 하나의 트랜잭션에서 "SELECT ... FOR UPDATE SKIP LOCKED, 이후 UPDATE" 패턴도 올바릅니다. 잠금 없는 SELECT와 별도의 UPDATE는 사용하지 마세요. 두 워커가 동일한 작업을 가져갈 수 있습니다.
• 서브쿼리의 ORDER BY는 예측 가능한 순서를 위함입니다.
• 워커가 작업을 들고 있는 상태에서 충돌할 수 있습니다. 일반적인 방법: taken_at을 기준으로 임대 기간을 설정하고, 해당 기간보다 오래 running 상태인 작업은 다시 'ready' 상태로 간주합니다.
• SKIP LOCKED는 일관되지 않은 데이터 뷰를 제공합니다. 큐에만 사용하세요.

다른 트랜잭션의 잠금에 블로킹되지 않기

기본적으로 쿼리는 잠금을 무기한 기다리며 사용자는 애플리케이션이 멈춘 것처럼 느낍니다.

• SET LOCAL lock_timeout 세션 파라미터가 아닌 트랜잭션 내에서 사용하세요. 풀의 연결은 이후 다른 요청에 사용됩니다. 타임아웃은 각 잠금 대기에 적용되며 전체 트랜잭션에 적용되지 않습니다.
• UPDATE 및 DELETE에는 NOWAIT가 없습니다. 즉각적인 오류가 필요하면 같은 트랜잭션에서 먼저 해당 행에 대해 SELECT ... FOR UPDATE NOWAIT를 수행하세요.
• NOWAIT와 SKIP LOCKED는 행에만 적용됩니다. 테이블 잠금(예: ALTER TABLE)은 쿼리가 평소처럼 기다립니다. 따라서 lock_timeout이 함께 필요합니다.
• 이러한 경우의 오류는 55P03(lock_not_available)입니다. 전체 트랜잭션을 재시도하세요.

return s.trManager.Do(ctx, func(ctx context.Context) error {
	q := s.getter.DefaultTrOrDB(ctx, s.pool)
	// 사용자 입력 없는 상수; 작업에 맞게 값을 조정
	if _, err := q.Exec(ctx, `SET LOCAL lock_timeout = '1s'`); err != nil {
		return err
	}
	return s.repo.MoveBooking(ctx, from, to)
})

대기 큐는 공평하지 않습니다. 새로운 FOR SHARE / FOR KEY SHARE는 핫 행에서 대기 중인 UPDATE를 추월할 수 있으며, 이러한 잠금의 흐름이 UPDATE를 무기한 유지할 수 있습니다. 대기 순서는 보장되지 않습니다. 핫 행(공통 카운터, 인기 상품 재고)에는 lock_timeout을 설정하고 트랜잭션을 재시도하세요.

교착 상태 분석. 행 잠금은 pg_locks에 표시되지 않습니다. 대기 중인 프로세스는 보유자의 transactionid를 기다리는 것으로 표시됩니다. pg_blocking_pids(pid)(자주 호출하지 마세요)가 블로킹 프로세스를 보여줍니다. 테이블의 행 잠금은 contrib의 pgrowlocks 확장(슈퍼유저가 설치)으로 확인할 수 있습니다.

Advisory 락

"단일 처리기 전용"(단일 아웃박스 워커, 마이그레이터) — 트랜잭션 내에서 pg_advisory_xact_lock을 사용하세요. 잠금은 커밋 또는 롤백 시 자동으로 해제됩니다.

const advisoryOutboxRelay int64 = 1 // 고정된 서브시스템 키

err := s.trManager.Do(ctx, func(ctx context.Context) error {
	q := s.getter.DefaultTrOrDB(ctx, s.pool)
	if _, err := q.Exec(ctx, `SELECT pg_advisory_xact_lock($1)`, advisoryOutboxRelay); err != nil {
		return err
	}
	return s.relayBatch(ctx) // 한 배치; 잠금은 트랜잭션과 함께 해제됨
})

• 세션 수준 pg_advisory_lock은 전용 연결에서만 사용하세요 (pool.Acquire). 잡은 횟수만큼 같은 연결에서 해제해야 합니다. 세션 잠금은 COMMIT이나 롤백으로 해제되지 않으며, 같은 세션이 다시 잡을 수 있습니다. 풀에 연결이 반환되면 잠금이 유지된 상태로 반환되며, unlock은 다른 연결에서 실행되어 잠금이 "새게" 됩니다.
• 무한 워커를 위해 잠금 하나 때문에 트랜잭션을 열어두지 마세요. 각 작업 배치마다 xact 잠금을 사용하세요. 장기 실행 리더에게는 전용 연결에 세션 잠금을 사용하세요.
• 키는 서브시스템에 대한 고정된 숫자 상수 또는 (int, int) 쌍입니다. hashtext는 사용하지 마세요. 문서화되지 않은 함수이며 32비트 해시로 인해 충돌이 발생합니다.

쿼리의 함수

테이블을 읽고 쿼리에서 테이블 읽기 옆에 호출되는 plpgsql 또는 SQL 함수는 STABLE로 표시하세요. VOLATILE은 함수 내부의 각 쿼리에 대해 새 스냅샷을 생성하므로, Read Committed에서는 테이블 데이터와 함수 데이터가 서로 다른 시점의 데이터가 됩니다. IMMUTABLE은 테이블을 읽지 않는 함수에만 사용하세요. VOLATILE은 부작용이 있는 함수에 사용하세요.

오류 코드

코드	내용	조치
40001 serialization_failure	repeatable read / serializable 충돌	전체 트랜잭션 재시도
40P01 deadlock_detected	상호 데드락	전체 트랜잭션 재시도; 잠금을 항상 같은 순서로 획득
55P03 lock_not_available	NOWAIT, lock_timeout	전체 트랜잭션 재시도 또는 "사용 중" 응답
23505 unique_violation	중복	도메인 충돌; 예상된 중복은 ON CONFLICT 사용
23P01 exclusion_violation	exclusion 제약 조건 위반으로 인한 범위 겹침	도메인 충돌