CS2026.10.019 min read

UPDATE: 기존 행을 변경하고 동시성·무결성을 보장하는 SQL 문

UPDATE는 조건에 맞는 기존 행의 열 값을 변경하는 데이터 조작어(DML)다. PostgreSQL은 다중 버전 동시성 제어(MVCC)에 따라 기존 행을 제자리에서 덮어쓰지 않고 새 행 버전을 생성한다. `RETURNING`, 조건부 갱신, 트랜잭션을 활용하면 변경 결과 확인과 동시성 제어를 안전하게 처리할 수 있다.

By 송민성

1. 개념

UPDATE는 테이블에 저장된 기존 행(Row)의 값을 변경하는 데이터 조작어(DML, Data Manipulation Language)다.

기본 문법은 다음과 같다.

sql

UPDATE 테이블명
SET 열1 = 값1,
    열2 = 값2
WHERE 조건;

예를 들어 특정 사용자의 이름을 변경할 수 있다.

sql

UPDATE users
SET name = '김개발'
WHERE id = 1;

WHERE 절은 변경할 행을 결정한다. 생략하면 테이블의 모든 행이 갱신된다.

sql

UPDATE users
SET active = false;

기존 값을 이용해 새 값을 계산할 수도 있다.

sql

UPDATE products
SET price = price * 1.1
WHERE category = 'book';

PostgreSQL의 RETURNING 절은 갱신된 행을 즉시 반환한다.

sql

UPDATE users
SET name = '이개발'
WHERE id = 1
RETURNING id, name, updated_at;

2. 왜 사용하는가

UPDATE는 이미 저장된 사실의 상태가 달라졌을 때 사용한다.

회원 정보 수정
주문 상태 변경
재고 차감
로그인 실패 횟수 증가
게시물 공개 여부 변경
처리 완료 시각 기록

갱신 규칙을 SQL 문 하나로 표현하면 조회와 변경 사이의 경쟁 상태(Race Condition)를 줄일 수 있다.

다음 방식은 현재 재고를 애플리케이션에서 조회한 뒤 새 값을 계산한다.

sql

SELECT stock
FROM products
WHERE id = 1;

이후 애플리케이션이 계산한 값을 저장하면 다른 트랜잭션의 변경을 덮어쓸 수 있다.

sql

UPDATE products
SET stock = 9
WHERE id = 1;

연산을 UPDATE 내부에서 수행하면 데이터베이스가 행 잠금(Row Lock)을 획득한 상태로 값을 계산한다.

sql

UPDATE products
SET stock = stock - 1
WHERE id = 1
  AND stock >= 1
RETURNING stock;

반환된 행이 없으면 재고가 부족하거나 대상 상품이 존재하지 않는다는 뜻이다.

3. 동작 원리

PostgreSQL의 UPDATE는 개념적으로 다음 순서로 처리된다.

대상 테이블과 열을 확인한다.
WHERE 조건에 맞는 행을 찾는다.
대상 행에 행 수준 잠금(Row-level Lock)을 획득한다.
SET 표현식과 기본값을 계산한다.
갱신 전 트리거(BEFORE Trigger)를 실행한다.
자료형과 제약 조건을 검사한다.
기존 행을 만료된 버전으로 표시하고 새 행 버전을 기록한다.
필요한 인덱스 항목을 갱신한다.
갱신 후 트리거(AFTER Trigger)를 실행한다.
RETURNING이 있으면 갱신 결과를 반환한다.

다중 버전 동시성 제어

PostgreSQL은 다중 버전 동시성 제어(MVCC, Multi-Version Concurrency Control)를 사용한다. UPDATE가 실행되면 기존 튜플(Tuple)을 물리적으로 덮어쓰는 대신 변경된 값을 가진 새 튜플 버전을 만든다.

각 트랜잭션은 자신의 스냅샷(Snapshot)에 따라 볼 수 있는 행 버전을 선택한다. 따라서 읽기 작업과 쓰기 작업이 항상 서로를 차단하지 않는다.

더 이상 어떤 트랜잭션에서도 보이지 않는 이전 행 버전은 데드 튜플(Dead Tuple)이 된다. 자동 청소(Autovacuum)는 이를 정리하고 공간을 재사용할 수 있게 만든다.

