[PHP/MySQL] Race Conditions verhindern

[Carny]

Hallo,

ich entwickel gerade ein Script und habe folgendes Problem:

In einem Schritt möchte ich aus einer Datenbank einen bestimmten Eintrag auswählen. Jeder Eintrag hat einen Status (0...3) und der ausgewählte Eintrag soll den Status 0 haben. Direkt danach ändere ich in einem zweiten Schritt den Status auf 1.

Wie löse ich das am klügsten, ohne das während Schritt 1 und Schritt 2 eine Race Condition auftreten kann?

Meine SQL Statements sehen aktuell wie folgt aus:

Schritt 1:

1:
SELECT * FROM codes WHERE state='0' LIMIT 1,1

Schritt 2:

1:
UPDATE codes SET state='1' WHERE id=$id

Am besten wäre, beide Schritte in einen zu verschmelzen, also das ich den Status update und gleichzeitig die ID als Rückgabe selektiere. Ist dies möglich?

Gruß Carny

[Sebmaster]

Schau dir mal Transaktionen an

[ice-breaker]

Transaktionen und SELECT ... LOCK IN EXCLUSIVE MODE

Edit: verdammt, da war einer schneller ^^ Aber Sebmaster, Transaktionen alleine verhindern den angesprochenen Sachverhalt nicht

[Carny]

Also sähe eine Beispiellösung so aus:

1:
2:
3:
4:
LOCK TABLES codes READ, codes WRITE
SELECT * FROM codes WHERE state='0' LIMIT 1,1
UPDATE codes SET state='1' WHERE id=$id
UNLOCK TABLES

Hab ich das richtig verstanden, das alle Anfragen auf eine bereits gesperrte Tabelle dann in eine Warteschlange kommen, anstatt das diese verloren gehen?

Gruß Carny

[Sebmaster]

Zitat von ice-breaker

Aber Sebmaster, Transaktionen alleine verhindern den angesprochenen Sachverhalt nicht

Könntest du das bitte noch genauer erklären?

[transversalis]

Würde denn ein

1:
SELECT * FROM codes WHERE state='0' LIMIT 1,1 FOR UPDATE;

nicht auch die gewünschte Sperre etablieren ?

[ice-breaker]

Zitat von Carny

Hab ich das richtig verstanden, das alle Anfragen auf eine bereits gesperrte Tabelle dann in eine Warteschlange kommen, anstatt das diese verloren gehen?

ja, aber LOCK TABLES ist kein wirklich sinnvoller Ansatz, da du damit jeden Datensatz sperrst, mit Transaktionen würden nur die Datensätze gesperrt werden, die du auch nutzt, dementsprechend könnten die meisten anderen Querys ganz normal weiterarbeiten.

Zitat von Sebmaster

Könntest du das bitte noch genauer erklären?

Datenbanken wie MySQL verwenden MVCC, jede Transaktion sieht also einen konsistenten Snapshot der gesamten Datenbank der bei Beginn der Transaktion definiert wird.
Aus diesem Grunde ist kein Locking für Reads mehr nötig (im Gegensatz zu den definierten Isolation Levels von ANSI-SQL), ein Read wird also nie irgendwelche Daten locken, weder shared noch exklusiv.
Erst beim Schreiben von Daten werden Daten gelockt, das ist aber für das Beispiel von Carny schon zu spät.

Zitat von transversalis

Würde denn ein

nicht auch die gewünschte Sperre etablieren ?

ja würde sie, ist auch die richtige Variante, meines war der Oracle-Befehl
SELECT ... FOR UPDATE und SELECT ... LOCK IN SHARE MODE, super Mysql, noch inkonsistenter kann man die Kommandos nicht bennen

[Carny]

Ok hab mir Select For Update jetzt mal genauer angeschaut. Wie bereits erwähnt ist mir das alles noch etwas unklar, aber ich versuch das mal etwas zu erklären.

