Postgres에서 FOR UPDATE는 자식 행이 아닌 부모 행을 잠급니다

PostgreSQL의 행 잠금은 하나의 행을 보호합니다. 그것은 자신을 가리키는 행들의 집합을 보호하지는 않습니다. 기본 격리 수준인 READ COMMITTED 하에서 부모 행에 대한 SELECT ... FOR UPDATE는 어떤 자식 행이 존재하는지, 다른 세션이 어떤 자식 행을 삽입하려 하는지, 또는 어떤 자식 행의 상태가 변경되고 있는지에 대해 아무것도 보장하지 않습니다. 이 게시물에서는 행 잠금을 관계에 대한 잠금으로 오해하여 발생하는 세 가지 동시성 버그와, 이를 해결하는 쿼리 형태, 트랜잭션 경계, 그리고 타임아웃에 대해 살펴봅니다.

행 잠금은 술어 잠금이 아닙니다

SELECT ... FOR UPDATE는 해당 구문이 반환한 행을 표시합니다. 해당 행을 업데이트, 삭제 또는 잠그려는 다른 트랜잭션은 사용자가 커밋하거나 롤백할 때까지 기다립니다. 이것이 보장의 전부입니다. 이 구문이 실행될 때 존재하지 않았던 행이나, 잠근 행을 참조하는 다른 테이블의 행에 대해서는 아무것도 보장하지 않습니다.

이러한 오해는 우리가 그것에 대해 이야기하는 방식에서 비롯됩니다. "부모를 잠갔으니 아무도 건드릴 수 없다"는 말이 어느새 "부모에 속한 것은 아무도 건드릴 수 없다"로 바뀝니다. 문서 모델에서는 자식이 부모 레코드 안에 존재하므로 그 해석이 맞습니다. 관계형 스키마에서는 그것들이 각자의 잠금을 가진 별개의 행입니다.

PostgreSQL에는 집합에 대해 추론하는 모드가 있습니다. SERIALIZABLE 격리 수준에서는 술어 읽기를 추적하고, 읽기 및 쓰기 집합이 충돌하는 두 트랜잭션 중 하나를 중단시킵니다. 여기에는 아직 존재하지 않았던 행을 통한 충돌도 포함됩니다. 기본값은 READ COMMITTED이며, 이 수준에서는 모든 구문이 새로운 스냅샷을 생성하고 아무것도 술어를 추적하지 않습니다. 이 경우, 행 집합에 대한 불변성은 전체 집합을 다루는 하나의 구문으로 강제하거나, 모든 작성자가 공통으로 잠그는 단일 행 뒤에서 직렬화하여 강제해야 합니다.

함정 1: 두 상태별 업데이트 사이의 간격

상황 설정: 부모 행은 비활성화(소프트 삭제)될 수 있습니다. 부모 행이 비활성화되면 그 아래의 모든 자식 행은 rejected 상태로 이동하며, 호출자는 감사 라인을 위해 이전 상태별 개수를 원합니다. 직관적인 코드는 상태별로 하나의 구문을 작성합니다.

-- Two statements, one logical intent. The gap between them is the bug.
UPDATE child SET status = 'rejected'
 WHERE parent_id = $1 AND status = 'confirmed';   -- count A

UPDATE child SET status = 'rejected'
 WHERE parent_id = $1 AND status = 'candidate';   -- count B

두 문장 모두 부모에 범위가 지정되어 있고, 둘 다 그 자체로 원자적이며, 트랜잭션이 이를 감싸고 있으므로 이 코드는 안전해 보입니다. 그렇지 않습니다. 두 문장 사이에서 다른 세션이 자식 행을 candidate에서 confirmed로 커밋할 수 있습니다. 첫 번째 문장은 이미 실행되었고 해당 행을 확정된(confirmed) 것으로 보지 않았습니다. 두 번째 문장은 candidate를 기준으로 필터링하므로 더 이상 해당 행을 후보(candidate)로 보지 않습니다. 해당 행은 두 필터 중 어느 것과도 일치하지 않으며, 비활성화된 부모 아래에서 confirmed 상태로 남게 됩니다.

