Abschnitt 14 · Lektion 6
Transaktionen
Mitten im Import von vier Buchungen geht etwas schief, bei der dritten. Zwei stehen danach in der Tabelle, zwei nicht. Ob ein Vorgang vollständig ist, sieht man der Tabelle nicht an.
Bei einer Überweisung wäre es dasselbe, nur teurer. Bei einem Konto wird abgebucht, beim anderen gutgeschrieben, und geht zwischen den beiden Schritten etwas schief, ist Geld verschwunden. Hier bleibt es beim harmloseren Beispiel, vier Buchungen, die zu einem Vorgang gehören.
Ohne Klammer bleibt die Hälfte liegen
<?php
declare(strict_types=1);
// Eine leere Tabelle fuer Buchungen. Der Beleg muss eindeutig sein,
// der Betrag groesser als null. Bei jedem Lauf neu aufgebaut.
$db = new PDO("sqlite:" . __DIR__ . "/daten.db", null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec("DROP TABLE IF EXISTS buchungen");
$db->exec("CREATE TABLE buchungen (
id INTEGER PRIMARY KEY,
beleg TEXT NOT NULL UNIQUE,
betrag INTEGER NOT NULL CHECK (betrag > 0)
)"); <?php
declare(strict_types=1);
// Vier Buchungen einlesen, ohne Transaktion. Die dritte ist kaputt.
require __DIR__ . "/vorbereiten.php";
$einfuegen = $db->prepare("INSERT INTO buchungen (beleg, betrag) VALUES (:beleg, :betrag)");
$liste = [
["R-1001", 4900],
["R-1002", 1250],
["R-1003", -300],
["R-1004", 800],
];
try {
foreach ($liste as [$beleg, $betrag]) {
$einfuegen->execute([":beleg" => $beleg, ":betrag" => $betrag]);
echo " ", $beleg, " eingetragen\n";
}
echo "Alle vier drin.\n";
} catch (PDOException $fehler) {
echo "Abgebrochen: ", $fehler->getMessage(), "\n";
}
echo "\nIn der Tabelle stehen jetzt ", $db->query("SELECT count(*) FROM buchungen")->fetchColumn(), " Buchungen.\n";
echo "Zwei davon gehören zu einem Vorgang, der nie fertig wurde.\n"; Die dritte Buchung hat einen negativen Betrag, und das lässt die Tabelle wegen ihres
CHECK (betrag > 0) nicht zu. Die Ausnahme wird gefangen, das Programm läuft weiter. Alles wirkt
sauber aufgeräumt.
In der Tabelle stehen aber zwei Buchungen, und die gehören zu einem Vorgang, den niemand zu Ende
gebracht hat. Wiederholst du den Import morgen, bekommst du bei R-1001 obendrein ein
UNIQUE constraint failed, denn die Zeile ist ja schon da.
Mit Klammer gibt es nur zwei Ausgänge
<?php
declare(strict_types=1);
// Eine leere Tabelle fuer Buchungen. Der Beleg muss eindeutig sein,
// der Betrag groesser als null. Bei jedem Lauf neu aufgebaut.
$db = new PDO("sqlite:" . __DIR__ . "/daten.db", null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec("DROP TABLE IF EXISTS buchungen");
$db->exec("CREATE TABLE buchungen (
id INTEGER PRIMARY KEY,
beleg TEXT NOT NULL UNIQUE,
betrag INTEGER NOT NULL CHECK (betrag > 0)
)"); <?php
declare(strict_types=1);
// Dieselben vier Buchungen, diesmal in einer Transaktion.
require __DIR__ . "/vorbereiten.php";
$einfuegen = $db->prepare("INSERT INTO buchungen (beleg, betrag) VALUES (:beleg, :betrag)");
$liste = [
["R-1001", 4900],
["R-1002", 1250],
["R-1003", -300],
["R-1004", 800],
];
$db->beginTransaction();
try {
foreach ($liste as [$beleg, $betrag]) {
$einfuegen->execute([":beleg" => $beleg, ":betrag" => $betrag]);
echo " ", $beleg, " eingetragen\n";
}
$db->commit();
echo "Alle vier drin.\n";
} catch (PDOException $fehler) {
$db->rollBack();
echo "Abgebrochen: ", $fehler->getMessage(), "\n";
}
echo "\nIn der Tabelle stehen jetzt ", $db->query("SELECT count(*) FROM buchungen")->fetchColumn(), " Buchungen.\n";
echo "Die zwei von eben sind mit zurückgenommen worden.\n"; $db->beginTransaction() macht die Klammer auf. Ab hier sammelt die Datenbank die Änderungen, ohne
sie festzuschreiben.
$db->commit() macht sie zu und schreibt alles auf einmal fest.
$db->rollBack() verwirft stattdessen alles, was seit dem beginTransaction() passiert ist, als
wäre es nie geschehen. Im Reiter Debug siehst du, wie es nach dem dritten execute() direkt zum
rollBack() geht.
Das Muster drumherum ist immer dasselbe. beginTransaction() steht vor dem try, commit() als
letzte Zeile im try und rollBack() als erste Zeile im catch.
Steht commit() hinter dem try statt darin, läuft es auch dann, wenn der catch gerade
zurückgerollt hat. Dann wirft es den Fehler aus dem nächsten Abschnitt.
Was an der Klammer selbst schiefgehen kann
<?php
declare(strict_types=1);
// Eine leere Tabelle fuer Buchungen. Der Beleg muss eindeutig sein,
// der Betrag groesser als null. Bei jedem Lauf neu aufgebaut.
$db = new PDO("sqlite:" . __DIR__ . "/daten.db", null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec("DROP TABLE IF EXISTS buchungen");
$db->exec("CREATE TABLE buchungen (
id INTEGER PRIMARY KEY,
beleg TEXT NOT NULL UNIQUE,
betrag INTEGER NOT NULL CHECK (betrag > 0)
)"); <?php
declare(strict_types=1);
// Wo eine Transaktion anfaengt und wo sie aufhoert.
require __DIR__ . "/vorbereiten.php";
echo "Am Anfang läuft keine: ", var_export($db->inTransaction(), true), "\n";
$db->beginTransaction();
echo "Nach beginTransaction(): ", var_export($db->inTransaction(), true), "\n";
$db->exec("INSERT INTO buchungen (beleg, betrag) VALUES ('R-1', 100)");
$db->commit();
echo "Nach commit(): ", var_export($db->inTransaction(), true), "\n";
echo "\nEin rollBack ohne laufende Transaktion ist ein Fehler:\n";
try {
$db->rollBack();
} catch (PDOException $fehler) {
echo " ", $fehler->getMessage(), "\n";
}
echo "\nDeshalb fragt man im catch lieber vorher nach:\n";
$db->beginTransaction();
try {
$db->exec("INSERT INTO buchungen (beleg, betrag) VALUES ('R-1', 100)");
$db->commit();
} catch (PDOException $fehler) {
if ($db->inTransaction()) {
$db->rollBack();
echo " zurückgenommen, weil noch eine lief\n";
}
echo " ", $fehler->getMessage(), "\n";
}
echo "\nUnd verschachteln geht nicht:\n";
$db->beginTransaction();
try {
$db->beginTransaction();
echo " zweimal angefangen\n";
} catch (PDOException $fehler) {
echo " ", $fehler->getMessage(), "\n";
}
$db->rollBack(); inTransaction() sagt, ob gerade eine Transaktion läuft. Das braucht man selten, an einer Stelle
aber schon, denn ein rollBack() ohne laufende Transaktion wirft There is no active transaction.
Fängt ein catch auch Fehler, die schon vor dem beginTransaction() passieren können, fragt er
dort besser vorher nach.
Verschachteln geht nicht. Ein zweites beginTransaction() innerhalb einer laufenden Transaktion
endet mit There is already an active transaction. Brauchst du so etwas wirklich, gibt es
SAVEPOINT, und das ist ein Thema für einen Kurs über SQL.
Endet ein Skript, während eine Transaktion offen ist, wird sie zurückgerollt und nicht
festgeschrieben. Ein vergessenes commit() hat dann gar nichts gemacht, weder halb noch ganz. Das
ist die richtige Richtung. Wenn du den Fehler suchst, verwirrt es trotzdem.
Viele Zeilen auf einmal
<?php
declare(strict_types=1);
// Eine leere Tabelle fuer Buchungen. Der Beleg muss eindeutig sein,
// der Betrag groesser als null. Bei jedem Lauf neu aufgebaut.
$db = new PDO("sqlite:" . __DIR__ . "/daten.db", null, null, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec("DROP TABLE IF EXISTS buchungen");
$db->exec("CREATE TABLE buchungen (
id INTEGER PRIMARY KEY,
beleg TEXT NOT NULL UNIQUE,
betrag INTEGER NOT NULL CHECK (betrag > 0)
)"); <?php
declare(strict_types=1);
// Fuenfhundert Zeilen, zweimal eingefuegt. Einmal ohne Transaktion,
// einmal mit. Gemessen mit hrtime(), der monotonen Uhr.
//
// Der Abstand zwischen den beiden Zahlen ist der Punkt.
require __DIR__ . "/vorbereiten.php";
$einfuegen = $db->prepare("INSERT INTO buchungen (beleg, betrag) VALUES (:beleg, :betrag)");
$start = hrtime(true);
for ($i = 1; $i <= 500; $i++) {
$einfuegen->execute([":beleg" => "A-" . $i, ":betrag" => 100]);
}
$ohne = (hrtime(true) - $start) / 1_000_000;
$start = hrtime(true);
$db->beginTransaction();
for ($i = 1; $i <= 500; $i++) {
$einfuegen->execute([":beleg" => "B-" . $i, ":betrag" => 100]);
}
$db->commit();
$mit = (hrtime(true) - $start) / 1_000_000;
printf("ohne Transaktion: %8.1f ms\n", $ohne);
printf("mit Transaktion: %8.1f ms\n", $mit);
printf("Faktor: %8.1f\n", $ohne / $mit);
echo "Zeilen insgesamt: ", $db->query("SELECT count(*) FROM buchungen")->fetchColumn(), "\n"; Beim Einfügen vieler Zeilen ist eine Transaktion um Größenordnungen schneller. Im Beispiel stehen dieselben 500 Zeilen einmal ohne und einmal mit. In der Sandbox braucht die Fassung ohne Klammer gemessen ein bis zwei Zehntelsekunden und die mit Klammer gut eine Millisekunde, also das Hundert- bis Zweihundertfache. Die Zahlen schwanken von Lauf zu Lauf und hängen an der Platte. In einem Docker-Container auf einem Laptop waren es rund zwei Sekunden gegen sechs Millisekunden.
Ohne Klammer macht SQLite aus jedem einzelnen INSERT eine eigene Transaktion. Am Ende jeder
Transaktion wartet es, bis die Daten wirklich auf der Platte sind. Das ist der teure Teil, und er
passiert 500-mal statt einmal.
Gemessen wird die Dauer mit hrtime() und nicht mit microtime(). hrtime() ist eine monotone
Uhr, die nur vorwärts läuft. Die Kalenderuhr kann während einer Messung springen, etwa wenn der
Rechner sich mit einem Zeitserver abgleicht, und dann steht in der Messung eine negative Dauer.
Was SQLite dabei anders macht
Eine Transaktion in SQLite sperrt beim Schreiben die ganze Datei. Schreiben kann also immer nur
einer zur selben Zeit. Ein zweiter Schreiber wartet oder bekommt ein database is locked. Bei
MySQL und PostgreSQL sperrt eine Transaktion feiner, im Normalfall nur die Zeilen, die sie wirklich
anfasst.
Für eine Notizliste oder eine kleine Seite ist das kein Problem. Ein Schreibvorgang dauert Millisekunden, und gelesen wird sowieso parallel weiter. Schreiben in einer Anwendung aber viele Leute gleichzeitig, ist das der Punkt, an dem man zu einer der beiden anderen wechselt. Eine Transaktion, die schon geschrieben hat und dann auf eine Antwort aus dem Internet wartet, hält die Datei so lange für alle anderen Schreiber gesperrt.
Vier Lieferscheine sollen in der Aufgabe ganz oder gar nicht in der Tabelle landen.
Zum Mitnehmen
Mit beginTransaction() vor dem try, commit() am Ende des try und rollBack() am Anfang des catch landen mehrere Änderungen ganz oder gar nicht in der Datenbank. Viele INSERT in einer Transaktion laufen außerdem einige hundertmal schneller.
Jetzt du
Basis Konto, kostenlosZu dieser Lektion gehört eine Aufgabe. Du schreibst den Code selbst, und nach jedem Lauf sagt dir eine Prüfliste, was schon stimmt.
Dafür brauchst du das Basis Konto. Es kostet nichts, und ein Passwort gibt es auch nicht.
In diesem Kurs läuft dein Code auf einem Server. Dafür hat das Basis Konto 1 Stunde im Monat, mehr Zeit gibt es mit dem Premium Konto.
Was in dieser Lektion steckt
-
Artikel mit 4 Beispielen zum Ausprobieren
Steht hier, ohne Konto lesbar.
-
Aufgabe, dein Code läuft auf einem Server
Öffnet sich mit dem Basis Konto.