Soweit ich das verstanden habe, wird bei diesem SQL Befehl eine Zeile aus der Datenbank bis zum nächsten Update gesperrt (für lesen oder schreiben gesperrt??). Was genau passiert jetzt, wenn eine andere Session genau die gleiche Zeile auswählt? Oder kann dies nicht passieren, wird dann wenn eine Zeile gesperrt ist diese bei Select-Anweisungen garnicht mehr beachtet, bis diese wieder entsperrt ist?

Aus den Fragen ergibt sich für mich auch, wie ich dies in Verbindung mit PHP behandeln muss, wenn zum Beispiel die zweite Session die gleiche Zeile auswählt. Kommt dann ein Fehler zurück und ich muss in einer Schleife arbeiten?

[transversalis]

mit dem SELECT FOR UPDATE setzt Du einen exklusiven LOCK auf die row.
Andere sessions können diese Zeile dann weder lesen noch ändern ( im allgemeinen warten diese sessions dann, bis der Lock wieder aufgehoben ist, nur wenn das zu lange dauert kommt eine Fehlermeldung zurück "Timeout" ).

Der Lock, den Du damit etablierst, bleibt bis zum nächsten COMMIT bestehen.
Ein COMMIT kann explizit abgesetzt werden, oder implizit durch Beenden des Programms. Vorsicht, wenn Du autocommit verwendest, dann wird nach jedem statement ein automatischer commit abgesetzt und der Lock freigegeben.

[Carny]

Super, vielen Dank für die ausführliche Erklärung.

Ich habe es gerade mal in Phpmyadmin versucht dies zu simulieren, indem ich auf eine Tabelle mit 2 Einträgen mehrmals SELECT ... FOR UPDATE aufgerufen habe. Ab dem 3. SELECT ... FOR UPDATE wurden allerdings immernoch Einträge ausgewählt, als wären die nicht gelocked worden. Oder benutzt Phpmyadmin dieses autocommit, das du angesprochen hattest?

[Sebmaster]

Wenn du die Querys immer getrennt absetzt, wird die Verbindung zu MySQL immer wieder getrennt => Locks aufgehoben.

[ice-breaker]

Zitat von transversalis

mit dem SELECT FOR UPDATE setzt Du einen exklusiven LOCK auf die row.

aber auch nur für Querys, die ebenfalls die FOR UPDATE Erweiterung nutzen, alle anderen werden diesen Lock ignorieren.

[transversalis]

Zitat von ice-breaker

aber auch nur für Querys, die ebenfalls die FOR UPDATE Erweiterung nutzen, alle anderen werden diesen Lock ignorieren.

warum das denn ?
Ein X-Lock ( exclusive-Lock ) sollte doch auch S-Locks ( Leselocks ) anderer threads verhindern, oder ?

[ice-breaker]

neija durch den MVCC-Ansatz bzw. die mögliche Implementierung Snapshot Isolation [1] werden nie Leselocks gesetzt, von daher können Reads nicht mit den Schreiblocks unterbunden werden.
Generell ist da das Problem, dass der MVCC-Ansatz viele Grundlagen des ANSI-Isolationsmodells obsolet macht [2], da das Modell Transaktionen auf Basis von Locks beschreibt.

[1] http://en.wikipedia.org/wiki/Snapshot_isolation
[2] http://research.microsoft.com/pubs/69541/tr-95-51.pdf

[chris_kay]

Um nochmal auf die Eingangsfrage zu kommen:

PHP-Code:

1: 2: 3: 4: 5:

mysqli::query("START TRANSMISSION"); mysqli::query("SELECT * FROM x WHERE y = z FOR UPDATE"); [dazwischen] mysqli::query("UPDATE x ... WHERE y = z"); mysqli::query("COMMIT");

... würde den gewünschten Effekt erzielen. [dazwischen] werden keine Änderungen an den entsprechenden Reihen vorgenommen. Andere PHP-Scripts [, die auf die selben Reihen zugreifen möchten] warten, bis die Transaktion beendet ist und laufen darauf hin normal weiter.