맨 위에 SELECT ... FROM parent WHERE id = $1 FOR UPDATE를 추가해도 이 간극을 메울 수 없습니다. 해당 잠금은 parent의 한 행에만 적용됩니다. 승격된 자식 행은 child에 있으며, 이를 쓴 주체는 부모 잠금이 필요 없었습니다. 왜냐하면 외래 키 검사는 상태 변경이 아닌 삽입 및 키 열 업데이트 시에만 발생하기 때문입니다. 해당 행이 A와 B 모두에서 누락되었기 때문에 두 카운트 또한 모두 틀립니다.

수정 사항은 대상 집합을 잠근 다음, 정확히 그 집합을 쓰는 단일 문장입니다.

WITH target AS MATERIALIZED (
  SELECT id, status AS old_status FROM child
   WHERE parent_id = $1 AND status IN ('confirmed','candidate') FOR UPDATE
), upd AS (
  UPDATE child c SET status = 'rejected'
    FROM target t WHERE c.id = t.id RETURNING t.id, t.old_status
) SELECT id, old_status FROM upd

target 안의 FOR UPDATE는 모든 일치하는 자식 행을 잠급니다. 다른 세션이 문장의 스냅샷 이후에 해당 행 중 하나에 대한 변경을 커밋했다면, PostgreSQL은 잠금을 기다린 다음 새로 커밋된 버전에 대해 WHERE 절을 다시 평가합니다. 그 메커니즘이 EvalPlanQual입니다. 방금 confirmed가 된 행은 여전히 status IN ('confirmed','candidate')를 만족하며 나머지 행들과 함께 강등됩니다. 방금 rejected로 이동한 행은 이미 목표 상태에 있으므로 올바르게 제외됩니다.

같은 이유로 개수가 올바르게 나옵니다. 즉, 개수는 서로 다른 시점에 샘플링된 두 개의 개수가 아니라, 문장이 실제로 잠그고 변경한 행을 설명합니다. MATERIALIZED는 CTE가 한 번 참조되므로 여기서 CTE가 실행되는 빈도를 변경하지 않습니다. 이것은 목표 집합을 고정하고 정확히 그 집합에 대해 동작하려는 의도를 나타냅니다.

함정 2: 잠금 없이 읽는 가드는 가드가 아니다

같은 기능에는 반대의 방어가 필요합니다. 부모가 폐기되면 그 아래에 새 자식을 삽입할 수 없으며 기존 자식도 확정할 수 없습니다. 이러한 검사를 수행하기에 자연스러운 위치는 쓰기 경로의 맨 위입니다.

// Broken: the read runs in its own implicit transaction, so any lock it
// takes is released before the write begins.
func AddChild(ctx context.Context, pool *pgxpool.Pool, parentID int64, label string) error {
	status, err := parentStatus(ctx, pool, parentID) // own connection, own snapshot
	if err != nil {
		return err
	}
	if status == statusRejected {
		return ErrParentRejected
	}
	return insertChild(ctx, pool, parentID, label) // new transaction, new snapshot
}

이는 컴파일되고, 리뷰를 통과하지만, 아무것도 강제하지 않습니다. 그 이유는 코드가 요청한 적 없는 락 때문입니다.

child에 대한 insert는 외래 키를 검증해야 하며, 이 검증은 참조된 부모 행에 FOR KEY SHARE 락을 겁니다. FOR KEY SHARE는 FOR UPDATE와 충돌하므로, cascade가 부모를 잡고 있는 동안 insert는 외래 키 검사 내부에서 블로킹되고 이를 기다립니다. 그 부분은 여러분이 원하던 동작입니다.

