원자적인 그룹 예약 저장하기

소개

한 그룹이 함께 참여하려고 합니다. 두 명 요청에서 한 자리만 예약하는 것은 성공이 아닙니다. 그룹 전체에 대한 실제 확정을 반환하거나 자리가 부족하면 아무것도 바꾸지 않는 재사용 가능한 SQL 문을 저장합니다. 실제 고유 키 실패로 자리 확보와 확정이 같은 트랜잭션에 속하는지 시험합니다.

이 독립 실습은 새 워크숍과 원래 예약을 준비하며 이전 실습 파일을 사용하지 않습니다. 앞 실습의 트랜잭션, 행 잠금, ROLLBACK 개념과 기본 PostgreSQL SELECT, INSERT, UPDATE 지식이 필요합니다. 이름 있는 클라이언트 매개변수, 정수 형 변환, 데이터를 변경하는 공통 테이블 표현식은 여기서 설명합니다. Workshop Desk는 셸이며 Desk A와 Desk B는 한 머신의 같은 데이터베이스에 대한 별개의 psql 연결입니다. 저자의 예상 소요 시간에는 읽기와 일반적인 실수가 포함되며 초보자 시간 측정 연구는 수행하지 않았습니다.

/home/labex/project/workshop-desk/reserve.sql을 만드세요. 제공된 도구, 테이블 정의, 원래 예약, 모든 워크숍의 ID, 제목, 정원, 메모를 유지하세요. 파일에는 SQL 문 하나만 있으며 psql 명령, 셸 명령, 명시적 BEGIN/COMMIT, 설정 변경, 외부 자원을 넣지 않습니다. 명시된 매개변수 네 개를 받아 실제 예약의 네 필드를 반환합니다. 동등하게 안전한 SQL도 허용됩니다. 이는 통제된 READ COMMITTED 실습이며 결제, 자동 재시도, 분산 참여 허용 보장은 포함하지 않습니다.

확정으로 이어지는 자리 확보 문장 저장하기

이 단계에서는 그룹 자리를 실제로 확보하지 않으면 확정을 만들 수 없는 문장 하나를 저장합니다.

Workshop Desk에서 시작하세요. ./는 현재 디렉터리의 준비된 프로그램을 실행합니다. 상태 프로그램은 빠진 작업을 채우지 않고 실제 테이블을 읽습니다:

./desk status

워크숍 201은 정원 6명, 원래 예약된 한 자리, 잔여 다섯 자리입니다. 워크숍 202에는 세 자리, 203에는 네 자리가 있습니다. original-family|201|1|Original은 남아 있어야 합니다. 첫 표시는 workshop_id|title|capacity|places_left|booked_seats, 두 번째는 request_code|workshop_id|seats|desk를 사용합니다.

파일은 psql 변수 workshop_id, seats, request_code, desk를 받습니다. 데이터베이스 열이나 영구 서버 설정이 아니라 요청마다 별도로 제공되는 클라이언트 매개변수입니다. :'name' 형식은 해당 변수를 안전하게 따옴표 처리된 SQL 문자열 리터럴로 넣도록 psql에 요청하며, 요청 코드 안의 작은따옴표도 보존합니다. 따옴표 없는 :name으로 바꾸지 마세요. PostgreSQL의 ::integer는 워크숍과 양수 그룹 인원수 텍스트를 정수로 변환합니다.

WITH claimed AS (...)는 이 문장이 사용하는 중간 관계인 공통 테이블 표현식, 즉 CTE의 이름을 지정합니다. 여기서 CTE는 조건부 UPDATE를 수행합니다. RETURNING workshop_id는 요청한 전체 그룹이 들어갈 때만 행 하나를 제공합니다. INSERT ... SELECT ... FROM claimed는 그 실제 반환 행을 사용합니다. 따라서 빈 확보 결과는 INSERT를 만들지 않습니다. 마지막 RETURNING은 실제 삽입한 예약을 보고합니다. 같은 문장 안에서 기본 테이블을 다시 읽어 데이터 변경 CTE의 새 값이 보일 것이라 기대하지 마세요. RETURNING 관계를 사용하세요.

뺄셈은 요청한 인원수를 사용하고 WHERE 조건도 같은 인원수를 검사합니다. 문장 하나는 암묵적 트랜잭션에서 실행됩니다. 자리 확보와 INSERT가 모두 성공하거나 SQL 오류가 둘 다 롤백합니다. 이후 두 창구 시험에서는 같은 문장을 명시적 트랜잭션 안에서 커밋하지 않은 채 유지해 잠금을 관찰합니다.

전체 문장을 저장하세요. cat > reserve.sql은 다음 텍스트로 파일을 덮어씁니다. 따옴표로 지정한 here-document 표시 SQL은 셸이 내용을 바꾸지 못하게 합니다. 닫는 SQL은 해당 줄에 단독으로 붙여 넣으세요. 데이터베이스 창구가 아니라 셸인 Workshop Desk에서 실행하세요:

