Chiko IT
Database

트랜잭션 격리 수준 완전 이해 — PostgreSQL·MySQL 실전 설정

Chiko
12min read

목차

PostgreSQL은 READ COMMITTED, MySQL InnoDB는 REPEATABLE READ를 기본 트랜잭션 격리 수준으로 쓴다. 같은 SQL 표준을 따르는데 기본값이 다르다. 더 헷갈리는 건, 두 DB에서 같은 이름의 격리 수준이 실제로는 다르게 동작한다는 점이다.

그래서, 신입이 입사해서 "결제 쪽에서 동시성 이슈가 보이는데 격리 수준을 올리면 되나요?"라고 물어볼 때마다 같은 설명을 반복하게 된다. 그래서 한번 정리해두기로 했다. 트랜잭션 격리 수준을 손대기 전에 알아야 할 것들이다.

왜 트랜잭션 격리 수준을 알아야 하는가

격리 수준은 동시성과 정합성 사이의 다이얼이다. 다이얼을 높이면 정합성이 좋아지지만 잠금이 늘고 처리량이 떨어진다. 낮추면 빨라지지만 이상 현상이 생긴다.

또한, 문제는 대부분의 백엔드 코드가 이 다이얼을 명시적으로 설정하지 않는다는 점이다. ORM이 알아서 트랜잭션을 열어주고, DB가 알아서 기본값을 적용한다. 그래서 "왜 가끔 잔액이 음수로 떨어지지?", "왜 같은 행을 두 번 읽었는데 값이 다르지?" 같은 증상이 나올 때, 코드부터 봐서는 답이 안 나온다.

따라서, 격리 수준을 모르고 동시성 버그를 잡으려는 건, 콜 스택을 모르고 스택 오버플로우를 디버깅하려는 것과 비슷하다. 결국 어딘가에 락을 박거나, FOR UPDATE를 남발하거나, Redis로 우회하는 식으로 끝난다. 정답은 아니다.

기본값이 다른 이유

반면, PostgreSQL과 MySQL이 기본값을 다르게 잡은 데는 역사적 배경이 있다. MySQL InnoDB는 복제 일관성 때문에 REPEATABLE READ를 선택했다고 알려져 있다. 바이너리 로그를 STATEMENT 포맷으로 복제할 때 Phantom Read가 일어나면 슬레이브와 마스터의 결과가 달라질 수 있었다. 그래서 갭락(gap lock)으로 막아둔다.

PostgreSQL은 MVCC 구현 자체가 다르다. READ COMMITTED 기본값으로도 충분히 안전하다고 본 것 같다. 무엇보다 PG의 REPEATABLE READ는 진짜 스냅숏 격리(Snapshot Isolation)라서 Phantom Read도 막아준다. 굳이 RR을 기본으로 강제할 필요가 없는 셈이다.

4가지 격리 수준과 막아주는 이상 현상

SQL 표준은 4단계의 격리 수준을 정의한다. 각 수준이 어떤 이상 현상을 막아주는지부터 정확히 알아야 한다.

격리 수준 Dirty Read Non-repeatable Read Phantom Read Serialization Anomaly
READ UNCOMMITTED 발생 발생 발생 발생
READ COMMITTED 차단 발생 발생 발생
REPEATABLE READ 차단 차단 발생 가능* 발생
SERIALIZABLE 차단 차단 차단 차단

그런데, 별표는 구현체마다 다르다는 뜻이다. 여기서부터 진짜 함정이 시작된다.

Dirty Read

다른 트랜잭션이 커밋하지 않은 데이터를 읽는 현상이다. 거의 모든 상용 DB가 READ UNCOMMITTED를 실제로는 READ COMMITTED처럼 처리한다. PostgreSQL은 READ UNCOMMITTED를 요청해도 내부적으로 READ COMMITTED로 올려버린다(출처: PostgreSQL 공식 문서, Transaction Isolation). 그래서 Dirty Read는 실무에서 거의 만날 일이 없다.

Non-repeatable Read와 Phantom Read

게다가, Non-repeatable Read는 같은 트랜잭션 안에서 같은 행을 두 번 읽었는데 값이 다른 현상이다. 중간에 다른 트랜잭션이 그 행을 UPDATE하고 커밋했을 때 일어난다. Phantom Read는 같은 조건의 쿼리를 두 번 실행했는데 새로 INSERT된 행이 추가로 보이는 현상이다. UPDATE가 아니라 INSERT가 원인이라는 점에서 다르다.

Serialization Anomaly

여러 트랜잭션의 결과가 어떤 순차 실행 결과와도 일치하지 않는 현상이다. 가장 미묘한 문제다. SERIALIZABLE만이 이를 완전히 막는다. 잔액 검증과 출금이 분리된 결제 로직, 재고 수량 합계와 예약이 분리된 주문 로직에서 잘 나타난다.

