제약으로 막고, 40001 만 다시 한다

목표

확인하고 넣는 코드가 두 세션에서 중복 가입을 만드는 장면을 재현한 뒤, 기존 중복을 정리하고 UNIQUE 와 INSERT … ON CONFLICT 로 막습니다. 예약 겹침은 배제 제약(EXCLUDE)으로, 좌석 맞바꾸기는 DEFERRABLE UNIQUE 로 풉니다. 여러 행에 걸친 규칙은 psycopg 3 로 SERIALIZABLE 재시도 루프를 써서 지키고, 마지막에 불변식마다 둘 자리를 정리합니다.

왜 중요한가

애플리케이션이 확인하고 쓰는 규칙은 확인과 쓰기 사이의 틈에서 깨지고, 그 틈은 부하가 몰릴 때만 드러납니다. 데이터베이스 제약은 격리 수준과 상관없이 늘 성립하고 나중에 합류한 코드가 우회할 수도 없습니다. 제약으로 못 쓰는 규칙은 SERIALIZABLE 에 맡기되, 재시도 루프가 어떤 오류를 다시 하고 어떤 오류를 올려보내는지가 정확성을 가릅니다. 그래서 채점기는 여러분이 적은 파일만 보지 않고, 제약이 카탈로그에 걸려 있는지 확인하고 반례 행을 직접 넣어 본 뒤 되돌립니다. 재시도 루프는 일부러 오류를 일으키는 별도 데이터베이스에서 불러 봅니다.

환경

PostgreSQL 16 이 파드 안에 떠 있습니다. 접속은 psql -h 127.0.0.1 -U lab -d labdb, 비밀번호는 lab 입니다(export PGPASSWORD=lab). 작업 파일은 전부 /root/svccs/invariants/ 에 둡니다. 표는 모두 inv 스키마에 만듭니다. 파이썬은 python3 와 psycopg 3 이 준비되어 있습니다.

단계

