데이터베이스 잠금과 교착상태

두 길드가 동시에 골드를 교환하면 각 문장은 정상이어도 실행 순서가 꼬일 수 있다. 잠금은 같은 데이터를 바꾸는 작업을 줄 세워 무결성을 지키지만, 서로가 가진 잠금을 기다리는 순환이 생기면 교착상태가 된다. 핵심은 잠금을 피하는 것이 아니라 범위·순서·보유 시간을 설계하는 것이다.

한 줄 정의

잠금은 충돌하는 동시 작업의 접근 순서를 제어하고, 교착상태는 둘 이상의 트랜잭션이 서로의 잠금 해제를 순환 대기해 누구도 진행할 수 없는 상태다.

실습용 길드 금고와 작업 큐

DROP SCHEMA IF EXISTS lock_demo CASCADE;
CREATE SCHEMA lock_demo;
SET search_path TO lock_demo;

CREATE TABLE guild_wallets (
    guild_id bigint PRIMARY KEY,
    guild_name text NOT NULL UNIQUE,
    gold bigint NOT NULL CHECK (gold >= 0),
    note text NOT NULL DEFAULT ''
);

CREATE TABLE reward_jobs (
    job_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    guild_id bigint NOT NULL REFERENCES guild_wallets(guild_id),
    status text NOT NULL DEFAULT 'ready' CHECK (status IN ('ready', 'running', 'done')),
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO guild_wallets VALUES
(1, '레몬 기사단', 1000, ''),
(2, '라임 연구회', 1000, '');
INSERT INTO reward_jobs (guild_id) VALUES (1), (2), (1), (2);

테이블 잠금과 행 잠금은 층이 다르다

종류	범위와 예시
테이블 수준	SELECT는 ACCESS SHARE, INSERT·UPDATE·DELETE는 ROW EXCLUSIVE 같은 모드를 자동 획득한다. 이름의 ROW는 행 잠금이라는 뜻이 아니다. DDL이나 전체 작업과의 충돌을 테이블 단위로 조정한다.
행 수준	UPDATE·DELETE와 SELECT ... FOR UPDATE 계열이 특정 행을 잠근다. 일반 SELECT는 행 잠금 때문에 차단되지 않지만 같은 행의 writer와 locker는 충돌할 수 있다.

명시적 LOCK TABLE은 강력하고 동시성을 크게 줄일 수 있다. 특별한 전체 테이블 불변식이 아니라면 거래에서는 필요한 금고 행만 잠그는 편이 낫다. 모든 잠금은 보통 트랜잭션이 끝날 때 해제된다.

FOR UPDATE와 FOR NO KEY UPDATE

FOR UPDATE는 선택 행을 업데이트할 것처럼 강하게 잠가 다른 트랜잭션의 UPDATE·DELETE와 충돌하는 행 잠금 요청을 기다리게 한다. FOR NO KEY UPDATE는 키를 바꾸지 않는 갱신에 대응하는 더 약한 모드로, FOR KEY SHARE와는 충돌하지 않는다. PostgreSQL은 키로 간주되는 특정 열을 바꾸는 UPDATE에는 FOR UPDATE를, 그보다 약한 갱신에는 FOR NO KEY UPDATE를 자동 선택한다.

SET search_path TO lock_demo;
BEGIN;
SELECT guild_id, gold
FROM guild_wallets
WHERE guild_id = 1
FOR UPDATE;
-- 이 트랜잭션이 끝날 때까지 같은 행의 충돌 작업은 대기한다.
ROLLBACK;

NOWAIT: 기다리지 말고 즉시 실패

터미널 A에서 앞의 FOR UPDATE를 COMMIT하지 않은 채 유지하고, 터미널 B에서 다음을 실행한다. NOWAIT은 행이 잠겨 있으면 즉시 오류를 내므로 실시간 거래 화면에서 ‘이미 처리 중’으로 빠르게 응답할 수 있다.

SET search_path TO lock_demo;
BEGIN;
SELECT guild_id, gold
FROM guild_wallets
WHERE guild_id = 1
FOR UPDATE NOWAIT;
ROLLBACK;

ERROR: could not obtain lock on row in relation "guild_wallets"
ROLLBACK

SKIP LOCKED: 작업 큐에서 다른 일 가져가기

SKIP LOCKED는 잠긴 행을 결과에서 건너뛴다. 전체 데이터의 일관된 화면에는 부적합하지만 여러 워커가 서로 다른 대기 작업을 집어가는 큐에는 유용하다. 다음 문장을 여러 워커가 동시에 실행하면 각 워커는 잠기지 않은 job 하나를 차지한다.

SET search_path TO lock_demo;
BEGIN;
WITH picked AS (
    SELECT job_id
    FROM reward_jobs
    WHERE status = 'ready'
    ORDER BY job_id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE reward_jobs AS j
SET status = 'running'
FROM picked
WHERE j.job_id = picked.job_id
RETURNING j.job_id, j.guild_id, j.status;
COMMIT;

 job_id | guild_id | status
--------+----------+---------
      1 |        1 | running

UPDATE 1
COMMIT

교착상태를 두 세션에서 재현하기

A는 길드 1을 잠근 뒤 2를 기다리고, B는 길드 2를 잠근 뒤 1을 기다리게 만든다. 두 터미널에서 번호 순서대로 실행한다. PostgreSQL은 순환을 감지해 트랜잭션 하나를 중단하고 SQLSTATE 40P01을 반환한다.

SET search_path TO lock_demo;
BEGIN;
-- A1
UPDATE guild_wallets SET gold = gold - 10 WHERE guild_id = 1;
-- B1을 실행한 뒤 A2: 길드 2를 B가 잡고 있어 대기
UPDATE guild_wallets SET gold = gold + 10 WHERE guild_id = 2;
COMMIT;

SET search_path TO lock_demo;
BEGIN;
-- B1
UPDATE guild_wallets SET gold = gold - 20 WHERE guild_id = 2;
-- A2가 대기하는 것을 확인한 뒤 B2: 순환 대기 발생
UPDATE guild_wallets SET gold = gold + 20 WHERE guild_id = 1;
COMMIT;

ERROR: deadlock detected
DETAIL: Process ... waits for ShareLock on transaction ...; blocked by process ...
SQL state: 40P01

어느 세션이 희생될지는 의존하지 않는다. 오류가 난 트랜잭션은 ROLLBACK해야 하며, 살아남은 트랜잭션은 상대 잠금이 풀린 뒤 진행할 수 있다. 테스트 후 setup.sql을 다시 실행하면 초기 상태로 돌아간다.

해결: 모든 거래가 같은 순서로 잠그기

송신자·수신자 역할과 무관하게 작은 guild_id부터 잠그면 두 거래가 서로 반대 방향이어도 잠금 그래프에 순환이 생기지 않는다. 한쪽은 첫 행에서 기다리고, 먼저 들어온 트랜잭션이 두 행을 처리한 뒤 다음 거래가 진행한다.

SET search_path TO lock_demo;

CREATE OR REPLACE FUNCTION transfer_gold(
    p_from bigint, p_to bigint, p_amount bigint
) RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
    v_from_gold bigint;
BEGIN
    IF p_from = p_to OR p_amount <= 0 THEN
        RAISE EXCEPTION 'invalid transfer';
    END IF;

    -- 모든 호출이 guild_id 오름차순으로 두 행을 잠근다.
    PERFORM guild_id
    FROM guild_wallets
    WHERE guild_id IN (p_from, p_to)
    ORDER BY guild_id
    FOR UPDATE;

    IF NOT FOUND OR (SELECT COUNT(*) FROM guild_wallets
                     WHERE guild_id IN (p_from, p_to)) <> 2 THEN
        RAISE EXCEPTION 'guild not found';
    END IF;

    SELECT gold INTO v_from_gold
    FROM guild_wallets WHERE guild_id = p_from;
    IF v_from_gold < p_amount THEN
        RAISE EXCEPTION 'insufficient gold';
    END IF;

    UPDATE guild_wallets SET gold = gold - p_amount WHERE guild_id = p_from;
    UPDATE guild_wallets SET gold = gold + p_amount WHERE guild_id = p_to;
END;
$$;

BEGIN;
SELECT transfer_gold(1, 2, 100);
COMMIT;

SELECT guild_id, gold FROM guild_wallets ORDER BY guild_id;

 guild_id | gold
----------+------
        1 |  900
        2 | 1100

짧은 트랜잭션과 재시도

일관된 순서는 위험을 크게 낮추지만 모든 코드 경로와 외부 시스템까지 완전히 통제하기 어려우므로 40P01 deadlock_detected와 40001 serialization_failure는 전체 트랜잭션 단위로 재시도한다. 재시도 사이에는 지수 백오프와 무작위 지터를 넣어 요청이 동시에 다시 충돌하는 현상을 줄인다.

delay_k=\min(d_{max},d_0\cdot2^k)+U(0,j)

여기서 $k$ 는 실패 횟수, $d_0$ 는 기본 지연, $d_{max}$ 는 상한, $U(0,j)$ 는 0부터 j 사이의 무작위 지터다. 예를 들어 50ms, 상한 1초, 지터 0~50ms로 3~5회 제한하고 계속 실패하면 사용자에게 재시도 가능한 오류를 반환한다.

트랜잭션 안에서 HTTP 호출, 사용자 입력 대기, 긴 계산을 하지 않는다.
필요한 데이터와 검증값을 준비한 뒤 BEGIN하고 잠금·검증·변경·COMMIT만 수행한다.
재시도할 거래에는 고유 요청 ID를 두어 네트워크 오류 뒤 중복 커밋을 방지한다.

Advisory lock의 경계

Advisory lock은 테이블 행과 자동 연결되지 않는 애플리케이션 정의 잠금이다. ‘시즌 정산은 전 서버에서 하나만 실행’처럼 자연스러운 단일 행이 없는 작업을 직렬화할 때 유용하다. 참여하는 모든 코드가 같은 키 규약을 자발적으로 지켜야 하며, 일반 UPDATE가 이 잠금을 무시해도 PostgreSQL이 막아주지 않는다.

BEGIN;
-- 트랜잭션 종료 시 자동 해제되는 2개 정수 키 규약: (작업종류, 시즌번호)
SELECT pg_try_advisory_xact_lock(1001, 202608) AS acquired;
-- acquired=true인 실행자만 시즌 정산을 계속한다.
COMMIT;

 acquired
----------
 t

세션 수준 advisory lock은 명시 해제하거나 세션이 끝날 때까지 남으므로 연결 풀에서 누수되기 쉽다. 업무 트랜잭션 경계와 같다면 pg_advisory_xact_lock 계열을 우선한다. advisory lock도 교착상태의 원인이 될 수 있으므로 여러 키는 일관된 순서로 획득한다.

운영 점검표

잠글 자원 집합과 전역 정렬 기준을 문서화하고 모든 쓰기 경로에서 지킨다.
사용자 응답에는 NOWAIT, 작업 큐에는 SKIP LOCKED처럼 대기 정책의 의미를 구분한다.
pg_stat_activity와 pg_locks를 함께 조회해 기다리는 세션, blocker, 오래 열린 트랜잭션을 추적한다.
40P01·40001 오류율과 재시도 횟수를 측정하고 무한 재시도를 금지한다.
강한 테이블 잠금이나 advisory lock은 더 좁은 행 잠금·고유 제약·상태 전이로 대체 가능한지 검토한다.

참고 자료

PostgreSQL 18 공식 문서 — 명시적 잠금·행 잠금·교착상태·advisory lock (2026-08-11 확인)
PostgreSQL 18 공식 문서 — SELECT 잠금 절과 NOWAIT·SKIP LOCKED (2026-08-11 확인)
PostgreSQL 18 공식 문서 — Advisory lock 함수 (2026-08-11 확인)
PostgreSQL 18 공식 문서 — 오류 코드 40P01·40001 (2026-08-11 확인)

댓글을 불러오는 중…