같은 REPEATABLE READ 인데 DB 를 옮기니 결과가 달라졌다

문서에 REPEATABLE READ 라고 적혀 있었다. 그래서 "같은 걸 두 번 읽으면 같은 값이 나온다"고 믿고 코드를 짰다. 그런데 운영에서 값이 어긋난다.

거꾸로인 경우도 있다. READ COMMITTED 인데 생각보다 훨씬 강하게 동작해서, 테스트에서 경합이 재현되지 않는다.

같은 이름의 격리 수준이 DB 마다 다르게 동작한다는 걸 모르면, 이 차이가 전부 애플리케이션 버그로 보인다. 그래서 엉뚱한 곳을 며칠씩 들여다보게 된다.

MySQL 에서 PostgreSQL 로, 혹은 그 반대로 옮길 때 특히 자주 만난다. 코드는 그대로인데 동작이 달라진다.

표준은 이름만 정해 줬다

SQL 표준은 격리 수준을 "무엇이 일어나면 안 되는가"로 정의했다. 기준은 더티 리드, 반복 불가능한 읽기, 팬텀 리드라는 세 가지 이상 현상이다.

문제는 이 정의가 구현 방법은 말하지 않는다는 데 있다. 그래서 DB 마다 자기 식으로 구현했고, 결국 같은 이름이 다른 보장을 갖게 됐다.

MySQL(InnoDB)의 REPEATABLE READ 는 스냅샷을 고정해서 읽기의 일관성을 지킨다. 그런데 쓰기는 스냅샷이 아니라 최신 값을 본다. SELECT 로 읽은 값과 UPDATE 가 실제로 고치는 값이 다를 수 있다는 뜻이다. 이걸 흔히 반일관성 읽기라고 부른다.

PostgreSQL 의 REPEATABLE READ 는 스냅샷 격리다. 표준이 요구하는 것보다 강해서 팬텀 리드도 안 일어난다. 대신 충돌하면 트랜잭션이 통째로 실패한다(could not serialize access). 실패를 받아 줄 코드가 없으면 그게 곧 장애다.

게다가 표준의 이상 현상 목록이 완전하지도 않다. 쓰기 스큐처럼 그 목록 어디에도 안 들어가는 문제가 있다. 이건 SERIALIZABLE 이 아니면 못 막는다.

10 을 읽고 3 에서 뺀다

이름은 같은데 보장은 이렇게 다르다.

코드

· MySQL REPEATABLE READ
BEGIN;
  SELECT qty FROM stock WHERE id=1;   -- 스냅샷: 10
  -- 다른 트랜잭션이 qty 를 3 으로 바꾸고 커밋
  SELECT qty FROM stock WHERE id=1;   -- 여전히 10
✕ UPDATE stock SET qty = qty - 1 WHERE id=1;
✕ -- 쓰기는 최신값(3)을 보고 2 가 된다
✕ -- 읽은 값과 쓴 값의 근거가 다르다
COMMIT;

· PostgreSQL REPEATABLE READ 는 대신 실패시킨다
✕ ERROR: could not serialize access due to concurrent update
✓ 앱이 재시도해야 한다. 재시도 코드가 없으면 그게 장애다

· 확실하게 하려면 읽을 때부터 잠근다
✓ SELECT qty FROM stock WHERE id=1 FOR UPDATE;

✕ 문제 지점 · ✓ 해법 · 그대로 돌리기 전에 자기 환경에 맞게 고친다.

재고 차감 코드가 제일 먼저 물린다

실무에서 제일 자주 물리는 건 MySQL 의 반일관성 읽기다.

재고를 확인하고 차감하는 코드를 떠올려 보자. SELECT 로 재고가 10 인 걸 확인하고, 조건을 통과한 뒤 UPDATE 로 1 을 뺀다. REPEATABLE READ 니까 안전할 것 같다. 그런데 그 사이 다른 트랜잭션이 재고를 0 으로 만들었다면 UPDATE 는 0 을 기준으로 계산한다. 애플리케이션은 10 을 보고 판단했는데, DB 는 0 위에서 실행했다.

