Salvar uma reserva atômica de grupo

Introdução

Um grupo quer participar junto. Reservar uma vaga de uma solicitação para duas pessoas não é sucesso. Você salvará uma instrução SQL reutilizável que retorna uma confirmação real para o grupo inteiro ou não faz alteração quando não há espaço suficiente. Uma falha real de chave única testará se a obtenção de vagas e a confirmação pertencem à mesma transação.

Este lab independente prepara novas oficinas e reservas originais; não usa arquivos do lab anterior. Você precisa dos conceitos de transação, bloqueio de linha e ROLLBACK desse lab, além de SELECT, INSERT e UPDATE básicos do PostgreSQL. Parâmetros nomeados do cliente, conversões para inteiro e uma expressão de tabela comum que modifica dados são explicados aqui. Workshop Desk é um shell; Desk A e Desk B são conexões psql separadas ao mesmo banco de dados em uma única máquina. A duração estimada pelo autor inclui leitura e erros comuns, sem um estudo de tempo com iniciantes.

Crie /home/labex/project/workshop-desk/reserve.sql. Mantenha as ferramentas fornecidas, as definições das tabelas, as reservas originais e o ID, título, capacidade e nota de cada oficina. Seu arquivo contém uma instrução SQL e nenhum comando psql, comando do shell, BEGIN/COMMIT explícito, alteração de configuração nem recurso externo. Ele recebe os quatro parâmetros documentados e retorna os quatro campos da reserva real. SQL seguro equivalente é aceito. Este é um exercício controlado de READ COMMITTED, sem pagamentos, novas tentativas automáticas nem garantias distribuídas de admissão.

Salvar uma obtenção de vagas que alimente uma confirmação

Nesta etapa, você salvará uma instrução que não consegue criar uma confirmação a menos que tenha realmente obtido as vagas do grupo.

Comece em Workshop Desk. ./ executa um programa preparado do diretório atual. O programa de status consulta tabelas reais sem completar trabalho ausente:

./desk status

Seu arquivo aceitará estas variáveis psql: workshop_id, seats, request_code e desk. São parâmetros do cliente fornecidos separadamente para cada solicitação, não colunas do banco de dados nem configurações permanentes do servidor. A forma :'name' pede ao psql para inserir essa variável como um literal de string SQL com aspas seguras; ela preserva um apóstrofo dentro de um código de solicitação. Não a substitua por :name sem aspas. O ::integer do PostgreSQL converte o texto da oficina e do tamanho positivo do grupo em números inteiros.

WITH claimed AS (...) nomeia uma expressão de tabela comum, ou CTE: uma relação intermediária usada por esta instrução. Aqui, a CTE executa um UPDATE condicional. Seu RETURNING workshop_id fornece uma linha apenas quando o grupo solicitado completo cabe. INSERT ... SELECT ... FROM claimed consome essa linha realmente retornada. Portanto, uma obtenção sem linhas não produz INSERT. O RETURNING final informa a reserva realmente inserida. Não releia a tabela base esperando que os novos valores de uma CTE que modifica dados estejam visíveis ali na mesma instrução; use sua relação RETURNING.

A subtração usa o tamanho solicitado, e a condição WHERE verifica esse mesmo tamanho. Uma instrução é executada em uma transação implícita: ou a obtenção e o INSERT têm sucesso, ou um erro SQL desfaz ambos. No teste posterior com dois postos, uma transação explícita manterá essa mesma instrução sem confirmação para que você possa observar seu bloqueio.

Salve a instrução completa. cat > reserve.sql substitui esse arquivo pelo texto a seguir. O marcador SQL do here-document entre aspas impede o shell de alterar seu conteúdo. Cole o SQL de fechamento sozinho em sua linha. Execute isto no shell Workshop Desk, não em nenhum dos postos de banco de dados:

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

Leia o arquivo salvo antes de usá-lo. cat o exibe; um erro de digitação deve ser corrigido na fonte salva, não ocultado em um comando separado:

cat reserve.sql

desk book executa exatamente este arquivo usando parâmetros nativos do psql; não fornece uma consulta de referência, não cria reservas alternativas nem corrige saldos. Seus argumentos são ID da oficina, tamanho positivo do grupo, código único de solicitação e rótulo do posto. Execute uma solicitação normal para duas pessoas:

./desk book 201 2 normal-group A

A linha realmente retornada é:

normal-group|201|2|A

Inspecione a fonte da verdade:

./desk status

A oficina 201 agora tem 3 vagas restantes e 3 vagas reservadas. A família original permanece junto à sua reserva exata de duas pessoas. Uma linha retornada, por si só, não comprova um algoritmo correto: a verificação também consulta linhas reais, preserva originais e chama sua fonte inalterada em dados isolados diferentes, incluindo conexões concorrentes reais. Esses bancos de dados temporários de teste nunca corrigem este posto real.

Se o SQL falhar, o programa imprime o erro nativo e termina com status diferente de zero; não chama isso de falta de vagas. Em uma transação explícita interativa, primeiro use ROLLBACK nesse mesmo posto. Um erro de digitação que não foi confirmado não altera nada. Se confirmou acidentalmente uma reserva de novo teste incorreto, remova apenas seu código exato e recalcule apenas o saldo de sua oficina em uma transação, conforme ensinado no lab anterior. Por exemplo, em Desk A, esta recuperação segura quando não há o erro remove apenas um typo-trial hipotético em 201:

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;