cat > reserve.sql <<'SQL'
WITH claimed AS (
  UPDATE workshops
  SET places_left = places_left - :'seats'::integer
  WHERE workshop_id = :'workshop_id'::integer
    AND places_left >= :'seats'::integer
    AND :'seats'::integer > 0
  RETURNING workshop_id
)
INSERT INTO bookings (request_code, workshop_id, seats, desk)
SELECT :'request_code', workshop_id, :'seats'::integer, :'desk'
FROM claimed
RETURNING request_code, workshop_id, seats, desk;
SQL

사용 전에 저장 파일을 읽으세요. cat은 파일을 표시합니다. 오타는 별도 명령으로 숨기지 말고 저장 원본에서 수정해야 합니다:

cat reserve.sql

desk book은 PostgreSQL 기본 psql 매개변수로 정확히 이 파일을 실행합니다. 기준 쿼리를 제공하거나, 대체 예약을 만들거나, 잔여 값을 고치지 않습니다. 인수는 워크숍 ID, 양수 그룹 인원수, 고유 요청 코드, 창구 표시입니다. 정상 두 명 요청을 실행하세요:

./desk book 201 2 normal-group A

실제 반환 행은 다음과 같습니다:

normal-group|201|2|A

실제 기준 데이터를 확인하세요:

./desk status

이제 워크숍 201에는 잔여 세 자리와 예약된 세 자리가 있습니다. 원래 가족 예약과 정확한 두 명 예약이 함께 남습니다. 반환 행 하나만으로 건전한 알고리즘이 입증되지는 않습니다. 검사도 실제 행을 읽고 원래 예약을 유지하며 실제 경쟁 연결을 포함한 다른 격리 데이터에서 변경하지 않은 원본을 호출합니다. 임시 테스트 데이터베이스는 이 실제 데스크를 고치지 않습니다.

SQL이 실패하면 프로그램은 PostgreSQL 자체 오류를 출력하고 0이 아닌 상태로 종료하며, 이를 자리 부족이라고 부르지 않습니다. 대화형 명시적 트랜잭션에서는 먼저 같은 창구에서 ROLLBACK하세요. 커밋되지 않은 오타는 아무것도 바꾸지 않습니다. 잘못된 새 시험 예약을 실수로 커밋했다면 앞 실습에서 배운 대로 정확히 그 코드만 제거하고 한 트랜잭션에서 그 워크숍의 잔여 값만 다시 계산하세요. 예를 들어 Desk A에서 다음 복구는 201에 가정한 typo-trial만 제거하며, 그런 예약이 없을 때도 안전합니다:

BEGIN;
DELETE FROM bookings WHERE request_code = 'typo-trial' AND workshop_id = 201;
UPDATE workshops
SET places_left = capacity -
  (SELECT COALESCE(SUM(seats), 0) FROM bookings WHERE workshop_id = 201)
WHERE workshop_id = 201;
COMMIT;

original-family나 적법하게 요청된 예약은 절대 제거하지 마세요. 어떤 작업이 남았는지 결정하기 전에 상태를 확인하세요. 나중 명령이 실패했다고 이미 성공한 요청을 다시 실행하지 마세요.

대기하는 그룹과 실패한 확정 시험하기

이 단계에서는 독립적인 PostgreSQL 기본 연결 두 개에서 같은 저장 파일을 실행하고, 다른 워크숍은 계속 진행시키며, 실패한 INSERT가 자리를 사용할 수 없음을 입증합니다.

Desk A로 전환하세요. 프롬프트는 셸이 아니라 psql 안에 있다는 뜻입니다. SQL은 세미콜론으로 끝납니다. 역슬래시로 시작하는 psql 명령은 클라이언트를 제어하며 세미콜론이 없습니다. \set은 이 연결 하나에서 클라이언트 변수를 설정하며 다른 탭으로 전달하지 않습니다. \i는 지정 파일을 읽어 실행합니다. 절대 파일 이름은 클라이언트의 현재 디렉터리와 관계없이 동작합니다.

READ COMMITTED 트랜잭션을 시작하고 처음에 세 자리가 있는 워크숍 202에 두 명 그룹을 제출하세요:

BEGIN ISOLATION LEVEL READ COMMITTED;
\set workshop_id 202
\set seats 2
\set request_code race-A
\set desk A
\i /home/labex/project/workshop-desk/reserve.sql

A는 race-A | 202 | 2 | A와 INSERT 0 1을 봅니다. 문장이 두 자리를 나타내는 예약 행 하나를 삽입한 것입니다. 아직 커밋되지 않았습니다. 프롬프트의 *가 열린 트랜잭션을 표시합니다.

Desk B로 전환해 독립적인 요청을 준비하세요:

BEGIN ISOLATION LEVEL READ COMMITTED;
\set workshop_id 202
\set seats 2
\set request_code race-B
\set desk B
\i /home/labex/project/workshop-desk/reserve.sql

B는 프롬프트를 반환하지 않고 A의 행 잠금을 기다립니다. 대기 상태로 두고 Workshop Desk로 전환하세요:

