Savepoints für teilweise Rollbacks in MySQL

1. Was Savepoints lösen: Teilrollback statt Alles-oder-Nichts

Eine Standardtransaktion in MySQL kennt nur zwei Endzustände: vollständiger COMMIT oder vollständiger ROLLBACK. Bei einer Transaktion, die mehrere unabhängige Schritte enthält, etwa das Einfügen von zehn Datensätzen in einem Batch, führt ein Fehler im achten Schritt ohne Savepoints dazu, dass entweder die gesamte Transaktion zurückgerollt wird, inklusive der sieben erfolgreichen Schritte, oder der Fehler ignoriert und die Transaktion trotzdem committet wird, was Dateninkonsistenz riskiert.

Savepoints lösen genau dieses Problem, indem sie benannte Markierungspunkte innerhalb einer Transaktion setzen. Ein ROLLBACK TO SAVEPOINT macht nur die Änderungen seit diesem Markierungspunkt rückgängig, die Transaktion selbst bleibt offen und alle Änderungen davor bleiben erhalten. Das ermöglicht feingranulare Fehlerbehandlung innerhalb einer einzigen Transaktion, ohne auf die Vorteile der Atomarität für den Gesamtvorgang zu verzichten.

2. Syntax: SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT

Die Syntax von Savepoints in MySQL besteht aus drei Befehlen. SAVEPOINT name setzt einen benannten Markierungspunkt innerhalb der aktuellen Transaktion. ROLLBACK TO SAVEPOINT name macht alle Änderungen seit diesem Punkt rückgängig, ohne die Transaktion zu beenden, sodass danach weitere Anweisungen ausgeführt werden können. RELEASE SAVEPOINT name entfernt den Savepoint, ohne einen Rollback durchzuführen, üblicherweise wenn der markierte Codepfad erfolgreich abgeschlossen wurde und der Savepoint nicht mehr benötigt wird.

Wichtig ist: Ein einfacher COMMIT oder ROLLBACK ohne TO SAVEPOINT beendet die gesamte Transaktion und löscht dabei automatisch alle innerhalb der Transaktion gesetzten Savepoints. Wird ein Savepoint mit demselben Namen ein zweites Mal gesetzt, ersetzt er implizit den vorherigen Savepoint gleichen Namens, ohne einen Fehler auszulösen, was bei Schleifen mit wiederverwendeten Namen zu beachten ist.

START TRANSACTION;

INSERT INTO orders (customer_id, total) VALUES (101, 49.90);

SAVEPOINT before_items;

INSERT INTO order_items (order_id, sku, qty) VALUES (LAST_INSERT_ID(), 'SKU-001', 2);
-- Suppose the next statement fails due to a constraint violation
INSERT INTO order_items (order_id, sku, qty) VALUES (LAST_INSERT_ID(), 'SKU-999', 1);

-- Undo only the item inserts, keep the order itself
ROLLBACK TO SAVEPOINT before_items;

-- Retry with corrected data
INSERT INTO order_items (order_id, sku, qty) VALUES (LAST_INSERT_ID(), 'SKU-002', 1);

RELEASE SAVEPOINT before_items;
COMMIT;

3. Savepoints und die InnoDB Storage Engine im Detail

InnoDB implementiert Savepoints über das interne Undo-Log-System, dasselbe Mechanismus, der auch für MVCC und normale Rollbacks genutzt wird. Beim Setzen eines Savepoints merkt sich InnoDB die aktuelle Position im Undo-Log der Transaktion. Ein ROLLBACK TO SAVEPOINT spielt die Undo-Log-Einträge bis zu dieser Position zurück und macht damit genau die Änderungen seit dem Savepoint rückgängig, während alle Sperren, die seit dem Savepoint erworben wurden, ebenfalls freigegeben werden.

Ein wichtiges Detail: Sperren, die vor dem Savepoint erworben wurden, bleiben nach einem ROLLBACK TO SAVEPOINT bestehen, weil sie zu Änderungen gehören, die nicht zurückgerollt werden. Nur Sperren, die für Zeilen erworben wurden, die nach dem Savepoint verändert wurden, werden freigegeben. Das bedeutet, dass ein Teilrollback die Transaktion nicht vollständig von ihren bisherigen Sperren befreit, ein wichtiger Unterschied zu einem vollständigen Rollback, der alle Sperren der Transaktion aufhebt.

4. Praxisbeispiel: Batch-Import mit Savepoint pro Datensatz

Ein klassischer Anwendungsfall für Savepoints ist der Import einer CSV-Datei mit mehreren hundert Zeilen, bei dem einzelne Zeilen fehlerhafte Daten enthalten können, der Gesamtimport aber trotzdem in einer einzigen Transaktion für Performance-Gründe laufen soll. Ohne Savepoints würde ein einziger fehlerhafter Datensatz den kompletten Import zunichtemachen. Mit einem Savepoint vor jeder Einzelzeile kann bei einem Fehler gezielt nur diese Zeile zurückgerollt und übersprungen werden, während der Rest des Imports in derselben Transaktion fortgesetzt wird.

