PostgreSQL의 트랜잭션 격리 수준

SQL 표준은 트랜잭션 격리 수준 네 개를 아래 표처럼 정의합니다. 각 수준에서 어떤 현상이 일어나면 안 되는지를 정한 표입니다.

이 글은 PostgreSQL 18.4에서 각 수준이 실제로 어떻게 동작하는지 psql 세션 두 개로 확인한 기록입니다. 결과를 먼저 세 줄로 적으면 이렇습니다.

• READ UNCOMMITTED는 PostgreSQL에 없습니다. 요청하면 PostgreSQL이 받아주지만 READ COMMITTED로 동작합니다.
• REPEATABLE READ는 표준이 요구하는 것에 더해 Phantom Read도 막습니다.
• 표준의 세 현상 외에 Serialization Anomaly가 있습니다. REPEATABLE READ와 SERIALIZABLE을 가르는 것은 이 현상입니다.

근거는 실측 결과와 PostgreSQL 18 공식문서 13.2절입니다. 문서 인용은 원문 그대로 적었습니다.

실험 환경

격리 수준 실험은 한 세션이 커밋하지 않은 채 멈춰 있는 동안 다른 세션이 읽거나 써야 합니다. psql을 한 번씩 실행하는 방식으로는 안 되고, 세션 두 개를 계속 열어 두어야 합니다. named pipe로 psql 프로세스 두 개를 띄워 두고 SQL을 흘려 넣는 방식을 썼습니다.

# tx-setup.sh — PostgreSQL 컨테이너 + 지속 세션 2개
docker run -d --name pg-tx-lab -e POSTGRES_PASSWORD=lab -e POSTGRES_DB=lab \
  -p 55433:5432 postgres:18
 
docker exec -i pg-tx-lab psql -U postgres -d lab <<'SQL'
CREATE TABLE account (
  id      int PRIMARY KEY,
  owner   text NOT NULL,
  balance int  NOT NULL
);
INSERT INTO account VALUES (1, '승철', 100000), (2, '영희', 50000);
SQL
 
mkdir -p lab
for n in 1 2; do
  mkfifo lab/s$n.in
  sleep 100000 > lab/s$n.in &                      # FIFO 쓰기 끝을 열어 둔다
  docker exec -i pg-tx-lab stdbuf -oL psql -U postgres -d lab --no-psqlrc \
    < lab/s$n.in > lab/s$n.out 2>&1 &
done

# tx.sh <세션번호> "<SQL>" — 세션에 SQL을 보내고 새로 나온 출력만 찍는다
LAB="$(cd "$(dirname "$0")" && pwd)/lab"
n="$1"; shift
IN="$LAB/s$n.in"; OUT="$LAB/s$n.out"
before=$(wc -c < "$OUT")
TOKEN="__TX_DONE_$(date +%s%N)__"
{ printf '%s\n' "$*"; printf '\\echo %s\n' "$TOKEN"; } > "$IN"
for _ in $(seq 1 50); do
  if tail -c +$((before+1)) "$OUT" | grep -q "$TOKEN"; then
    tail -c +$((before+1)) "$OUT" | grep -v "$TOKEN"; exit 0
  fi
  sleep 0.1
done
tail -c +$((before+1)) "$OUT" | grep -v "$TOKEN"
echo "[세션 $n 응답 없음 — 락 대기 중]"

./tx.sh 1 "BEGIN;"처럼 쓰면 세션 1에 BEGIN이 들어가고, 5초 안에 응답이 없으면 락에 걸린 것으로 표시합니다. 두 세션이 실제로 다른 백엔드 프로세스인지 pg_backend_pid()로 확인했습니다.

./tx.sh 1 "SELECT pg_backend_pid();"   → 58356
./tx.sh 2 "SELECT pg_backend_pid();"   → 58355

격리 수준이 막으려는 네 가지 현상

문서 13.2절이 정의하는 현상은 네 가지입니다. 격리 수준은 이 중 무엇을 막을지 정하는 단계입니다.

네 번째 현상은 표준의 표에는 없고, PostgreSQL 문서의 표(Table 13.1)에 열로 추가되어 있습니다. 뒤에서 Write Skew라는 이름으로 다룹니다.

SQL 표준은 "이 수준에서는 이 현상들이 일어나면 안 된다"라는 금지 목록으로만 격리 수준을 정의합니다. 구현 방법은 정하지 않습니다. 그래서 표보다 더 많이 막는 구현도 표준을 지킨 것입니다.

