SQL DML과 트랜잭션

게임 아이템 거래는 판매자 수량을 줄이고 구매자 수량을 늘리는 두 변경으로 보이지만, 데이터베이스에는 하나의 사건이어야 한다. 중간에 오류가 나거나 두 거래가 동시에 같은 아이템을 가져가면 복제 또는 소실이 발생할 수 있다. DML과 트랜잭션은 이 경계를 코드로 만든다.

한 줄 정의

DML은 행을 조회·추가·변경·삭제하고, 트랜잭션은 여러 DML을 모두 반영하거나 모두 취소되는 하나의 원자적 작업으로 묶는다.

PostgreSQL은 BEGIN이 없으면 각 문장을 각각 자동 커밋한다. 판매자 차감과 구매자 지급을 별도 문장으로 실행한다면 반드시 명시적 트랜잭션으로 감싸야 한다.

실습 데이터 준비

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

CREATE TABLE players (
    player_id bigint PRIMARY KEY,
    nickname text NOT NULL UNIQUE
);

CREATE TABLE item_master (
    item_id bigint PRIMARY KEY,
    item_name text NOT NULL UNIQUE
);

CREATE TABLE inventory (
    player_id bigint NOT NULL REFERENCES players(player_id),
    item_id bigint NOT NULL REFERENCES item_master(item_id),
    quantity integer NOT NULL CHECK (quantity >= 0),
    PRIMARY KEY (player_id, item_id)
);

CREATE TABLE trade_log (
    trade_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    seller_id bigint NOT NULL REFERENCES players(player_id),
    buyer_id bigint NOT NULL REFERENCES players(player_id),
    item_id bigint NOT NULL REFERENCES item_master(item_id),
    quantity integer NOT NULL CHECK (quantity > 0),
    traded_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (seller_id <> buyer_id)
);

INSERT INTO players VALUES (1, 'Nova'), (2, 'Mira'), (3, 'SoloFox');
INSERT INTO item_master VALUES (101, '철검'), (205, '회복약');
INSERT INTO inventory VALUES (1, 101, 5), (1, 205, 8), (2, 205, 2);

SELECT·INSERT·UPDATE·DELETE의 역할

문장	인벤토리에서 하는 일
SELECT	조건에 맞는 행과 열을 읽고 JOIN·집계한다. 기본 SELECT는 행을 수정용으로 잠그지 않는다.
INSERT	새 보유 행이나 거래 기록을 추가한다. RETURNING으로 실제 삽입된 값을 즉시 받는다.
UPDATE	기존 수량을 변경한다. WHERE를 생략하면 모든 행이 대상이므로 거래 조건을 문장 안에 둔다.
DELETE	더 이상 필요 없는 행을 제거한다. 삭제된 값을 RETURNING으로 감사 로그에 활용할 수 있다.

SET search_path TO trade_demo;

-- SELECT: 플레이어별 전체 아이템 수량
SELECT p.nickname, COALESCE(SUM(i.quantity), 0) AS total_quantity
FROM players AS p
LEFT JOIN inventory AS i USING (player_id)
GROUP BY p.player_id, p.nickname
ORDER BY p.player_id;

-- INSERT: SoloFox에게 철검 보유 행 추가
INSERT INTO inventory (player_id, item_id, quantity)
VALUES (3, 101, 1)
RETURNING player_id, item_id, quantity;

-- UPDATE: SoloFox의 철검 한 개 추가
UPDATE inventory
SET quantity = quantity + 1
WHERE player_id = 3 AND item_id = 101
RETURNING quantity;

-- DELETE: 수량이 0인 행만 정리
DELETE FROM inventory
WHERE quantity = 0
RETURNING player_id, item_id;

 nickname | total_quantity
----------+---------------
 Nova     |            13
 Mira     |             2
 SoloFox  |             0

 player_id | item_id | quantity
-----------+---------+----------
         3 |     101 |        1

 quantity
----------
        2

DELETE 0

BEGIN·COMMIT·ROLLBACK으로 작업 경계 만들기

BEGIN은 트랜잭션 블록을 시작한다. 이후 변경은 아직 다른 세션에 최종 확정되지 않는다.
COMMIT은 블록 안의 변경을 확정해 다른 트랜잭션에 보이고 내구성 있게 만든다.
ROLLBACK은 블록 안의 모든 변경을 취소한다. PostgreSQL에서는 한 문장이 오류가 나면 현재 트랜잭션이 중단 상태가 되므로 ROLLBACK 또는 SAVEPOINT로 복구해야 한다.

