Eine atomare Gruppenbuchung speichern

Einführung

Eine Gruppe möchte gemeinsam teilnehmen. Einen Platz für eine Zweipersonenanfrage zu buchen, ist kein Erfolg. Du speicherst eine wiederverwendbare SQL-Anweisung, die eine tatsächliche Bestätigung für die gesamte Gruppe zurückgibt oder bei unzureichendem Platz nichts verändert. Ein tatsächlicher Fehler wegen eines eindeutigen Schlüssels prüft, ob Platzbeanspruchung und Bestätigung zur selben Transaktion gehören.

Dieses unabhängige Lab bereitet neue Workshops und ursprüngliche Buchungen vor; es verwendet keine Dateien aus dem vorherigen Lab. Du benötigst dessen Konzepte zu Transaktionen, Zeilensperren und ROLLBACK sowie grundlegendes PostgreSQL SELECT, INSERT und UPDATE. Benannte Clientparameter, Ganzzahlumwandlungen und eine datenändernde Common Table Expression werden hier erklärt. Workshop Desk ist eine Shell; Desk A und Desk B sind separate psql-Verbindungen zu derselben Datenbank auf einer Maschine. Die vom Autor geschätzte Dauer umfasst Lesen und gewöhnliche Fehler, ohne Zeitstudie mit Anfängern.

Erstelle /home/labex/project/workshop-desk/reserve.sql. Bewahre die bereitgestellten Werkzeuge, Tabellendefinitionen, ursprünglichen Buchungen sowie ID, Titel, Kapazität und Notiz jedes Workshops. Deine Datei enthält eine SQL-Anweisung und keine psql-Befehle, Shell-Befehle, expliziten BEGIN/COMMIT, Konfigurationsänderungen oder externen Ressourcen. Sie nimmt die vier dokumentierten Parameter entgegen und gibt die vier Felder der tatsächlichen Buchung zurück. Gleichwertiges sicheres SQL wird akzeptiert. Dies ist eine kontrollierte READ-COMMITTED-Übung ohne Zahlungen, automatische Wiederholungsversuche oder verteilte Zulassungsgarantien.

Eine Platzbeanspruchung speichern, die eine Bestätigung speist

In diesem Schritt speicherst du eine Anweisung, die keine Bestätigung erzeugen kann, ohne die Plätze der Gruppe tatsächlich beansprucht zu haben.

Beginne in Workshop Desk. ./ führt ein vorbereitetes Programm aus dem aktuellen Verzeichnis aus. Das Statusprogramm liest tatsächliche Tabellen, ohne fehlende Arbeit zu ergänzen:

./desk status

Deine Datei nimmt diese psql-Variablen entgegen: workshop_id, seats, request_code und desk. Sie sind für jede Anfrage separat übergebene Clientparameter, keine Datenbankspalten oder dauerhaften Servereinstellungen. Die Form :'name' fordert psql auf, diese Variable als sicher in Anführungszeichen gesetztes SQL-Zeichenfolgenliteral einzufügen; sie bewahrt einen Apostroph innerhalb eines Anfragecodes. Ersetze sie nicht durch ein unquotiertes :name. PostgreSQLs ::integer wandelt den Workshoptext und den Text der positiven Gruppengröße in ganze Zahlen um.

WITH claimed AS (...) benennt eine Common Table Expression, kurz CTE: eine von dieser Anweisung verwendete Zwischenrelation. Hier führt die CTE ein bedingtes UPDATE aus. Ihr RETURNING workshop_id liefert nur dann eine Zeile, wenn die gesamte angeforderte Gruppe Platz hat. INSERT ... SELECT ... FROM claimed verarbeitet diese tatsächlich zurückgegebene Zeile. Eine leere Platzbeanspruchung erzeugt daher kein INSERT. Das abschließende RETURNING meldet die tatsächlich eingefügte Buchung. Lies nicht erneut die Basistabelle in der Erwartung, dort die neuen Werte einer datenändernden CTE in derselben Anweisung zu sehen; verwende deren RETURNING-Relation.

Die Subtraktion verwendet die angeforderte Größe, und die WHERE-Bedingung prüft dieselbe Größe. Eine Anweisung läuft in einer impliziten Transaktion: Entweder sind Platzbeanspruchung und INSERT beide erfolgreich, oder ein SQL-Fehler rollt beide zurück. Im späteren Test mit zwei Schaltern hält eine explizite Transaktion dieselbe Anweisung ohne Commit offen, damit du ihre Sperre beobachten kannst.

Speichere die vollständige Anweisung. cat > reserve.sql ersetzt diese Datei durch den folgenden Text. Die Here-Dokument-Markierung SQL in Anführungszeichen verhindert, dass die Shell ihren Inhalt ändert. Füge das abschließende SQL allein in seiner Zeile ein. Führe dies in der Shell Workshop Desk aus, nicht in einem der Datenbankschalter:

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