This is acceptable under the SQL standard because the standard specifies which anomalies must not occur at certain isolation levels; higher guarantees are acceptable.

UPDATE는 덮어쓰지 않는다

PostgreSQL이 격리를 구현하는 바탕을 먼저 봐야 뒤의 결과가 읽힙니다. PostgreSQL의 UPDATE는 값을 고치지 않습니다. 새 버전 행을 하나 더 만들고, 옛 버전에는 어느 트랜잭션이 자기를 대체했는지 적습니다.

세션 1이 트랜잭션을 열고 승철 계좌에서 3만 원을 뺀 뒤 커밋하지 않은 상태에서, 두 세션이 같은 id = 1 행을 읽은 결과입니다.

-- 세션 1
BEGIN;
UPDATE account SET balance = balance - 30000 WHERE id = 1;
SELECT ctid, xmin, xmax, balance FROM account WHERE id = 1;
 
-- 세션 2 (같은 시각)
SELECT ctid, xmin, xmax, balance FROM account WHERE id = 1;

세션 1:  ctid=(0,5)  xmin=769  xmax=0    balance=70000
세션 2:  ctid=(0,4)  xmin=768  xmax=769  balance=100000

ctid는 행의 물리적 위치입니다. 두 세션이 서로 다른 자리에 있는 다른 행을 읽고 있습니다. xmin은 그 버전을 만든 트랜잭션 번호, xmax는 그 버전을 대체하거나 지운 트랜잭션 번호입니다. xmax가 0이면 아직 살아 있는 버전입니다.

세션 2가 보는 옛 버전에는 xmax=769가 적혀 있습니다. 769번이 이 행을 대체했다는 표시입니다. 그런데 769번이 아직 커밋하지 않았으므로 세션 2는 이 표시를 무시하고 옛 버전을 유효한 것으로 봅니다.

pageinspect 확장으로 페이지를 열어 보면 두 버전이 물리적으로 나란히 있습니다.

CREATE EXTENSION pageinspect;
SELECT lp AS slot, t_xmin AS xmin, t_xmax AS xmax
FROM heap_page_items(get_raw_page('account', 0))
WHERE t_xmin IS NOT NULL ORDER BY lp;

 slot | xmin | xmax
------+------+------
    4 |  768 |  771     ← 옛 버전 100000 (771이 대체했다고 표시됨, 771은 커밋 전)
    6 |  771 |    0     ← 새 버전 70000 (771만 볼 수 있음)

읽는 쪽은 lock을 잡지 않습니다. 행마다 xmin과 xmax를 자기 스냅샷과 대조해서, 스냅샷 시점에 커밋되어 있던 버전을 고릅니다. 스냅샷은 그 시점에 커밋되어 있던 트랜잭션 목록입니다.

이 구조에서 격리 수준을 가르는 것은 하나뿐입니다. 스냅샷을 언제 뜨느냐입니다.

READ UNCOMMITTED은 없다

문서가 직접 말합니다.

In PostgreSQL, you can request any of the four standard transaction isolation levels, but internally only three distinct isolation levels are implemented, i.e., PostgreSQL's Read Uncommitted mode behaves like Read Committed.

실측입니다. 세션 1이 승철 계좌에서 3만 원을 빼고 커밋하지 않은 상태에서, 세션 2가 READ UNCOMMITTED로 읽었습니다.

-- 세션 1
BEGIN;
UPDATE account SET balance = balance - 30000 WHERE id = 1;   -- 커밋하지 않음
 
-- 세션 2
BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW transaction_isolation;
SELECT balance FROM account WHERE id = 1;

 transaction_isolation
-----------------------
 read uncommitted
 
 balance
---------
  100000

SHOW는 요청한 값을 그대로 돌려주지만, 세션 2가 읽은 값은 커밋 전 원래 값 100000입니다. Dirty Read는 일어나지 않았습니다. 커밋 안 된 버전은 어느 스냅샷에서도 고를 대상이 아니기 때문입니다.

이어서 세션 1이 커밋한 뒤, 세션 2가 같은 트랜잭션 안에서 다시 읽었습니다.

세션 1: COMMIT
세션 2: SELECT balance FROM account WHERE id = 1;  → 70000

