MySQL Transaktionen – Für besonders sensitive Abläufe
Standardmäßig werden MySQL-Befehle, wie UPDATE, INSERT, oder DELETE direkt auf vom MySQL-Server ausgeführt und auf der Festplatte persistent gespeichert. Führt man zusammenhängende Operationen aus, z.B. von einem Nutzerkonto einen Betrag abbuchen, bei einem anderen Nutzer das Guthaben hinzufügen und die ganze Aktion in einer Kontobewegungs-Tabelle festhalten, so kann diese einzelne Ausführung der entsprechenden SQL-Befehle in selten Fällen zu unangenehmen Seiteneffekten führen.
Stürzt z.B. der MySQL-Server nach der ersten Operation ab, so wurde dem Kunden Guthaben abgebucht, aber es wurde weder dem Empfänger gut geschrieben noch in der Kontobewegungs-Tabelle protokolliert. Der Betrag ist einfach verschwunden. Mittels Datenbanktransaktion lässt sich dies zu Glück gut verhindern.
Ursachen für unterbrochene Script-Abläufe
Der Script-Ablauf kann durch verschiedene Ursachen unterbrochen werden. Dies kann ein Absturz, eine Fehlermeldung oder ein Verbindungsabbruch durch den MySQL-Server sein, oder aber auch ein Absturz eures Webserver. Gerne treten auch im PHP-Script Fehler auf, die dazu führen, dass das Script nur zur Hälfte ausgeführt wird. Halb ausgeführte Operationen sind besonders kritisch, wenn die Integrität der Daten absolute Priorität hat und Daten-Inkonsistenzen nicht toleriert werden können. Dies ist der Fall bei Finanztransaktionen oder bei Cryptocurrency-Anwendung wie beispielsweise der bitqt app.
Solche halb abgebrochenen Operationen treten relativ selten auf. Dies macht es aber gerade schwierig, entsprechend kritische Stellen zu identifizieren, da die Abbrüche beim Testen dann quasi nie auftreten. Daher gilt es von Anfang an gleich ein sauberes Software-Design zu haben und zusammenhängende Datenbank-Operationen per Datenbanktransaktion abzusichern.
Datenbanktransaktionen in MySQL
Normalerweise werden von MySQL sämtliche Änderungen sofort dauerhaft gespeichert. Um dies auszuschalten, muss autocommit deaktiviert werden:
|
1 |
SET autocommit = 0; |
Anschließend können wir eine neue Transaktion starten:
|
1 |
START TRANSACTION; |
Danach können wir beliebig viele SQL-Befehle ausführen, für das obige Beispiel mit der Kontobewegung:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT guthaben FROM users; -- Ausgabe z.b. 200 Euro für User_ID 1, 0 Euro für User_ID 2 -- Guthaben abbuchen UPDATE users SET guthaben = guthaben - 100 WHERE user_id=1; SELECT guthaben FROM users; -- Ausgabe: 100 Euro für User_ID 1, 0 Euro für User_ID 2 -- Guthaben gutschreiben UPDATE users SET guthaben = guthaben + 100 WHERE user_id=2; SELECT guthaben FROM users; -- Ausgabe: 100 Euro für User_ID 1, 100 Euro für User_ID 2 -- Vorgang in Tabelle protokollieren INSERT INTO kontobewegungen (von_id, nach_id, betrag) VALUES (1, 2, 100); |
Diese drei Befehle buchen dem Benutzer 84 einen Betrag von 100 ab und schreibt es dem Kunden 93 gut. Das ganze wird noch in einer Kontobewegungs-Tabelle protokolliert.
Führt man zwischen den UPDATE/INSERT/DELETE-Befehlen eine Datenbankabfrage durch, erhält man das aktualisierte Ergebnis zurück. Sprich, nach dem ersten Update wurden dem Benutzer 1 bereits 100 Euro abgebucht. Diese ist aber noch nicht persistent auf der Festplatte gespeichert und ihr erhaltet diesen Abfragewert nur für eure Transaktion.
Hat man seine Operationen alle durchgeführt, kann man die Transaktion wie folgt abschließen:
|
1 |
COMMIT; |
Mittels COMMIT werden alle Änderungen, seit Beginn der Transaktion, durchgeführt und persistent gespeichert. Sollte die Transaktion nicht beendet werden, z.B. durch einen Absturz oder ein Programmfehler, so werden die Änderungen nicht gespeichert und die vorherigen, unveränderten Werte sind vorhanden.
Möchte man die Transaktion manuell abbrechen, und die Änderungen Rückgängig machen, so geht dies mittels:
|
1 |
ROLLBACK; |
Für weitere Details zu diesen Befehlen, siehe die MySQL Dokumentation.
Datenbanktransaktion mittels PDO
Verwendet ihr PDO um eure Datenbank-Abfragen zu senden, so existieren zum Glück einige Funktionen, die das Leben etwas vereinfachen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
<?php $pdo = new PDO('mysql:host=localhost;dbname=test', 'username', 'password'); //Beginne die Transaktion $pdo->beginTransaction(); //Beliebige Anzahl an SQL Befehlen ausführen $pdo->exec("UPDATE users SET guthaben = guthaben - 100 WHERE user_id=1;"); $pdo->exec("UPDATE users SET guthaben = guthaben + 100 WHERE user_id=2;"); $pdo->exec("INSERT INTO kontobewegungen (von_id, nach_id, betrag) VALUES (1, 2, 100);"); //Datenbank-Transaktion durchführen $pdo->commit(); |
Mittels beginTransaction() starten wir eine neue Transaktion. Alle nachfolgenden Befehle / Änderungen sind erst mal nur temporär, bis abschließend commit() ausgeführt wird.
Kommt es zu einem Abbruch vor dem Aufruf von commit(), so werden keine Teildaten / Teiländerungen gespeichert. Es gilt das Prinzip: Ganz oder gar nicht.
Autor: Nils Reimers