/root/svccs/invariants/schema.sql 을 만들어 적재합니다. DROP SCHEMA IF EXISTS inv CASCADE; CREATE SCHEMA inv; 로 시작해 아래 '표' 를 만들고 행을 넣습니다. 이 단계에서는 표에 적힌 것 말고 다른 제약을 걸지 않습니다.
/root/svccs/invariants/signup_naive.sql 을 만듭니다. 트랜잭션 안에서 inv.signups_naive 에 race@example.com 이 몇 개인지 세고(\gset), pg_sleep(2) 로 쉰 뒤, 0 이었을 때만 넣습니다. 두 세션을 겹쳐 돌려 중복을 만들고, 그 이메일의 행 수를 /root/svccs/invariants/02-race.txt 에 rows <수> 로 적습니다.
inv.members 의 기존 중복을 정리하고(가장 작은 id 를 남김) members_email_key 라는 이름으로 UNIQUE (email) 을 겁니다(지연 불가). /root/svccs/invariants/signup.sql 에 psql 변수 :'email' 을 쓰는 문장 하나(BEGIN/COMMIT 없이)로 ON CONFLICT (email) DO NOTHING RETURNING id 가입을 씁니다. race@example.com 으로 두 세션을 겹쳐 돌리고 /root/svccs/invariants/03-unique.txt 에 returned_a, returned_b(각 세션이 돌려받은 행 수), rows(그 이메일의 행 수)를 한 줄씩 적습니다.
/root/svccs/invariants/touch.sql 에 문장 하나로 upsert 를 씁니다. 없으면 넣고(visits 1), 있으면 기존 visits 에 1 을 더하며, 어느 쪽이든 RETURNING id, visits 로 그 행을 돌려줍니다. 이미 있는 kim@example.com 에 signup.sql 과 touch.sql 을 각각 돌렸을 때(되돌리기) 돌려받은 행 수를 /root/svccs/invariants/04-upsert.txt 에 do_nothing_rows <수>, do_update_rows <수> 로 적습니다.
btree_gist 확장을 만들고, 같은 방에서 [) 범위가 겹치는 기존 예약 쌍의 수를 셉니다. 겹치는 쌍에서 id 가 큰 쪽을 status = 'cancelled' 로 바꾸고(지우지 않음), bookings_no_overlap 이라는 이름으로 활성(status = 'active') 예약에만 거는 배제 제약을 만듭니다 — 방이 같고 tstzrange(starts_at, ends_at, '[)') 가 겹치면 안 됩니다. /root/svccs/invariants/05-exclude.txt 에 overlaps_found <센 쌍 수>, cancelled <취소 상태 행 수>, sqlstate <겹치는 예약을 넣었을 때 받은 코드> 를 적습니다.
/root/svccs/invariants/swap.sql 에 한 트랜잭션에서 kim 의 좌석을 14로, lee 의 좌석을 12로 바꾸는 UPDATE 두 개를 씁니다. 지금의 seats_seat_no_key 로 돌려 받은 SQLSTATE 를 /root/svccs/invariants/06-swap.txt 에 immediate_error <코드> 로 적고, 같은 이름의 제약을 DEFERRABLE INITIALLY DEFERRED 로 다시 만든 뒤 swap.sql 을 돌려 실제로 맞바꿉니다.
/root/svccs/invariants/retry.py 에 withdraw(conninfo, wallet_id, amount, max_attempts=8) 을 만듭니다. 규칙은 아래 '출금 규칙' 그대로입니다. 같은 파일의 if __name__ == "__main__": 에서 kim 가족 지갑 둘을 100 으로 되돌리고, 스레드 6개가 동시에(시작을 맞춰) 지갑 1·2 에서 번갈아 80씩 출금한 뒤, /root/svccs/invariants/07-retry.json 에 workers, committed, rejected, retries(전체 시도 수 − 스레드 수), final_sum(kim 가족 합계)을 적습니다.
/root/svccs/invariants/map.json 에 아래 여섯 불변식마다 {"where": "constraint" | "isolation" | "application", "how": "쓴 도구 한 줄"} 을 적습니다. 채점기는 constraint 라고 적은 것이 지금 데이터베이스에 실제로 걸려 있는지도 봅니다.

표

inv.members        id bigserial PK, email text NOT NULL, visits int NOT NULL DEFAULT 1
                   행: kim@example.com, lee@example.com, legacy@example.com, legacy@example.com
inv.signups_naive  id bigserial PK, email text NOT NULL                (행 없음, 끝까지 제약 없음)
inv.bookings       id bigserial PK, room text, starts_at timestamptz, ends_at timestamptz, who text,
                   status text NOT NULL DEFAULT 'active',
                   CONSTRAINT bookings_positive_length CHECK (starts_at < ends_at)
                   행: river 10:00~11:00 kim · river 11:00~12:00 lee · river 11:30~12:30 park
                       · hill 10:00~11:00 choi   (모두 2026-10-01, 시간대 +09)
inv.seats          id int PK, passenger text NOT NULL, seat_no int NOT NULL,
                   CONSTRAINT seats_seat_no_key UNIQUE (seat_no)
                   행: (1, kim, 12) (2, lee, 14) (3, park, 15) (4, choi, 16)
inv.wallets        id int PK, family text, owner text, balance int   (모두 NOT NULL)
                   행: (1, kim, kim-a, 100) (2, kim, kim-b, 100) (3, lee, lee-a, 50) (4, lee, lee-b, 50)

출금 규칙

연결은 conninfo 로 새로 연다. 격리 수준은 SERIALIZABLE.
한 트랜잭션: wallet_id 의 family 를 읽고 → 그 family 의 balance 합계를 읽고 →
  합계 < amount 면 아무것도 바꾸지 않고 {"status": "rejected", "attempts": n} 을 돌려준다
  아니면 UPDATE inv.wallets SET balance = balance - amount WHERE id = wallet_id 후 커밋하고
  {"status": "committed", "attempts": n} 을 돌려준다     (n = 이번 호출에서 시도한 횟수)