같은 트랜잭션 안에서 같은 SELECT의 답이 바뀌었습니다. REPEATABLE READ였다면 100000이 유지됐어야 합니다. 이 트랜잭션은 READ COMMITTED로 동작하고 있습니다.

SHOW transaction_isolation이 read uncommitted를 돌려준다고 해서 그 수준이 실제로 동작하는 것은 아닙니다. 동작은 실제 결과로 확인해야 합니다.

READ COMMITTED: 구문마다 스냅샷

Read Committed is the default isolation level in PostgreSQL.

READ COMMITTED는 각 SQL 구문이 시작할 때 스냅샷을 새로 뜹니다. 그래서 같은 트랜잭션 안에서 같은 SELECT를 다시 실행하면 그 사이에 커밋된 남의 변경이 보입니다.

Non-repeatable Read

세션 2가 승철 잔액을 읽는 사이 세션 1이 1만 원을 더하고 커밋했습니다.

세션 2: BEGIN ISOLATION LEVEL READ COMMITTED;
세션 2: SELECT balance FROM account WHERE id = 1;   → 100000
세션 1: UPDATE account SET balance = balance + 10000 WHERE id = 1;   (자동 커밋)
세션 2: SELECT balance FROM account WHERE id = 1;   → 110000

Phantom Read

세션 2가 balance >= 50000인 행 수를 세는 사이 세션 1이 조건에 맞는 행을 넣고, 이어서 지웠습니다.

세션 2: BEGIN ISOLATION LEVEL READ COMMITTED;
세션 2: SELECT count(*) FROM account WHERE balance >= 50000;   → 3
세션 1: INSERT INTO account VALUES (5, '지훈', 60000);
세션 2: SELECT count(*) FROM account WHERE balance >= 50000;   → 4
세션 1: DELETE FROM account WHERE id = 1;
세션 2: SELECT count(*) FROM account WHERE balance >= 50000;   → 3

없던 행이 나타나고 있던 행이 사라졌습니다. 구문마다 스냅샷을 새로 뜨기 때문에 두 번째 SELECT의 스냅샷에는 세션 1의 INSERT가 커밋된 트랜잭션으로 들어 있습니다.

같은 행을 동시에 고치면

두 트랜잭션이 같은 행을 고치려 하면 나중 쪽이 먼저 쪽을 기다립니다. 문서 원문입니다.

In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). [...] If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row.

실측입니다. 세션 1이 승철 계좌에서 1만 원을 빼고 커밋하지 않은 상태에서 세션 2가 같은 행에서 1만 원을 빼려 했습니다.

세션 1: BEGIN; UPDATE account SET balance = balance - 10000 WHERE id = 1;   → UPDATE 1
세션 2: BEGIN ISOLATION LEVEL READ COMMITTED;
세션 2: UPDATE account SET balance = balance - 10000 WHERE id = 1;   → [락 대기 중]
세션 1: COMMIT
세션 2:                                                               → UPDATE 1
세션 2: COMMIT
최종 잔액: 80000

세션 2의 UPDATE는 세션 1이 커밋할 때까지 기다렸다가, 갱신된 버전 90000에 자기 연산을 적용해 80000을 만들었습니다. 두 번의 인출이 모두 반영됐고, 먼저 쓴 갱신이 나중 갱신에 덮여 사라지는 일(Lost Update)은 없었습니다.

REPEATABLE READ: 첫 구문에서 스냅샷 한 번

This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction.

BEGIN이 아니라 첫 SQL 구문에서 스냅샷을 뜨고, 트랜잭션이 끝날 때까지 그 스냅샷만 씁니다. 첫 구문이 SELECT든 UPDATE든 그때 뜹니다.

Non-repeatable Read와 Phantom Read가 모두 없다

READ COMMITTED에서 돌린 것과 같은 실험을 REPEATABLE READ로 돌렸습니다.

세션 2: BEGIN ISOLATION LEVEL REPEATABLE READ;
세션 2: SELECT balance FROM account WHERE id = 1;   → 100000
세션 1: UPDATE account SET balance = balance + 10000 WHERE id = 1;
세션 2: SELECT balance FROM account WHERE id = 1;   → 100000

세션 2: BEGIN ISOLATION LEVEL REPEATABLE READ;
세션 2: SELECT count(*) FROM account WHERE balance >= 50000;   → 3
세션 1: INSERT INTO account VALUES (4, '민수', 90000);         (커밋됨)
세션 2: SELECT count(*) FROM account WHERE balance >= 50000;   → 3
세션 2: COMMIT;
세션 2: SELECT count(*) FROM account WHERE balance >= 50000;   → 4

