Introduction
Un groupe souhaite participer ensemble. Réserver une place pour une demande de deux personnes n’est pas une réussite. Vous enregistrerez une instruction SQL réutilisable qui renvoie une véritable confirmation pour tout le groupe ou ne change rien lorsque la capacité est insuffisante. Un véritable échec de clé unique testera si l’attribution et la confirmation appartiennent à la même transaction.
Ce TP indépendant prépare de nouveaux ateliers et réservations d’origine ; il n’utilise aucun fichier du TP précédent. Vous devez maîtriser ses concepts de transaction, de verrou de ligne et de ROLLBACK, ainsi que les bases de SELECT, INSERT et UPDATE dans PostgreSQL. Les paramètres nommés du client, les conversions en entier et une expression de table commune modificatrice sont expliqués ici. Workshop Desk est un shell ; Desk A et Desk B sont des connexions psql distinctes à la même base sur une seule machine. La durée estimée par l’auteur comprend la lecture et les erreurs ordinaires, sans étude chronométrée auprès de débutants.
Créez /home/labex/project/workshop-desk/reserve.sql. Conservez les outils fournis, les définitions des tables, les réservations d’origine et l’identifiant, le titre, la capacité et la note de chaque atelier. Votre fichier contient une seule instruction SQL, sans commandes psql, commandes shell, BEGIN/COMMIT explicites, changements de configuration ni ressources externes. Il prend les quatre paramètres documentés et renvoie les quatre champs de la véritable réservation. Du SQL sûr équivalent est accepté. C’est un exercice READ COMMITTED contrôlé, sans paiements, nouvelles tentatives automatiques ni garanties d’admission distribuées.
Enregistrer une attribution qui alimente une confirmation
Dans cette étape, vous enregistrerez une instruction incapable de créer une confirmation sans avoir réellement obtenu les places du groupe.
Commencez dans Workshop Desk. ./ exécute un programme préparé du répertoire courant. Le programme d’état lit les véritables tables sans compléter le travail manquant :
./desk status
L’atelier 201 possède une capacité de 6, une place réservée à l’origine et 5 places restantes. L’atelier 202 a 3 places et 203 en a 4. original-family|201|1|Original doit être conservé. Le premier affichage utilise workshop_id|title|capacity|places_left|booked_seats ; le second utilise request_code|workshop_id|seats|desk.
Votre fichier acceptera ces variables psql : workshop_id, seats, request_code et desk. Ce sont des paramètres client fournis séparément pour chaque demande, pas des colonnes de base ni des réglages permanents du serveur. La forme :'name' demande à psql d’insérer cette variable comme un littéral de chaîne SQL correctement protégé par des guillemets ; elle préserve une apostrophe dans un code de demande. Ne la remplacez pas par :name sans guillemets. Le ::integer de PostgreSQL convertit le texte de l’atelier et de la taille de groupe positive en nombres entiers.
WITH claimed AS (...) nomme une expression de table commune, ou CTE : une relation intermédiaire utilisée par cette instruction. Ici, la CTE effectue un UPDATE conditionnel. Son RETURNING workshop_id fournit une ligne uniquement lorsque tout le groupe demandé peut être accueilli. INSERT ... SELECT ... FROM claimed utilise cette véritable ligne renvoyée. Une attribution vide ne produit donc aucun INSERT. Le RETURNING final indique la réservation réellement insérée. Ne relisez pas la table de base en espérant y voir les nouvelles valeurs d’une CTE modificatrice dans la même instruction ; utilisez sa relation RETURNING.
La soustraction utilise la taille demandée et la condition WHERE vérifie cette même taille. Une instruction s’exécute dans une transaction implicite : soit l’attribution et l’INSERT réussissent tous deux, soit une erreur SQL annule les deux. Dans l’essai ultérieur à deux guichets, une transaction explicite gardera cette même instruction non validée afin que vous puissiez observer son verrou.
Enregistrez l’instruction complète. cat > reserve.sql remplace ce fichier par le texte suivant. Le marqueur de here-document SQL entre guillemets empêche le shell d’en modifier le contenu. Collez le SQL final seul sur sa ligne. Exécutez ceci dans le shell Workshop Desk, pas dans l’un des guichets de base de données :
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
Lisez le fichier enregistré avant de l’utiliser. cat l’affiche ; une faute doit être corrigée dans la source enregistrée, pas masquée dans une commande séparée :
cat reserve.sql
desk book exécute ce fichier exact avec les paramètres psql natifs ; il ne fournit pas de requête de référence, ne crée pas de réservations de remplacement et ne répare pas les soldes. Ses arguments sont l’identifiant d’atelier, la taille positive du groupe, le code de demande unique et le libellé de guichet. Exécutez une demande normale de deux personnes :
./desk book 201 2 normal-group A
La véritable ligne renvoyée est :
normal-group|201|2|A
Examinez la source de vérité :
./desk status
L’atelier 201 possède maintenant 3 places restantes et 3 places réservées. La famille d’origine reste aux côtés de votre réservation exacte de deux personnes. Une ligne renvoyée seule ne prouve pas la solidité d’un algorithme : la vérification lit aussi les véritables lignes, préserve les originaux et appelle votre source inchangée sur différentes données isolées, y compris de véritables connexions concurrentes. Ces bases temporaires de test ne réparent jamais ce poste actif.
Si le SQL échoue, le programme affiche l’erreur native et se termine avec un code non nul ; il ne présente pas cela comme un manque de places. Dans une transaction explicite interactive, utilisez d’abord ROLLBACK dans ce même guichet. Une faute qui n’a pas été validée ne change rien. Si vous avez validé accidentellement une réservation de nouvel essai erroné, supprimez uniquement son code exact et recalculez uniquement le solde de son atelier dans une transaction, comme enseigné au TP précédent. Par exemple, dans Desk A, cette récupération sans effet en l’absence d’erreur retire seulement un hypothétique typo-trial sur 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;
Ne supprimez jamais original-family ni une réservation légitime demandée. Examinez l’état avant de décider quel travail reste à faire. Ne relancez pas une demande déjà réussie simplement parce qu’une commande ultérieure a échoué.
Tester des groupes en attente et une confirmation en échec
Dans cette étape, vous exécuterez le même fichier enregistré dans deux connexions natives indépendantes, laisserez un autre atelier continuer et prouverez qu’un INSERT en échec ne peut pas consommer de places.
Passez à Desk A. Son invite signifie que vous êtes dans psql, pas dans le shell. Le SQL se termine par des points-virgules. Les commandes psql commençant par une barre oblique inverse contrôlent le client et n’ont pas de point-virgule. \set définit une variable client dans cette seule connexion ; elle n’est pas transmise à un autre onglet. \i lit et exécute le fichier nommé. Le nom de fichier absolu fonctionne indépendamment du répertoire courant du client.
Démarrez une transaction READ COMMITTED et soumettez un groupe de deux pour l’atelier 202, qui possède initialement trois places :
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 voit race-A | 202 | 2 | A et INSERT 0 1 : l’instruction a inséré une ligne de réservation représentant deux places. Ce n’est pas encore validé. Le * dans l’invite indique la transaction ouverte.
Passez à Desk B et préparez sa demande indépendante :
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 attend le verrou de ligne de A, sans retour de l’invite. Laissez-le attendre et passez à Workshop Desk :
./desk waits
Le wait_event_type de Desk B est Lock, avec le véritable identifiant de processus serveur de A dans blocked_by. Sous READ COMMITTED, après validation par A, l’UPDATE en attente revérifiera la disponibilité suffisante sur la ligne mise à jour. Le solde visible précédemment n’est pas une réservation.
Toujours dans Workshop Desk, utilisez le même fichier enregistré pour réserver un autre atelier :
./desk book 203 2 other-workshop C
Il renvoie other-workshop|203|2|C et se termine pendant que A garde encore le verrou sur 202. N’introduisez pas un verrou global unique qui arrêterait inutilement cet atelier sans rapport.
Passez à Desk A :
COMMIT;
Passez à Desk B. Son fichier en attente se termine maintenant avec zéro ligne renvoyée et INSERT 0 0. Une seule place reste ; ce groupe de deux personnes ne peut donc pas être accueilli. Ne divisez pas le groupe et n’insérez pas de confirmation séparée. Terminez la transaction de B :
ROLLBACK;
Passez à Workshop Desk :
./desk status
L’atelier 202 possède une place restante et exactement race-A|202|2|A ; il n’y a pas de race-B. L’atelier 203 possède deux places restantes et sa réservation de deux personnes. Vérifiez la relation, pas seulement un code de sortie de réussite.
Faites ensuite échouer l’INSERT alors qu’il reste assez de place pour en attribuer une sur 202. Une clé primaire impose l’unicité de chaque code de demande dans toute la table bookings, pas seulement au sein d’un atelier. Le code original-family appartient déjà à 201. Le réutiliser ici doit produire SQLSTATE 23505, une violation de clé unique. C’est un échec attendu ; ce n’est pas un résultat d’indisponibilité :
./desk book 202 1 original-family A
L’exécution du fichier signale son erreur native et se termine avec le statut 3. Capturez ce statut immédiatement, avant qu’une autre commande shell ne remplace $? (le statut de sortie de la commande précédente) :
printf 'Previous native exit: %s\n' "$?"
Vous devez obtenir Previous native exit: 3. Un statut non nul distingue l’erreur d’un résultat réussi sans ligne. La transaction implicite de l’instruction doit annuler l’attribution précédente. Relisez le registre :
./desk status
L’atelier 202 possède toujours une place et original-family est inchangé sur 201. Un raccourci tel que ON CONFLICT DO NOTHING après l’attribution peut masquer l’échec de l’INSERT tout en validant la décrémentation. Ce n’est pas un refus atomique : le compteur cesserait de correspondre aux véritables réservations. La vérification teste ce cas avec assez de places ; une attribution vide ne peut donc pas faire réussir accidentellement le test d’erreur.
Utilisez un nouveau code de demande pour réserver légitimement cette dernière place :
./desk book 202 1 after-error A
Vous devez obtenir after-error|202|1|A. Demandez maintenant un autre groupe de deux lorsqu’il ne reste aucune place :
./desk book 202 2 too-late B
Le programme affiche No booking returned. Inspect the real ledger before drawing a conclusion. Cet appel se termine avec le code zéro, car le SQL était valide et n’a renvoyé aucune réservation ; il ne doit laisser aucune ligne too-late ni modifier le solde.
./desk status
Les nouvelles réservations finales sont normal-group (201, 2, A), race-A (202, 2, A), other-workshop (203, 2, C) et after-error (202, 1, A), plus la famille d’origine inchangée. Il n’y a ni race-B ni too-late. Les places restantes sont respectivement 3, 0 et 2.
Si vous annulez un fichier en attente avec Ctrl+C, SQLSTATE 57014 met sa transaction explicite en échec. Comme pratiqué précédemment, utilisez ROLLBACK dans ce même onglet de base de données avant de réessayer. Examinez d’abord le registre validé : l’autre guichet a peut-être déjà réussi. Une erreur SQL interactive et un résultat ordinaire à zéro ligne exigent des décisions différentes.
Résumé
Votre SQL enregistré transforme une véritable attribution conditionnelle de places en exactement une confirmation pour tout le groupe. Deux connexions natives ont démontré l’attente et la réévaluation, tandis qu’un autre atelier continuait. Un code de demande en double a provoqué un véritable échec d’INSERT et annulé son attribution ; une demande légitime ultérieure a ensuite utilisé la place toujours disponible. Le fichier réutilisable et le registre actif préservent les données d’origine et distinguent un manque de places d’une erreur SQL.