SQLSTATE 40001·40P01 이면 트랜잭션 전체를 처음부터 다시 한다(짧은 무작위 대기, 합계 1초 이내).
그 밖의 오류는 다시 하지 않고 그대로 올려보낸다. max_attempts 번 모두 실패하면 마지막 오류를 올려보낸다.

여섯 불변식(8단계)

member_email_unique       이메일 하나에 회원 하나
room_no_overlap           같은 방의 활성 예약 시간이 겹치지 않는다
seat_unique               좌석 하나에 승객 하나(맞바꾸는 동안은 잠깐 깨져도 된다)
booking_positive_length   예약은 끝이 시작보다 뒤다
family_sum_nonnegative    가족 지갑 합계가 음수가 되지 않는다(여러 행에 걸친 조건)
welcome_mail_once         가입 환영 메일(외부 메일 서비스 호출)은 한 번만 보낸다

참고

psql 은 -v email=값 으로 변수를 넘기고 파일 안에서 :'email' 로 씁니다. -c "BEGIN" -f 파일 -c "COMMIT" 처럼 여러 개를 주면 한 세션에서 차례로 돕니다. 오류에 SQLSTATE 를 보려면 -v VERBOSITY=verbose 를 줍니다.
두 세션을 겹치려면 한쪽을 & 로 배경에 띄우고 잠깐 뒤 다른 쪽을 돌린 다음 wait 합니다.
흔한 실수: UNIQUE 없이 WHERE NOT EXISTS 같은 애플리케이션 검사만 두는 것, 범위를 [] 로 잡아 맞닿은 예약까지 막는 것, 맞바꾸기를 임시 번호로 우회하고 제약은 그대로 두는 것, 재시도 루프가 except Exception 으로 제약 위반까지 다시 하거나 실패를 성공으로 보고하는 것.
산출물과 데이터베이스는 세션이 끝나면 사라집니다. 필요하면 따로 보관하세요.

제약 없는 표를 만든다

/root/svccs/invariants/schema.sql 을 만들어 적재한다 — inv 스키마에 members·signups_naive·bookings·seats·wallets 를 지시문의 '표' 그대로.

DROP SCHEMA IF EXISTS inv CASCADE 로 시작하면 몇 번을 다시 적재해도 같은 상태가 됩니다. psql 은 오류가 나도 다음 문장을 계속 돌리니 -v ON_ERROR_STOP=1 을 주세요. 시간은 '2026-10-01 10:00+09' 처럼 시간대까지 적습니다.

확인하고 넣기가 중복을 만든다

/root/svccs/invariants/signup_naive.sql 로 두 세션을 겹쳐 inv.signups_naive 에 race@example.com 중복을 만들고, 행 수를 /root/svccs/invariants/02-race.txt 에 rows <수> 로 적는다.

SELECT count(*) AS n … \gset 으로 센 값을 :n 으로 씁니다. 두 세션 모두 상대가 커밋하기 전에 세어야 둘 다 0 을 봅니다 — 한쪽을 & 로 띄우고 0.5초 뒤 다른 쪽을 돌리세요. 차례로 돌리면 두 번째가 1 을 보고 넣지 않습니다.

UNIQUE 와 ON CONFLICT DO NOTHING

inv.members 의 중복을 정리하고 members_email_key UNIQUE (email) 을 건 뒤, /root/svccs/invariants/signup.sql(문장 하나)로 두 세션을 겹쳐 /root/svccs/invariants/03-unique.txt 에 returned_a·returned_b·rows 를 적는다. 채점기는 signup.sql 을 탐침 이메일로 두 번 돌려 보고 되돌린다.

제약은 기존 행도 검사하므로 legacy 중복이 남아 있으면 ALTER TABLE 이 실패합니다. DELETE … USING 으로 같은 이메일의 큰 id 를 지우세요. 겹친 두 세션 가운데 뒤 세션은 앞 세션의 커밋을 기다렸다가 아무것도 넣지 않고, RETURNING 도 비어 있습니다.

DO UPDATE 와 RETURNING 의 차이

