MySQL: "unique"-artig, über mehrere Datensätze:-|

[x51398]

Guten Tag,

ich habe vor Jahren ein Ticketvorverkaufsportal programmiert, welches bislang auch problemlos läuft. Es wird für eine jährlich stattfindende Veranstaltungsreihe genutzt, der Ansturm bei Verkaufsstart ist immer immens (ca. 24.000 Tickets, ca. 600.000 Serverrequests in der ersten Stunde).

Bisher können nur komplette Tische gebucht werden; um Dopplungen zu vermeiden, werden die gebuchten oder im Buchungsprozess befindlichen Tische in einer Tabelle mit einem UNIQUE-Key über Tisch-ID und Veranstaltungs-ID gespeichert - jeder Tisch also maximal einmal pro Veranstaltung. So weit, so gut:-)

Nun soll es aber auch Tische mit Einzelsitzplätzen (1-10 Plätze) geben, also kann der gleiche Tisch einer Veranstaltung mehrfach gebucht werden. Die o.a. Lösung ist also nicht geeignet - oder? Gibt es eine Möglichkeit, ein INSERT nur stattfinden zu lassen, wenn die Summe der bereits gebuchten Plätze eines Tisches < 10 ist?

Vielen Dank, viele Grüße Basti

[Rolf b]

Das wirst Du über eine Transaktion machen müssen. Das Folgende ist Pseudocode, den Du nach vollständiger SQLifizierung in eine Stored Procedure (=MySQL Routing) packen könntest. Du musst natürlich noch einen Erfolgscode zurückgeben.

Kritisch ist es nur, wenn Du demjenigen, der die Seite zuerst aufrief, Priorität geben willst. Das klappt damit nicht, es ist eine Art Windhundprinzip: Wer zuerst "verbindlich buchen" drückt, bekommt den Tisch.

Und je nach Isolationsstufe der Transaktion kann es auch ärgerliche Kollisionen geben: User 1 bucht 7 Plätze, User 2 bucht 5 Plätze. Beide machen den Insert, beide bekommen die Meldung "Tisch voll", beide fliegen raus. User 3 kommt, bucht 6 Plätze und dreht beiden die Nase.

Wenn Du sowas verhindern willst, musst Du vermutlich einen Reservierungs-Timestamp hinzufügen, erstmal blind INSERTen und dann nachprüfen was passiert ist. Wird die Anzahl überschritten, werden beginnend mit der jüngsten alle Buchungen entfernt, die zur Überschreitung führten. Das löst aber mehr Aufwand in der DB aus.

BEGIN TRANSACTION
   INSERT (Tischnummer, AnzPlätze) Value (?, ?)

   SELECT SUM(AnzPlätze) WHERE Tischnummer = ?

   IF (summe <= maxPlätze)
      COMMIT
   ELSE
      ROLLBACK

END TRANSACTION

Rolf

[TS]

Hello,

Nun soll es aber auch Tische mit Einzelsitzplätzen (1-10 Plätze) geben, also kann der gleiche Tisch einer Veranstaltung mehrfach gebucht werden. Die o.a. Lösung ist also nicht geeignet - oder? Gibt es eine Möglichkeit, ein INSERT nur stattfinden zu lassen, wenn die Summe der bereits gebuchten Plätze eines Tisches < 10 ist?

Entweder erweitern um die Platznummer, weil die Zuschauer sicherlich auch genauer wissen wollen, wo sie sitzen, oder Subquery einbauen, ggf. mittels Trigger. Ein Before-Insert-Trigger wird vermutlich die wenigste Arbeit machen, da er nur an einer Stelle (in der DB) eingabaut werden muss. Allerdings müsste man für den User bestimmt die Fehlermeldung der Datenbank auswerten, damit er weiß, warum er nicht buchen durfte.

Und man sollte bedenken, dass freie und besetze Plätze ja auch generell angezeigt werden müssten.

Liebe Grüße
Tom S.

[dedlfix]