HOT 갱신

갱신 대상 열이 인덱스에 포함되지 않고 같은 힙 페이지(Heap Page)에 새 튜플을 저장할 공간이 있으면 PostgreSQL은 힙 전용 튜플 갱신(HOT, Heap-Only Tuple Update)을 사용할 수 있다.

HOT 갱신은 새 인덱스 항목 생성을 피하므로 쓰기 비용과 인덱스 팽창(Index Bloat)을 줄인다. 인덱스 열을 변경하거나 페이지에 여유 공간이 없으면 사용할 수 없다.

원자성

UPDATE 문 하나는 원자적 연산(Atomic Operation)이다. 여러 행을 갱신하는 도중 한 행에서 제약 조건 오류가 발생하면 문장 전체가 실패한다.

sql

UPDATE accounts
SET balance = balance - 10000
WHERE group_id = 10;

대상 행 중 하나라도 CHECK (balance >= 0) 제약 조건을 위반하면 일부 행만 변경된 상태로 남지 않는다.

여러 SQL 문을 하나의 작업으로 묶으려면 트랜잭션(Transaction)을 사용한다.

sql

BEGIN;

UPDATE accounts
SET balance = balance - 10000
WHERE id = 1;

UPDATE accounts
SET balance = balance + 10000
WHERE id = 2;

COMMIT;

중간 작업이 실패하면 ROLLBACK으로 전체 변경을 취소한다.

4. 코드 예제

다음 예제는 PostgreSQL에서 그대로 실행할 수 있다.

sql

DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric(12, 2) NOT NULL CHECK (price >= 0),
    stock integer NOT NULL CHECK (stock >= 0),
    version integer NOT NULL DEFAULT 1,
    updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO products (name, price, stock)
VALUES
    ('키보드', 120000, 10),
    ('마우스', 60000, 20),
    ('모니터', 350000, 5);

단일 행 갱신

sql

UPDATE products
SET price = 110000,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1
RETURNING id, name, price, updated_at;

여러 행 일괄 갱신

sql

UPDATE products
SET price = round(price * 0.9, 2),
    updated_at = CURRENT_TIMESTAMP
WHERE price >= 100000
RETURNING id, name, price;

조건부 재고 차감

sql

UPDATE products
SET stock = stock - 2,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1
  AND stock >= 2
RETURNING id, stock;

이 문장은 재고 확인과 차감을 하나의 원자적 연산으로 수행한다.

CASE를 이용한 조건별 갱신

sql

UPDATE products
SET price = CASE
        WHEN price >= 300000 THEN round(price * 0.85, 2)
        WHEN price >= 100000 THEN round(price * 0.90, 2)
        ELSE round(price * 0.95, 2)
    END,
    updated_at = CURRENT_TIMESTAMP
RETURNING id, name, price;

UPDATE FROM

다른 테이블의 값을 이용해 갱신할 수 있다.

sql

DROP TABLE IF EXISTS price_changes;

CREATE TEMP TABLE price_changes (
    product_id bigint PRIMARY KEY,
    new_price numeric(12, 2) NOT NULL
);

INSERT INTO price_changes (product_id, new_price)
VALUES
    (1, 115000),
    (2, 55000);

UPDATE products AS p
SET price = c.new_price,
    updated_at = CURRENT_TIMESTAMP
FROM price_changes AS c
WHERE p.id = c.product_id
RETURNING p.id, p.name, p.price;

낙관적 잠금

낙관적 잠금(Optimistic Locking)은 조회 당시 버전이 유지된 경우에만 갱신한다.

sql

UPDATE products
SET price = 125000,
    version = version + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1
  AND version = 1
RETURNING id, price, version;

반환된 행이 없으면 다른 트랜잭션이 먼저 같은 행을 변경했거나 대상 행이 존재하지 않는다.

5. 시간 복잡도 또는 성능 특성

UPDATE의 시간 복잡도는 고정된 하나의 값으로 표현할 수 없다. 대상 행 탐색 방식과 갱신 행 수, 인덱스 수, 제약 조건, 트리거에 따라 달라진다.

대상 행 탐색

인덱스가 없는 조건은 일반적으로 테이블 전체를 검사한다.

