Gleichzeitige Transaktionen
Kurz gesagt: Wenn zwei Transaktionen dieselben Daten erst lesen und dann schreiben, kann eine die Änderung der anderen überschreiben (ein verlorenes Update); ein einziges bedingtes UPDATE oder eine Zeilensperre verhindert das.
Problem
Die letzten Packungen Kaffee
Es sind 15 Packungen Kaffeebohnen da. Die Kasse im Shop und der Onlineshop verkaufen in derselben Sekunde je eine Packung. Beide Programme machen dasselbe: den Bestand lesen, 1 abziehen und die neue Zahl schreiben.
Führ die Schritte einzeln aus. Jede Abfrage läuft wirklich, auf einer gemeinsamen Datenbank:
Kasse
SELECT stock FROM products WHERE id = 17;Die Kasse im Shop liest den Kaffeebestand: 15.
Online
SELECT stock FROM products WHERE id = 17;In derselben Sekunde liest eine Online-Bestellung dasselbe: 15.
Kasse
UPDATE products SET stock = 14 WHERE id = 17;Die Kasse verkauft 1 Packung und schreibt 15 − 1 = 14.
Online
UPDATE products SET stock = 14 WHERE id = 17;Die Online-Bestellung verkauft auch 1 und schreibt auch 14. Von der Kasse weiß sie nichts.
Kasse
SELECT stock FROM products WHERE id = 17;2 Packungen wurden verkauft, aber der Bestand ist nur um 1 gesunken. Eine Änderung ist verloren.
Die Idee
Ein verlorenes Update
Das ist ein verlorenes Update (lost update). Jede Abfrage für sich ist richtig. Schuld ist die Lücke zwischen Lesen und Schreiben: In ihr hat das andere Programm etwas geschrieben, das dann überschrieben wurde.
Der einfachste Schutz ist, keine Lücke zu haben. Lass die Datenbank selbst lesen und schreiben, in einer Abfrage:
Kasse
UPDATE products SET stock = stock - 1 WHERE id = 17 AND stock >= 1;Die Kasse sagt der Datenbank „nimm 1 weg, wenn eine da ist“. Lesen und Schreiben sind eine Abfrage.
Online
UPDATE products SET stock = stock - 1 WHERE id = 17 AND stock >= 1;Die Online-Bestellung macht dasselbe. Die Datenbank führt die beiden nacheinander aus, jede mit dem echten aktuellen Wert.
Kasse
SELECT stock FROM products WHERE id = 17;15 − 2 = 13. Nichts geht verloren.
Die Bedingung stock >= 1 schützt auch davor, etwas zu verkaufen, das nicht da ist. Wie viele Zeilen die Abfrage geändert hat, sagt dem Programm, ob es verkauft hat:
Kasse
UPDATE products SET stock = stock - 5 WHERE id = 23 AND stock >= 5;Das Messerset: Es sind 5 da. Die Kasse verkauft alle 5 auf einmal (ein Firmengeschenk): 1 Zeile geändert.
Online
UPDATE products SET stock = stock - 5 WHERE id = 23 AND stock >= 5;Die Online-Bestellung will 5. Die Bedingung ist nicht mehr wahr: 0 Zeilen geändert. Das Programm sagt dem Kunden „nicht auf Lager“, statt Messer zu verkaufen, die es nicht gibt.
Online
SELECT stock FROM products WHERE id = 23;Der Bestand ist nicht unter null gefallen.
Probier es aus
Drei Packungen, wenn es drei gibt
Schreib eine Abfrage, die 3 Packungen Kaffee (Produkt 17) verkauft, nur wenn mindestens 3 da sind.
Lösung
UPDATE products SET stock = stock - 3
WHERE id = 17 AND stock >= 3;UPDATE products SET stock = stock - 3 WHERE id = 17 AND stock >= 3; Der Kaffee geht auf 12. Wären es 2 gewesen, hätte die Abfrage nichts geändert, und das Programm würde es an der Zahl der geänderten Zeilen erkennen.
Die Idee
Isolationsstufen
Nicht alles passt in eine Abfrage. Deshalb halten Datenbanken Transaktionen in Stufen voneinander getrennt. Je höher die Stufe, desto weniger Seltsames siehst du, aber desto mehr wird gewartet:
| Stufe | Dirty Read | Non-repeatable Read | Phantome |
|---|---|---|---|
| READ UNCOMMITTED | möglich | möglich | möglich |
| READ COMMITTED | nein | möglich | möglich |
| REPEATABLE READ | nein | nein | möglich |
| SERIALIZABLE | nein | nein | nein |
- Dirty Read: Du siehst eine Änderung, die die andere Transaktion noch nicht bestätigt hat (und vielleicht zurücknimmt).
- Non-repeatable Read: Du liest in deiner Transaktion eine Zeile zweimal und bekommst verschiedene Werte, weil jemand sie dazwischen geändert hat.
- Phantom: Du wiederholst eine Abfrage mit einer Bedingung, und es erscheinen neue Zeilen, die jemand hinzugefügt hat.
PostgreSQL arbeitet standardmäßig mit READ COMMITTED, MySQL (InnoDB) mit REPEATABLE READ. SQLite ist am strengsten: Es schreibt immer nur eine Transaktion, die anderen warten.
Genauer, für Neugierige
Die Tabelle stammt aus dem Standard. Echte Datenbanken tun mehr, als er verlangt: In PostgreSQL verhält sich READ UNCOMMITTED wie READ COMMITTED, und REPEATABLE READ lässt auch keine Phantome zu.
Die Idee
Sperren und Deadlock
Um diese Versprechen zu halten, sperrt die Datenbank: Eine Transaktion, die eine Zeile ändert, hält sie fest, bis sie COMMIT oder ROLLBACK sagt. Wer dieselbe Zeile will, wartet.
Manchmal warten zwei aufeinander: Die erste Transaktion hält den Kaffee und will den Tee, die zweite hält den Tee und will den Kaffee. Das ist ein Deadlock (Verklemmung). Die Datenbank bemerkt es und bricht eine von beiden mit einem Fehler ab. Das Programm muss bereit sein, es noch einmal zu versuchen.
Wie du es seltener machst: kurze Transaktionen (kein Warten auf einen Menschen mittendrin) und alle Programme sperren in derselben Reihenfolge, zum Beispiel nach aufsteigender Produkt-id.
Genauer, für Neugierige
In PostgreSQL und MySQL sperrt SELECT … FOR UPDATE die Zeilen, die es liest, sofort, sodass „lesen, rechnen, schreiben“ innerhalb einer Transaktion sicher wird. SQLite hat kein FOR UPDATE. Dort sperrt BEGIN IMMEDIATE zu Beginn der Transaktion die ganze Datenbank zum Schreiben.
Test