DELETE도 같습니다. 세션 1이 조건에 맞는 행을 지우고 커밋해도 세션 2는 트랜잭션이 끝날 때까지 그 행을 계속 봤습니다.

INSERT된 행이 안 보이는 이유는 앞에서 본 행 버전 구조 그대로입니다. 세션 1이 넣은 민수 행은 xmin=774였습니다. 세션 2의 스냅샷은 774가 시작되기 전에 떴으므로, 774는 세션 2의 커밋된 트랜잭션 목록에 없습니다. 774가 실제로 커밋했는지와 무관하게 이 행은 세션 2에게 안 보입니다. DELETE된 행은 반대로 xmax가 스냅샷 목록에 없는 번호라서 아직 안 지워진 행으로 남습니다.

SQL 표준은 REPEATABLE READ에서 Phantom Read를 허용합니다. PostgreSQL 문서의 표(Table 13.1)는 이 칸을 이렇게 적어 뒀습니다.

Repeatable Read — Phantom Read: Allowed, but not in PG

표준이 이 칸을 열어 둔 이유는 PostgreSQL 문서 밖의 이야기입니다. 격리를 읽은 행에 lock을 거는 방식으로 구현하면, 이미 읽은 행은 남이 못 고치니 Non-repeatable Read는 막히지만 아직 존재하지 않는 행에는 lock을 걸 수 없어서 새 행이 들어오는 것은 못 막습니다. 표준은 그런 구현도 REPEATABLE READ로 인정되도록 하한선을 그어 두었습니다. 스냅샷 방식은 새 행이든 기존 행이든 xmin/xmax 대조 하나로 처리하므로 그런 경우가 생기지 않습니다.

같은 행을 동시에 고치면

REPEATABLE READ에서는 기다린 뒤 적용하는 대신 오류가 납니다.

But if the first updater commits (and actually updated or deleted the row, not just locked it) then the repeatable read transaction will be rolled back with the message: ERROR: could not serialize access due to concurrent update. When an application receives this error message, it should abort the current transaction and retry the whole transaction from the beginning.

세션 1: BEGIN; UPDATE account SET balance = balance - 10000 WHERE id = 1;   → UPDATE 1
세션 2: BEGIN ISOLATION LEVEL REPEATABLE READ;
세션 2: UPDATE account SET balance = balance - 10000 WHERE id = 1;   → [락 대기 중]
세션 1: COMMIT
세션 2:   → ERROR:  could not serialize access due to concurrent update
세션 2: COMMIT   → ROLLBACK
최종 잔액: 90000

세션 2는 자기 스냅샷의 100000을 근거로 계산한 결과를 이미 바뀐 행에 적용할 수 없으니 취소됩니다. 애플리케이션이 트랜잭션 전체를 다시 시작하면, 새 스냅샷에서 90000을 읽고 다시 계산합니다.

REPEATABLE READ가 못 막는 것: Write Skew

여기까지 보면 REPEATABLE READ가 표준의 세 현상을 다 막습니다. 그런데 문서의 Table 13.1은 REPEATABLE READ의 Serialization Anomaly 칸을 "Possible"로 표시합니다. 그 현상을 만드는 시나리오입니다.

계좌가 두 개 있고, 업무 규칙은 "두 계좌 합계는 100,000원 이상을 유지해야 한다"입니다. 애플리케이션은 인출 전에 합계를 확인합니다. 두 세션이 이 로직을 동시에 실행하되, 서로 다른 계좌에서 5만 원씩 뺍니다.

-- 세션 1                                       -- 세션 2
BEGIN ISOLATION LEVEL REPEATABLE READ;          BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM account;  -- 150000    SELECT sum(balance) FROM account;  -- 150000
-- 5만 빼도 10만 남는다, 진행                    -- 5만 빼도 10만 남는다, 진행
UPDATE account SET balance = balance - 50000    UPDATE account SET balance = balance - 50000
  WHERE owner = '승철';                           WHERE owner = '영희';
COMMIT;                                         COMMIT;