sql

UPDATE products
SET price = price + 1000
WHERE name = '키보드';

행 수가 N이면 순차 스캔(Sequential Scan)의 탐색 비용은 O(N)이다.

기본 키나 B-트리(B-tree) 인덱스를 이용한 동등 조건 검색은 일반적으로 O(log N) 탐색 비용을 가진다.

sql

UPDATE products
SET price = price + 1000
WHERE id = 1;

다만 실제 성능은 디스크 입출력, 캐시 적중률, 통계 정보, 조건 선택도(Selectivity), 잠금 대기 시간에도 영향을 받는다.

갱신 행 수

조건에 맞는 행이 K개라면 최소한 K개의 새 행 버전을 생성해야 한다. 인덱스 갱신이 필요하면 각 행마다 관련 인덱스에도 쓰기가 발생한다.

인덱스가 많을수록 읽기 검색은 빨라질 수 있지만 INSERT와 UPDATE 비용은 증가한다. 특히 자주 변경되는 열에 여러 인덱스를 만들면 쓰기 증폭(Write Amplification)이 커진다.

대량 갱신

대량 UPDATE는 다음 비용을 유발한다.

많은 행 잠금과 새 튜플 생성
미리 쓰기 로그(WAL, Write-Ahead Log) 증가
데드 튜플과 테이블 팽창(Table Bloat)
인덱스 갱신 비용
복제 지연
긴 트랜잭션으로 인한 VACUUM 정리 지연

대량 작업은 업무 요구에 맞게 범위를 나누어 실행할 수 있다.

sql

UPDATE products
SET updated_at = CURRENT_TIMESTAMP
WHERE id >= 1
  AND id < 10001;

작은 배치가 항상 더 빠른 것은 아니다. 잠금 시간, WAL 발생량, 트랜잭션 오버헤드, 복제 상태를 측정해 배치 크기를 결정해야 한다.

실행 계획은 EXPLAIN으로 확인한다.

sql

EXPLAIN
UPDATE products
SET price = price * 1.05
WHERE id = 1;

실제 실행 통계가 필요하면 트랜잭션 안에서 EXPLAIN ANALYZE를 실행한 뒤 롤백한다.

sql

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE products
SET price = price * 1.05
WHERE id = 1;

ROLLBACK;

6. 실무 사용 사례

주문 상태 전이

현재 상태를 조건에 포함하면 허용된 상태 전이만 수행할 수 있다.

sql

UPDATE orders
SET status = 'paid',
    paid_at = CURRENT_TIMESTAMP
WHERE id = $1
  AND status = 'pending'
RETURNING id, status, paid_at;

반환 행이 없으면 주문이 존재하지 않거나 이미 다른 상태로 변경된 것이다.

작업 선점

여러 작업자가 큐를 처리할 때 FOR UPDATE SKIP LOCKED로 다른 작업자가 잠근 행을 건너뛸 수 있다.

sql

BEGIN;

