Introduction
Deux guichets vendent des places pour un atelier créatif. Un visiteur attend une place confirmée, pas un compteur qui semble simplement rester positif ou nul. Vous commencerez par une réservation de groupe normale, puis reproduirez deux confirmations pour une seule place sur un atelier d’entraînement jetable. Enfin, vous protégerez cette place tout en laissant un autre atelier continuer à accueillir des visiteurs.
Vous devez savoir naviguer dans un terminal et utiliser SELECT, WHERE, INSERT et UPDATE dans PostgreSQL. Les transactions, l’identité des connexions, les verrous de ligne, RETURNING et la récupération sont expliqués ici. PostgreSQL et les données fictives sont prêts. Workshop Desk est un shell ; Desk A et Desk B sont des connexions natives psql distinctes à la même base de données sur une seule machine. Gardez les trois onglets ouverts. Le texte et les libellés de l’environnement d’exécution sont en anglais. La lecture et les erreurs ordinaires sont comprises dans la durée estimée par l’auteur ; il ne s’agit pas d’une étude chronométrée auprès de débutants.
Ajoutez uniquement les réservations d’essai nommées et mettez à jour le places_left de leurs ateliers. L’exercice de concurrence autorise explicitement la suppression de vos propres réservations d’essai. Préservez les réservations d’origine, les identifiants, titres, capacités et notes des ateliers, les définitions des tables et les outils préparés. Ne changez pas les paramètres du serveur et ne créez pas de tables. Cet exercice utilise l’isolation READ COMMITTED de PostgreSQL sur une seule base ; les paiements, les nouvelles tentatives et les interblocages entre plusieurs ateliers sont hors périmètre.
Effectuer une réservation de groupe normale
Dans cette étape, vous relierez une véritable confirmation de groupe au nombre de places qu’elle consomme.
Commencez dans Workshop Desk, au shell. ./ exécute un programme du répertoire courant. desk status lit les véritables tables ; il ne les répare pas. Son premier résultat liste workshop_id|title|capacity|places_left|booked_seats. Le suivant liste request_code|workshop_id|seats|desk. Les barres verticales séparent les champs.
./desk status
L’atelier 101 a une capacité de 5, une place réservée à l’origine et 4 places restantes. L’atelier 102 possède une place et aucune réservation. Conservez original-family sur l’atelier 101 exactement en l’état.
Passez à Desk A. Son invite Desk A workshops=> signifie que vous êtes dans le client psql de PostgreSQL, connecté à la base workshops. Saisissez du SQL ici, pas des commandes shell. Un point-virgule termine une instruction SQL. Une connexion est une conversation avec le serveur de base de données ; pg_backend_pid() identifie son processus serveur. Une valeur différente dans Desk B identifiera une connexion indépendante.
SELECT pg_backend_pid(), current_user;
L’utilisateur est workshop_write ; l’identifiant de processus varie. BEGIN démarre une transaction : un ensemble de modifications qui deviennent visibles ensemble à COMMIT. Jusque-là, une autre connexion ne peut pas voir votre nouvelle réservation. ROLLBACK abandonne les modifications non validées. Dans ce premier exemple, aucun autre guichet ne vend l’atelier 101.
RETURNING affiche les lignes réellement modifiées par UPDATE. Soustrayez 2 places et insérez la réservation correspondante de deux places dans la même transaction. Le texte utilise des guillemets simples ; le code de demande identifie cette réservation de façon unique.
BEGIN;
UPDATE workshops
SET places_left = places_left - 2
WHERE workshop_id = 101
RETURNING workshop_id, places_left;
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('normal-group', 101, 2, 'A');
La mise à jour renvoie 101 | 2. L’invite contient *, qui indique une transaction ouverte. Passez à Workshop Desk et examinez le registre validé :
./desk status
Il indique toujours 4 places et uniquement la réservation d’origine pour l’atelier 101. Revenez à Desk A et terminez :
COMMIT;
Passez à Workshop Desk :
./desk status
L’atelier 101 possède maintenant 2 places restantes et 3 places réservées : celle d’origine plus votre groupe de deux. Sa véritable réservation est normal-group|101|2|A. Une transaction réussie préserve capacity = places_left + booked_seats. La première vérification lit ces faits métier ; elle ne déduit pas comment vous les avez saisis.
Si vous faites une faute de SQL dans une transaction, l’invite peut afficher !. La transaction est alors en échec : tout SQL supplémentaire échouera jusqu’à ROLLBACK;. Annulez dans ce même guichet, examinez ./desk status dans Workshop Desk et réessayez uniquement le travail qui n’a pas été validé. Ne répétez jamais aveuglément une demande déjà validée. Un conflit de code de demande est une erreur, pas une deuxième confirmation.
Reproduire une décision de disponibilité périmée
Dans cette étape, vous produirez volontairement un registre d’entraînement invalide, observerez pourquoi un compteur positif ou nul ne suffit pas et limiterez les dégâts à l’atelier 102.
Passez à Desk A. Cette expérience écrit volontairement un nombre périmé au lieu de calculer une attribution conditionnelle. Ne l’utilisez pas comme méthode finale de réservation. Le niveau d’isolation READ COMMITTED donne à chaque instruction une vue des données validées au début de cette instruction ; un SELECT antérieur ne réserve pas son résultat.
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT workshop_id, places_left FROM workshops WHERE workshop_id = 102;
Passez à Desk B. Confirmez que son identifiant de processus diffère de celui de Desk A, puis démarrez sa propre transaction et lisez la même place :
SELECT pg_backend_pid(), current_user;
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT workshop_id, places_left FROM workshops WHERE workshop_id = 102;
Les deux SELECT renvoient une place. Aucun ne l’a réservée. Passez à Desk A et enregistrez une décision fondée sur ce nombre lu auparavant :
UPDATE workshops SET places_left = 0 WHERE workshop_id = 102;
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('unsafe-A', 102, 1, 'A');
Laissez la transaction de A ouverte. Passez à Desk B et saisissez uniquement cet UPDATE :
UPDATE workshops SET places_left = 0 WHERE workshop_id = 102;
L’invite ne revient pas encore. C’est une attente de verrou prévue, pas une connexion en panne. A a modifié cette ligne et conserve son verrou de ligne jusqu’à la fin de la transaction. Passez à Workshop Desk :
./desk waits
Le véritable processus serveur de Desk B a wait_event_type égal à Lock, et blocked_by contient l’identifiant de processus de A. Les identifiants varient. L’observateur lit uniquement l’activité de PostgreSQL ; aucune animation ni minuterie ne fournit cet état.
Passez à Desk A :
COMMIT;
Passez à Desk B. Son UPDATE se termine maintenant. Il peut encore fixer à zéro le solde déjà nul, car sa condition WHERE nomme uniquement l’atelier. Ajoutez sa confirmation et validez :
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('unsafe-B', 102, 1, 'B');
COMMIT;
Passez à Workshop Desk :
./desk status
L’atelier 102 possède maintenant une capacité de 1, 0 place restante et 2 places réservées. unsafe-A et unsafe-B existent tous deux. Le solde respectait la vérification de non-négativité de la base, mais un visiteur n’a pas de place. L’attente seule n’a pas corrigé la décision métier périmée. L’étape suivante retire uniquement ces essais explicitement jetables et remplace la décision. La vérification de l’étape accepte cet état d’entraînement ou l’état final correctement réparé ; elle n’utilise pas l’historique du terminal comme preuve de votre investigation.
Attribuer la place avant de la confirmer
Dans cette étape, vous exigerez une véritable attribution conditionnelle avant d’écrire une confirmation et récupérerez une demande en attente annulée.
Passez à Desk A. DELETE supprime uniquement les lignes sélectionnées par sa condition WHERE. IN nomme les deux codes de demande jetables ; ne supprimez pas la réservation d’origine ni la réservation normale. Restaurez uniquement l’atelier d’entraînement 102, dans la même transaction :
BEGIN;
DELETE FROM bookings WHERE request_code IN ('unsafe-A', 'unsafe-B');
UPDATE workshops SET places_left = 1 WHERE workshop_id = 102;
COMMIT;
Entraînez-vous d’abord à annuler une attente de verrou sur l’atelier distinct 104. A abandonnera sa modification avec ROLLBACK ; cet essai ne crée donc aucune réservation :
BEGIN;
UPDATE workshops SET places_left = 0 WHERE workshop_id = 104;
Passez à Desk B :
BEGIN;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 104 AND places_left >= 1
RETURNING workshop_id;
Pendant que B attend, appuyez sur Ctrl+C dans Desk B. PostgreSQL signale canceling statement due to user request, avec le SQLSTATE 57014. SQLSTATE est la catégorie d’erreur PostgreSQL à cinq caractères. Une instruction annulée laisse cette transaction explicite en échec. Montrez la différence avec un résultat vide ordinaire :
SELECT 1;
C’est une erreur attendue : current transaction is aborted, SQLSTATE 25P02. Récupérez dans Desk B :
ROLLBACK;
Passez à Desk A et abandonnez sa modification d’entraînement :
ROLLBACK;
Protégez maintenant la véritable dernière place de l’atelier 102. La condition WHERE vérifie la disponibilité suffisante dans l’UPDATE lui-même. Après l’attente d’une mise à jour concurrente, READ COMMITTED revérifie cette condition sur la ligne mise à jour. RETURNING indique si cette transaction a réellement obtenu la place.
Dans Desk A :
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 102 AND places_left >= 1
RETURNING workshop_id, places_left;
A reçoit 102 | 0 : il a obtenu la place. Uniquement parce qu’une ligne a été renvoyée, créez sa confirmation correspondante :
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('safe-last-A', 102, 1, 'A');
Laissez-la non validée. Passez à Desk B :
BEGIN ISOLATION LEVEL READ COMMITTED;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 102 AND places_left >= 1
RETURNING workshop_id, places_left;
B attend. Passez à Workshop Desk et lisez la véritable attente :
./desk waits
Un verrou de ligne ne doit pas arrêter les ateliers sans rapport. Toujours dans Workshop Desk, ouvrez une courte troisième connexion. ./db --desk A utilise le même compte de connexion préparé à la base ; cet appel temporaire du shell est indépendant de l’onglet Desk A déjà ouvert. Le here-document entre guillemets envoie toutes les lignes jusqu’au SQL final à ce client, puis le ferme ; gardez le marqueur final seul sur sa ligne. Le C stocké dans la réservation indique cette troisième demande.
./db --desk A <<'SQL'
BEGIN;
UPDATE workshops SET places_left = places_left - 1
WHERE workshop_id = 103 AND places_left >= 1
RETURNING workshop_id, places_left;
INSERT INTO bookings (request_code, workshop_id, seats, desk)
VALUES ('other-workshop', 103, 1, 'C');
COMMIT;
SQL
Elle renvoie 103 | 2 et valide pendant que l’atelier 102 est encore verrouillé. Passez à Desk A :
COMMIT;
Passez à Desk B. Son UPDATE renvoie maintenant zéro ligne et UPDATE 0 : le nouveau solde nul ne satisfait pas places_left >= 1. C’est une instruction SQL réussie sans attribution, pas une erreur SQL. N’insérez pas de confirmation. Terminez la transaction de B :
ROLLBACK;
Passez à Workshop Desk :
./desk status
L’atelier 102 possède une réservation, safe-last-A|102|1|A, et zéro place. L’atelier 103 possède other-workshop|103|1|C et deux places. L’atelier 104 conserve sa place d’origine et aucune réservation ; 101 conserve son groupe normal et sa famille d’origine. Chaque atelier satisfait maintenant l’équation de solde.
Pour une confirmation ordinaire erronée de nouvel essai, commencez par exécuter ROLLBACK dans ce guichet pour annuler toute transaction encore ouverte. Dans cet environnement d’entraînement, vous pouvez supprimer uniquement votre propre code erroné et recalculer uniquement le solde de son atelier dans une transaction. Par exemple, si vous avez validé par erreur typo-trial sur l’atelier 103, utilisez ce qui suit dans Desk A, puis examinez l’état avant de réessayer la demande prévue. La sous-requête additionne les places réellement réservées ; COALESCE(..., 0) utilise zéro lorsqu’aucune réservation ne reste. C’est une récupération explicite d’entraînement, pas une partie de l’algorithme de réservation :
BEGIN;
DELETE FROM bookings WHERE request_code = 'typo-trial' AND workshop_id = 103;
UPDATE workshops
SET places_left = capacity -
(SELECT COALESCE(SUM(seats), 0) FROM bookings WHERE workshop_id = 103)
WHERE workshop_id = 103;
COMMIT;
L’exemple ne change rien lorsque vous n’avez pas commis cette erreur. Ne l’utilisez jamais pour supprimer une réservation d’origine ou une demande légitime. Le TP suivant relie l’attribution et la confirmation dans une seule instruction réutilisable, afin que l’appelant ne puisse pas insérer accidentellement après une attribution vide.
Résumé
Vous avez créé une véritable réservation normale, reproduit une surréservation due à une lecture périmée et protégé la dernière place avec un UPDATE conditionnel et une confirmation dans la même transaction. Les observations natives des verrous ont montré un guichet concurrent en attente pendant qu’un autre atelier continuait. Vous avez aussi annulé une instruction en attente et récupéré sa transaction en échec avec ROLLBACK. Votre registre final préserve les réservations d’origine et relie chaque place confirmée à la capacité restante.