문제는 가드 읽기가 그 대기 시점을 기준으로 어디에 위치하는지에 있습니다. 그것은 더 일찍, 다른 커넥션에서, 자체의 단일 구문 트랜잭션 안에서 실행되었고, cascade가 아직 커밋되지 않았기 때문에 candidate를 보았습니다. 그러면 insert가 블로킹되고, cascade가 커밋되며, insert가 깨어나 성공합니다. 외래 키 락은 그러한 인터리빙이 발생할 가능성을 낮추지 않습니다. 오히려 cascade가 끝날 때까지 insert를 정확히 멈춰두기 때문에, 그것을 선호되는 인터리빙으로 만듭니다.

해결책은 쓰기와 동일한 트랜잭션 내에서, cascade와 충돌하는 락을 잡고 가드를 실행하는 것입니다.

SELECT status FROM parent WHERE id = $1 FOR SHARE

FOR SHARE는 FOR UPDATE와 충돌하므로, 가드는 이를 우회하여 읽는 대신 캐스케이드를 기다리고, 잠금이 허용되면 EvalPlanQual은 새로 커밋된 버전을 반환합니다. 가드는 rejected를 확인하고 거부합니다. FOR SHARE는 일반적인 UPDATE parent SET status = ...가 암시적으로 사용하는 모드인 FOR NO KEY UPDATE와도 충돌하므로, 잠금 절을 작성하지 않은 캐스케이드에 대해서도 기다립니다. 공유 잠금은 호환되므로, 두 개의 가드가 동시에 있어도 서로를 차단하지 않습니다.

// Fixed: guard and write share one transaction, and the guard holds a lock
// that conflicts with the cascade until that transaction commits.
func AddChildTx(ctx context.Context, tx pgx.Tx, parentID int64, label string) error {
	var status string
	err := tx.QueryRow(ctx,
		`SELECT status FROM parent WHERE id = $1 FOR SHARE`, parentID).Scan(&status)
	if err != nil {
		return err
	}
	if status == statusRejected {
		return ErrParentRejected
	}
	return insertChildTx(ctx, tx, parentID, label)
}

일반적인 교훈은 다음과 같습니다. 함수 시그니처가 가드의 작동 여부를 결정합니다. 트랜잭션 핸들을 인수로 받는 함수는 호출자의 트랜잭션을 상속하므로, 가드의 잠금은 해당 트랜잭션이 커밋될 때까지 유지됩니다. 커넥션 풀을 인수로 받는 함수는 자체 트랜잭션을 열고 반환하기 전에 잠금을 해제합니다. 두 방식 모두 타입 검사를 통과하고, diff에서도 동일하게 보이지만, 오직 하나만이 무언가를 강제합니다.

함정 3: 모든 새 잠금은 새로운 대기 지점을 추가합니다

잠금을 획득하는 가드는 이전에 잠금을 사용하지 않던 경로의 타이밍을 변경하며, 그 경쟁 상대는 방금 작성한 연쇄 작업만이 아닙니다. 부모 행을 오랫동안 점유하는 트랜잭션은 이제 해당 부모 하위의 모든 쓰기 작업을 지연시킵니다. 부모를 잠그고, 느리게 작동하며, 마지막에 커밋하는 야간 작업은 실행되는 내내 사용자의 확인 클릭을 지연시킬 것입니다. 행 잠금 대기에는 기본 타임아웃이 없습니다.

따라서 잠금과 그에 대한 제한을 함께 도입하십시오.

BEGIN;
SET LOCAL lock_timeout = '3s';
SELECT status FROM parent WHERE id = $1 FOR SHARE;

SET LOCAL은 설정을 현재 트랜잭션으로 범위를 지정하므로, 풀링된 연결의 다음 사용자에게 유출될 수 없습니다. 대기가 한도를 초과하면 PostgreSQL은 SQLSTATE 55P03 (lock_not_available)을 발생시키며, 이는 호출자가 조치를 취할 수 있는 무언가로 변환되어야 합니다.

var pgErr *pgconn.PgError
if errors.As(err, &pgErr) && pgErr.Code == "55P03" { // lock_not_available
	return ErrBusyRetryLater
}

