- Published on
트랜잭션 격리 수준과 실제 이상 현상 — 표준 정의가 구현과 다른 지점
- Authors
- Name
- Youngju Kim
- @fjvbn20031
- 들어가며 — 격리 수준을 올렸는데 왜 데이터가 어긋나는가
- 표준이 정의한 네 수준과 세 가지 이상 현상
- PostgreSQL의 Read Committed는 표준과 다르다
- Repeatable Read는 사실 스냅샷 격리다
- MySQL InnoDB의 Repeatable Read와 갭 락
- write skew — 스냅샷 격리의 진짜 구멍
- SERIALIZABLE, SELECT FOR UPDATE, 낙관적 잠금 중 무엇을 고를까
- 마치며 — 재시도 없는 격리 수준 상향은 의미가 없다
들어가며 — 격리 수준을 올렸는데 왜 데이터가 어긋나는가
동시성 버그를 만나면 흔히 나오는 처방이 있습니다. "격리 수준을 Repeatable Read로 올려라." 그리고 상당수의 경우 그 처방은 문제를 고치지 못합니다. 잔고가 음수가 되고, 재고가 초과 판매되고, 중복 예약이 계속 생깁니다.
이유는 두 가지입니다. 첫째, 격리 수준 표로 배운 정의와 실제 구현이 다릅니다. PostgreSQL의 Read Committed는 표준이 허용하는 dirty read를 애초에 만들 수 없고, Repeatable Read는 표준이 허용하는 팬텀도 대부분 막습니다. MySQL InnoDB의 Repeatable Read는 또 다른 방식으로 동작합니다. 둘째, 네 가지 수준 중 어느 것도 잡지 못하는 이상 현상이 하나 있습니다. 대부분의 실무 동시성 버그는 바로 그것입니다.
이 글은 표준의 표를 먼저 정리한 뒤, 그 표가 실제 데이터베이스에서 어떻게 무너지는지를 두 세션의 SQL로 확인합니다.
표준이 정의한 네 수준과 세 가지 이상 현상
ANSI SQL 표준은 격리 수준을 "무엇을 허용하는가"로 정의했습니다. 알고리즘이 아니라 금지 목록으로 정의했다는 점이 나중에 혼란의 씨앗이 됩니다.
- dirty read — 커밋되지 않은 다른 트랜잭션의 변경을 읽는 것
- non-repeatable read — 같은 행을 두 번 읽었는데 값이 달라지는 것
- phantom read — 같은 조건으로 두 번 조회했는데 행의 개수가 달라지는 것
여기에 표준에 없지만 실무에서 가장 중요한 하나를 더해 정리하면 이렇습니다.
| 격리 수준 | dirty read | non-repeatable read | phantom read | write skew |
|---|---|---|---|---|
| Read Uncommitted | 표준상 허용 (PostgreSQL은 불가) | 허용 | 허용 | 허용 |
| Read Committed | 불가 | 허용 | 허용 | 허용 |
| Repeatable Read | 불가 | 불가 | 표준상 허용 (PostgreSQL은 불가) | 허용 |
| Serializable | 불가 | 불가 | 불가 | 불가 |
표의 오른쪽 끝 열이 핵심입니다. Serializable을 제외한 모든 수준이 write skew를 허용합니다. 그리고 대부분의 서비스는 Read Committed에서 돌아갑니다.
현재 설정은 이렇게 확인합니다.
-- PostgreSQL
SHOW default_transaction_isolation; -- 기본값: read committed
SELECT current_setting('transaction_isolation');
-- 트랜잭션 단위로 지정
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- 또는
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- MySQL
SELECT @@transaction_isolation; -- 기본값: REPEATABLE-READ
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
기본값이 다르다는 점을 먼저 인지해야 합니다. PostgreSQL은 Read Committed, MySQL은 Repeatable Read입니다. MySQL에서 PostgreSQL로 마이그레이션할 때 조용히 동작이 바뀌는 첫 번째 지점이 여기입니다.
PostgreSQL의 Read Committed는 표준과 다르다
PostgreSQL은 잠금이 아니라 MVCC로 격리를 구현합니다. 모든 읽기는 스냅샷을 통해 이뤄지고, 스냅샷에는 커밋된 버전만 보입니다. 그래서 Read Uncommitted를 요청해도 Read Committed로 동작합니다. 문법은 받아 주지만 dirty read는 구조적으로 불가능합니다.
BEGIN ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;
transaction_isolation
-----------------------
read uncommitted
설정값은 그대로 보이지만 내부 동작은 Read Committed와 같습니다. 이 사실을 모르고 "Read Uncommitted로 낮춰 성능을 올리자"는 제안을 하는 경우가 있는데, PostgreSQL에서는 아무 효과가 없습니다.
Read Committed에서 스냅샷은 문장 단위로 새로 잡힙니다. 트랜잭션 안이라도 SELECT를 두 번 하면 서로 다른 스냅샷을 봅니다.
-- 세션 A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 10000
-- 세션 B
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;
-- 세션 A (같은 트랜잭션 안)
SELECT balance FROM accounts WHERE id = 1; -- 5000 <-- 값이 바뀌었다
COMMIT;
여기까지는 교과서대로입니다. 그런데 Read Committed에는 잘 알려지지 않은 동작이 하나 더 있습니다. UPDATE가 다른 트랜잭션이 잠근 행을 만나면 대기하다가, 상대가 커밋하면 갱신된 최신 버전을 다시 읽어서 WHERE 조건을 재평가합니다.
-- 세션 A
BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
-- 커밋하지 않고 대기
-- 세션 B
BEGIN;
UPDATE accounts SET balance = balance - 4000 WHERE id = 1; -- 대기
-- 세션 A
COMMIT;
세션 B는 잠금이 풀리면 balance가 7000이 된 최신 행을 다시 읽고 3000으로 갱신합니다. 결과는 3000, 즉 두 차감이 모두 반영됩니다. balance = balance - 4000처럼 현재 값을 기준으로 하는 갱신에서는 이 재평가 덕분에 lost update가 발생하지 않습니다.
문제는 애플리케이션이 값을 읽어서 계산한 뒤 상수로 쓰는 패턴입니다.
-- 애플리케이션이 흔히 하는 일
SELECT balance FROM accounts WHERE id = 1; -- 10000을 읽음
-- 애플리케이션에서 10000 - 4000 = 6000 계산
UPDATE accounts SET balance = 6000 WHERE id = 1; -- 상수로 덮어씀
이 경우 세션 A의 차감은 흔적도 없이 사라집니다. 재평가는 WHERE 조건에만 적용되고, SET 절의 상수는 그대로 쓰이기 때문입니다. ORM이 엔티티 전체를 읽어 필드를 바꾸고 저장하는 방식이 정확히 이 패턴입니다.
Repeatable Read는 사실 스냅샷 격리다
PostgreSQL의 Repeatable Read는 트랜잭션의 첫 문장 시점에 스냅샷을 한 번 잡고 끝까지 그것만 봅니다. 이것은 표준의 Repeatable Read보다 강합니다. 같은 행의 값이 안 변하는 것은 물론이고, 조건에 맞는 행의 개수도 변하지 않습니다. 즉 팬텀도 보이지 않습니다.
-- 세션 A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders WHERE status = 'pending'; -- 42
-- 세션 B
INSERT INTO orders (status) VALUES ('pending');
COMMIT;
-- 세션 A
SELECT count(*) FROM orders WHERE status = 'pending'; -- 42 <-- 그대로
COMMIT;
대가는 쓰기 충돌 시 트랜잭션이 죽는다는 것입니다. 스냅샷 이후에 다른 트랜잭션이 커밋한 행을 갱신하려 하면 PostgreSQL은 기다렸다가 재평가하는 대신 오류를 냅니다.
-- 세션 A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1; -- 10000
-- 세션 B
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;
-- 세션 A
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
ERROR: could not serialize access due to concurrent update
SQLSTATE: 40001
이것이 Repeatable Read를 쓸 때 반드시 알아야 할 계약입니다. 격리 수준을 올리면 애플리케이션이 재시도할 책임을 지게 됩니다. 재시도 로직 없이 격리 수준만 올리는 것은 사용자에게 500 에러를 더 자주 보여 주는 변경일 뿐입니다.
Repeatable Read에서 함께 나타나는 오류가 하나 더 있습니다.
ERROR: could not serialize access due to concurrent delete
읽은 행이 다른 트랜잭션에 의해 삭제되었을 때 발생합니다. 대응은 동일하게 재시도입니다.
MySQL InnoDB의 Repeatable Read와 갭 락
같은 이름이지만 InnoDB의 Repeatable Read는 다르게 동작합니다. 차이는 두 군데에 있습니다.
첫째, 일반 SELECT는 트랜잭션 스냅샷을 보지만 잠금 읽기와 갱신문은 최신 커밋 버전을 봅니다. 그래서 한 트랜잭션 안에서 SELECT와 UPDATE가 서로 다른 데이터를 볼 수 있습니다.
-- 세션 A (MySQL, REPEATABLE READ)
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 10000 (스냅샷)
-- 세션 B
UPDATE accounts SET balance = 5000 WHERE id = 1;
COMMIT;
-- 세션 A
SELECT balance FROM accounts WHERE id = 1; -- 10000 (여전히 스냅샷)
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE; -- 5000 (최신)
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
COMMIT;
PostgreSQL이라면 세 번째 문장에서 40001 오류가 났을 상황인데, InnoDB는 조용히 진행합니다. 오류가 없다는 것이 안전하다는 뜻은 아닙니다. 애플리케이션이 첫 SELECT의 10000을 근거로 판단했다면 그 판단은 이미 낡은 것입니다.
둘째, InnoDB는 팬텀을 갭 락으로 막습니다. 잠금 읽기나 갱신문은 인덱스 레코드뿐 아니라 레코드 사이의 간격까지 잠급니다. 이것이 넥스트 키 락입니다.
-- 세션 A
BEGIN;
SELECT * FROM orders WHERE user_id = 42 FOR UPDATE;
-- user_id = 42 인덱스 구간과 그 앞뒤 간격이 잠긴다
-- 세션 B
INSERT INTO orders (user_id, status) VALUES (42, 'pending'); -- 대기
갭 락은 팬텀을 막아 주지만 대가가 큽니다. 존재하지 않는 값에 대해서도 잠금이 걸리고, 잠금 범위가 넓어져 데드락 확률이 올라갑니다. 특히 조건 컬럼에 인덱스가 없으면 InnoDB는 훑는 모든 레코드에 잠금을 걸기 때문에 사실상 테이블 전체가 잠깁니다. MySQL에서 데드락이 자주 난다면 인덱스부터 확인해야 하는 이유입니다.
-- 현재 잠금 상태 확인 (MySQL 8.0)
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
갭 락이 부담스러워 많은 팀이 MySQL에서도 Read Committed로 낮춥니다. 실제로 대규모 서비스에서 흔한 선택이고, 이 경우 팬텀은 애플리케이션이 감당해야 합니다.
write skew — 스냅샷 격리의 진짜 구멍
이제 표의 오른쪽 끝 열로 갑니다. 두 트랜잭션이 같은 집합을 읽고, 서로 다른 행을 갱신할 때 스냅샷 격리는 아무 충돌도 감지하지 못합니다. 겹치는 쓰기가 없으니 충돌이 없는 것이 맞습니다. 그런데 불변식은 깨집니다.
당직 의사 예제가 고전입니다. 규칙은 "당직자가 최소 한 명은 남아야 한다"입니다.
CREATE TABLE doctors (
id int PRIMARY KEY,
name text NOT NULL,
on_call boolean NOT NULL
);
INSERT INTO doctors VALUES (1, 'Kim', true), (2, 'Lee', true);
-- 세션 A: 김 의사가 당직을 뺀다
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true; -- 2, 조건 통과
-- 세션 B: 이 의사가 동시에 당직을 뺀다
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call = true; -- 2, 조건 통과
-- 세션 A
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;
-- 세션 B
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;
SELECT count(*) FROM doctors WHERE on_call = true;
count
-------
0
두 트랜잭션 모두 성공했고 오류도 없었지만 당직자는 0명이 되었습니다. 각각은 서로 다른 행을 갱신했으므로 쓰기 충돌이 없고, 스냅샷 격리는 이를 잡을 수단이 없습니다.
같은 구조의 버그가 실무에서 반복됩니다. 재고 확인 후 차감, 좌석 중복 예약 검사, 계좌 잔고 합계 검증, 유니크 제약이 없는 중복 가입 검사가 모두 write skew입니다. 격리 수준을 Read Committed에서 Repeatable Read로 올려도 하나도 고쳐지지 않습니다.
PostgreSQL의 SERIALIZABLE은 이것을 잡습니다. 구현 방식은 잠금이 아니라 SSI, 즉 Serializable Snapshot Isolation입니다. 스냅샷 격리 위에서 트랜잭션 사이의 읽기 쓰기 의존성을 추적하다가, 직렬 순서로 설명할 수 없는 패턴이 감지되면 한쪽을 중단시킵니다.
-- 두 세션 모두 SERIALIZABLE로 위 시나리오를 반복하면
COMMIT;
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during commit attempt.
HINT: The transaction might succeed if retried.
SQLSTATE: 40001
주의할 점이 세 가지 있습니다.
- 오류가 커밋 시점에 나올 수 있습니다. 트랜잭션 도중에 아무 문제가 없어도 COMMIT에서 실패할 수 있으므로 재시도 범위는 트랜잭션 전체여야 합니다.
- 참여하는 모든 트랜잭션이 SERIALIZABLE이어야 보장이 성립합니다. 한쪽만 올려 두면 아무 의미가 없습니다.
- 읽기 전용 트랜잭션이라면
SET TRANSACTION READ ONLY DEFERRABLE로 선언해 추적 부담과 중단 확률을 줄일 수 있습니다.
MySQL의 SERIALIZABLE은 SSI가 아닙니다. 일반 SELECT를 공유 잠금 읽기로 바꾸는 방식, 즉 2단계 잠금에 가깝습니다. 안전하지만 동시성 저하가 훨씬 큽니다.
SERIALIZABLE, SELECT FOR UPDATE, 낙관적 잠금 중 무엇을 고를까
세 가지 선택지가 있고 각각 맞는 자리가 다릅니다.
첫째, 잠글 대상 행이 명확하다면 비관적 잠금이 가장 단순하고 예측 가능합니다.
BEGIN;
SELECT stock FROM products WHERE id = 77 FOR UPDATE;
-- 여기서 다른 트랜잭션은 같은 행을 잠글 수 없다
UPDATE products SET stock = stock - 1 WHERE id = 77;
COMMIT;
행이 존재하지 않는 경우까지 막아야 한다면 FOR UPDATE로는 부족합니다. 없는 행은 잠글 수 없기 때문입니다. 이때는 유니크 제약이나 advisory lock을 씁니다.
-- 애플리케이션 수준의 명시적 잠금 (트랜잭션 종료 시 자동 해제)
SELECT pg_advisory_xact_lock(hashtext('reserve-seat:' || 'A12'));
대기 시간을 제어하려면 옵션을 함께 씁니다.
SELECT * FROM products WHERE id = 77 FOR UPDATE NOWAIT; -- 즉시 오류
SELECT * FROM products WHERE id = 77 FOR UPDATE SKIP LOCKED; -- 잠긴 행은 건너뜀
SKIP LOCKED는 큐 테이블 구현에 특히 유용합니다. 여러 워커가 같은 테이블에서 서로 다른 작업을 집어 갈 수 있습니다.
-- 작업 큐에서 안전하게 한 건 집어 오기
UPDATE jobs SET status = 'running', picked_at = now()
WHERE id = (
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING *;
둘째, 충돌이 드물고 사용자 상호작용이 트랜잭션 중간에 끼는 경우에는 낙관적 잠금이 낫습니다. 화면을 열어 둔 채 잠금을 유지할 수는 없기 때문입니다.
-- 버전 컬럼으로 충돌만 감지한다
UPDATE documents
SET title = 'new title',
version = version + 1
WHERE id = 5 AND version = 12;
-- 갱신된 행이 0이면 누군가 먼저 바꿨다는 뜻
셋째, 불변식이 여러 행이나 여러 테이블에 걸쳐 있어서 잠글 대상을 특정할 수 없을 때 SERIALIZABLE이 답입니다. 앞의 당직 의사 예제가 정확히 그 경우입니다. 다만 처리량이 높은 경로에서는 중단율이 올라가므로, 중단율을 지표로 관찰하면서 적용 범위를 좁히는 것이 좋습니다.
가능한 경우라면 데이터베이스 제약으로 바꾸는 것이 가장 견고합니다. 격리 수준으로 지키던 불변식을 제약으로 옮기면 재시도도 필요 없습니다.
-- 중복 예약을 격리 수준이 아니라 제약으로 막는다
CREATE UNIQUE INDEX uq_seat_reservation
ON reservations (showtime_id, seat_no)
WHERE cancelled_at IS NULL;
-- 시간 구간 겹침을 제약으로 막는다
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings
ADD CONSTRAINT no_overlap
EXCLUDE USING gist (room_id WITH =, during WITH &&);
마치며 — 재시도 없는 격리 수준 상향은 의미가 없다
세 가지만 기억하면 됩니다.
첫째, Read Committed 위의 모든 격리 수준은 애플리케이션에 재시도 의무를 지웁니다. SQLSTATE 40001과 40P01을 잡아 트랜잭션 전체를 다시 실행하는 코드가 없다면, 격리 수준을 올리는 것은 장애를 늘리는 변경입니다.
import time, random
import psycopg
RETRYABLE = {"40001", "40P01"} # serialization_failure, deadlock_detected
def run_in_tx(conn, fn, max_attempts=5):
for attempt in range(max_attempts):
try:
with conn.transaction():
return fn(conn)
except psycopg.errors.Error as e:
if e.sqlstate not in RETRYABLE or attempt == max_attempts - 1:
raise
# 지수 백오프 + 지터. 같은 순간 재충돌을 막는다.
time.sleep((2 ** attempt) * 0.05 * (0.5 + random.random()))
재시도 함수 안에는 데이터베이스 작업만 넣어야 합니다. 메일 발송이나 외부 API 호출이 섞여 있으면 재시도할 때마다 부수 효과가 반복됩니다.
둘째, 대부분의 동시성 버그는 non-repeatable read나 phantom read가 아니라 write skew입니다. 격리 수준 표에서 그 열은 Serializable에서만 비어 있습니다. Repeatable Read로 올려도 안 고쳐진다면 지금 겪는 문제가 write skew는 아닌지 의심하십시오.
셋째, 격리 수준으로 지키려는 불변식이 있다면 그것을 제약으로 표현할 수 있는지 먼저 검토하십시오. 유니크 인덱스와 EXCLUDE 제약은 격리 수준과 무관하게 항상 성립하고, 재시도도 필요 없으며, 나중에 합류한 팀원이 실수로 우회할 수도 없습니다.