[PostgreSQL] 격리수준 Read Committed vs Repeatable Read 같은 트랜잭션 안에서 값이 바뀔 때

설치·접속: PostgreSQL 설치와 접속

부제: 리포트를 만드느라 한 트랜잭션 안에서 같은 행을 두 번 읽는데, 그 사이 다른 세션이 값을 바꿔 커밋해버리는 상황

-- 세션 A: 트랜잭션을 열고, 잠깐 텀을 둔 뒤 같은 행을 두 번 읽는다
BEGIN ISOLATION LEVEL READ COMMITTED;   -- 기본값
SELECT price FROM m7_prod WHERE id=1;   -- 첫 읽기
-- (이 사이에 세션 B가 UPDATE price=250; COMMIT)
SELECT price FROM m7_prod WHERE id=1;   -- 둘째 읽기
COMMIT;

########## READ COMMITTED ##########
 A: 첫 SELECT (RC)
-------------------
            100.00
-- B가 250으로 UPDATE+COMMIT 완료
 A: 둘째 SELECT (RC)
---------------------
              250.00   <- 같은 트랜잭션인데 값이 바뀜 (non-repeatable read)

########## REPEATABLE READ ##########
 A: 첫 SELECT (RR)
-------------------
            100.00
-- B가 250으로 UPDATE+COMMIT 완료
 A: 둘째 SELECT (RR)
---------------------
              100.00   <- 트랜잭션 시작 시점 스냅샷 유지, 안 바뀜

PostgreSQL 기본 격리수준인 READ COMMITTED에서는 매 SQL 문마다 새 스냅샷을 뜬다. 그래서 트랜잭션 도중 다른 세션이 커밋하면, 같은 트랜잭션 안에서 두 번째 읽기가 첫 번째와 다른 값을 돌려준다. 반면 REPEATABLE READ는 트랜잭션의 첫 쿼리 시점에 스냅샷을 고정하고 커밋까지 그걸 유지하므로, 중간에 누가 뭘 커밋하든 트랜잭션 내내 같은 값을 본다. 여러 테이블을 걸쳐 일관된 집계를 내야 하는 리포트·정산 배치라면 REPEATABLE READ가 정답이다. 그냥 단건을 읽고 바로 끝나는 요청이면 기본값으로 충분하다.

이렇게도 쓴다

트랜잭션을 시작하면서 격리수준을 명시한다.

BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

 

이미 BEGIN 한 상태에서 첫 쿼리 전에 바꾼다. (조합: SET TRANSACTION)

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT ...;

 

세션 전체의 기본 격리수준을 바꾼다. (조합: SET SESSION)

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL REPEATABLE READ;

 

지금 이 트랜잭션이 어떤 격리수준인지 확인한다.

SHOW transaction_isolation;   -- read committed / repeatable read / serializable

 

리포트 스냅샷을 확실히 고정하되 쓰기는 안 한다고 선언한다. (조합: READ ONLY)

BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;

 

데이터베이스 기본 격리수준을 바꾼다. (조합: ALTER DATABASE)

ALTER DATABASE shop SET default_transaction_isolation = 'repeatable read';