격리 수준이 무엇을 막고 무엇을 못 막는가

목표

읽기에서 본 표의 네 번째 열, 곧 어떤 격리 수준도 잡아 주지 않는 쓰기 편향을 진짜 PostgreSQL 에서 직접 만들어 봅니다. 그리고 그것을 막는 세 가지 도구인 행 잠금, 직렬화 가능 수준, 작업 대기열용 건너뛰기를 차례로 써 봅니다.

왜 중요한가

동시성 버그를 만나면 "격리 수준을 올려라"라는 처방이 먼저 나옵니다. 이 실습은 그 처방이 언제 듣고 언제 안 듣는지를 숫자로 보여 줍니다. 2단계에서 잃은 100원은 4단계에서 오류로 바뀌고, 5단계에서는 오류조차 없이 규칙이 깨집니다.

특히 눈여겨볼 것은 같은 900이 두 번 나온다는 점입니다. 한 번은 갱신이 사라져서, 한 번은 갱신이 거부되어서입니다. 값만 보면 구분되지 않고, 어느 쪽인지는 오류를 받았는지로만 알 수 있습니다. 운영에서 이 차이가 신고되는 사고와 영영 모르는 사고를 가릅니다.

환경

이 파드 안에 PostgreSQL 16 이 돕니다. 접속은 이렇습니다.

export PGPASSWORD=labpsql -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 1psql ... -f /root/txn/lost.sql > /root/txn/lost_b.out 2>&1wait

산출물은 모두 /root/txn/ 아래에 둡니다. 시드 표(customers 등)는 건드리지 않습니다.

단계

1. /root/txn/schema.sql 로 purse, oncall, tasks 세 표를 만들어 적재합니다.
2. /root/txn/lost.sql 로 계좌 1에서 갱신 손실을 재현해 /root/txn/02-lost.txt 에 남깁니다.
3. /root/txn/forupdate.sql 로 계좌 2를 행 잠금으로 지켜 /root/txn/03-forupdate.txt 에 남깁니다.
4. /root/txn/repeatable.sql 로 계좌 3에서 직렬화 오류를 받아 /root/txn/04-repeatable.txt 에 남깁니다.
5. /root/txn/skew.sql 로 rr 팀의 당직 규칙을 깨뜨려 /root/txn/05-skew.txt 에 남깁니다.
6. /root/txn/serializable.sql 로 ssi 팀의 규칙을 지켜 /root/txn/06-serializable.txt 에 남깁니다.
7. /root/txn/claim.sql 로 두 워커가 큐를 나눠 가져가게 하고 /root/txn/07-queue.txt 에 남깁니다.
8. 배운 것을 정리해 /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: 두 세션의 시작 간격을 대기 시간보다 길게 두는 것입니다. 첫 세션이 이미 커밋한 뒤에 두 번째가 읽으면 아무 일도 일어나지 않습니다.

단계 8개

실습용 표 세 개를 만든다
갱신 손실을 재현한다
SELECT ... FOR UPDATE 로 막는다
REPEATABLE READ 는 오류로 막는다
REPEATABLE READ 로도 안 막히는 것
SERIALIZABLE 로 쓰기 편향을 잡는다
SKIP LOCKED 로 작업을 나눠 집는다
무엇이 무엇을 막았는지 정리한다