트랜잭션과 잠금 — 격리 수준·교착·타임아웃

목표

격리 수준·행 잠금·교착·advisory lock·타임아웃을 직접 재현하고, 각 현상을 살아 있는 데이터베이스의 상태로 증명합니다. 명령을 외우는 것이 아니라, 동시성이 어긋나는 순간을 눈으로 보는 것이 이 실습입니다.

왜 중요한가

읽어서-고쳐-쓰는 코드는 어디에나 있습니다 — 잔액, 재고, 카운터, 작업 대기열. 기본 격리 수준은 그 흐름을 지켜 주지 않아서, 동시 요청이 겹치면 갱신이 조용히 사라집니다. 오류도 로그도 없이 값만 틀립니다. 격리 수준이 무엇을 보장하고 무엇을 안 보장하는지, 그리고 행 잠금과 타임아웃이 각각 어떤 사고를 막는지를 알아야, 이 버그를 코드가 아니라 설계로 막을 수 있습니다.

환경

이 파드는 postgres 계정으로 돕니다. psql 만 치면 labdb 에 바로 붙습니다.

export PATH=/usr/lib/postgresql/16/bin:$PATHexport PGHOST=127.0.0.1 PGUSER=lab PGDATABASE=labdbpsql

산출물은 전부 /root/tx/ 아래에 둡니다. 실습 표(accounts·jobs)는 1단계에서 /opt/lab/fixtures/pg/tx_bank.sql 을 적재해 만듭니다. 시드 표(customers 등)는 건드리지 않습니다.

세션 여러 개가 필요합니다. 터미널이 하나뿐이므로 백그라운드로 띄웁니다. 아래 형태가 트랜잭션을 연 채 노는 세션을 만듭니다 — stdin 을 열어 두면 psql 이 다음 명령을 기다립니다.

( { printf 'begin;\nupdate accounts set balance = balance where id = 1;\n'; sleep 3600; } \    | PGAPPNAME=holder psql -X -q ) >/dev/null 2>&1 </dev/null &

세 리다이렉션(>/dev/null·2>&1·</dev/null)을 모두 붙이세요. 하나라도 빠지면 셸이 그 세션을 기다립니다.

단계

1. 픽스처(accounts·jobs)를 적재하고 초기 상태를 확인합니다.
2. READ COMMITTED 에서 갱신 손실을 재현해 /root/tx/02-lost.txt 에 남깁니다.
3. REPEATABLE READ 에서 직렬화 실패를 재현해 /root/tx/03-rr.txt 에 남깁니다.
4. SELECT FOR UPDATE 로 손실 없이 갱신하고 /root/tx/04-forupdate.txt 에 남깁니다.
5. FOR UPDATE SKIP LOCKED 로 두 워커가 작업 큐를 나눠 비웁니다.
6. 교착을 일부러 만들고 서버 로그에서 찾아 /root/tx/06-deadlock.txt 에 남깁니다.
7. advisory lock 으로 배치 중복 실행을 막고 /root/tx/07-advisory.txt 에 남깁니다.
8. lock_timeout 과 idle_in_transaction_session_timeout 을 걸고 효과를 확인합니다.

참고

• 사고를 낸 세션(7단계의 advisory 홀더)은 채점 때까지 살려 두세요. 채점이 살아 있는 잠금을 보기 때문입니다.
• 잔액·큐를 다시 초기화하려면 그 단계의 정답지가 먼저 계좌/큐를 되돌립니다 — 여러 번 돌려도 같은 결과가 됩니다.
• 백그라운드 세션은 kill $! 로 잡지 말고 stdin·sleep 으로 스스로 끝나게 두세요.

단계 8개

1. 픽스처를 적재하고 시작 상태를 본다
2. READ COMMITTED 의 갱신 손실
3. REPEATABLE READ 의 직렬화 실패
4. SELECT FOR UPDATE 로 손실을 없앤다
5. FOR UPDATE SKIP LOCKED 로 큐 나눠 뽑기
6. 교착을 만들고 로그에서 찾는다
7. advisory lock 으로 중복 실행 막기
8. lock_timeout 과 유휴 트랜잭션 한도