Abschnitt 14 · Lektion 6

Transaktionen

PHP Sandbox 4 Min Lesezeit 4 BeispieleLektion 6 von 9

Bisher war jede Änderung für sich allein. Ein INSERT gelingt oder scheitert, und danach ist die Welt in Ordnung. Interessant wird es, sobald zwei Änderungen nur zusammen richtig sind.

Das Standardbeispiel ist eine Überweisung: Bei einem Konto wird abgebucht, beim anderen gutgeschrieben. Geht zwischen den beiden Schritten etwas schief, ist Geld verschwunden. In diesem Kurs nehmen wir ein harmloseres, aber genauso echtes Beispiel: einen Import von vier Buchungen, die alle zu einem Vorgang gehören.

Ohne Klammer bleibt die Hälfte liegen

Vier Buchungen, ohne Klammer

 <?php

// 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

// 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 $e) {
    echo "Abgebrochen: ", $e->getMessage(), "\n";
}

echo "\nIn der Tabelle stehen jetzt ", $db->query("SELECT count(*) FROM buchungen")->fetchColumn(), " Buchungen.\n";
echo "Zwei davon gehoeren zu einem Vorgang, der nie fertig wurde.\n";

Die dritte Buchung hat einen negativen Betrag, und die Tabelle lässt das wegen ihres CHECK (betrag > 0) nicht zu. Die Ausnahme wird gefangen, das Programm läuft weiter, alles wirkt sauber aufgeräumt.

Ist es aber nicht. In der Tabelle stehen zwei Buchungen, und die gehören zu einem Vorgang, den niemand zu Ende gebracht hat. Im Reiter Debug siehst du den Weg dorthin: beleg und betrag gehen dreimal durch die Schleife, beim dritten Mal steht betrag auf -300, und der nächste Schritt ist die Zeile im catch. Schlimmer noch: Wer den Import morgen wiederholt, bekommt bei R-1001 ein UNIQUE constraint failed, weil die Zeile ja schon da ist. Ein halb erledigter Vorgang ist oft aufwendiger zu reparieren als gar keiner.

Mit Klammer gibt es nur zwei Ausgänge

Dieselben vier, in einer Transaktion

 <?php

// 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

// 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 $e) {
    $db->rollBack();
    echo "Abgebrochen: ", $e->getMessage(), "\n";
}

echo "\nIn der Tabelle stehen jetzt ", $db->query("SELECT count(*) FROM buchungen")->fetchColumn(), " Buchungen.\n";
echo "Die zwei von eben sind mit zurueckgenommen worden.\n";

$db->beginTransaction() macht die Klammer auf. Ab hier sammelt die Datenbank deine Ä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 ist es derselbe Weg wie eben, nur dass nach dem dritten execute() als nächster Schritt das rollBack() kommt.

Das Muster drumherum ist immer dasselbe und du kennst es aus Abschnitt 7: beginTransaction() vor dem try, commit() als letzte Zeile im try, rollBack() als erste Zeile im catch. So gibt es genau zwei mögliche Ausgänge, und einen dritten kann es nicht geben.

Achte darauf, dass commit() wirklich im try steht und nicht dahinter. Steht es dahinter, läuft es auch dann, wenn der catch gerade zurückgerollt hat, und dann bekommst du den Fehler aus dem nächsten Abschnitt.

Was schiefgehen kann an der Klammer selbst

Wo eine Transaktion anfängt und aufhört

 <?php

// 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

// Wo eine Transaktion anfaengt und wo sie aufhoert.

require __DIR__ . "/vorbereiten.php";

echo "Am Anfang laeuft 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 $e) {
    echo "  ", $e->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 $e) {
    if ($db->inTransaction()) {
        $db->rollBack();
        echo "  zurueckgenommen, weil noch eine lief\n";
    }

    echo "  ", $e->getMessage(), "\n";
}

echo "\nUnd verschachteln geht nicht:\n";