PostgreSQL과 MySQL — 같은 이름, 다른 구현

예를 들어, 여기가 핵심이다. 표준 격리 수준이라는 같은 이름을 써도 두 DB의 실제 동작은 다르다.

이처럼, PostgreSQL 9.1부터 REPEATABLE READ는 Snapshot Isolation을 구현한다. 트랜잭션 시작 시점의 스냅숏을 끝까지 유지한다. UPDATE든 INSERT든 그 시점 이후의 변경은 안 보인다. 그래서 Phantom Read가 실질적으로 발생하지 않는다.

실제로, PostgreSQL의 SERIALIZABLE은 SSI(Serializable Snapshot Isolation)다. 락을 안 걸고도 직렬화 가능성을 보장한다. 충돌이 감지되면 트랜잭션을 abort시킨다. 그래서 애플리케이션 쪽에서 재시도 로직을 반드시 넣어야 한다. 이걸 안 넣어두면 운영 환경에서 could not serialize access due to concurrent update 에러가 그대로 사용자에게 노출된다.

이처럼, MySQL InnoDB의 REPEATABLE READ는 다르다. 일관된 읽기는 스냅숏을 쓰지만, 갭락과 넥스트 키 락(next-key lock)으로 Phantom Read를 막는다(출처: MySQL 8.0 공식 문서, InnoDB Transaction Isolation Levels). 잠금 기반이라 PG보다 데드락이 잘 난다. 특히 인덱스 범위 스캔이 들어가는 UPDATE에서 갭락이 의외로 넓게 잡힌다.

반면, MySQL의 SERIALIZABLE은 모든 일반 SELECT를 자동으로 LOCK IN SHARE MODE처럼 처리한다. 단순한 조회조차 공유 잠금을 건다. 성능 저하가 크다. OLTP에서는 거의 안 쓴다.

같은 시나리오, 다른 결과

특히, 간단한 예를 들어본다. 계좌 잔액을 두 트랜잭션이 동시에 갱신하는 상황이다.

-- 트랜잭션 A
BEGIN;
SELECT balance FROM accounts WHERE id = 1;  -- 1000으로 읽힘
-- (여기서 B가 끼어들어 커밋)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

-- 트랜잭션 B (A의 SELECT와 UPDATE 사이에 실행)
BEGIN;
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
COMMIT;

물론, PostgreSQL의 READ COMMITTED에서는 A의 UPDATE가 B의 커밋된 결과를 기준으로 다시 계산한다. 그래서 1000 – 200 – 100 = 700이 된다. 의외로 안전하다.

PostgreSQL의 REPEATABLE READ에서는 A의 UPDATE 시점에 충돌이 감지되어 직렬화 에러가 난다. 애플리케이션이 재시도해야 한다.

그러나, MySQL InnoDB의 REPEATABLE READ에서는 A의 UPDATE가 행 잠금을 기다리다가, B가 커밋되면 그 위에 자기 UPDATE를 적용한다. 결과는 700. 하지만 A가 SELECT로 읽은 잔액(1000)을 신뢰해서 분기 처리하는 코드라면 Lost Update가 그대로 박힌다. 잔액이 음수가 되는 버그는 여기서 자주 나온다.

실전 설정 — 어디서, 어떻게 바꾸나

그런데, 격리 수준은 3개 레이어에서 설정한다. 글로벌, 세션, 트랜잭션 단위다. 우선순위는 트랜잭션 > 세션 > 글로벌 순으로 적용된다.

글로벌 설정

게다가, PostgreSQL은 postgresql.conf에서:

default_transaction_isolation = 'read committed'

그런데, MySQL은 my.cnf에서:

[mysqld]
transaction-isolation = REPEATABLE-READ

물론, 글로벌을 함부로 바꾸지 마라. 모든 커넥션이 영향을 받는다. 운영 중 변경은 신규 커넥션부터 적용된다. 기존 풀에 남아있는 커넥션은 그대로다. 풀을 비우거나 재기동해야 일관되게 적용된다.

세션 단위

커넥션을 열고 첫 번째 트랜잭션 전에 설정한다.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

한편, 여기서 함정이 하나 있다. Connection Pool을 쓸 때 세션 설정을 바꾼 커넥션이 다시 풀로 돌아가면 다음 사용자가 그 설정을 이어받을 수 있다. HikariCP의 transactionIsolation 옵션을 쓰거나, 풀에 돌려보낼 때 리셋을 강제하는 게 안전하다. 아니면 트랜잭션 단위로만 설정하라.

트랜잭션 단위

가장 안전한 방식이다.

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 작업
COMMIT;

특정 비즈니스 로직만 격리 수준을 올리고 싶을 때 이 방식이 맞다. 결제, 재고 차감, 포인트 적립 같은 곳에 부분 적용하면 비용을 최소화할 수 있다.