세션 1 합계 조회  → 150000
세션 2 합계 조회  → 150000
세션 1 UPDATE 승철 → UPDATE 1
세션 2 UPDATE 영희 → UPDATE 1
세션 1 COMMIT     → COMMIT
세션 2 COMMIT     → COMMIT
최종: 승철 50000 / 영희 0 / 합계 50000

두 COMMIT이 모두 성공했고 합계는 50,000원입니다. 규칙이 깨졌습니다.

세션 하나만 떼어 보면 둘 다 올바른 판단입니다. 합계 15만에서 5만을 빼면 10만이니 규칙 안입니다. 그런데 세션 1 다음에 세션 2를 실행했다면 세션 2는 합계 10만을 보고 인출을 거부했을 것이고, 반대 순서여도 마찬가지입니다. 어느 순서로 하나씩 실행해도 나올 수 없는 결과가 나왔습니다. 이것이 Serialization Anomaly이고, 이 형태를 Write Skew라고 부릅니다.

REPEATABLE READ가 이 현상을 못 잡는 이유는 검사 대상 때문입니다. REPEATABLE READ의 충돌 검사는 "내가 고치려는 행을 남이 먼저 고쳐 커밋했는가" 하나입니다. 두 세션이 고친 행이 서로 다르니 검사할 것이 없습니다. 각자가 읽은 합계가 낡았는지는 검사하지 않습니다.

SERIALIZABLE: 감시자가 검사하는 규칙

In fact, this isolation level works exactly the same as Repeatable Read except that it also monitors for conditions which could make execution of a concurrent set of serializable transactions behave in a manner inconsistent with all possible serial (one at a time) executions of those transactions.

REPEATABLE READ와 똑같이 동작하되, 감시자가 하나 더 붙습니다. 감시자가 하는 일은 세 단계이고, 출처는 문서 13.2.3절과 소스 트리의 README-SSI입니다.

1단계: 읽은 자리에 표시를 남긴다

SERIALIZABLE 트랜잭션이 읽은 행(또는 행이 든 page, 테이블 전체)에 SIREAD lock을 겁니다. 이름에 lock이 붙어 있지만 이 lock은 다른 트랜잭션을 기다리게 하지 않습니다.

To guarantee true serializability PostgreSQL uses predicate locking, which means that it keeps locks which allow it to determine when a write would have had an impact on the result of a previous read from a concurrent transaction, had it run first. In PostgreSQL these locks do not cause any blocking and therefore can not play any part in causing a deadlock.

pg_locks에서 확인됩니다. 같은 SELECT sum(balance)를 두 수준에서 실행하고 세션별로 셌습니다.

SELECT count(*) FROM pg_locks WHERE mode = 'SIReadLock' AND pid = <세션 pid>;

SERIALIZABLE 세션:     1   (locktype=relation, relation=account)
REPEATABLE READ 세션:  0

REPEATABLE READ는 이 표시를 남기지 않습니다. 두 수준의 구현 차이가 이것입니다.

2단계: 남이 그 표시 위에 쓰면 간선을 하나 그린다

Modifying a heap tuple creates a rw-conflict with any transaction that holds a SIREAD lock on that tuple, or on the page or relation that contains it.

동시에 실행 중인 다른 트랜잭션이 표시가 있는 행을 고치면, PostgreSQL은 읽은 트랜잭션에서 쓴 트랜잭션 방향으로 rw-conflict 간선을 하나 기록합니다. 뜻은 "읽은 쪽이 직렬 순서에서 앞이어야 한다"입니다. 읽은 쪽이 쓴 쪽의 결과를 못 봤으니, 읽은 쪽이 먼저 실행된 것으로 쳐야 앞뒤가 맞습니다.

3단계: 간선 두 개가 한 트랜잭션을 관통하면 취소한다

README-SSI가 정의하는 위험 구조입니다.

      Tin ------> Tpivot ------> Tout
            rw             rw

어떤 트랜잭션에 들어오는 간선과 나가는 간선이 둘 다 생기고, 나가는 쪽 끝(Tout)이 셋 중 가장 먼저 커밋했으면, PostgreSQL은 가운데(Tpivot)를 취소합니다.

We only roll back a transaction if Tout commits before Tpivot and Tin.

이 검사는 완전한 순환 탐색이 아닙니다. 그래서 실제로는 문제없는 트랜잭션도 취소할 수 있습니다.

It will produce some false positives (where a transaction is rolled back even though there would not have been an anomaly), but will never let an anomaly occur.

Write Skew를 SERIALIZABLE로 돌리면