SET search_path TO trade_demo;

BEGIN;
UPDATE inventory
SET quantity = quantity - 2
WHERE player_id = 1 AND item_id = 101;

SELECT quantity FROM inventory
WHERE player_id = 1 AND item_id = 101;
-- 같은 세션에서는 3이 보인다.

ROLLBACK;

SELECT quantity FROM inventory
WHERE player_id = 1 AND item_id = 101;
-- 롤백 뒤 다시 5가 보인다.

 quantity
----------
        3

ROLLBACK

 quantity
----------
        5

거래의 핵심 불변식: 아이템 총량 보존

거래가 아이템을 생성하거나 소모하지 않는다면 거래 전후 전체 수량은 같아야 한다. 판매자 $s$ 에서 구매자 $b$ 로 수량 $q$ 를 옮길 때 다음 보존 법칙이 성립한다.

Q_{before}=s+b=(s-q)+(b+q)=Q_{after}

s \ge q > 0

첫 식은 총량 보존, 둘째 식은 판매자가 충분한 양을 가지고 있고 거래량이 양수라는 사전조건이다. CHECK 제약과 조건부 UPDATE, 트랜잭션이 이 규칙을 함께 보호한다.

조건부 UPDATE로 마이너스 수량 막기

먼저 SELECT로 수량을 읽고 나중에 UPDATE하면 그 사이 다른 거래가 수량을 바꿀 수 있다. 차감 조건을 UPDATE의 WHERE에 넣으면 확인과 변경이 한 문장 안에서 이루어진다. RETURNING이 1행이면 성공, 0행이면 아이템이 없거나 부족한 것이다.

SET search_path TO trade_demo;

UPDATE inventory
SET quantity = quantity - 3
WHERE player_id = 1
  AND item_id = 101
  AND quantity >= 3
RETURNING player_id, item_id, quantity;

 player_id | item_id | quantity
-----------+---------+----------
         1 |     101 |        2

UPDATE 1

위 코드는 차감만 실습한다. 다음 전체 거래 예제를 같은 초기 상태에서 실행하려면 setup.sql을 다시 실행해 Nova의 철검 수량을 5로 복원한다.

행 잠금과 원자적 거래 함수

SELECT ... FOR UPDATE는 선택한 판매자 인벤토리 행을 트랜잭션 종료까지 잠근다. 같은 행을 갱신하거나 다시 잠그려는 다른 거래는 기다린다. 교착상태 위험을 줄이려면 여러 행을 잠글 때 항상 player_id, item_id처럼 동일한 순서로 잠근다.

아래 PL/pgSQL 함수는 유효성 검사, 판매자 행 잠금, 조건부 차감, 구매자 UPSERT, 로그 기록을 한 호출에 모은다. 함수가 예외를 발생시키면 이를 호출한 문장의 효과가 취소되고, 명시적 트랜잭션에서는 ROLLBACK으로 블록 전체를 정리한다.

SET search_path TO trade_demo;

CREATE OR REPLACE FUNCTION trade_item(
    p_seller_id bigint,
    p_buyer_id bigint,
    p_item_id bigint,
    p_quantity integer
) RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
    v_available integer;
    v_changed integer;
    v_trade_id bigint;
BEGIN
    IF p_quantity <= 0 OR p_seller_id = p_buyer_id THEN
        RAISE EXCEPTION 'invalid trade arguments';
    END IF;

    SELECT quantity INTO v_available
    FROM inventory
    WHERE player_id = p_seller_id AND item_id = p_item_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'seller does not own item %', p_item_id;
    END IF;

    UPDATE inventory
    SET quantity = quantity - p_quantity
    WHERE player_id = p_seller_id
      AND item_id = p_item_id
      AND quantity >= p_quantity;

    GET DIAGNOSTICS v_changed = ROW_COUNT;
    IF v_changed <> 1 THEN
        RAISE EXCEPTION 'insufficient quantity: available %, requested %',
            v_available, p_quantity;
    END IF;

    INSERT INTO inventory (player_id, item_id, quantity)
    VALUES (p_buyer_id, p_item_id, p_quantity)
    ON CONFLICT (player_id, item_id) DO UPDATE
    SET quantity = inventory.quantity + EXCLUDED.quantity;

    INSERT INTO trade_log (seller_id, buyer_id, item_id, quantity)
    VALUES (p_seller_id, p_buyer_id, p_item_id, p_quantity)
    RETURNING trade_id INTO v_trade_id;

    RETURN v_trade_id;