Tach!

Gibt es eine Möglichkeit, ein INSERT nur stattfinden zu lassen, wenn die Summe der bereits gebuchten Plätze eines Tisches < 10 ist?

Nein, es sei denn, du bastelst da was mit Triggern. Oder du führst den Insert-Vorgang per Konvention nur über eine Stored Procedure aus, die das vorher prüft. Prüfung und Einfügen sollten in eine Transaction, oder anderweitig verhindern, dass kein TOCTTOU-Problem auftritt.

Oder ganz anders, du baust um von Tisch auf Einzelplätze.

dedlfix.

[x51398]

Dank Euch für die Lösungsansätze! Die explizite Wahl eines bestimmten Sitzplatzes ist nicht vorgesehen, so dass ich mich mit der Stored Procedur weitergehend beschäftigen werde.

[Matthias Apsel]

Hallo x51398,

Die explizite Wahl eines bestimmten Sitzplatzes ist nicht vorgesehen,

Es muss ja nicht ein konkreter Sitzplatz sein, aber du könntest die Plätze eines Tisches dennoch von einander unterscheiden. Dass du dann in Wirklichkeit keine Sitzplatznummern auf die Tische stellst, sondern nur Tischnummern vergibst, ist unschädlich.

Bis demnächst
Matthias

[TS]

Hello,

Die explizite Wahl eines bestimmten Sitzplatzes ist nicht vorgesehen,

Es muss ja nicht ein konkreter Sitzplatz sein, aber du könntest die Plätze eines Tisches dennoch von einander unterscheiden. Dass du dann in Wirklichkeit keine Sitzplatznummern auf die Tische stellst, sondern nur Tischnummern vergibst, ist unschädlich.

Ein Unique-Kombinationsschlüssel scheint mir auch die sauberste Lösung zu sein.

Außerdem muss er noch sein API/GUI umbauen an der Stelle, an der angezeigt werden soll, an welchem Tisch noch wieviel Plätze frei sind.

Liebe Grüße
Tom S.

[x51398]

Es muss ja nicht ein konkreter Sitzplatz sein, aber du könntest die Plätze eines Tisches dennoch von einander unterscheiden. Dass du dann in Wirklichkeit keine Sitzplatznummern auf die Tische stellst, sondern nur Tischnummern vergibst, ist unschädlich.

Ein Unique-Kombinationsschlüssel scheint mir auch die sauberste Lösung zu sein.

Ihr habt recht, die Lösung mit dem Unique-Key gefällt mir eigentlich auch am besten. Ich werde berichten...:-)

[TS]

Hello,

Ein Unique-Kombinationsschlüssel scheint mir auch die sauberste Lösung zu sein.

Ihr habt recht, die Lösung mit dem Unique-Key gefällt mir eigentlich auch am besten. Ich werde berichten...:-)

Da die Tische und Plätze doch vorher sowieso bekannt sind, kann man auch "direktgestreut" arbeiten. Für jeden Platz an jedem Tisch gibt es von vornherein einen Datensatz und der kann geändert werden in "frei" und "besetzt". Da brauchst Du noch nicht einmal einen extra-Key. Und da kann man das Update dann von der Where-Klausel abhängig machen. "Wenn frei, dann trage den Sitzbucher ein und setze auf 'besetzt'"

Liebe Grüße
Tom S.

[TS]

Hello,

Dank Euch für die Lösungsansätze! Die explizite Wahl eines bestimmten Sitzplatzes ist nicht vorgesehen, so dass ich mich mit der Stored Procedur weitergehend beschäftigen werde.

Soweit mir kennt ist, werden aber die Einzelstatements in einer Stored Routine trotzdem nicht gemeinschaftlich atomar. Du müsstest in der Routine also einmal vor dem Einfügen und einmal nach dem Einfügen prüfen, ob das Limit überschritten wurde.

Auch bei Transaktionen ist das mMn nicht sichergestellt, wenn man nicht Table- oder Row-Locking benutzt.

