Phantom Read

같은 조건으로 두 번 조회했는데 행의 개수가 달라지는 현상. 사이에 다른 트랜잭션이 조건에 맞는 행을 INSERT하거나 DELETE한 것이다.

Non-repeatable Read와의 차이

아직 존재하지 않는 행은 락을 걸 대상이 없다는 것이 phantom이 어려운 이유다.

-- T1
SELECT count(*) FROM orders WHERE amount > 1000;   -- 10건
-- T2:  INSERT INTO orders (amount) VALUES (5000); COMMIT;
SELECT count(*) FROM orders WHERE amount > 1000;   -- 11건  ← 유령이 나타났다

두 엔진의 해법이 다르다

• [MySQL InnoDB] — 갭 락 — '행 사이의 틈'까지 잠근다

  • SELECT ... WHERE amount BETWEEN 100 AND 200 FOR UPDATE;
  • 그 범위에 새 INSERT 자체를 막는다

• [PostgreSQL] — 스냅샷 — 애초에 새 행이 내 스냅샷에 안 보인다

  • 잠글 필요 없이 phantom 이 사라진다

목표는 같고 메커니즘이 다르다. InnoDB는 남을 막고, PostgreSQL은 나를 안 보이게 한다.

스냅샷으로도 안 막히는 것 — Write Skew

규칙: 당직자는 최소 1명 있어야 한다 (현재 A, B 두 명)

T1: A가 본다 → "B가 있으니 나는 빠져도 되겠다" → A 당직 해제
T2: B가 본다 → "A가 있으니 나는 빠져도 되겠다" → B 당직 해제
둘 다 커밋 → 당직자 0명. 각자는 규칙을 지켰는데 합치면 깨졌다

각자 다른 행을 읽고 다른 행을 쓰므로 충돌이 없다. 스냅샷 격리로는 못 막는다. PostgreSQL은 SERIALIZABLE(SSI) 에서 이런 이상을 탐지해 한쪽을 롤백시킨다(could not serialize 오류) — 애플리케이션은 재시도해야 한다.

면접 함정

• ❌ "REPEATABLE READ면 phantom이 생긴다" → 표준 정의상 그렇고, PostgreSQL·MySQL 실제 구현에서는 막힌다.
• ❌ "SERIALIZABLE이면 에러가 안 난다" → 오히려 직렬화 실패 오류가 늘어난다. 재시도가 짝이다.

두 엔진에서 실제로 확인하기

-- [세션 1] MySQL (기본 REPEATABLE READ)
BEGIN;
SELECT * FROM orders WHERE amount BETWEEN 100 AND 200 FOR UPDATE;

-- [세션 2]
INSERT INTO orders (amount) VALUES (150);
-- → 대기한다. 갭 락에 걸렸다 (Lock wait timeout 까지 감)

-- [세션 1] PostgreSQL (REPEATABLE READ)
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM orders WHERE amount BETWEEN 100 AND 200;   -- 10

-- [세션 2] INSERT ... ; COMMIT;   → 막히지 않고 바로 성공한다

-- [세션 1]
SELECT count(*) FROM orders WHERE amount BETWEEN 100 AND 200;   -- 여전히 10 (스냅샷)

MySQL은 남을 막고, PostgreSQL은 나를 안 보이게 한다. 결과는 같지만 세션 2의 체감이 정반대다 — 이 차이가 락 대기·데드락 양상을 가른다.

Write Skew를 SERIALIZABLE로 잡기

BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM oncall WHERE on_duty = true;   -- 2명
UPDATE oncall SET on_duty = false WHERE id = 1;
COMMIT;
-- 다른 세션이 대칭적으로 같은 일을 하면:
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- HINT:  The transaction might succeed if retried.        ← 재시도가 짝이다