돌아가는 원 하나에 예산이 세 개 숨어 있다

목표

잠금 대기·문장 실행·재시도 횟수라는 세 예산을 psql 두 세션으로 직접 재 봅니다. 55P03 과 57014 를 갈라 받고, 트랜잭션 범위로 건 예산이 같은 연결에 남지 않는 것을 백엔드 PID 로 증명하고, 실패한 트랜잭션이 저절로 닫히지 않는다는 것까지 확인한 뒤 예산 표를 씁니다.

왜 중요한가

읽기가 말한 세 예산은 화면에서 구분되지 않습니다. 사용자에게는 전부 "돌아가는 원" 하나로 보이고, 그래서 버튼을 다시 누릅니다. 다시 누른 요청은 같은 행을 기다리는 줄에 하나 더 붙는 것이라 문제를 키웁니다. 고객에게 언제 다시 눌러도 되는지 말하려면 어디서 얼마나 기다렸는지, 그리고 그 실패가 다시 시도해도 되는 종류인지 먼저 알아야 합니다.

코드로 그것을 구분하는 방법은 오류 문장이 아니라 SQLSTATE 입니다. 잠금을 못 얻어 끊긴 것은 55P03(lock_not_available), 문장 자체가 취소된 것은 57014(query_canceled), 실패한 트랜잭션 안에서 다음 명령을 보낸 것은 25P02(in_failed_sql_transaction) 입니다. 메시지 문자열을 비교하는 코드는 로케일이 바뀌는 순간 무너지지만 이 다섯 글자는 바뀌지 않습니다.

예산을 거는 자리도 중요합니다. set_config 의 세 번째 인자를 true 로 주면 그 트랜잭션에서만 살고, 커밋으로도 롤백으로도 사라집니다. 세션에 걸거나 데이터베이스 기본값으로 걸면 그 연결을 다음에 빌려 가는 사람의 요청까지 짧은 시간에 끊깁니다. 그 사고는 원인이 이 코드에 없어서 찾기가 아주 어렵습니다.

예상 80분입니다. 만료 전에 ＋시간을 눌러 세션을 연장하세요. 세션이 끝나면 /root 아래 파일이 모두 사라집니다.

환경

PostgreSQL 16 이 파드 안에서 이미 돌고 있습니다. 접속은 psql -X -U lab -d labdb 이며 호스트는 127.0.0.1 입니다(export PGHOST=127.0.0.1). 인터넷이나 추가 설치는 필요 없습니다.

이 실습은 스키마 budget 안에서만 작업합니다. 산출물은 모두 /root/budget 아래에 둡니다. blue 고객의 9번 주문은 시험용 행이라 여러분이 돌릴 때마다, 그리고 채점할 때마다 revision 이 올라갑니다 — 그 값 자체를 외워 두지 말고 필요할 때 다시 읽으세요. 채점기는 여러분이 만든 스크립트를 실제로 다시 돌려 판정하므로, 스크립트는 몇 번을 실행해도 같은 모양의 출력을 내야 합니다.

잠금을 기다리는 일은 고정 sleep 으로 다루지 않습니다. 홀더가 잠금을 실제로 쥔 것을 pg_stat_activity 에서 확인하고 넘어가는 것이 1단계 과제이고, 그 뒤 단계는 모두 그 스크립트를 씁니다.

단계