Diese Technik reduziert außerdem den Overhead im Vergleich zu einer separaten Transaktion pro Zeile, weil COMMIT-Operationen bei InnoDB mit aktiviertem innodb_flush_log_at_trx_commit einen Fsync-Aufruf auslösen können, der bei tausenden Einzeltransaktionen spürbar Zeit kostet. Ein Savepoint pro Zeile innerhalb einer größeren Transaktion vermeidet diesen Overhead vollständig, weil kein echtes Commit stattfindet, bis der gesamte Batch verarbeitet ist.

-- Batch import with per-row savepoints inside one transaction
START TRANSACTION;

SAVEPOINT row_1;
INSERT INTO product_import (sku, name, price) VALUES ('A-100', 'Widget', 9.99);
-- If this row is invalid, the application issues:
-- ROLLBACK TO SAVEPOINT row_1;

SAVEPOINT row_2;
INSERT INTO product_import (sku, name, price) VALUES ('A-101', 'Gadget', 19.99);

SAVEPOINT row_3;
INSERT INTO product_import (sku, name, price) VALUES (NULL, 'Invalid Row', -5.00);
-- Constraint violation detected by application logic
ROLLBACK TO SAVEPOINT row_3;
-- Log the skipped row, continue with the next one

COMMIT; -- rows 1 and 2 persisted, row 3 skipped

5. Savepoints in PHP mit PDO nutzen

PDO bietet keine dedizierte Methode für Savepoints, sie werden über PDO::exec() mit rohem SQL gesetzt, da das Konzept in der PDO-API nicht abstrahiert ist. Die folgende Klasse kapselt die Savepoint-Logik für einen robusten Batch-Import, bei dem einzelne fehlerhafte Zeilen übersprungen werden, ohne den gesamten Import abzubrechen oder für jede Zeile eine eigene Transaktion mit vollständigem Commit-Overhead zu öffnen.

Ein wichtiger Punkt bei der PHP-Implementierung: Der Savepoint-Name muss ein gültiger SQL-Identifier sein und darf nicht direkt aus Nutzereingaben zusammengesetzt werden, um SQL-Injection über den Namen zu vermeiden. In der Praxis reicht ein programmatisch generierter, streng alphanumerischer Name wie sp_1, sp_2 und so weiter völlig aus.

<?php
declare(strict_types=1);

/**
 * Imports rows one by one, skipping invalid rows via a savepoint
 * rollback instead of aborting the whole transaction.
 */
final class BatchImporter
{
    public function __construct(private readonly PDO $pdo)
    {
    }

    public function importRows(array $rows): array
    {
        $skipped = [];
        $this->pdo->beginTransaction();

        foreach ($rows as $index => $row) {
            $savepoint = "sp_{$index}"; // safe, generated identifier
            $this->pdo->exec("SAVEPOINT {$savepoint}");

            try {
                $stmt = $this->pdo->prepare(
                    'INSERT INTO product_import (sku, name, price) VALUES (:sku, :name, :price)'
                );
                $stmt->execute($row);
            } catch (PDOException $e) {
                // Undo only this row, keep everything imported so far
                $this->pdo->exec("ROLLBACK TO SAVEPOINT {$savepoint}");
                $skipped[] = ['row' => $row, 'error' => $e->getMessage()];
                continue;
            }

            $this->pdo->exec("RELEASE SAVEPOINT {$savepoint}");
        }

        $this->pdo->commit();
        return $skipped;
    }
}

6. Verschachtelte Savepoints und Namenskonflikte

Savepoints lassen sich innerhalb derselben Transaktion beliebig oft verschachteln, indem einfach mehrere Savepoints nacheinander gesetzt werden. Ein ROLLBACK TO SAVEPOINT auf einen äußeren Savepoint macht dabei automatisch auch alle nach diesem Punkt gesetzten inneren Savepoints ungültig, sie müssen nicht manuell aufgeräumt werden. Diese Eigenschaft erlaubt es, mehrstufige Fehlerbehandlung mit unterschiedlichen Granularitäten in einer einzigen Transaktion abzubilden, etwa ein Savepoint pro Batch und ein weiterer pro Zeile innerhalb des Batches.

Bei Namenskonflikten gilt: Wird ein Savepoint mit einem bereits existierenden Namen erneut gesetzt, verschiebt sich die Markierung dieses Namens auf die neue Position, der ältere Savepoint mit demselben Namen ist danach nicht mehr über diesen Namen erreichbar. In Schleifen, die denselben Savepoint-Namen wiederverwenden, etwa immer SAVEPOINT loop_iteration, ist das gewünschtes Verhalten. Werden dynamische Namen benötigt, wie im PHP-Beispiel oben, verhindert das Namenskonflikte vollständig.