/root/svccs/invariants/touch.sql 에 visits 를 올리는 upsert 를 문장 하나로 쓰고, 이미 있는 회원에 signup.sql·touch.sql 이 돌려주는 행 수를 /root/svccs/invariants/04-upsert.txt 에 적는다. 채점기는 touch.sql 을 탐침 이메일로 세 번 돌려 id 와 visits 를 본다.

SET 절에서 기존 행은 표 별칭(AS m)으로, 넣으려던 행은 EXCLUDED 로 가리킵니다. EXCLUDED.visits 는 기본값 1 이라서 거기에 더하면 몇 번을 돌려도 2 입니다. DO NOTHING 의 RETURNING 은 건너뛴 행을 돌려주지 않습니다.

예약 겹침은 EXCLUDE 로

btree_gist 를 만들고 기존 겹침을 세어 취소한 뒤, 활성 예약에만 거는 bookings_no_overlap 배제 제약을 만들고 /root/svccs/invariants/05-exclude.txt 에 overlaps_found·cancelled·sqlstate 를 적는다. 채점기는 탐침 방에 겹치는·맞닿은·취소된 예약을 넣어 보고 되돌린다.

겹침은 tstzrange(starts_at, ends_at, '[)') && … 로 봅니다. '[)' 에서 11시에 끝나는 예약과 11시에 시작하는 예약은 겹치지 않습니다 — '[]' 로 잡으면 kim·lee 예약 때문에 제약 자체가 걸리지 않습니다. 부분 제약은 EXCLUDE … WHERE (status = 'active') 입니다.

좌석 맞바꾸기와 DEFERRABLE

/root/svccs/invariants/swap.sql 로 즉시 검사 UNIQUE 에서 받는 오류를 /root/svccs/invariants/06-swap.txt 에 immediate_error 로 적고, seats_seat_no_key 를 DEFERRABLE INITIALLY DEFERRED 로 다시 만든 뒤 kim 14 · lee 12 로 맞바꾼다. 채점기는 제약 속성을 보고, 트랜잭션 안에서 맞바꾸기와 중복을 시험한 뒤 되돌린다.

ALTER CONSTRAINT 는 외래 키만 바꿀 수 있어서, 같은 ALTER TABLE 에서 DROP CONSTRAINT 와 ADD CONSTRAINT 를 함께 씁니다. 즉시 검사에서는 첫 UPDATE 가 끝나는 순간 12 가 둘이 되어 23505 가 납니다. 임시 번호(0)로 세 번 바꾸면 통과는 하지만 제약은 여전히 즉시 검사입니다.

40001 만 다시 하는 재시도 루프

/root/svccs/invariants/retry.py 에 출금 규칙대로 withdraw 를 만들고, 스레드 6개로 동시에 출금한 결과를 /root/svccs/invariants/07-retry.json 에 적는다. 채점기는 별도 데이터베이스에서 40001·40P01·23514 를 일부러 일으키며 withdraw 를 부른다.

psycopg.connect(conninfo, autocommit=True) 로 열고 conn.isolation_level = psycopg.IsolationLevel.SERIALIZABLE 을 둔 뒤 with conn.transaction(): 블록을 루프 안에서 엽니다. 예외의 sqlstate 로 분기하세요 — except Exception 으로 전부 다시 하면 제약 위반까지 되풀이합니다. 합계를 읽는 SELECT 도 루프 안이어야 다시 할 때 새 값을 봅니다.

불변식마다 둘 자리

/root/svccs/invariants/map.json 에 여섯 불변식마다 where(constraint·isolation·application)와 how 를 적는다. 채점기는 constraint 로 적은 것이 지금 데이터베이스에 걸려 있고 반례를 실제로 거부하는지 본다.

CHECK 는 검사 중인 행 말고 다른 행을 볼 수 없으니, 여러 행의 합계는 제약으로 못 씁니다. 트랜잭션이 되돌려져도 이미 나간 외부 호출은 돌아오지 않으니 그것은 데이터베이스가 지킬 수 없는 규칙입니다.