Lies die gespeicherte Datei vor ihrer Verwendung. cat zeigt sie an; ein Tippfehler sollte in der gespeicherten Quelle korrigiert werden, nicht in einem separaten Befehl verborgen:

cat reserve.sql

desk book führt genau diese Datei mit nativen psql-Parametern aus; es liefert keine Referenzabfrage, erstellt keine Ersatzbuchungen und repariert keine Bestände. Seine Argumente sind Workshop-ID, positive Gruppengröße, eindeutiger Anfragecode und Schalterbezeichnung. Führe eine normale Zweipersonenanfrage aus:

./desk book 201 2 normal-group A

Die tatsächlich zurückgegebene Zeile ist:

normal-group|201|2|A

Prüfe die maßgebliche Datenquelle:

./desk status

Workshop 201 hat nun 3 freie und 3 gebuchte Plätze. Die ursprüngliche Familie bleibt neben deiner exakten Zweipersonenbuchung erhalten. Eine zurückgegebene Zeile allein beweist keinen korrekten Algorithmus: Die Prüfung liest außerdem tatsächliche Zeilen, bewahrt Originale und ruft deine unveränderte Quelle auf anderen isolierten Daten auf, einschließlich tatsächlicher konkurrierender Verbindungen. Diese vorübergehenden Testdatenbanken reparieren niemals diesen aktuellen Arbeitsplatz.

Schlägt SQL fehl, gibt das Programm den nativen Fehler aus und endet mit einem Status ungleich null; es bezeichnet dies nicht als Platzmangel. Verwende in einer interaktiven expliziten Transaktion zuerst ROLLBACK in demselben Schalter. Ein Tippfehler ohne bestätigtes Commit ändert nichts. Hast du versehentlich eine neue irrtümliche Testbuchung per Commit bestätigt, entferne nur ihren exakten Code und berechne nur den Bestand ihres Workshops in einer Transaktion neu, wie im vorherigen Lab vermittelt. In Desk A entfernt diese auch bei fehlendem Testeintrag sichere Wiederherstellung zum Beispiel nur ein hypothetisches typo-trial auf 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;

Entferne niemals original-family oder eine berechtigte angeforderte Buchung. Prüfe den Status, bevor du entscheidest, welche Arbeit noch fehlt. Wiederhole keine bereits erfolgreiche Anfrage bloß deshalb, weil ein späterer Befehl fehlgeschlagen ist.

Wartende Gruppen und eine fehlgeschlagene Bestätigung testen

In diesem Schritt führst du dieselbe gespeicherte Datei in zwei unabhängigen nativen Verbindungen aus, lässt einen anderen Workshop weiterlaufen und weist nach, dass ein fehlgeschlagenes INSERT keine Plätze verbrauchen kann.

Wechsle zu Desk A. Seine Eingabeaufforderung bedeutet, dass du in psql bist, nicht in der Shell. SQL endet mit Semikolons. psql-Befehle, die mit einem umgekehrten Schrägstrich beginnen, steuern den Client und haben kein Semikolon. \set setzt eine Clientvariable in dieser einen Verbindung; sie wird nicht in einen anderen Tab übernommen. \i liest die benannte Datei und führt sie aus. Der absolute Dateiname funktioniert unabhängig vom aktuellen Verzeichnis des Clients.

Starte eine READ-COMMITTED-Transaktion und übermittle eine Zweiergruppe für Workshop 202, der anfangs drei Plätze hat:

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 sieht race-A | 202 | 2 | A und INSERT 0 1: Die Anweisung hat eine Buchungszeile eingefügt, die zwei Plätze darstellt. Dies ist noch nicht per Commit bestätigt. Das * in der Eingabeaufforderung markiert die offene Transaktion.

Wechsle zu Desk B und bereite seine unabhängige Anfrage vor:

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 wartet auf As Zeilensperre, ohne eine Eingabeaufforderung zurückzugeben. Lass es warten und wechsle zu Workshop Desk:

./desk waits

Der wait_event_type von Desk B ist Lock, mit As tatsächlicher Backend-Prozess-ID in blocked_by. Unter READ COMMITTED prüft das wartende UPDATE nach As Commit erneut gegen die aktualisierte Zeile, ob genügend Plätze vorhanden sind. Der zuvor sichtbare Bestand ist keine Reservierung.

Buche weiterhin in Workshop Desk mit derselben gespeicherten Datei einen anderen Workshop:

./desk book 203 2 other-workshop C

Es gibt other-workshop|203|2|C zurück und endet, während A weiterhin 202 hält. Führe keine einzelne globale Sperre ein, die diesen unbeteiligten Workshop unnötig stoppt.