Die Einfachste Art, die Anzahl zu prüfen, ist einen zusätzlichen Datensatz mit der Anzahl mitzuführen, der nur updated wird, wenn die Anzahl kleiner als Limit ist. Dann kannst Du in der Routine erst diesen Datensatz updaten (versuchen) und wenn es geklappt hat, den Sitzer in die bisherige Sitzer-Tabelle eintragen. Das Update-Statement als solches ist ja atomar.

Liebe Grüße
Tom S.

[Rolf b]

Das ist richtig, man muss Inserten und dann prüfen. Es bleibt aber mit der Routine immerhin im SQL Server und kostet daher weniger Turnaround-Zeit.

Ein Table-Lock ist riskant, wenn es einen Run auf den Server gibt. Man will schreiben, braucht also einen Write-Lock, und das führt zu einer Serialisierung aller Zugriffe, inclusive der lesenden. Das skaliert dann nicht im geringsten, damit fackelt man keine 600.000 Server Requests in einer Stunde ab. In dem Fall hilft auch keine Webserver-Farm, weil die DB den Engpass bildet.

Wieweit einem ein Pre-Insert Trigger auf die atomaren Beine helfen kann, weiß ich nicht.

Ich finde aber gerade noch den SELECT FOR UPDATE Befehl von MySQL, damit liest man eine Row und sperrt sie sofort für Updates. Es setzt sinnvollerweise voraus, dass man InnoDB als Database Storage Engine verwendet, und eine Transaktion starten muss man natürlich auch. Das TOCTTOU Problem sollte sich damit nicht stellen. Man KANN auf diese Weise freie Plätze reservieren (wenn man eine Row pro Platz hat), oder einen Tisch, um dann daran die freien Plätze auf den neuen Stand zu bringen.

Bevor Du mit deiner Lösung live gehst, MUSST Du Dir überlegen, wie Du das Produkt lasttestet. Ggf. brauchst Du Scripte, die nichts weiter tun als in kürzester Zeit die entsprechenden Requeste für Tischreservierungen aufzurufen. Mir sagte mal einer: Das schlimmste, was einem im Web passieren kann, ist Erfolg. Weil man dann auf einmal merkt, wo die Site in Last-Engpässe gerät :)

Rolf

[TS]

Hello,

Wieweit einem ein Pre-Insert Trigger auf die atomaren Beine helfen kann, weiß ich nicht.

Keide4r gar nicht. Der kann nur helfen, ohnehin Sinnloses gar nicht erst zu probieren. Und wenn man dann das Update durchgeführt hat, muss man trotzdem noch testen, ob nicht trotzdem Unerlaubtes passiert ist, also noch einen after-Update-Trigger ausführen, der ggf. den DS wieder löscht.

Das Problem liegt aber mMn in der falschen Modellierung. WIe ich schon schrieb, sollte man das Modell dahingehend ändern (vereinfacht):

Anzahl_pro_Tisch        Buchungen
-------------—–-        ---------------—-- 
id_tisch                id_tisch            | ggf. unique
anzahl (0 .. n)         Platz-Nr   (1 .. n) | index
                        Name
                        Kontodaten

Man kann dann die Limiten unabhängig von der nachgeschalteten eigentlichen Buchung prüfen. Das geht auch ganz ohne Transaktion und Sperren, denn ein
update anzahl_pro_tisch set anzahl = anzahl + x where (anzahl + x) < $max
ist als Update atomar und somit nicht zu stören durch andere Requests. Die Eintragung in die Buchungsliste kann dann bei Erfolg des Update-Statements ebenfalls erfolgen. Die Platznummern würde man hier der Reihe nach vergeben, sie sind nicht vorbestellbar. Sonst wirds noch kompilzierter.

Und normalisieren könnte man selbstverständlich auch noch und eine zus- Kundentabelle anlegen mit Namen, Anschrift, Kontodaten.

Liebe Grüße
Tom S.