Nunca remova original-family nem uma reserva legitimamente solicitada. Inspecione o status antes de decidir qual trabalho resta. Não execute novamente uma solicitação já bem-sucedida apenas porque um comando posterior falhou.

Testar grupos em espera e uma confirmação com falha

Nesta etapa, você executará o mesmo arquivo salvo em duas conexões nativas independentes, manterá outra oficina funcionando e comprovará que um INSERT com falha não pode consumir vagas.

Mude para Desk A. Seu prompt significa que você está no psql, não no shell. SQL termina com ponto e vírgula. Comandos psql iniciados por barra invertida controlam o cliente e não têm ponto e vírgula. \set define uma variável de cliente nesta conexão; ela não passa para outra aba. \i lê e executa o arquivo nomeado. O nome de arquivo absoluto funciona independentemente do diretório atual do cliente.

Inicie uma transação READ COMMITTED e envie um grupo de duas pessoas para a oficina 202, que inicialmente tem três vagas:

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 vê race-A | 202 | 2 | A e INSERT 0 1: a instrução inseriu uma linha de reserva representando duas vagas. Isso ainda não foi confirmado. O * no prompt marca a transação aberta.

Mude para Desk B e prepare sua solicitação independente:

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 espera pelo bloqueio de linha de A, sem retornar um prompt. Deixe-o esperando e mude para Workshop Desk:

./desk waits

O wait_event_type de Desk B é Lock, com o ID real do processo backend de A em blocked_by. Sob READ COMMITTED, após A confirmar, o UPDATE em espera reavaliará se há vagas suficientes em relação à linha atualizada. O saldo visível anteriormente não é uma reserva.

Ainda em Workshop Desk, use o mesmo arquivo salvo para reservar outra oficina:

./desk book 203 2 other-workshop C

Ele retorna other-workshop|203|2|C e termina enquanto A ainda mantém 202 bloqueada. Não introduza um único bloqueio global que pare desnecessariamente esta oficina não relacionada.

Mude para Desk A:

COMMIT;

Mude para Desk B. Seu arquivo pendente agora termina com zero linhas retornadas e INSERT 0 0. Resta apenas uma vaga, portanto este grupo de duas pessoas não cabe. Não divida o grupo nem insira uma confirmação separada. Encerre a transação de B:

ROLLBACK;

Mude para Workshop Desk:

./desk status

A oficina 202 tem uma vaga restante e exatamente race-A|202|2|A; não há race-B. A oficina 203 tem duas vagas restantes e sua reserva de duas pessoas. Verifique o relacionamento, não apenas um código de saída de sucesso.

Agora faça o INSERT falhar enquanto ainda há espaço suficiente para obter uma vaga em 202. Uma chave primária exige que cada código de solicitação seja único em toda a tabela de reservas, não apenas dentro de uma oficina. O código original-family já pertence a 201. Reutilizá-lo aqui deve produzir SQLSTATE 23505, uma violação de chave única. Esta é uma falha esperada; não é um resultado de indisponibilidade:

./desk book 202 1 original-family A

A execução do arquivo informa seu erro nativo e termina com status 3. Capture esse status imediatamente, antes que qualquer outro comando do shell substitua $? (o status de saída do comando anterior):

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

Espere Previous native exit: 3. Um status diferente de zero distingue o erro de um resultado bem-sucedido sem linhas. A transação implícita da instrução deve desfazer a obtenção de vaga anterior. Consulte os registros novamente:

./desk status

A oficina 202 ainda tem uma vaga, e original-family está inalterada em 201. Um atalho como ON CONFLICT DO NOTHING após a obtenção pode ocultar a falha do INSERT enquanto confirma o decremento. Isso não é uma recusa atômica: a contagem deixaria de corresponder às reservas reais. A verificação testa esse caso com espaço suficiente, para que uma obtenção sem linhas não faça acidentalmente o teste de erro passar.

Use um novo código de solicitação para reservar essa última vaga legitimamente:

./desk book 202 1 after-error A

Espere after-error|202|1|A. Agora solicite outro grupo de duas pessoas quando não resta nenhuma vaga:

./desk book 202 2 too-late B

O programa imprime No booking returned. Inspect the real ledger before drawing a conclusion. Esta chamada termina com zero porque o SQL era válido e não retornou reserva; ela não deve deixar uma linha too-late nem alterar o saldo.

./desk status

As novas reservas finais são normal-group (201, 2, A), race-A (202, 2, A), other-workshop (203, 2, C) e after-error (202, 1, A), além da família original intocada. Não há race-B nem too-late. As vagas restantes são 3, 0 e 2, respectivamente.

Se cancelar um arquivo em espera com Ctrl+C, SQLSTATE 57014 aborta sua transação explícita. Conforme praticado anteriormente, use ROLLBACK nessa mesma aba do banco de dados antes de tentar novamente. Primeiro inspecione os registros confirmados: o outro posto pode já ter obtido sucesso. Um erro SQL interativo e um resultado comum de zero linhas exigem decisões diferentes.

Resumo

Seu SQL salvo transforma uma obtenção condicional real de vagas em exatamente uma confirmação de grupo inteiro. Duas conexões nativas demonstraram espera e reavaliação, enquanto outra oficina continuava. Um código de solicitação duplicado causou uma falha real de INSERT e desfez sua obtenção de vaga; uma solicitação legítima posterior usou então a vaga ainda disponível. O arquivo reutilizável e os registros reais preservam os dados originais e diferenciam falta de vagas de erro SQL.