./desk waits

Desk B의 wait_event_type은 Lock이고 blocked_by에는 A의 실제 백엔드 프로세스 ID가 있습니다. READ COMMITTED에서는 A가 커밋한 후 대기하던 UPDATE가 갱신된 행에 대해 충분한 자리가 있는지 다시 검사합니다. 앞서 보였던 잔여 값은 예약이 아닙니다.

계속 Workshop Desk에서 같은 저장 파일로 다른 워크숍을 예약하세요:

./desk book 203 2 other-workshop C

A가 여전히 202를 잠근 동안 other-workshop|203|2|C를 반환하고 끝납니다. 무관한 이 워크숍을 불필요하게 막는 하나의 전역 잠금을 도입하지 마세요.

Desk A로 전환하세요:

COMMIT;

Desk B로 전환하세요. 대기하던 파일은 이제 반환 0행과 INSERT 0 0으로 끝납니다. 한 자리만 남아 두 명 그룹이 들어갈 수 없습니다. 그룹을 나누거나 별도 확정을 삽입하지 마세요. B의 트랜잭션을 끝내세요:

ROLLBACK;

Workshop Desk로 전환하세요:

./desk status

워크숍 202에는 잔여 한 자리와 정확히 race-A|202|2|A가 있으며 race-B는 없습니다. 워크숍 203에는 잔여 두 자리와 두 명 예약이 있습니다. 성공 종료 코드만 보지 말고 관계를 확인하세요.

다음으로 202에 한 자리를 확보할 여유가 남아 있을 때 INSERT를 실패시키세요. 기본 키는 워크숍 안에서만이 아니라 bookings 테이블 전체에서 요청 코드가 고유하도록 요구합니다. 코드 original-family는 이미 201에 속합니다. 재사용하면 고유 키 위반인 SQLSTATE 23505가 나와야 합니다. 이는 예상된 실패이며 자리가 없는 결과가 아닙니다:

./desk book 202 1 original-family A

파일 실행은 PostgreSQL 자체 오류를 보고하고 상태 3으로 종료합니다. 다른 셸 명령이 이전 명령의 종료 상태인 $?를 덮어쓰기 전에 즉시 상태를 캡처하세요:

printf 'Previous native exit: %s\n' "$?"

Previous native exit: 3이 나와야 합니다. 0이 아닌 상태는 성공한 0행 결과와 오류를 구분합니다. 문장의 암묵적 트랜잭션은 앞선 자리 확보를 취소해야 합니다. 원장을 다시 읽으세요:

./desk status

워크숍 202에는 여전히 한 자리가 있고 original-family도 201에서 바뀌지 않았습니다. 자리 확보 뒤 ON CONFLICT DO NOTHING을 쓰는 지름길은 INSERT 실패를 숨기면서 차감을 커밋할 수 있습니다. 이는 원자적인 거부가 아닙니다. 수치가 실제 예약과 맞지 않게 됩니다. 검사는 충분한 자리가 있을 때 이 사례를 시험하므로, 빈 확보 결과 때문에 오류 시험이 우연히 통과할 수 없습니다.

새 요청 코드로 마지막 자리를 적법하게 예약하세요:

./desk book 202 1 after-error A

after-error|202|1|A가 나와야 합니다. 이제 자리가 없을 때 두 명 그룹을 추가 요청하세요:

./desk book 202 2 too-late B

프로그램은 No booking returned. Inspect the real ledger before drawing a conclusion.을 출력합니다. SQL이 유효하고 예약을 반환하지 않았으므로 이 호출은 0으로 종료합니다. too-late 행을 남기거나 잔여 값을 바꾸면 안 됩니다.

./desk status

최종 새 예약은 normal-group(201, 2, A), race-A(202, 2, A), other-workshop(203, 2, C), after-error(202, 1, A)이며 원래 가족 예약도 그대로 있습니다. race-B나 too-late는 없습니다. 잔여 자리는 각각 3, 0, 2입니다.

Ctrl+C로 대기 파일을 취소하면 SQLSTATE 57014가 명시적 트랜잭션을 중단시킵니다. 앞서 연습했듯 재시도 전에 같은 데이터베이스 탭에서 ROLLBACK하세요. 먼저 커밋된 원장을 확인하세요. 다른 창구가 이미 성공했을 수도 있습니다. 대화형 SQL 오류와 일반적인 0행 결과에는 서로 다른 판단이 필요합니다.

요약

저장한 SQL은 실제 조건부 자리 확보를 정확히 하나의 전체 그룹 확정으로 연결합니다. PostgreSQL 기본 연결 두 개로 대기와 재평가를 확인했고 그동안 다른 워크숍은 계속 진행했습니다. 중복 요청 코드는 실제 INSERT 실패를 일으켜 자리 확보를 롤백했으며, 이후 적법한 요청이 여전히 남아 있는 자리를 사용했습니다. 재사용 가능한 파일과 실제 원장은 원본 데이터를 보존하고 자리 부족과 SQL 오류를 구분합니다.