-- Same savepoint name reused inside a loop-like pattern
START TRANSACTION;

SAVEPOINT loop_iteration;
INSERT INTO log_entries (message) VALUES ('Step 1');

-- Re-declaring the same name moves the marker forward
SAVEPOINT loop_iteration;
INSERT INTO log_entries (message) VALUES ('Step 2');

-- Only "Step 2" is undone, "Step 1" remains committed within the transaction
ROLLBACK TO SAVEPOINT loop_iteration;

COMMIT;

7. Savepoints vs. echtes Nesting: Was MySQL nicht bietet

Ein häufiges Missverständnis ist, Savepoints mit echten verschachtelten Transaktionen zu verwechseln. MySQL unterstützt, wie die meisten relationalen Datenbanken, keine echten Nested Transactions, bei denen eine innere Transaktion unabhängig von der äußeren committen könnte. Ein START TRANSACTION innerhalb einer bereits laufenden Transaktion committet in MySQL implizit die vorherige Transaktion, statt eine echte innere Transaktion zu öffnen, was bei unbedachter Nutzung zu unerwartetem Verhalten führt.

Savepoints sind der Ersatz, den MySQL für dieses fehlende Feature anbietet, mit einer wichtigen Einschränkung: Ein Savepoint kann nicht unabhängig committen. Alle Änderungen, auch die nach einem RELEASE SAVEPOINT, werden erst mit dem finalen COMMIT der äußeren Transaktion dauerhaft. Wer echte Unabhängigkeit zwischen Teiloperationen benötigt, etwa damit ein Teilergebnis auch bei einem späteren Rollback des Hauptvorgangs erhalten bleibt, muss auf separate, eigenständige Transaktionen zurückgreifen, nicht auf Savepoints.

8. Performance-Aspekte und Grenzen von Savepoints

Das Setzen eines Savepoints selbst ist eine sehr günstige Operation, da InnoDB lediglich die aktuelle Position im Undo-Log vermerkt, ohne zusätzliche Daten zu schreiben oder eine Festplattensynchronisation auszulösen. Der eigentliche Performance-Vorteil gegenüber einzelnen Transaktionen pro Zeile entsteht, weil ein ROLLBACK TO SAVEPOINT keinen Fsync erfordert, während ein echtes COMMIT je nach innodb_flush_log_at_trx_commit-Einstellung sehr wohl einen kostet.

Die Grenze von Savepoints liegt in der Transaktionsdauer: Da alle Savepoints Teil derselben, potenziell langen Transaktion sind, gelten für sie dieselben Regeln wie für jede andere lange offene Transaktion, etwa bezüglich Undo-Log-Wachstum und gehaltener Sperren. Ein Batch-Import mit tausenden Savepoints innerhalb einer einzigen Transaktion kann selbst zur Long-Running Transaction werden, wenn der gesamte Batch zu groß gewählt wird. Eine sinnvolle Batch-Größe von einigen hundert bis wenigen tausend Zeilen pro Transaktion balanciert Commit-Overhead und Transaktionsdauer.

9. Savepoint, vollständiger Rollback und separate Transaktion im Vergleich

Die folgende Tabelle stellt die drei gängigen Strategien für Fehlerbehandlung innerhalb mehrstufiger Datenbankoperationen gegenüber.

Für Batch-Verarbeitung mit vielen unabhängigen Zeilen sind Savepoints in aller Regel die beste Wahl, weil sie bereits erledigte Arbeit erhalten, ohne den Commit-Overhead separater Transaktionen zu verursachen. Bei wenigen, stark voneinander abhängigen Schritten, bei denen ein Fehler in einem Schritt die gesamte Operation ungültig macht, ist ein vollständiger Rollback dagegen weiterhin die richtige und einfachere Wahl.

10. Zusammenfassung

Savepoints erweitern das binäre Commit-oder-Rollback-Modell von MySQL um feingranulare Kontrollpunkte innerhalb einer Transaktion. Mit SAVEPOINT, ROLLBACK TO SAVEPOINT und RELEASE SAVEPOINT lässt sich gezielt nur der fehlerhafte Teil einer mehrstufigen Operation zurückrollen, während bereits erfolgreiche Schritte erhalten bleiben. InnoDB implementiert das effizient über das bestehende Undo-Log-System, ohne den Overhead eines echten Commits pro Savepoint.

In der Praxis eignen sich Savepoints besonders für Batch-Imports mit potenziell fehlerhaften Einzelzeilen, wo sie separate Transaktionen pro Zeile mit ihrem Fsync-Overhead ersetzen. Wichtig bleibt, dass Savepoints kein Ersatz für echte verschachtelte Transaktionen sind, die MySQL grundsätzlich nicht unterstützt, und dass alle Änderungen letztlich vom finalen Commit der äußeren Transaktion abhängen.

11. FAQ: Savepoints für teilweise Rollbacks