SQLite Transaktionen: BEGIN, COMMIT, ROLLBACK

Eine SQLite-Transaktion ist ein Alles-oder-nichts-Paket

Mit einer SQLite-Transaktion fasst du mehrere Statements zu einer Einheit zusammen: Entweder werden alle übernommen – oder keines davon. Geht unterwegs etwas schief, kannst du per Rollback alles zurückdrehen, und die Datenbank steht wieder genau so da wie vorher.

Das klassische Beispiel ist eine Geldüberweisung:

CREATE TABLE accounts (
    id    INTEGER PRIMARY KEY,
    owner TEXT,
    cents INTEGER NOT NULL CHECK (cents >= 0)
);

INSERT INTO accounts (owner, cents) VALUES ('Ada', 10000), ('Boris', 500);

BEGIN;
UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
COMMIT;

SELECT owner, cents FROM accounts;

Die beiden UPDATE-Anweisungen gehören zusammen. Würde die Datenbank zwischen den beiden Anweisungen abstürzen, hätte Ada 2000 Cent weniger und Boris gleichzeitig nichts dazubekommen. Packt man die beiden in ein BEGIN ... COMMIT, wird das Paar atomar — entweder werden beide ausgeführt oder keine.

Autocommit: Der Standard, den du längst nutzt

Jede SQL-Anweisung, die du bisher ausgeführt hast, war bereits eine Transaktion. SQLite läuft standardmäßig im Autocommit-Modus: Jede einzelne Anweisung bekommt automatisch ein implizites BEGIN und COMMIT drumherum.

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT);

INSERT INTO notes (body) VALUES ('erste');
INSERT INTO notes (body) VALUES ('zweite');
INSERT INTO notes (body) VALUES ('dritte');

SELECT * FROM notes;

Drei Inserts, drei separate Transaktionen, drei Plattenzugriffe zum Synchronisieren via fsync. Für einzelne Schreibvorgänge ist das in Ordnung, aber bei größeren Datenmengen wird's schnell zäh – und du kannst eine Gruppe von Statements nicht mehr als Einheit zurücknehmen. BEGIN schaltet den Autocommit-Modus aus, und zwar so lange, bis das nächste COMMIT oder ROLLBACK kommt.

ROLLBACK: So tun, als wäre nichts passiert

ROLLBACK verwirft alles, was seit dem zugehörigen BEGIN passiert ist. Die Datenbank kehrt in den Zustand vor der Transaktion zurück.

CREATE TABLE inventory (item TEXT PRIMARY KEY, qty INTEGER);
INSERT INTO inventory VALUES ('apple', 10), ('pear', 5);

BEGIN;
UPDATE inventory SET qty = 0 WHERE item = 'apple';
DELETE FROM inventory WHERE item = 'pear';
ROLLBACK;

SELECT * FROM inventory;

Sowohl das UPDATE als auch das DELETE sind verschwunden — die Tabelle sieht genauso aus wie vor dem BEGIN. Genau dieses Sicherheitsnetz erlaubt es deinem Anwendungscode, sauber abzubrechen, wenn mitten in einer mehrstufigen Operation ein Fehler auftritt.

Übrigens: Eine Constraint-Verletzung innerhalb einer Transaktion rollt nicht automatisch alles zurück. Zurückgerollt wird nur das fehlerhafte Statement, die Transaktion bleibt offen und wartet auf deine Entscheidung. Wenn du das Alles-oder-nichts-Verhalten willst, muss die Anwendung beim Auftreten eines Fehlers selbst ein ROLLBACK absetzen.

Bulk Inserts beschleunigen

Da jedes Statement im Autocommit-Modus seinen eigenen fsync auslöst, ist ein Batch innerhalb einer einzigen Transaktion oft 100-mal schneller:

CREATE TABLE events (id INTEGER PRIMARY KEY, payload TEXT);

BEGIN;
INSERT INTO events (payload) VALUES ('a');
INSERT INTO events (payload) VALUES ('b');
INSERT INTO events (payload) VALUES ('c');
INSERT INTO events (payload) VALUES ('d');
INSERT INTO events (payload) VALUES ('e');
COMMIT;

SELECT count(*) FROM events;

Eine einzige Disk-Synchronisation beim COMMIT statt einer pro Zeile. Falls du also mal Tausende von Zeilen importierst und dich fragst, warum das Ganze im Schneckentempo läuft – die Erklärung liegt fast immer hier.

DEFERRED, IMMEDIATE und EXCLUSIVE

BEGIN kennt verschiedene Modi, mit denen du steuerst, wann SQLite seine Sperren setzt:

• BEGIN DEFERRED (der Standardwert) — zunächst keine Sperre. Erst beim ersten Lese- oder Schreibzugriff passiert etwas, und das Write-Lock wird träge erst beim ersten Schreibstatement geholt.
• BEGIN IMMEDIATE — das Write-Lock wird sofort gesetzt. Andere Verbindungen dürfen weiterhin lesen, aber keine andere Verbindung kann mit dem Schreiben anfangen.
• BEGIN EXCLUSIVE — funktioniert wie IMMEDIATE, blockiert aber zusätzlich auch lesende Zugriffe anderer Verbindungen. Im WAL-Modus verhält sich das identisch zu IMMEDIATE; der Unterschied ist nur im älteren Rollback-Journal-Modus relevant.