END;
$$;

COMMIT 성공 경로와 보존 법칙 검증

SET search_path TO trade_demo;

BEGIN;
SELECT SUM(quantity) AS before_total FROM inventory WHERE item_id = 101;
SELECT trade_item(1, 2, 101, 2) AS trade_id;
SELECT SUM(quantity) AS after_total FROM inventory WHERE item_id = 101;
COMMIT;

SELECT p.nickname, i.quantity
FROM inventory AS i
JOIN players AS p USING (player_id)
WHERE i.item_id = 101
ORDER BY p.player_id;

 before_total
--------------
            5

 trade_id
----------
        1

 after_total
-------------
           5

COMMIT

 nickname | quantity
----------+---------
 Nova     |        3
 Mira     |        2

구매자에게 철검 행이 없었기 때문에 INSERT가 실행되었다. 이미 보유 중이면 ON CONFLICT DO UPDATE가 기존 수량에 더한다. 전후 합계가 5로 같아 보존 법칙도 만족한다.

오류를 만들고 ROLLBACK 확인하기

성공 거래 뒤 Nova에게 남은 철검은 3개다. 99개를 보내면 함수가 예외를 내고 트랜잭션은 중단 상태가 된다. psql에서는 오류 다음에 ROLLBACK을 실행한 뒤 수량과 로그 건수가 그대로인지 확인한다.

SET search_path TO trade_demo;

BEGIN;
SELECT trade_item(1, 2, 101, 99);
-- ERROR가 발생하면 현재 트랜잭션은 aborted 상태다.
ROLLBACK;

SELECT player_id, quantity
FROM inventory
WHERE item_id = 101
ORDER BY player_id;

SELECT COUNT(*) AS committed_trades FROM trade_log;

ERROR: insufficient quantity: available 3, requested 99
ROLLBACK

 player_id | quantity
-----------+---------
         1 |        3
         2 |        2

 committed_trades
------------------
                1

두 세션으로 행 잠금 관찰하기

터미널 A와 B에서 같은 데이터베이스에 접속한다. A가 Nova의 철검 행을 잠근 동안 B의 같은 행 잠금은 대기한다. A가 COMMIT하면 B는 최신 행을 얻고 진행한다. 일반 SELECT는 행 잠금 때문에 막히지 않지만 같은 행의 writer와 locker는 충돌한다.

SET search_path TO trade_demo;
BEGIN;
SELECT quantity FROM inventory
WHERE player_id = 1 AND item_id = 101
FOR UPDATE;
-- B가 대기하는 것을 확인한 뒤 실행
COMMIT;

SET search_path TO trade_demo;
BEGIN;
SELECT quantity FROM inventory
WHERE player_id = 1 AND item_id = 101
FOR UPDATE;
-- A가 COMMIT할 때까지 이 문장에서 대기
ROLLBACK;

실전 점검표

차감과 지급, 거래 로그가 같은 트랜잭션 안에 있는가?
수량 충분 조건을 UPDATE의 WHERE에 넣고 실제 변경 행 수가 1인지 확인하는가?
동시에 접근하는 같은 자원은 FOR UPDATE로 잠그며 여러 자원은 항상 같은 순서로 잠그는가?
오류 경로에서 ROLLBACK하고, 재시도는 거래 요청 ID 같은 멱등성 키로 중복 실행을 막는가?
거래 전후 아이템 총량과 거래 로그를 함께 검사하는 자동 테스트가 있는가?

참고 자료

PostgreSQL 18 공식 문서 — 트랜잭션 튜토리얼 (2026-08-11 확인)
PostgreSQL 18 공식 문서 — 명시적 잠금과 행 잠금 (2026-08-11 확인)
PostgreSQL 18 공식 문서 — 수정된 행의 RETURNING (2026-08-11 확인)
PostgreSQL 18 공식 문서 — UPDATE (2026-08-11 확인)

댓글을 불러오는 중…