1. /root/budget/01-schema.sql 로 스키마 budget 과 표 budget.orders 를 만드세요 — tenant·id·qty·state·revision 다섯 열에 기본 키는 (tenant, id) 이고, blue 고객의 1번(7개 pending revision 3)·2번(4개 pending revision 1)·9번(6개 pending revision 0) 세 행을 넣습니다. 9번은 이 실습 내내 시험용으로 쓰는 행이라 버전이 계속 올라갑니다. 그다음 /root/budget/holder.sh 를 만드세요 — bash holder.sh <초> <주문번호> 로 부르면 백그라운드 psql 세션이 그 행을 select ... for update 로 잡고 pg_sleep 으로 버틴 뒤 커밋합니다. 그 세션의 application_name 은 반드시 lockholder 여야 하고, 스크립트는 고정 sleep 이 아니라 pg_stat_activity 를 폴링해 그 세션이 잠금을 쥔 채 잠든 것을 확인한 뒤에야 granted=yes 를 찍고 끝나야 합니다. 마지막으로 bash holder.sh 3 9 를 실행하고 /root/budget/01-holder.txt 에 rows·probe_id·granted·holder_seconds 네 줄을 남기세요.
2. /root/budget/attempt.sh 를 만드세요 — bash attempt.sh <잠금ms> <문장ms> <주문번호> 로 부르면 한 트랜잭션 안에서 set_config 의 세 번째 인자를 true 로 주어 lock_timeout 과 statement_timeout 을 걸고, blue 고객의 그 주문 행을 revision = revision + 1 로 갱신한 뒤 커밋합니다. 성공하든 실패하든 종료 코드는 0 이고, 표준 출력에 sqlstate·rows·elapsed_ms 세 줄만 찍습니다. 오류가 없으면 sqlstate 는 00000 입니다. 그다음 bash holder.sh 5 9 로 9번 행을 잡아 놓고 bash attempt.sh 200 1500 9 를 실행해 결과를 /root/budget/02-lockwait.txt 에 sqlstate·lock_timeout_ms·statement_timeout_ms·waited_ms·rows 다섯 줄로 남기세요.
3. 이번에는 두 예산을 거꾸로 걸어 봅니다. bash holder.sh 5 9 로 9번 행을 잡아 놓고 bash attempt.sh 400 150 9 를 실행하세요 — 잠금 예산 400ms, 문장 예산 150ms 입니다. 결과를 /root/budget/03-stmt.txt 에 inverted_lock_ms·inverted_statement_ms·inverted_sqlstate·normal_sqlstate·first_budget 다섯 줄로 남기세요. normal_sqlstate 는 2단계에서 받은 코드이고, first_budget 에는 이번에 먼저 끊은 설정의 이름(lock_timeout 또는 statement_timeout)을 적습니다.
4. /root/budget/leak.sh 를 만드세요 — psql 을 한 번만 띄워 같은 연결 안에서 네 번 물어봅니다. 트랜잭션 밖에서 한 번(before), 트랜잭션 안에서 set_config('lock_timeout','250ms',true) 와 set_config('statement_timeout','1200ms',true) 를 건 뒤 한 번(inside), 커밋한 뒤 한 번(after_commit), 다시 걸었다가 롤백한 뒤 한 번(after_rollback)입니다. 네 줄 모두 키=값/백엔드PID 모양으로 찍습니다. 실행 결과를 보고 /root/budget/04-noleak.txt 에 inside·after_commit·after_rollback·pid_same·db_role_settings 다섯 줄을 남기세요. db_role_settings 는 pg_db_role_setting 에 lock_timeout 이나 statement_timeout 이 저장된 항목의 수입니다.
5. /root/budget/retry.sh 를 만드세요 — bash retry.sh <시도횟수> <잠금ms> <문장ms> <주문번호> 로 부르면 attempt.sh 를 불러 sqlstate 가 55P03 일 때만, 그리고 남은 시도가 있을 때만 잠깐 쉬었다가 다시 부릅니다. 시도 횟수에는 최초 호출이 포함됩니다 — 3 이면 최초 한 번과 재시도 두 번입니다. 그 밖의 코드는 그대로 전달하고 더 부르지 않습니다. 시도 횟수가 1 부터 16 의 정수가 아니면 attempt.sh 를 한 번도 부르지 않고 tries=0·final_sqlstate=22023·outcome=invalid 를 찍습니다. 정상 경로에서는 tries·final_sqlstate·outcome 세 줄을 찍고, outcome 은 final_sqlstate 가 00000 이면 applied, 아니면 gaveup 입니다. 아무도 잡고 있지 않을 때 한 번, bash holder.sh 6 9 뒤에 3 200 1500 9 로 한 번, 같은 홀더가 남아 있는 동안 3 400 150 9 로 한 번 실행해 결과를 /root/budget/05-retry.txt 에 free_tries·free_outcome·locked_tries·locked_outcome·locked_sqlstate·cancelled_tries·cancelled_sqlstate 일곱 줄로 남기세요.
6. /root/budget/idle.sh 를 만드세요 — bash idle.sh <주문번호> 로 부르면 연결 하나 안에서 예산 두 개를 트랜잭션 범위로 걸고 그 행을 select ... for update 로 잡으려다 실패한 뒤, 롤백하기 전에 아무 SELECT 하나를 더 보내 보고, 그다음 롤백하고, 롤백 뒤에 다시 SELECT 를 보내고, 두 예산의 현재 값을 읽고, 처음과 마지막의 백엔드 PID 를 비교합니다. 출력은 sqlstate·aborted_sqlstate·after_rollback·lock_timeout_after·statement_timeout_after·pid_same 여섯 줄입니다. bash holder.sh 5 9 로 잡아 놓고 bash idle.sh 9 를 실행해 그 여섯 줄을 그대로 /root/budget/06-idle.txt 에 남기세요.
7. /root/budget/apply.sh 를 만드세요 — bash apply.sh <잠금ms> <문장ms> <주문번호> <승인당시revision> 으로 부르면 한 트랜잭션 안에서 두 예산을 걸고, 승인 당시 revision 과 pending 상태가 모두 맞을 때만 그 행의 revision 을 1 올립니다. 출력은 sqlstate·rows·verdict 세 줄이고 verdict 는 다음과 같습니다 — 오류 없이 1행이 바뀌면 applied, 오류 없이 0행이면 stale, 55P03 이면 locked, 57014 면 cancelled, 그 밖은 error 입니다. 지금 9번 행의 revision 을 읽어 그 값으로 한 번(applied), 같은 값으로 한 번 더(stale), bash holder.sh 5 9 뒤에 현재 revision 으로 200 1500 한 번(locked)과 400 150 한 번(cancelled) 실행해, 결과를 /root/budget/07-verdict.txt 에 fresh_verdict·stale_verdict·locked_verdict·locked_sqlstate·cancelled_verdict·cancelled_sqlstate 여섯 줄로 남기세요.
8. 마지막으로 이 실습에서 정한 예산을 /root/budget/08-budget.txt 에 열 줄로 적으세요 — lock_timeout_ms 는 200, statement_timeout_ms 는 1500, attempts 는 3, retry_pause_ms 는 200 입니다(2단계부터 쓰던 값입니다). worst_case_db_wait_ms 는 시도 횟수 곱하기 잠금 예산, worst_case_elapsed_ms 는 거기에 쉬는 시간 곱하기 (시도 횟수 빼기 1)을 더한 값입니다. measured_locked_ms 와 measured_sqlstate 는 bash holder.sh 5 9 로 잡아 놓고 bash attempt.sh 200 1500 9 를 한 번 더 실행해 얻은 실제 값입니다. retry_on 에는 다시 시도하는 SQLSTATE 를, propagate 에는 그대로 전달하는 SQLSTATE 를 적습니다.

