트랜잭션은 실패를 예상하는 방법이다
데이터베이스 작업은 한 문장으로 끝나지 않을 때가 많다. 쇼핑몰에서 주문을 생성한다고 생각해 보자. 주문 행을 만들고, 주문 상세를 넣고, 재고를 줄이고, 쿠폰을 사용 처리하고, 결제 요청 결과를 기록해야 한다. 이 중 하나라도 실패하면 앞에서 끝난 작업만 남아서는 안 된다.
트랜잭션은 여러 SQL을 하나의 작업 단위로 묶는다. 모두 성공하면 COMMIT으로 확정하고, 중간에 문제가 생기면 ROLLBACK으로 되돌린다. 중요한 점은 COMMIT하기 전까지 결과를 확인해야 한다는 것이다. 한 번 COMMIT한 작업은 일반적인 ROLLBACK으로 되돌릴 수 없다.
BEGIN;
-- 애플리케이션은 RETURNING id 값을 받아 order_id 변수에 보관한다.
INSERT INTO orders (customer_id, status, total_amount, ordered_at)
VALUES (1, 'pending', 59000, now())
RETURNING id;
-- 설명을 위해 위 INSERT가 반환한 주문 id가 101이라고 가정한다.
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (101, 10, 2, 29500);
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE id = 10
AND stock_quantity >= 2
RETURNING id, stock_quantity;
-- 재고 차감 결과가 1행이면 COMMIT한다.
COMMIT;여기서 재고 차감 UPDATE가 아무 행도 반환하지 않았다면 재고가 부족하다는 뜻이다. 이때는 주문을 확정하면 안 된다. 위 예제의 마지막 줄처럼 COMMIT으로 끝내는 것이 아니라, COMMIT 대신 다음 문장을 실행해야 한다.
ROLLBACK;실무에서는 애플리케이션 코드가 RETURNING 결과 행 수를 확인해야 한다. 재고 차감 결과가 0행이면 결제 요청으로 넘어가지 않고 전체 트랜잭션을 취소한다. 반대로 결과가 1행이면 필요한 후속 작업을 마친 뒤 COMMIT한다. 트랜잭션은 단순히 SQL을 묶는 문법이 아니라, 실패했을 때 어디까지 되돌릴지 미리 정해 두는 안전장치다.
AI 생성 이미지
SAVEPOINT로 일부 단계만 되돌리기
모든 실패가 전체 주문 취소로 이어지는 것은 아니다. 예를 들어 쿠폰 적용은 실패해도 주문 자체는 계속 진행할 수 있다. 이때 SAVEPOINT를 사용한다. SAVEPOINT는 트랜잭션 안에 작은 되돌림 지점을 만드는 기능이다.
BEGIN;
INSERT INTO orders (customer_id, status, total_amount, ordered_at)
VALUES (1, 'pending', 59000, now())
RETURNING id;
-- 위 INSERT가 반환한 주문 id가 101이라고 가정한다.
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (101, 10, 2, 29500);
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE id = 10
AND stock_quantity >= 2
RETURNING id, stock_quantity;
SAVEPOINT before_coupon;
UPDATE coupons
SET used_at = now(), used_order_id = 101
WHERE code = 'WELCOME10'
AND customer_id = 1
AND used_at IS NULL
RETURNING id;
-- 쿠폰 적용 결과가 0행이라면 쿠폰 단계만 취소한다.
ROLLBACK TO SAVEPOINT before_coupon;
-- 쿠폰 없이 주문 금액을 다시 계산하거나 그대로 진행한다.
UPDATE orders
SET status = 'paid'
WHERE id = 101;
COMMIT;위 예제에서 ROLLBACK TO SAVEPOINT before_coupon은 쿠폰 적용이 실패했다고 판단했을 때만 실행하는 문장이다. 쿠폰 적용이 성공했다면 이 문장을 실행하지 않고 그대로 다음 단계로 진행하면 된다. 명확하게 표시하고 싶다면 RELEASE SAVEPOINT before_coupon으로 저장점을 해제할 수도 있지만, 트랜잭션을 COMMIT하면 저장점도 함께 정리된다.
ROLLBACK TO SAVEPOINT는 해당 지점 이후의 작업만 취소한다. 전체 주문, 주문 상세, 재고 차감은 남아 있다. 반대로 결제 승인 실패처럼 주문 자체를 확정할 수 없는 오류라면 SAVEPOINT가 아니라 전체 ROLLBACK이 맞다.
psql에서 트랜잭션 중 오류가 발생하면 프롬프트가 !를 포함한 형태로 바뀔 수 있다. 이 상태에서는 새 SQL을 계속 실행하기보다 ROLLBACK으로 정리하는 습관을 들이자. 저장점을 만들어 둔 상황이라면 전체 취소 대신 ROLLBACK TO SAVEPOINT로 오류가 난 구간만 되돌릴 수도 있다.
격리 수준은 동시에 실행될 때 무엇을 볼지 정한다
트랜잭션은 혼자 실행될 때보다 동시에 실행될 때 중요하다. 같은 상품을 여러 사용자가 동시에 주문하고, 같은 포인트를 동시에 사용하고, 같은 정산 데이터를 동시에 집계할 수 있기 때문이다.
격리 수준은 한 트랜잭션이 다른 트랜잭션의 변경을 언제 볼 수 있는지 정한다. PostgreSQL의 기본 격리 수준은 READ COMMITTED다. 이 수준에서는 한 SQL 문장이 시작될 때 이미 커밋된 데이터와 자기 트랜잭션이 앞에서 변경한 데이터를 본다. 그래서 같은 트랜잭션 안에서도 두 번의 SELECT가 서로 다른 결과를 볼 수 있다.1
다음 실험은 두 개의 psql 창을 열고 진행한다.
DROP TABLE IF EXISTS wallet;
CREATE TABLE wallet (
customer_id integer PRIMARY KEY,
balance integer NOT NULL
);
INSERT INTO wallet (customer_id, balance)
VALUES (1, 10000);세션 A:
BEGIN;
SELECT balance FROM wallet WHERE customer_id = 1;
-- 10000세션 B:
BEGIN;
UPDATE wallet
SET balance = 7000
WHERE customer_id = 1;
COMMIT;다시 세션 A:
SELECT balance FROM wallet WHERE customer_id = 1;
-- READ COMMITTED에서는 7000을 볼 수 있다.
COMMIT;이 현상을 반복 불가능 읽기라고 한다. 같은 트랜잭션에서 같은 행을 다시 읽었는데 값이 달라진 것이다. PostgreSQL은 커밋되지 않은 변경을 읽는 더 위험한 현상인 더티 리드는 허용하지 않는다. READ UNCOMMITTED를 요청해도 PostgreSQL에서는 사실상 READ COMMITTED처럼 동작한다.1
REPEATABLE READ에서는 트랜잭션의 첫 번째 실제 SQL 문장이 실행될 때 만들어진 스냅샷을 계속 본다. BEGIN 자체가 아니라, 그 뒤에 실행되는 첫 조회나 변경 문장을 기준으로 생각하면 된다.
먼저 값을 다시 10000으로 맞춰 두고 실험해 보자.
UPDATE wallet
SET balance = 10000
WHERE customer_id = 1;세션 A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM wallet WHERE customer_id = 1;
-- 10000세션 B:
BEGIN;
UPDATE wallet
SET balance = 7000
WHERE customer_id = 1;
COMMIT;다시 세션 A:
SELECT balance FROM wallet WHERE customer_id = 1;
-- 여전히 10000
COMMIT;보고서 생성, 정산 미리보기처럼 한 작업 안에서 일관된 화면이 필요한 경우 REPEATABLE READ가 유용하다. 단, 오래 열린 트랜잭션은 뒤에서 설명할 VACUUM과 저장 공간 회수에 부담을 줄 수 있으므로 필요한 시간만 유지해야 한다.
SERIALIZABLE은 가장 엄격한 수준이다. 동시에 실행했더라도 결과가 어떤 순서로 하나씩 실행한 것과 같아야 한다. PostgreSQL은 불가능한 조합을 감지하면 한쪽 트랜잭션을 실패시킬 수 있다. 애플리케이션은 이 오류를 버그로만 보지 말고, 전체 트랜잭션을 처음부터 재시도해야 한다.1
ERROR: could not serialize access due to read/write dependencies among transactions정리하면 기준은 단순하다.
일반적인 짧은 조회와 단일 행 변경:
READ COMMITTED한 작업 안에서 같은 조회 결과가 유지되어야 하는 보고서:
REPEATABLE READ복잡한 업무 규칙을 동시에 검증하고 저장해야 하는 작업:
SERIALIZABLE과 재시도 로직
격리 수준을 높이면 모든 문제가 저절로 사라지는 것은 아니다. 대신 더 일관된 스냅샷을 제공하거나, 충돌 가능성이 있는 작업을 실패시켜 애플리케이션이 다시 시도하게 만든다. 따라서 격리 수준을 선택할 때는 데이터베이스 설정만 보는 것이 아니라, 실패했을 때 재시도할 수 있는 애플리케이션 구조까지 함께 봐야 한다.
MVCC는 읽기와 쓰기를 동시에 살린다
PostgreSQL의 동시성 제어 핵심은 MVCC다. MVCC는 Multi-Version Concurrency Control의 약자로, 한 행의 여러 버전을 두고 각 트랜잭션이 자기에게 보이는 버전을 읽는 방식이다.
예를 들어 상품 10번의 재고가 5개라고 하자. 어떤 트랜잭션이 재고를 3개로 수정해도 PostgreSQL은 기존 행을 그 자리에서 단순히 덮어쓰지 않는다. 새 행 버전을 만들고, 예전 스냅샷을 가진 트랜잭션은 이전 버전을 계속 볼 수 있게 한다. 그래서 일반적인 SELECT는 쓰기 작업 때문에 바로 막히지 않고, 쓰기 작업도 단순 읽기 때문에 막히지 않는다.
이 구조에는 비용이 있다. UPDATE와 DELETE 후에는 더 이상 최신 데이터가 아니지만 아직 디스크에 남아 있는 행 버전이 생긴다. 이를 dead tuple이라고 부른다. 오래된 트랜잭션이 끝나고 나면 이 버전들은 더 이상 읽을 필요가 없으며, VACUUM이 공간을 재사용할 수 있게 정리한다.3
운영에서 idle in transaction 상태가 위험한 이유가 여기에 있다. 애플리케이션이 BEGIN만 열어 놓고 오래 대기하면 PostgreSQL은 그 트랜잭션이 과거 버전을 아직 필요로 할 수 있다고 판단한다. 그러면 dead tuple 정리가 늦어지고 테이블과 인덱스가 불필요하게 커질 수 있다.
다음 쿼리로 오래 열린 트랜잭션을 확인할 수 있다.
SELECT pid,
state,
now() - xact_start AS transaction_age,
query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;트랜잭션은 짧을수록 좋다. 사용자 입력, 외부 API 응답, 파일 업로드 완료를 기다리는 동안 트랜잭션을 열어 두지 말자. 데이터베이스 안에서 꼭 원자적으로 묶어야 하는 작업만 트랜잭션 안에 넣는 것이 좋다.
행 잠금과 SELECT FOR UPDATE
MVCC가 읽기와 쓰기를 잘 분리해 주지만, 같은 행을 동시에 바꾸는 문제까지 자동으로 업무 의도대로 해결해 주지는 않는다. 재고 차감처럼 경쟁이 있는 작업은 명확한 방식으로 작성해야 한다.
가장 좋은 방식은 조건을 포함한 단일 UPDATE다.
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 10
AND stock_quantity >= 1
RETURNING id, stock_quantity;이 SQL은 재고 확인과 차감을 한 문장으로 묶는다. 결과가 1행이면 성공이고, 0행이면 재고 부족이거나 해당 상품이 없다는 뜻이다. 애플리케이션에서 먼저 SELECT로 재고를 읽고 나중에 UPDATE하는 방식보다 안전하다. 동시에 같은 상품을 차감하려는 트랜잭션이 있더라도 PostgreSQL은 같은 행 변경을 순서대로 처리하고, 조건을 다시 확인한 뒤 결과를 결정한다.
그런데 여러 테이블을 확인한 뒤 결정해야 하는 작업도 있다. 예를 들어 주문 생성 전에 상품 행을 잠그고, 쿠폰과 회원 등급까지 확인해야 한다면 SELECT FOR UPDATE를 사용할 수 있다.
BEGIN;
SELECT id, stock_quantity
FROM products
WHERE id = 10
FOR UPDATE;
-- 이 트랜잭션이 끝날 때까지 다른 트랜잭션은 이 행을 수정하거나
-- 같은 방식으로 잠그려고 하면 기다린다.
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 10
AND stock_quantity >= 1;
COMMIT;SELECT FOR UPDATE는 조회한 행을 수정할 예정인 것처럼 잠근다. 이 잠금은 트랜잭션이 끝날 때까지 유지된다. 다른 일반 SELECT는 보통 막지 않지만, 같은 행을 UPDATE, DELETE, SELECT FOR UPDATE 하려는 작업은 기다리게 된다.2
잠금은 필요한 행에만, 필요한 시간만 잡아야 한다. 조건이 넓으면 많은 행이 잠기고 대기 시간이 늘어난다. 인덱스가 없다고 해서 PostgreSQL이 조건에 맞지 않는 모든 행을 무조건 잠그는 것은 아니지만, 원하는 행을 찾는 시간이 길어져 이미 잡은 잠금을 더 오래 들고 있을 수 있다. 그래서 잠금이 필요한 쿼리일수록 조건과 인덱스를 함께 점검해야 한다.
현재 대기 중인 잠금을 간단히 보려면 다음 쿼리를 사용할 수 있다.
SELECT pid,
locktype,
relation::regclass AS relation_name,
mode,
granted
FROM pg_locks
WHERE NOT granted;운영에서는 pg_stat_activity와 함께 보며 어떤 SQL이 오래 잠금을 잡고 있는지 확인한다.
교착 상태는 잠금 순서가 엇갈릴 때 생긴다
교착 상태, 즉 deadlock은 두 트랜잭션이 서로가 가진 잠금을 기다리면서 아무도 앞으로 가지 못하는 상태다.
다음 상황을 보자.
세션 A:
BEGIN;
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 10;
-- 아직 COMMIT하지 않는다.세션 B:
BEGIN;
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 20;
-- 아직 COMMIT하지 않는다.세션 A가 이어서 상품 20번을 수정하려 한다.
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 20;
-- 세션 B가 가진 잠금을 기다린다.세션 B가 이어서 상품 10번을 수정하려 한다.
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 10;
-- 세션 A가 가진 잠금을 기다린다.이제 A는 B를 기다리고, B는 A를 기다린다. PostgreSQL은 이런 교착 상태를 감지하면 관련 트랜잭션 중 하나를 중단시킨다. 어떤 쪽이 중단될지는 애플리케이션이 의존하면 안 된다.2
ERROR: deadlock detected교착 상태를 줄이는 가장 효과적인 방법은 잠금 순서를 통일하는 것이다. 여러 상품을 한 주문에서 차감한다면 항상 상품 ID 오름차순으로 잠근다.
BEGIN;
SELECT id
FROM products
WHERE id IN (10, 20)
ORDER BY id
FOR UPDATE;
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 10;
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 20;
COMMIT;모든 코드 경로가 같은 순서로 행을 잠그면 서로 꼬일 가능성이 크게 줄어든다. 여기에 다음 원칙을 더한다.
트랜잭션 안에서 외부 API나 사용자 입력을 기다리지 않는다.
여러 행을 잠글 때는 항상 같은 기준으로 정렬해 접근한다.
필요한 잠금은 가능한 한 처음에 잡고, 중간에 잠금 강도를 높이는 상황을 줄인다.
교착 상태와 직렬화 실패는 재시도할 수 있게 애플리케이션 로직을 만든다.
조건 열에 적절한 인덱스를 두어 불필요하게 오래 검색하지 않게 한다.
동시성 문제는 테스트 환경에서 잘 드러나지 않을 수 있다. 사용자가 적을 때는 거의 동시에 같은 행을 바꾸는 일이 드물기 때문이다. 하지만 주문, 포인트, 쿠폰, 정산처럼 돈이나 수량과 연결된 데이터는 처음부터 트랜잭션과 잠금 기준을 정해 두어야 한다. 안전한 SQL은 우연히 한 번 성공한 SQL이 아니라, 동시에 여러 번 실행되어도 업무 규칙을 깨지 않는 SQL이다.
참고
1PostgreSQL Documentation: Transaction Isolation
2PostgreSQL Documentation: Explicit Locking
3PostgreSQL Transactions Deep Dive: MVCC, Isolation Levels, Deadlocks, and VACUUM
이 노트는 보도·비평·교육·연구 목적으로 작성되었습니다. 저작권 문의가 있으시면 에서 알려주세요.