WITH next_job AS (
    SELECT id
    FROM jobs
    WHERE status = 'pending'
    ORDER BY created_at, id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs AS j
SET status = 'processing',
    started_at = CURRENT_TIMESTAMP
FROM next_job
WHERE j.id = next_job.id
RETURNING j.id, j.payload;

COMMIT;

소프트 삭제

행을 물리적으로 삭제하지 않고 삭제 시각을 기록한다.

sql

UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE id = $1
  AND deleted_at IS NULL
RETURNING id, deleted_at;

조회 쿼리에서는 삭제되지 않은 행만 선택한다.

sql

SELECT id, email, name
FROM users
WHERE deleted_at IS NULL;

만료 데이터 상태 변경

sql

UPDATE coupons
SET status = 'expired'
WHERE status = 'active'
  AND expires_at <= CURRENT_TIMESTAMP
RETURNING id;

조건에 맞는 행만 갱신하므로 같은 작업을 반복 실행해도 이미 만료된 행은 다시 변경하지 않는다.

7. 주의할 점

WHERE 절 누락

WHERE를 생략하면 모든 행이 변경된다.

sql

UPDATE users
SET active = false;

실행 전 같은 조건으로 대상 행을 확인하면 범위 오류를 줄일 수 있다.

sql

SELECT id, active
FROM users
WHERE last_login_at < CURRENT_TIMESTAMP - INTERVAL '1 year';

그다음 동일한 조건으로 갱신한다.

sql

UPDATE users
SET active = false
WHERE last_login_at < CURRENT_TIMESTAMP - INTERVAL '1 year';

잃어버린 갱신

두 요청이 같은 값을 읽고 각각 새 값을 저장하면 나중 요청이 앞선 변경을 덮어쓸 수 있다. 이를 잃어버린 갱신(Lost Update)이라 한다.

카운터나 재고는 값을 읽어서 덮어쓰기보다 SQL 내부에서 계산한다.

sql

UPDATE counters
SET value = value + 1
WHERE id = 1;

사용자가 편집한 전체 문서를 저장하는 경우에는 버전 열을 조건에 포함하는 낙관적 잠금을 사용할 수 있다.

잠금 순서와 교착 상태

여러 트랜잭션이 같은 행들을 서로 다른 순서로 갱신하면 교착 상태(Deadlock)가 발생할 수 있다.

한 트랜잭션은 1번 계좌 다음 2번 계좌를 갱신하고, 다른 트랜잭션은 2번 다음 1번을 갱신하면 서로의 잠금을 기다릴 수 있다. 여러 행을 갱신할 때는 일관된 순서를 사용한다.

sql

SELECT id
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

교착 상태가 발생하면 PostgreSQL은 트랜잭션 하나를 중단한다. 애플리케이션은 해당 트랜잭션을 처음부터 재시도해야 한다.

UPDATE FROM의 중복 조인

FROM의 한 대상 행이 여러 소스 행과 연결되면 어떤 소스 행의 값이 사용되는지 의존해서는 안 된다.

sql

UPDATE products AS p
SET price = c.new_price
FROM price_changes AS c
WHERE p.id = c.product_id;

price_changes.product_id에 기본 키나 고유 제약 조건을 설정해 대상 행마다 소스 행이 하나만 존재하도록 보장한다.

NULL 처리

NULL은 일반 비교 연산자로 검사하지 않는다.

sql

UPDATE users
SET deleted_at = CURRENT_TIMESTAMP
WHERE deleted_at IS NULL;

두 값이 NULL인 경우까지 같은 값으로 비교하려면 IS NOT DISTINCT FROM을 사용할 수 있다.

sql

UPDATE settings
SET value = $2
WHERE value IS NOT DISTINCT FROM $1;

updated_at 자동 변경

DEFAULT CURRENT_TIMESTAMP는 INSERT에서 열이 생략될 때만 적용된다. UPDATE마다 자동으로 바뀌지 않는다.

sql

UPDATE products
SET price = 130000,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

모든 갱신에서 자동 변경해야 한다면 트리거를 사용할 수 있지만, 명시적 갱신으로 충분한 환경에서는 트리거가 동작을 숨길 수 있다.

RETURNING 결과 확인

SQL 실행 성공과 실제 행 갱신은 같은 의미가 아니다. 조건에 맞는 행이 없으면 오류 없이 0개 행이 갱신될 수 있다.

sql

UPDATE orders
SET status = 'cancelled'
WHERE id = $1
  AND status = 'pending'
RETURNING id;

애플리케이션은 반환 행 수를 확인해 성공, 대상 없음, 상태 충돌을 구분해야 한다.

8. 핵심 정리

UPDATE는 WHERE 조건에 맞는 기존 행의 값을 변경한다.
PostgreSQL은 기존 행을 덮어쓰지 않고 MVCC에 따라 새 행 버전을 생성한다.
SET value = value + 1 같은 조건부 갱신은 조회 후 저장 방식의 경쟁 상태를 줄인다.
RETURNING은 변경된 값과 실제 갱신 여부를 한 번의 SQL 실행으로 확인한다.
성능은 대상 탐색, 갱신 행 수, 인덱스, 트리거, WAL, 잠금 대기에 따라 달라진다.
대량 갱신은 데드 튜플, 테이블 팽창, 복제 지연을 고려해야 한다.
동시 갱신에는 원자적 UPDATE, 낙관적 잠금, 행 잠금, 일관된 잠금 순서를 상황에 맞게 사용한다.