Wechsle zu Desk A:

COMMIT;

Wechsle zu Desk B. Seine ausstehende Datei endet nun mit null zurückgegebenen Zeilen und INSERT 0 0. Nur ein Platz bleibt, daher passt diese Zweiergruppe nicht hinein. Teile die Gruppe nicht auf und füge keine separate Bestätigung ein. Beende Bs Transaktion:

ROLLBACK;

Wechsle zu Workshop Desk:

./desk status

Workshop 202 hat einen freien Platz und genau race-A|202|2|A; es gibt kein race-B. Workshop 203 hat zwei freie Plätze und seine Zweipersonenbuchung. Prüfe die Beziehung, nicht nur einen erfolgreichen Exit-Status.

Lass als Nächstes das INSERT fehlschlagen, während noch genügend Raum vorhanden ist, um einen Platz auf 202 zu beanspruchen. Ein Primärschlüssel verlangt, dass jeder Anfragecode in der gesamten Buchungstabelle eindeutig ist, nicht nur innerhalb eines Workshops. Der Code original-family gehört bereits zu 201. Seine Wiederverwendung hier sollte SQLSTATE 23505 erzeugen, eine Verletzung eines eindeutigen Schlüssels. Dies ist ein erwarteter Fehler, kein Ergebnis ohne Verfügbarkeit:

./desk book 202 1 original-family A

Die Dateiausführung meldet ihren nativen Fehler und endet mit Status 3. Erfasse diesen Status sofort, bevor ein anderer Shell-Befehl $? ersetzt, den Exit-Status des vorherigen Befehls:

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

Erwartet wird Previous native exit: 3. Ein Status ungleich null unterscheidet den Fehler von einem erfolgreichen Ergebnis ohne Zeilen. Die implizite Transaktion der Anweisung muss die vorangegangene Platzbeanspruchung rückgängig machen. Lies die Buchungsübersicht erneut:

./desk status

Workshop 202 hat weiterhin einen Platz, und original-family ist auf 201 unverändert. Eine Abkürzung wie ON CONFLICT DO NOTHING nach der Platzbeanspruchung kann den INSERT-Fehler verbergen und dabei den Abzug per Commit bestätigen. Dies ist keine atomare Ablehnung: Die Anzahl würde nicht mehr zu den tatsächlichen Buchungen passen. Die Prüfung testet diesen Fall mit genügend Platz, damit eine leere Platzbeanspruchung den Fehlertest nicht versehentlich bestehen lässt.

Verwende einen neuen Anfragecode, um diesen letzten Platz berechtigt zu buchen:

./desk book 202 1 after-error A

Erwartet wird after-error|202|1|A. Fordere nun eine weitere Zweiergruppe an, wenn keine Plätze mehr frei sind:

./desk book 202 2 too-late B

Das Programm gibt No booking returned. Inspect the real ledger before drawing a conclusion. aus. Dieser Aufruf endet mit null, weil das SQL gültig war und keine Buchung zurückgab; es darf keine too-late-Zeile hinterlassen und keinen Bestand ändern.

./desk status

Die abschließenden neuen Buchungen sind normal-group (201, 2, A), race-A (202, 2, A), other-workshop (203, 2, C) und after-error (202, 1, A), dazu die unberührte ursprüngliche Familie. Es gibt weder race-B noch too-late. Die verbleibenden Plätze sind jeweils 3, 0 und 2.

Brichst du eine wartende Datei mit Ctrl+C ab, versetzt SQLSTATE 57014 ihre explizite Transaktion in den abgebrochenen Zustand. Verwende wie zuvor geübt ROLLBACK in demselben Datenbanktab, bevor du es erneut versuchst. Prüfe zuerst die bestätigte Buchungsübersicht: Der andere Schalter könnte bereits erfolgreich gewesen sein. Ein interaktiver SQL-Fehler und ein gewöhnliches Ergebnis mit null Zeilen verlangen unterschiedliche Entscheidungen.

Zusammenfassung

Dein gespeichertes SQL überführt eine tatsächliche bedingte Platzbeanspruchung in genau eine Bestätigung für die gesamte Gruppe. Zwei native Verbindungen demonstrierten Warten und erneute Prüfung, während ein anderer Workshop weiterlief. Ein doppelter Anfragecode verursachte einen tatsächlichen INSERT-Fehler und rollte dessen Platzbeanspruchung zurück; eine spätere berechtigte Anfrage nutzte daraufhin den weiterhin verfügbaren Platz. Die wiederverwendbare Datei und die aktuelle Buchungsübersicht bewahren ursprüngliche Daten und unterscheiden Platzmangel von einem SQL-Fehler.