같은 시나리오를 SERIALIZABLE로 돌렸습니다.

세션 1 합계 조회  → 150000
세션 2 합계 조회  → 150000
세션 1 UPDATE 승철 → UPDATE 1
세션 2 UPDATE 영희 → UPDATE 1     (기다리지 않고 즉시)
세션 1 COMMIT     → COMMIT
세션 2 COMMIT     → ERROR:  could not serialize access due to read/write dependencies among transactions
                     DETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.
                     HINT:  The transaction might succeed if retried.
최종: 승철 50000 / 영희 50000 / 합계 100000

규칙에 대입하면 이렇습니다. 세션 1이 테이블을 읽었고(SIREAD), 세션 2가 영희 행을 썼으니 세션 1에서 세션 2로 가는 간선이 생깁니다. 세션 2도 테이블을 읽었고 세션 1이 승철 행을 썼으니 세션 2에서 세션 1로 가는 간선도 생깁니다. 트랜잭션이 둘뿐이면 Tin과 Tout이 같은 트랜잭션이고, 양쪽 모두 들어오는 간선과 나가는 간선을 갖습니다. 세션 1이 먼저 커밋했으므로 세션 2의 커밋 시도에서 세션 2가 pivot으로 취소됐습니다.

두 UPDATE는 모두 기다리지 않고 즉시 실행됐습니다. PostgreSQL은 두 트랜잭션을 순서대로 실행하지 않았습니다. 동시에 실행시킨 뒤 커밋 시점에 한쪽을 취소했습니다.

재시도하면

HINT대로 세션 2를 처음부터 다시 실행했습니다.

세션 2: BEGIN ISOLATION LEVEL SERIALIZABLE;
세션 2: SELECT sum(balance) FROM account;   → 100000
-- 5만 빼면 5만, 규칙 위반. 인출 거부.
세션 2: ROLLBACK;

새 스냅샷에서는 세션 1이 커밋한 결과가 보이고, 애플리케이션이 인출을 거부합니다. SERIALIZABLE은 규칙을 대신 지켜 주지 않습니다. 낡은 스냅샷으로 판단한 트랜잭션을 취소하고 다시 실행하게 만듭니다. 문서가 재시도를 전제로 두는 이유입니다.

like the Repeatable Read level, applications using this level must be prepared to retry transactions due to serialization failures.

취소 시점은 상대의 커밋이 아니다

세션 2가 먼저 커밋하고 세션 1이 나중에 쓰는 순서로 바꿔 봤습니다.

세션 1 합계 조회  → 150000
세션 2 합계 조회  → 150000
세션 2 UPDATE 영희 → UPDATE 1
세션 2 COMMIT     → COMMIT          ← 세션 1이 읽은 행이 바뀌어 커밋됨
세션 1 SELECT sum → 150000          ← 취소되지 않음, 자기 스냅샷 그대로
세션 1 SELECT id=2 → 영희 50000      ← 여전히 정상
세션 1 UPDATE 승철 → ERROR: ... Canceled on identification as a pivot, during write.

세션 2가 커밋하는 순간 세션 1이 취소되지 않았습니다. 세션 1은 그 뒤에도 SELECT를 두 번 더 정상 실행했습니다. 취소는 세션 1이 직접 쓰기를 시도해 두 번째 간선이 생기는 순간 일어났습니다. 읽기는 표시만 남기고 간선을 만들지 않습니다. 간선은 쓰기가 만들기 때문에, 취소 시점은 항상 두 번째 간선이 생기는 쓰기 또는 커밋입니다.

읽은 값이 낡았다는 것만으로는 취소되지 않는다

세션 2가 합계를 읽지 않고, 인덱스로 영희 행만 찾아 고치도록 바꿨습니다. enable_seqscan = off로 Index Scan using account_pkey를 확인하고 돌렸습니다.

세션 1 합계 조회  → 150000
세션 2 UPDATE id=2 → UPDATE 1        (합계를 읽지 않음)
세션 2 COMMIT     → COMMIT
세션 1 UPDATE 승철 → UPDATE 1
세션 1 COMMIT     → COMMIT           ← 성공
최종 합계: 50000