PostgreSQL 은 다르게 실패한다. 스냅샷 격리라 충돌을 감지하면 에러를 낸다. 정확한 대신, 재시도는 앱이 구현해야 한다. 이걸 모르고 REPEATABLE READ 로 올리면 평소엔 멀쩡하다가, 트래픽이 오른 날 직렬화 실패가 쏟아진다.

기본값도 다르다. MySQL 은 REPEATABLE READ, PostgreSQL·Oracle·SQL Server 는 READ COMMITTED 다. 여기에 ORM 이나 프레임워크가 자기 기본값을 또 씌우기도 한다. 그러니 "우리 서비스의 격리 수준"은 문서가 아니라 실제 세션에서 확인해야 한다.

READ COMMITTED 도 오해를 많이 받는다. 한 문장 안에서는 일관된 스냅샷을 보지만, 문장이 바뀌면 새 스냅샷을 뜬다. 그래서 같은 트랜잭션 안의 두 SELECT 가 다른 결과를 낼 수 있다. 리포트 쿼리를 여러 문장으로 나눠 돌리면 서로 안 맞는 숫자가 나온다.

SERIALIZABLE 도 DB 마다 구현이 다르다. PostgreSQL 은 직렬화 가능 스냅샷 격리라 락 대신 충돌 감지로 처리하고, 실패하면 재시도를 요구한다. MySQL 은 사실상 모든 읽기를 잠그는 방식이라 성능 특성이 완전히 딴판이다.

격리 수준에 기대지 않고 짜기

문서 말고 실제 값을 본다. 세션에서 격리 수준을 직접 조회하고, ORM 이 씌우는 값도 확인해야 한다.
읽고 판단해서 쓰는 흐름이라면 격리 수준에 기대면 안 된다. 길은 둘이다. 읽을 때부터 FOR UPDATE 로 잠그거나, 조건을 UPDATE 문 안에 넣어 원자적으로 처리하거나. 뒤의 것이 더 싸다.
PostgreSQL 에서 REPEATABLE READ 이상을 쓴다면 재시도는 반드시 구현한다. 직렬화 실패는 버그가 아니라 정상 동작이다. 재시도 횟수와 백오프도 정해 두자.
격리 수준을 올려서 문제를 덮으면 대개 처리량이 떨어지고 새 실패 모드가 생긴다. 문제 지점만 명시적으로 잠그는 편이 낫다.
SQLite 나 H2 로 하는 동시성 테스트는 격리 동작이 달라서 아무것도 검증하지 못한다. 실제 DB 로 해야 한다.
팀 문서에 "우리는 X 를 쓰고, 그래서 Y 를 보장하지 않는다"를 적어 두면 좋다. 보장하는 것보다 보장하지 않는 것을 적는 쪽이 사고를 막는다.

우리 세션의 격리 수준 직접 열어 보기

세션에 직접 물어보면 된다.

코드

· PostgreSQL
SHOW transaction_isolation;
SHOW default_transaction_isolation;

· MySQL
SELECT @@transaction_isolation, @@global.transaction_isolation;

· 직렬화 실패가 실제로 나고 있는지 (PG)
✓ 로그에서 'could not serialize access' 를 센다
✓ 재시도 로직이 없다면 이 수가 곧 실패한 요청 수다

· 트랜잭션 안에서 두 번 읽어 직접 확인해 본다
BEGIN;
  SELECT qty FROM stock WHERE id = 1;
  -- 다른 세션에서 값을 바꾸고 커밋한 뒤
  SELECT qty FROM stock WHERE id = 1;
✕ 값이 바뀌면 READ COMMITTED 다
COMMIT;

핵심 한 줄격리 수준의 이름은 보장이 아니라 관습이다. 실제 동작을 확인하라.