참고

SQLSTATE 는 psql -v VERBOSITY=verbose 를 주면 오류 줄에 함께 나옵니다. 스크립트에서는 그 줄에서 다섯 글자만 뽑아 쓰세요.
흔한 실수 하나: 예산을 set lock_timeout = ... 로 세션에 거는 것. 그 연결이 살아 있는 한 다음 트랜잭션도 그 예산을 씁니다.
흔한 실수 둘: 실패 뒤 롤백을 빠뜨리는 것. 같은 연결의 다음 명령이 모두 25P02 로 거절됩니다.
공식 문서: [클라이언트 기본 설정](https://www.postgresql.org/docs/16/runtime-config-client.html), [set_config](https://www.postgresql.org/docs/16/functions-admin.html), [오류 코드](https://www.postgresql.org/docs/16/errcodes-appendix.html), [pg_stat_activity](https://www.postgresql.org/docs/16/monitoring-stats.html).

단계 8개

다른 담당자가 행을 쥐고 있는 상태를 만든다
잠금 예산이 끊었다는 증거를 코드로 받는다
문장 예산이 잠금 예산보다 짧으면 무엇이 먼저 끊는가
예산이 빌려 온 연결에 남지 않는 것을 같은 PID 로 증명한다
다시 시도해도 되는 실패는 한 가지뿐이다
실패한 트랜잭션은 저절로 닫히지 않는다
기다리다 실패한 것과 승인이 낡아 거절된 것을 가른다
어디서 얼마나 기다리는지 표로 적는다