사용자가 기다리는 요청의 경우 55P03은 "이 레코드는 사용 중이니 잠시 후 다시 시도하세요"를 의미합니다. 많은 부모(parent)에 대한 백그라운드 작업의 경우, 이는 "배치를 중단하세요"가 아니라 "이 항목은 남겨두고 다음 실행 시 처리하세요"를 의미합니다. 이것을 잘못 처리하면 한 번의 클릭이 실패한 야간 실행으로 이어집니다. 즉, 누군가 자식(child)을 확인하면 배치가 3초 제한에 도달하고, 이를 치명적인 오류로 간주하여 수백 개의 부모(parent)를 처리하지 않은 채 중단됩니다. NOWAIT와 SKIP LOCKED는 동일한 아이디어의 더 정교한 버전입니다.

잠금 순서, 타임스탬프, 그리고 동작을 고정하는 테스트

위의 수정 사항이 실제 코드베이스에서 올바르게 유지될지 여부는 세 가지 세부 사항에 따라 결정됩니다.

잠금 순서가 첫 번째입니다. 교착 상태(deadlock)가 발생하려면 두 경로가 동일한 두 행을 반대 순서로 가져가야 합니다. 모든 경로가 자식보다 부모를 먼저 가져가면 순환이 형성될 수 없으며, 새로운 보호 로직은 부모를 먼저 읽으므로 그 순서에 합류합니다. 자식을 부모보다 먼저 잠그는 경로가 하나만 있어도 순환을 만들기에 충분하므로, 기억에 의존하지 말고 검색을 통해 확인해야 합니다. 즉, 쓰기 경로에서 자식 테이블에 대한 잠금 읽기 및 업데이트를 grep으로 찾고, 각각이 이미 무엇을 보유하고 있는지 확인해야 합니다.

타임스탬프가 두 번째이며, 이로 인해 발생하는 버그는 조용합니다. 이와 같은 대량 상태 전환은 사람이 해당 행을 판단한 시점을 기록하기 위한 reviewed_at과 같은 열을 건드리는 경우가 많습니다. 만약 대시보드가 해당 열을 기준으로 집계를 그룹화한다면, 연쇄 작업 중에 타임스탬프를 찍는 것은 기록을 다시 쓰는 것과 같습니다. 어제 판단된 행이 오늘 거부된 것으로 집계되고, 아무도 아무것도 수정하지 않았는데도 지난주 수치가 이동합니다. 대량 작업의 결과는 사람의 판단이 아니므로 해당 타임스탬프는 그대로 두어야 합니다.

테스트가 세 번째입니다. 상태 전환 분기들이 상호 배타적인지 여부는 현재 상태와 요청된 상태에 대한 순수 함수이므로, SQL 경로에서 분리하여 테이블 테스트로 고정해야 합니다. 이 작업에서는 이미 비활성화된 부모를 비활성화하려는 요청이 복원 분기로 빠지는 경우가 있었습니다. 분기 조건들이 상호 배타적이지 않은 별개의 조건문이었기 때문입니다. 코드는 컴파일되었고, 기존 테스트는 통과했으며, 현재 클라이언트가 더 새로운 엔드포인트를 사용하고 오직 더 오래된 경로만이 해당 분기에 도달했기 때문에 수동 확인 과정에서 이를 놓쳤습니다.

tests := []struct {
	name    string
	current string
	request string
	want    action
}{
	{"retire a live parent", statusActive, statusRejected, actionCascade},
	{"retire an already retired parent", statusRejected, statusRejected, actionNoop},
	{"revive a retired parent", statusRejected, statusActive, actionRevive},
	{"revive a live parent", statusActive, statusActive, actionNoop},
}

자주 묻는 질문

대신 SERIALIZABLE로 전환해야 할까요?