$db->beginTransaction();

try {
    $db->beginTransaction();
    echo "  zweimal angefangen\n";
} catch (PDOException $e) {
    echo "  ", $e->getMessage(), "\n";
}

$db->rollBack();

inTransaction() sagt dir, ob gerade eine läuft. Das brauchst du seltener, als man denkt, aber an einer Stelle ist es wirklich nützlich: Ein rollBack() ohne laufende Transaktion wirft There is no active transaction. Wenn dein catch also auch Fehler fängt, die schon vor dem beginTransaction() passieren können, fragst du dort besser vorher nach.

Verschachteln geht nicht. Ein zweites beginTransaction() innerhalb einer laufenden Transaktion endet mit There is already an active transaction. Wer so etwas wirklich braucht, arbeitet mit SAVEPOINT, und das ist ein Thema für einen SQL-Kurs.

Und noch etwas, das man einmal wissen muss: Wenn dein Skript endet, während eine Transaktion offen ist, wird sie zurückgerollt und nicht festgeschrieben. Ein vergessenes commit() heißt also nicht „unsicher”, sondern „hat gar nichts gemacht”. Das ist die richtige Richtung, verwirrt aber, wenn man den Fehler sucht.

Der Nebeneffekt, mit dem niemand rechnet

Fünfhundert Zeilen, mit und ohne

 <?php

// 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

// Fuenfhundert Zeilen, zweimal eingefuegt. Einmal ohne Transaktion,
// einmal mit. Gemessen mit hrtime(), der monotonen Uhr.
//
// Der Lauf dauert ein paar Sekunden, und das 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";

Eine Transaktion ist beim Einfügen vieler Zeilen nicht ein bisschen schneller, sondern um Größenordnungen. Im Beispiel stehen dieselben 500 Zeilen einmal ohne und einmal mit, und der gemessene Unterschied liegt bei rund zwei Sekunden gegen wenige Millisekunden.

Der Grund ist simpel. Ohne Klammer macht SQLite aus jedem einzelnen INSERT eine eigene Transaktion, und am Ende jeder Transaktion muss es sicherstellen, dass die Daten wirklich auf der Platte sind. Das ist der teure Teil, und er passiert 500 mal statt einmal.

Die Regel daraus: Wenn du in einer Schleife schreibst, gehört eine Transaktion drumherum. Immer, auch wenn dir das Alles-oder-nichts an dieser Stelle egal ist.

Gemessen wird die Dauer übrigens 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 weil der Rechner sich mit einem Zeitserver abgleicht, und dann steht in deiner Messung eine negative Dauer.

Was SQLite dabei anders macht

Eine Transaktion in SQLite sperrt beim Schreiben die ganze Datei. Es kann also immer nur einer gleichzeitig schreiben, und 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 Lesen geht sowieso parallel weiter. Für eine Anwendung, in der viele Leute gleichzeitig schreiben, ist es genau der Punkt, an dem man auf eine der beiden anderen wechselt. Lange Transaktionen sind bei SQLite deshalb eine schlechte Idee: Was in der Klammer steht, sollte kurz sein und nicht auf eine Antwort aus dem Internet warten.

Zum Mitnehmen

Alles oder nichts, und zwar wörtlich. Eine Transaktion ist die Klammer um mehrere Änderungen, die nur zusammen einen Sinn ergeben. Nebenbei ist sie beim Einfügen vieler Zeilen um ein paar Hundert schneller, und das ist kein Tippfehler.

Jetzt du

Basis Konto, kostenlos

Zu 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.

Transaktionen

01 / 05Ohne Klammer bleibt die Hälfte liegen

02 / 05Mit Klammer gibt es nur zwei Ausgänge

03 / 05Was schiefgehen kann an der Klammer selbst

04 / 05Der Nebeneffekt, mit dem niemand rechnet

05 / 05Was SQLite dabei anders macht