PostgreSQL과 MySQL의 REPEATABLE READ가 다른 이유
선착순 쿠폰 발급 기능을 만들던 때였다. 쿠폰마다 남은 수량을 한 행에 들고 있다가 발급 요청이 오면 그 숫자를 하나씩 줄이는 구조였다. DB는 MySQL이었고 격리 수준은 손대지 않은 기본값 REPEATABLE READ였다. 이름부터 ‘여러 번 읽어도 같은 값’이니 요청이 몰려도 숫자가 꼬일 일은 없다고 믿었다. 이벤트가 끝나고 발급 내역을 세어 보니 수량 컬럼과 맞지 않았다. 쿠폰은 103장이 나갔는데 남은 수량은 100장만 줄어 있었다.
얼마 뒤 비슷한 기능을 PostgreSQL에 새로 만들었을 때는 반대 방향으로 놀랐다. 숫자는 정확히 맞았지만 부하 테스트를 돌리자 에러 로그가 could not serialize access due to concurrent update로 가득 찼다. 두 번 다 격리 수준 이름은 똑같이 REPEATABLE READ였다.
REPEATABLE READ라는 이름은 SQL 표준에서 왔지만 실제 동작은 DB마다 구현을 따른다. 같은 시나리오를 두 DB에서 나란히 돌려 보면 차이가 또렷해진다.
실험 준비: 쿠폰 한 행, 세션 두 개
테이블은 두 DB에 똑같이 만든다. 남은 수량이 10장인 쿠폰 한 행이면 충분하다.
CREATE TABLE coupons (
id INT PRIMARY KEY,
remain INT NOT NULL
);
INSERT INTO coupons VALUES (1, 10);터미널 두 개를 열어 세션 A와 B로 쓴다. A가 트랜잭션을 열고 수량을 읽는다. 그사이 B가 3장을 발급하고 커밋한다. 마지막으로 A가 2장을 발급한다. 두 발급이 모두 반영되면 남은 수량은 5여야 한다.
A의 UPDATE는 두 가지로 나눠 본다. 하나는 remain = remain - 2처럼 DB 안에서 빼는 상대식이다. 다른 하나는 애플리케이션이 앞서 읽은 10에서 2를 빼고 remain = 8을 그대로 넣는 방식이다. 내 쿠폰 코드는 뒤쪽이었다. ORM으로 엔티티를 불러와 필드를 고치고 저장하면 SQL은 자연스럽게 이 모양이 된다.
PostgreSQL: 늦게 온 쓰기는 거절된다
-- 세션 A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT remain FROM coupons WHERE id = 1; -- 10
-- 세션 B
BEGIN;
UPDATE coupons SET remain = remain - 3 WHERE id = 1;
COMMIT;
-- 세션 A
SELECT remain FROM coupons WHERE id = 1; -- 여전히 10
UPDATE coupons SET remain = remain - 2 WHERE id = 1;
-- ERROR: could not serialize access due to concurrent update
ROLLBACK;A의 두 번째 SELECT는 B가 커밋한 뒤인데도 10을 돌려준다. 여기까지는 이름 그대로다. 문제는 UPDATE다. PostgreSQL 문서에 따르면 REPEATABLE READ 트랜잭션은 자기가 시작된 뒤 다른 트랜잭션이 바꾼 행을 수정하거나 잠글 수 없다. 그래서 A는 바로 오류를 받고 롤백할 수밖에 없다. 상대식이든 remain = 8이든 결과는 같다. B가 아직 커밋하지 않았다면 A는 기다리고, B가 롤백하면 A의 UPDATE는 그대로 진행된다.
이 오류의 SQLSTATE는 40001이고, 이 수준을 쓰는 애플리케이션은 트랜잭션을 다시 시도할 준비를 해 둬야 한다고 문서에 적혀 있다. 실패한 UPDATE 한 줄만 다시 보내면 안 된다. 오류가 난 트랜잭션은 이미 중단돼서 어떤 문장도 받아 주지 않는다. 처음엔 드라이버 버그부터 의심했는데, 코드 리뷰에서 동료가 트랜잭션 함수를 통째로 감싸 40001이면 BEGIN부터 새로 실행하는 헬퍼를 제안했다. 그 헬퍼를 넣은 다음 배포부터 에러 로그 대신 재시도 횟수 지표가 쌓였다.
MySQL: 최신 값을 읽고 그 위에 쓴다
-- 세션 A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT remain FROM coupons WHERE id = 1; -- 10
-- 세션 B
START TRANSACTION;
UPDATE coupons SET remain = remain - 3 WHERE id = 1;
COMMIT;
-- 세션 A
SELECT remain FROM coupons WHERE id = 1; -- 여전히 10
UPDATE coupons SET remain = remain - 2 WHERE id = 1;
-- Query OK, 1 row affected
SELECT remain FROM coupons WHERE id = 1; -- 5
COMMIT;순서는 같은데 A의 UPDATE가 아무 일 없이 성공하고, 바로 다음 SELECT에서 5가 나온다. 방금 전까지 10을 보여 주던 트랜잭션이 B의 변경을 갑자기 보게 된 것이다. MySQL 문서는 이 동작을 숨기지 않는다. 스냅샷은 트랜잭션 안의 SELECT에 적용될 뿐 DML에는 꼭 적용되지 않는다. UPDATE와 DELETE는 다른 트랜잭션이 막 커밋한 최신 행을 대상으로 움직이고, 그렇게 건드린 행은 이후 현재 트랜잭션에도 보인다.
상대식이라면 이 동작이 오히려 반갑다. 최신 값 7에서 2를 빼니 결과가 5로 맞는다. 그런데 애플리케이션이 계산한 remain = 8을 넣으면 B가 뺀 3장은 흔적 없이 사라진다. 갱신 손실이다. 내 쿠폰 수량이 어긋난 이유가 바로 이것이었다. 격리 수준 이름만 믿고 코드를 짰는데, 그 이름이 내가 기대한 걸 약속한 적은 처음부터 없었다.
Jepsen이 2023년 12월에 낸 MySQL 8.0.34 분석도 REPEATABLE READ에서 두 트랜잭션이 같은 버전을 읽고 둘 다 갱신하는 갱신 손실을 여러 번 관찰했다.
스냅샷은 언제 찍히나
두 DB 모두 트랜잭션을 여는 순간에는 스냅샷을 찍지 않는다. PostgreSQL은 트랜잭션 제어문이 아닌 첫 문장이 시작될 때 스냅샷을 잡는다. MySQL InnoDB는 트랜잭션 안의 첫 일관된 읽기, 대개 첫 SELECT가 스냅샷을 만든다.
이 사실을 몰라서 테스트 하나를 반나절 붙잡은 적이 있다. A에서 BEGIN만 실행해 두고 B에서 값을 바꿔 커밋한 뒤 A에서 SELECT를 했더니 새 값이 그대로 보였다. REPEATABLE READ가 고장 났다고 생각하고 설정 파일부터 뒤졌다. 실제로는 A의 첫 SELECT가 B의 커밋 뒤에 실행됐으니 스냅샷도 그때 찍힌 것이다.
트랜잭션을 여는 순간을 기준으로 삼고 싶다면 PostgreSQL에서는 BEGIN 바로 뒤에 아무 SELECT나 하나 실행하면 된다. MySQL에는 전용 구문이 있다.
-- MySQL: 여는 순간 스냅샷을 잡는다
START TRANSACTION WITH CONSISTENT SNAPSHOT;스냅샷이 묶는 범위도 다르다. PostgreSQL에서는 스냅샷이 읽기와 쓰기를 함께 붙든다. 스냅샷 이후에 바뀐 행을 쓰려 하면 오류가 나는 이유다. MySQL에서는 스냅샷이 SELECT만 붙들고 쓰기는 최신 행을 따라간다. 한 트랜잭션 안에서 과거와 현재가 섞여 보일 수 있다는 뜻이다.
두 DB에서 안전하게 차감하는 법
| A의 쓰기 방식 | PostgreSQL | MySQL |
|---|---|---|
상대식 remain = remain - 2 | 직렬화 오류, 재시도 필요 | 최신 값 기준으로 반영 |
읽은 값으로 계산한 remain = 8 | 직렬화 오류, 재시도 필요 | 갱신 손실 |
조건부 WHERE id = 1 AND remain = 10 | 직렬화 오류 | 0건 갱신, 애플리케이션이 감지 |
먼저 SELECT ... FOR UPDATE | 스냅샷 이후 바뀐 행이면 직렬화 오류 | 최신 값을 잠그고 읽음 |
조건을 거는 낙관적 갱신은 MySQL에서 특히 쓸모가 있다. 읽은 값을 WHERE에 다시 적어 두면 B가 먼저 바꾼 경우 0건이 돌아오고, 애플리케이션이 그걸 보고 다시 시도할 수 있다. 버전 컬럼을 둬도 된다. SELECT ... FOR UPDATE로 먼저 잠그는 방법도 있지만 MySQL에서 없는 행에 걸면 갭 잠금 때문에 교착이 생기기 쉽다. 이 이야기는 없는 행에 SELECT FOR UPDATE를 걸면 MySQL이 교착되는 이유에 따로 적었다.
지금 내가 쓰는 방식
요즘은 새 프로젝트에서 DB를 정하면 이 글의 두 세션 실험을 먼저 돌려 본다. 5분이면 끝나는데, 이름만 믿고 짠 코드를 나중에 고치는 데는 며칠이 걸렸다.
수량을 줄이는 코드에는 애플리케이션에서 계산한 값을 넣지 않는다. remain = remain - 1에 WHERE remain > 0을 붙이고 영향받은 행 수를 확인한다. MySQL에서는 이 한 줄로 최신 값에서 안전하게 빠지고, 수량이 바닥나면 0건이 돌아온다. PostgreSQL에서 단순 차감만 할 때는 기본값인 READ COMMITTED에 둔다. 이 수준에서는 같은 UPDATE가 앞선 커밋을 기다렸다가 갱신된 행에 조건을 다시 적용하므로 오류 없이 지나간다. 여러 번 읽은 값이 서로 맞아야 해서 REPEATABLE READ를 고를 때는 앞서 말한 40001 헬퍼를 꼭 함께 붙인다.
어느 DB가 옳다고 말하기는 어렵다. PostgreSQL은 어긋날 기미가 보이면 멈추고, MySQL은 일단 진행하고 판단을 코드에 맡긴다. 쿠폰 3장이 사라진 그 이벤트 이후로, 트랜잭션 코드를 리뷰할 때는 ‘이 값은 언제 어디서 읽은 값인가’부터 묻는다.