현재 설정 확인

PostgreSQL:

SHOW transaction_isolation;
SHOW default_transaction_isolation;

따라서, MySQL 8.0:

SELECT @@transaction_isolation;
SELECT @@global.transaction_isolation;

실제로, 운영에서 의심스러운 동시성 버그가 나면 가장 먼저 이걸 찍어본다. ORM이나 풀이 자기 마음대로 바꿔놨을 가능성이 의외로 있다.

격리 수준만으로 막을 수 없는 것

격리 수준을 올린다고 모든 동시성 문제가 사라지는 게 아니다. 대표적인 사각지대가 Lost Update다.

읽고-수정하고-쓰는(read-modify-write) 패턴이 애플리케이션 메모리에서 일어나면, DB가 아무리 RR이어도 막을 수 없다. 두 트랜잭션이 같은 행의 잔액 1000을 읽어가서 각각 메모리에서 -100, -200을 계산한 뒤 900, 800을 쓰면 그대로 100원이 사라진다.

결국, 이런 패턴은 격리 수준 대신 명시적 잠금으로 푸는 게 맞다.

-- 비관적 잠금
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

또는 낙관적 잠금. 행에 version 컬럼을 두고 UPDATE 절에서 WHERE id = ? AND version = ?로 검사한다. 갱신된 행 수가 0이면 실패로 보고 재시도. 트래픽이 많지 않고 충돌이 드물 때 효율적이다.

물론, PostgreSQL의 SERIALIZABLE은 이걸 어느 정도 자동으로 막아준다. 충돌이 감지되면 abort된다. 다만 모든 비즈니스 로직을 SERIALIZABLE로 돌리는 건 비용이 크다. 부분 적용이 현실적이다.

운영 중 자주 마주치는 함정

ORM이 격리 수준을 덮어쓰는 경우가 많다. 확인이 필수다.

따라서, Spring의 @Transactional(isolation = ...)을 명시적으로 지정하면 트랜잭션을 시작할 때 SET TRANSACTION ISOLATION LEVEL을 매번 발행한다. 풀로 돌아간 커넥션이 다음 사용자에게 영향을 줄 수 있다. Spring은 이걸 인지하고 트랜잭션 종료 시점에 원복을 시도하지만, 드라이버나 풀 설정에 따라 빠지는 케이스가 있다. 운영에서 격리 수준이 들쭉날쭉하다면 이쪽을 먼저 의심하라.

Django는 ATOMIC_REQUESTS 기준으로 요청 단위 트랜잭션을 쓰지만 격리 수준은 DB 기본을 그대로 따른다. Postgres 백엔드에서 명시적으로 바꾸려면 DATABASES 설정의 OPTIONS에 isolation 관련 옵션을 명시한다.

MySQL InnoDB의 RR에서 갭락 때문에 발생하는 데드락이 의외로 많다. 인덱스가 없는 컬럼으로 UPDATE를 하거나 범위 조건을 쓰면 락이 넓게 잡힌다. SHOW ENGINE INNODB STATUS의 데드락 로그에서 LOCK_GAP 키워드가 보이면 인덱스부터 점검하라.

따라서, 모니터링 측면에서 한 가지 덧붙인다. pg_stat_activity의 state가 idle in transaction인 세션이 쌓이면 격리 수준과 무관하게 장애가 난다. 트랜잭션을 길게 끌고 가는 코드를 찾아내야 한다. 백그라운드 작업이 트랜잭션을 열어둔 채 외부 API를 호출하는 패턴이 가장 흔한 원인이다.

어디부터 손을 댈까

반면, 신입이 결제 동시성 버그를 들고 왔을 때, 나는 먼저 격리 수준을 묻지 않는다. 어떤 락 전략을 쓰고 있는지, FOR UPDATE가 빠져있는지, 트랜잭션이 너무 길지 않은지를 본다. 격리 수준은 그 다음 순서다.

물론, 당장 실행할 수 있는 일 세 가지를 적어둔다. 첫째, 운영 DB에서 SHOW transaction_isolation을 실행해서 현재 기본값을 확인하라. 둘째, ORM 설정과 어노테이션 중 격리 수준을 명시적으로 지정하는 코드를 찾아내고 의도된 것인지 검토하라. 셋째, 결제·재고·포인트 같은 핵심 로직에 명시적 잠금(FOR UPDATE 또는 version 컬럼)이 들어가 있는지 점검하라.

그래서, 개인적으로는 PostgreSQL 기준으로 READ COMMITTED를 기본으로 두고, 정합성이 중요한 비즈니스 로직만 트랜잭션 단위로 REPEATABLE READ나 SERIALIZABLE로 올리는 방식이 가장 무난한 것 같다.

관련 글

More posts