What Isolation Levels Stop and What They Do Not
한국어 원문으로 표시합니다.
목표
읽기에서 본 표의 네 번째 열, 곧 어떤 격리 수준도 잡아 주지 않는 쓰기 편향을 진짜 PostgreSQL 에서 직접 만들어 봅니다. 그리고 그것을 막는 세 가지 도구인 행 잠금, 직렬화 가능 수준, 작업 대기열용 건너뛰기를 차례로 써 봅니다.
왜 중요한가
동시성 버그를 만나면 "격리 수준을 올려라"라는 처방이 먼저 나옵니다. 이 실습은 그 처방이 언제 듣고 언제 안 듣는지를 숫자로 보여 줍니다. 2단계에서 잃은 100원은 4단계에서 오류로 바뀌고, 5단계에서는 오류조차 없이 규칙이 깨집니다.
특히 눈여겨볼 것은 같은 900이 두 번 나온다는 점입니다. 한 번은 갱신이 사라져서, 한 번은 갱신이 거부되어서입니다. 값만 보면 구분되지 않고, 어느 쪽인지는 오류를 받았는지로만 알 수 있습니다. 운영에서 이 차이가 신고되는 사고와 영영 모르는 사고를 가릅니다.
환경
이 파드 안에 PostgreSQL 16 이 돕니다. 접속은 이렇습니다.
export PGPASSWORD=lab
psql -X -q -v ON_ERROR_STOP=1 -h 127.0.0.1 -U lab -d labdb
세션 두 개가 겹쳐야 하는 실습입니다. 터미널은 하나뿐이므로 한쪽을 배경으로 띄웁니다. 트랜잭션 안에 SELECT pg_sleep(3); 을 넣어 두면 그 세션이 트랜잭션을 연 채로 기다립니다.
psql ... -f /root/txn/lost.sql > /root/txn/lost_a.out 2>&1 &
sleep 1
psql ... -f /root/txn/lost.sql > /root/txn/lost_b.out 2>&1
wait
산출물은 모두 /root/txn/ 아래에 둡니다. 시드 표(customers 등)는 건드리지 않습니다.
단계
/root/txn/schema.sql로purse,oncall,tasks세 표를 만들어 적재합니다./root/txn/lost.sql로 계좌 1에서 갱신 손실을 재현해/root/txn/02-lost.txt에 남깁니다./root/txn/forupdate.sql로 계좌 2를 행 잠금으로 지켜/root/txn/03-forupdate.txt에 남깁니다./root/txn/repeatable.sql로 계좌 3에서 직렬화 오류를 받아/root/txn/04-repeatable.txt에 남깁니다./root/txn/skew.sql로rr팀의 당직 규칙을 깨뜨려/root/txn/05-skew.txt에 남깁니다./root/txn/serializable.sql로ssi팀의 규칙을 지켜/root/txn/06-serializable.txt에 남깁니다./root/txn/claim.sql로 두 워커가 큐를 나눠 가져가게 하고/root/txn/07-queue.txt에 남깁니다.- 배운 것을 정리해
/root/txn/08-notes.md에 남깁니다.
참고
- 표 목록은
\dt, 컬럼 구조는\d purse로 볼 수 있습니다. SELECT balance AS b FROM purse WHERE id = 1 \gset은 조회 결과를 psql 변수b에 담습니다. 뒤에서:b로 씁니다.- 오류에 SQLSTATE 를 함께 보려면 파일 맨 위에
\set VERBOSITY verbose를 넣습니다. - 각 단계는 시작할 때 대상 계좌나 팀을 초기값으로 되돌리세요. 그래야 몇 번을 다시 해도 같은 결과가 나옵니다.
- 흔한 실수 1:
UPDATE purse SET balance = balance - 100으로 쓰는 것입니다. 그러면 데이터베이스가 최신 값을 읽어 계산하므로 손실이 재현되지 않습니다. 읽은 값을 그대로 쓰는:b - 100이어야 합니다. - 흔한 실수 2: 두 세션의 시작 간격을 대기 시간보다 길게 두는 것입니다. 첫 세션이 이미 커밋한 뒤에 두 번째가 읽으면 아무 일도 일어나지 않습니다.
실습용 표 세 개를 만든다
/root/txn/schema.sql 을 만들어 적재합니다. purse(id, owner, balance) 에 잔고 1000짜리 계좌 세 개(id 1, 2, 3), oncall(id, team, name, on_duty) 에 rr 팀 두 명(id 1, 2)과 ssi 팀 두 명(id 3, 4)을 모두 당직으로, tasks(id, state, worker) 에 queued 상태 작업 여섯 건(id 1~6)을 넣습니다.
접속은 psql -h 127.0.0.1 -U lab -d labdb 이고 비밀번호는 lab 입니다. export PGPASSWORD=lab 을 해 두면 매번 묻지 않습니다.
여러 번 적재해도 같은 상태가 되도록 DROP TABLE IF EXISTS 로 시작하세요. 뒤 단계들이 이 표를 계속 되돌려 쓰기 때문에 멱등성이 중요합니다.
작업 여섯 건은 INSERT ... SELECT g, 'queued', null FROM generate_series(1, 6) g 로 한 번에 넣을 수 있습니다.
갱신 손실을 재현한다
/root/txn/lost.sql 을 만들어 계좌 1에서 100을 빼는 트랜잭션을 두 세션이 겹쳐 돌립니다. 잔고를 먼저 읽고, 3초 쉬고, 읽은 값에서 100을 뺀 절대값을 씁니다. 결과를 /root/txn/02-lost.txt 에 before, after, expected 세 줄로 남깁니다.
psql 은 SELECT balance AS b FROM purse WHERE id = 1 \gset 으로 조회 결과를 변수에 담고, 뒤에서 :b 로 씁니다. SELECT pg_sleep(3); 은 트랜잭션을 연 채로 3초를 기다리게 합니다.
한 세션을 배경으로 띄우고 1초 뒤에 두 번째 세션을 돌리면 두 세션이 겹칩니다. 둘 다 커밋 전의 1000을 읽으므로 둘 다 900을 씁니다. 두 번 출금했는데 잔고는 한 번만 줄어듭니다.
돌리기 전에 계좌 1을 1000으로 되돌려 두세요. 그래야 몇 번을 다시 해도 같은 결과가 나옵니다.
SELECT ... FOR UPDATE 로 막는다
/root/txn/forupdate.sql 을 만듭니다. 2단계와 같은 흐름인데 잔고를 읽을 때 FOR UPDATE 로 행을 잠급니다. 계좌 2로 두 세션을 겹쳐 돌리고 결과를 /root/txn/03-forupdate.txt 에 before, after, expected 세 줄로 남깁니다.
SELECT balance AS b FROM purse WHERE id = 2 FOR UPDATE \gset 으로 읽으면 그 행이 잠깁니다. 두 번째 세션은 첫 세션이 커밋할 때까지 이 조회 자체에서 기다리고, 깨어난 뒤에는 갱신된 값을 읽습니다.
비관적 잠금이라 부르는 이유가 이것입니다. 충돌이 날 것이라고 미리 보고 줄을 세웁니다. 대신 기다리는 시간이 생기고, 여러 행을 각기 다른 순서로 잠그면 교착이 납니다.
REPEATABLE READ 는 오류로 막는다
/root/txn/repeatable.sql 을 만듭니다. 2단계와 같은 흐름을 BEGIN ISOLATION LEVEL REPEATABLE READ; 로 시작하고 계좌 3으로 두 세션을 겹쳐 돌립니다. 뒤늦게 쓰려던 세션이 받은 SQLSTATE 와 최종 잔고를 /root/txn/04-repeatable.txt 에 sqlstate, after, expected 세 줄로 남깁니다.
psql 은 기본적으로 오류 메시지에 SQLSTATE 를 안 보여 줍니다. 파일 맨 위에 \set VERBOSITY verbose 를 넣으면 ERROR: 40001: ... 처럼 코드가 함께 나옵니다.
여기서 잔고는 900이 됩니다. 2단계와 같은 숫자이지만 뜻이 정반대입니다. 2단계는 출금 하나가 조용히 사라진 900이고, 4단계는 출금 하나가 거부된 900입니다. 거부된 쪽은 재시도하면 되지만, 사라진 쪽은 재시도할 기회조차 없습니다.
REPEATABLE READ 로도 안 막히는 것
/root/txn/skew.sql 을 만듭니다. rr 팀 당직자 수가 둘을 넘는지 확인한 뒤 자기 자신을 당직에서 빼는 트랜잭션이고, 격리 수준은 REPEATABLE READ 입니다. 두 세션이 각각 id 1과 id 2로 겹쳐 돌게 하고, 남은 당직자 수를 /root/txn/05-skew.txt 에 isolation, on_duty 두 줄로 남깁니다.
조건 판단과 갱신을 psql 안에서 이어 붙이려면 SELECT (count(*) > 1)::text AS ok ... \gset 으로 참거짓을 변수에 담고 \if :ok 와 \endif 로 감싸면 됩니다. 자기 번호는 psql -v me=1 처럼 넘기고 SQL 에서 :me 로 씁니다.
두 세션은 서로 다른 행을 고칩니다. 쓰기가 겹치지 않으므로 스냅샷 격리는 충돌을 감지할 근거가 없습니다. 둘 다 성공하고 당직자는 0명이 됩니다. 이것이 읽기의 결과에 기대어 다른 행을 고칠 때 생기는 쓰기 편향입니다.
SERIALIZABLE 로 쓰기 편향을 잡는다
/root/txn/serializable.sql 을 만듭니다. 5단계와 같은 흐름을 BEGIN ISOLATION LEVEL SERIALIZABLE; 로 시작하고 대상은 ssi 팀(id 3과 4)입니다. 한쪽이 받은 SQLSTATE 와 남은 당직자 수를 /root/txn/06-serializable.txt 에 sqlstate, on_duty 두 줄로 남깁니다.
SERIALIZABLE 은 쓰기 충돌만 보는 것이 아니라 읽기와 쓰기 사이의 의존성까지 추적합니다. 두 트랜잭션이 서로가 읽은 집합을 고쳤다는 것을 알아채고 한쪽을 되돌립니다.
5단계 파일을 복사해 격리 수준과 팀 이름, 자기 번호만 바꾸면 됩니다. SQLSTATE 를 보려면 여기서도 \set VERBOSITY verbose 가 필요합니다.
대가는 오류입니다. 이 수준을 쓰기로 했다면 애플리케이션에 재시도가 반드시 있어야 합니다.
SKIP LOCKED 로 작업을 나눠 집는다
/root/txn/claim.sql 을 만듭니다. queued 상태 작업을 id 순으로 세 건 골라 FOR UPDATE SKIP LOCKED 로 잠그고 running 으로 바꾸면서 worker 에 자기 이름을 적는 트랜잭션입니다. worker-a 와 worker-b 두 세션을 겹쳐 돌려 여섯 건을 모두 가져가고, 결과를 /root/txn/07-queue.txt 에 worker_a, worker_b, unclaimed 세 줄로 남깁니다.
고를 행과 고칠 행을 한 문장에 담으려면 CTE 가 편합니다.
WITH picked AS (
SELECT id FROM tasks WHERE state = 'queued' ORDER BY id
FOR UPDATE SKIP LOCKED LIMIT 3
)
UPDATE tasks t SET state = 'running', worker = :'me'
FROM picked WHERE t.id = picked.id;
:'me' 는 psql 변수를 문자열 리터럴로 감싸 넣습니다. SKIP LOCKED 가 없으면 두 번째 워커가 첫 워커가 잠근 행 앞에서 줄을 서고, 깨어난 뒤에는 이미 처리된 행을 다시 보게 됩니다.
무엇이 무엇을 막았는지 정리한다
/root/txn/08-notes.md 에 네 줄 이상 적습니다. 2단계와 4단계의 잔고가 왜 같은 900인데 뜻이 다른지, 5단계에서 REPEATABLE READ 가 왜 못 막았는지, 잠금과 격리 수준을 각각 어떤 상황에 고를지를 씁니다.
본문에 쓰기 편향, FOR UPDATE, SKIP LOCKED 가 들어가야 합니다.
특히 5단계를 잘 정리해 두세요. 실무의 동시성 사고는 대부분 표에 없는 그 네 번째 열에서 납니다.