SERIALIZABLE인데 규칙이 깨졌습니다. 그런데 이것은 올바른 동작입니다. 세션 1 다음에 세션 2를 실행하면 이 결과가 그대로 나옵니다. 세션 1이 15만을 보고 승철에서 빼서 10만, 그 뒤 세션 2가 확인 없이 영희에서 빼서 5만. 규칙이 깨진 것은 동시성 때문이 아니라 세션 2가 검사를 안 해서입니다.

간선으로 보면 세션 1에서 세션 2로 가는 간선 하나뿐입니다. 세션 2는 승철 행에 SIREAD 표시를 남기지 않았으므로 세션 1이 승철 행을 써도 두 번째 간선이 생기지 않습니다. pivot이 없으니 취소할 것도 없습니다. SERIALIZABLE이 약속하는 것은 어떤 직렬 순서와 같은 결과이지, 읽은 값이 끝까지 최신이라는 것이 아닙니다.

seq scan은 테이블 전체에 표시를 남긴다

바로 위 실험에서 enable_seqscan = off를 쓴 이유입니다. 테이블이 작아서 WHERE id = 2도 seq scan으로 실행되는데, seq scan은 읽은 행이 아니라 테이블 전체에 SIREAD 표시를 남깁니다. 그러면 세션 2가 테이블을 읽은 것이 되어 두 번째 간선이 생기고 세션 1이 취소됩니다. 문서가 성능 항목에서 이 점을 경고합니다.

A sequential scan will always necessitate a relation-level predicate lock. This can result in an increased rate of serialization failures.

SERIALIZABLE을 쓰는 테이블에서 seq scan이 자주 돌면, 실제로는 겹치지 않는 트랜잭션끼리도 취소가 늘어납니다.

정리

네 수준을 네 현상에 대해 실측한 결과입니다. Serialization Anomaly 열의 READ UNCOMMITTED·READ COMMITTED 칸은 실측하지 않았고 문서 Table 13.1의 "Possible"을 옮겼습니다.

표준이 허용하는데 PostgreSQL이 막는 칸은 두 곳입니다. READ UNCOMMITTED 행 전체가 READ COMMITTED와 같고, REPEATABLE READ의 Phantom Read 칸이 없음입니다. 마지막 열은 표준의 세 현상 밖에 있고, REPEATABLE READ와 SERIALIZABLE을 가르는 칸은 그 열 하나입니다.

세 수준의 동작을 한 줄씩 적으면 이렇습니다.

• READ COMMITTED는 구문마다 스냅샷을 새로 뜹니다. 같은 행 충돌은 기다렸다가 갱신된 버전에 적용합니다.
• REPEATABLE READ는 첫 구문에서 스냅샷을 한 번 뜨고 끝까지 씁니다. 같은 행 충돌은 오류로 취소합니다. 서로 다른 행을 고치는 Write Skew는 못 잡습니다.
• SERIALIZABLE은 REPEATABLE READ에 감시자를 더한 것입니다. 읽은 자리에 SIREAD 표시를 남기고, 남이 그 위에 쓰면 rw-conflict 간선을 기록하고, 한 트랜잭션에 들어오는 간선과 나가는 간선이 둘 다 생기면 취소합니다. 재시도 코드가 필요합니다.

어느 것을 쓸지는 로직의 형태에 따라 정합니다.

• 조회와 단순 갱신은 기본값 READ COMMITTED로 충분합니다.
• 한 트랜잭션 안에서 여러 번 읽은 값이 서로 맞아야 하면 REPEATABLE READ를 씁니다.
• 읽은 값을 근거로 다른 행을 고치는 로직이 동시에 돌 수 있으면 SERIALIZABLE을 쓰고 재시도를 붙이거나, 읽을 때 SELECT ... FOR UPDATE로 행을 잡아 두 번째 트랜잭션이 갱신된 값을 읽게 만듭니다.

마치며

표준의 격리 수준 표는 각 수준이 최소한 막아야 하는 현상을 정한 것이고, 구현 방법은 정하지 않습니다. 각 DBMS가 실제로 무엇을 막는지는 그 DBMS의 문서와 실측이 답합니다. PostgreSQL은 행 버전과 스냅샷으로 구현해서 표준이 요구하는 것보다 두 칸을 더 막고, 표준의 세 현상 밖에 있는 Serialization Anomaly가 REPEATABLE READ와 SERIALIZABLE을 가릅니다.

실험 스크립트는 글 앞부분에 있으니 docker만 있으면 그대로 돌려 볼 수 있습니다. 긴 글 읽어주셔서 감사합니다.