BEGIN DEFERRED;     -- gleichbedeutend mit einfachem BEGIN
BEGIN IMMEDIATE;    -- Schreibsperre sofort reservieren
BEGIN EXCLUSIVE;    -- alles reservieren (Rollback-Journal-Modus)

Die Wahl ist nicht egal — sie entscheidet darüber, wie gut deine Anwendung mit gleichzeitigen Zugriffen klarkommt. Bei einem schlichten BEGIN können zwei Verbindungen problemlos parallel eine Transaktion starten, beide lesen vor sich hin und geraten erst dann ins Gehege, wenn sie schreiben wollen: Die zweite Verbindung, die den Write-Lock anfordert, kassiert ein SQLITE_BUSY. Schlimmer noch — sie hat bereits gelesen und muss diese Arbeit nun wegwerfen.

Genau hier kommt BEGIN IMMEDIATE ins Spiel: Wenn von vornherein klar ist, dass geschrieben wird, holst du dir den Write-Lock direkt am Anfang. Die zweite Verbindung blockiert dann sofort (oder bricht schnell ab), bevor sie überhaupt Arbeit verrichtet, die sie später verwerfen müsste.

Faustregel: Sobald deine Transaktion schreibt, nimm BEGIN IMMEDIATE.

Lesen innerhalb einer Transaktion: konsistenter Snapshot

Solange eine Transaktion offen ist, siehst du beim Lesen einen konsistenten Snapshot der Datenbank — und zwar so, wie sie zum Startzeitpunkt der Transaktion aussah (im WAL-Modus) bzw. beim ersten Lesezugriff (im Rollback-Journal-Modus). Änderungen, die andere Verbindungen zwischendurch committen, tauchen in deinen Abfragen nicht plötzlich auf.

CREATE TABLE counter (n INTEGER);
INSERT INTO counter VALUES (1);

BEGIN;
SELECT n FROM counter;   -- 1
UPDATE counter SET n = 99;
SELECT n FROM counter;   -- 99, dein eigener Schreibvorgang ist sichtbar
COMMIT;

SELECT n FROM counter;

Du siehst deine eigenen, noch nicht festgeschriebenen Änderungen; andere Verbindungen sehen sie nicht. Sobald du COMMIT ausführst, wird der neue Wert für alle sichtbar. Genau das ist gemeint, wenn man sagt, SQLite sei serializable – es gibt keinen READ COMMITTED-Schalter, weil schon die Standardeinstellung das stärkste Isolation Level ist.

SQLite Transaktion im Anwendungscode

In einem echten Programm sieht das Muster meistens so aus: try/except (bzw. try/catch) um den eigentlichen Block, mit einem ROLLBACK im Fehlerfall:

-- Pseudocode für eine beliebige Client-Bibliothek
BEGIN IMMEDIATE;
try:
    UPDATE accounts SET cents = cents - 2000 WHERE owner = 'Ada';
    UPDATE accounts SET cents = cents + 2000 WHERE owner = 'Boris';
    COMMIT;
except:
    ROLLBACK;
    raise;

Die meisten Client-Bibliotheken (Pythons sqlite3, better-sqlite3 usw.) kapseln das Ganze für dich – meist über einen with-Block oder einen transaction()-Helper. Ein Blick in die Doku deiner Bibliothek lohnt sich aber: Die Defaults sind nicht immer das, was man erwartet. Pythons sqlite3 hatte beim Autocommit-Verhalten historisch so seine Macken; in neueren Versionen gibt es endlich einen ordentlichen autocommit-Parameter, der das geradezieht.

Stolperfallen, über die alle stolpern

• DDL funktioniert innerhalb von Transaktionen. CREATE TABLE, ALTER TABLE, sogar DROP TABLE lassen sich per Rollback rückgängig machen. SQLite ist da eine Ausnahme – viele andere Datenbanken committen DDL automatisch.
• VACUUM läuft nicht innerhalb einer Transaktion. Das Gleiche gilt für ein paar weitere Wartungsbefehle. Solche Kommandos gehören in den Autocommit-Modus.
• Ein fehlgeschlagenes COMMIT ist ein echter Fehler. Wenn COMMIT ein SQLITE_BUSY zurückgibt (selten, aber möglich), ist die Transaktion nicht committet. Dein Code muss das abfangen – in der Regel durch einen Retry.
• Lange Transaktionen blockieren Schreiber. Eine Transaktion, die minutenlang offen bleibt, blockiert andere Schreiber genauso lange. Spät öffnen, schnell committen.

Als Nächstes: Savepoints

BEGIN und COMMIT kennen nur Alles-oder-nichts. Manchmal willst du aber nur einen Teil einer Transaktion zurückrollen – also einen riskanten Schritt verwerfen, den Rest aber behalten. Genau dafür gibt es Savepoints, und um die geht es im nächsten Kapitel.