이는 근본 원인인 술어 충돌을 해결합니다. 아직 존재하지 않았던 행의 삽입을 포함한 충돌이 감지되고, 하나의 트랜잭션이 SQLSTATE 40001 오류를 내며 중단됩니다. 그 대가로 모든 트랜잭션이 재시도 가능해지므로, 모든 쓰기 경로에 재시도 루프가 필요하고 모든 부작용이 재실행을 견뎌야 합니다. 술어 잠금 추적은 메모리 비용도 발생시키며, 행 단위에서 페이지 단위로 잠금 범위가 확대될 수 있습니다. 이는 전혀 겹치지 않았던 트랜잭션 간의 충돌로 나타납니다. 하나의 핫 경로만 직렬화 가능(serializable)으로 만들고 나머지는 READ COMMITTED로 유지하는 것은 얻는 것이 거의 없습니다. 왜냐하면 이 검사는 둘 다 해당 격리 수준에서 실행되는 트랜잭션 사이에서만 적용되기 때문입니다.

advisory lock을 사용하는 것이 더 간단할까요?

부모 ID에서 파생된 키에 pg_advisory_xact_lock을 사용하면 해당 부모 하위의 모든 쓰기 작업을 직렬화하며, 잠글 행이 없는 경우에도 작동합니다. 두 가지 단점이 있습니다. 잠금은 제약 조건이라기보다는 관례이므로, 이를 잊어버린 새로운 코드 경로는 오류나 대기 없이 아무도 보지 못하는 경쟁 상태만 발생시킵니다. 또한, 키 공간이 전역적이므로 네임스페이스와 함께 두 개의 인자를 사용하는 형식을 사용하지 않는 한 관련 없는 기능들이 충돌할 수 있습니다.

FOR NO KEY UPDATE는 어떤가요?

이는 키가 아닌 열에 대한 일반 UPDATE가 취하는 더 약한 모드이며, 외래 키 검사가 키를 깨뜨릴 수 없는 업데이트를 기다리지 않도록 하기 위해 존재합니다. 이것이 핵심입니다. FOR NO KEY UPDATE는 FOR KEY SHARE와 충돌하지 않으므로 자식 삽입을 기다리게 하지 않습니다. 삽입을 막으려면 FOR UPDATE로 부모를 잠가야 합니다. 가드의 경우, FOR SHARE만으로도 충분한데, 이는 두 쓰기 모드와 모두 충돌하기 때문입니다.

데이터베이스가 트리거나 제약 조건으로 이를 강제해야 할까요?

child 테이블에 대해 부모의 상태를 읽는 BEFORE INSERT 트리거는 애플리케이션 가드와 동일한 결함을 가지며, 그 이유도 같습니다. 즉, 읽기 작업에 FOR SHARE가 필요하고, 차단했을 외래 키 검사는 구문에서 나중에 실행되기 때문입니다. 선언적인 버전도 가능합니다. ON UPDATE CASCADE가 있는 (id, status)에 대한 복합 참조를 사용하여 자식의 외래 키에 부모 상태를 포함시키고, 자식에 대해 비활성화된 값을 금지하는 CHECK 제약 조건을 추가하는 것입니다. 더 넓은 키와 실패 지점이 반전되는(이제는 부모를 비활성화하는 것이 실패함) 대가를 치르지만, 어떤 코드 경로도 이를 우회할 수 없습니다.

한 가지 경계는 명확히 짚고 넘어갈 가치가 있습니다. 여기서 제시된 모든 해결책은 단일 부모 행에 대한 쓰기 작업을 직렬화하므로, 서로 다른 부모에 대한 두 작업이 절대 경합하지 않아 비용이 저렴하게 유지됩니다. 확인된 자식의 총 수 제한과 같이 여러 부모에 걸친 불변성은 잠글 단일 행이 없으므로, 모든 쓰기 작업자가 잠그는 전용 행, 규칙에 대한 advisory lock, 또는 SERIALIZABLE 격리 수준이 필요합니다. 대부분의 경우 부모 행이 올바른 단위이며, 유일한 질문은 여러분의 구문과 트랜잭션 경계가 이를 올바르게 사용하고 있는지 여부입니다.