Datenbank-Transaktionen: ACID und Isolationsstufen Erklärt

Warum eine Banküberweisung mehr als zwei UPDATE-Anweisungen braucht

Verschieben Sie 50 € von einem Konto auf ein anderes, und die naive Version sind zwei Anweisungen: vom Konto A abziehen, dem Konto B hinzufügen. Führen Sie sie getrennt aus, und wenn der Prozess abstürzt oder die Verbindung zwischen den beiden abbricht, ist das Geld von A verschwunden und kommt nie bei B an. Die Datenbank hat genau das getan, was ihr gesagt wurde, zweimal. Das Geschäft hat trotzdem 50 € verloren.

Eine Transaktion fasst beide Anweisungen so zusammen, dass die Datenbank sie als eine Einheit behandelt: entweder passieren beide, oder keine. Diese Garantie, plus drei weitere, ist das, was “ACID” bezeichnet. Deshalb können Sie einer Datenbank Geld oder Lagerbestände anvertrauen, wo “halb erledigt” schlimmer ist als “nicht erledigt”.

Was die vier ACID-Eigenschaften garantieren

• Atomarität (Atomicity). Alle Anweisungen einer Transaktion werden zusammen committet, oder keine davon. Stürzt der Prozess nach der Abbuchung, aber vor der Gutschrift ab, macht die Datenbank auch die Abbuchung rückgängig. Es gibt keinen Teilzustand, den Sie von Hand aufräumen müssten.
• Konsistenz (Consistency). Eine Transaktion kann die Datenbank nur von einem gültigen Zustand in einen anderen überführen. Sagt eine CHECK-Constraint, dass ein Saldo nicht negativ werden darf, darf keine Transaktion eine Zeile committen, die das verletzt, egal was der Anwendungscode versucht hat.
• Isolation. Gleichzeitig laufende Transaktionen sehen die halbfertige Arbeit der anderen nicht. Wie strikt das durchgesetzt wird, ist ein Regler, keine feste Regel: Dieser Regler ist die Isolationsstufe, die weiter unten behandelt wird.
• Dauerhaftigkeit (Durability). Sobald die Datenbank meldet, dass eine Transaktion committet wurde, überlebt dieser Datensatz einen Absturz eine Sekunde später. Dafür ist das Write-Ahead-Log da: Die Änderung ist auf der Festplatte (oder repliziert), bevor der Commit zurückkehrt.

Für Atomarität und Dauerhaftigkeit sorgt die Datenbank, und die gängigen Engines setzen das korrekt um. Bei der Isolation entscheiden Sie mit. Strengere Isolation kostet Nebenläufigkeit, und diese Kosten zeigen sich in den Latenzgraphen der Produktion.

Eine Transaktion mit BEGIN, COMMIT und ROLLBACK ausführen

In PostgreSQL (und den meisten SQL-Datenbanken, mit kleinen Syntaxunterschieden) ist eine Transaktion die Menge der Anweisungen zwischen BEGIN und COMMIT:

1
2
3
4
5
6

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

COMMIT;

Geht vor dem COMMIT etwas schief, etwa eine Constraint-Verletzung oder eine Geschäftsregel-Prüfung im Anwendungscode, macht ROLLBACK alles rückgängig, was seit BEGIN passiert ist:

1
2
3
4
5

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- die Geschäftsregel-Prüfung im Anwendungscode stellt einen negativen Saldo fest
ROLLBACK;

Nach dem Rollback ist der Saldo von Konto 1 genau der, den er vor dem Ausführen von BEGIN hatte. Für eine lange Transaktion, bei der Sie nur einen Teil der Arbeit rückgängig machen wollen, gibt Ihnen ein Savepoint ein partielles Rollback:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

BEGIN;

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;

ROLLBACK TO before_credit;  -- macht nur die Gutschrift rückgängig
UPDATE accounts SET balance = balance + 50 WHERE id = 3; -- schreibt stattdessen einem anderen Konto gut

COMMIT;

Außerhalb eines expliziten BEGIN führen die meisten Treiber jede Anweisung in ihrer eigenen, automatisch committeten Transaktion aus. Jede Anweisung ist für sich atomar, aber nichts verknüpft zwei Anweisungen miteinander. Lassen Sie BEGIN bei der Banküberweisung weg, und Sie haben die verlorenen 50 € wieder.

Was Isolationsstufen von Transaktionen verhindern

Zwei gleichzeitige Transaktionen können sich auf drei konkrete Arten gegenseitig stören, und Isolationsstufen werden danach definiert, welche davon sie zulassen:

Der SQL-Standard definiert vier Stufen rund um diese drei Anomalien:

READ UNCOMMITTED werden Sie selten wollen. PostgreSQL implementiert es nicht einmal und führt stillschweigend Read Committed aus; MySQL hält sich tatsächlich daran und zeigt Ihnen Dirty Reads. In der Praxis zählen die beiden Stufen Read Committed und Repeatable Read, und die beiden großen Open-Source-Datenbanken liefern unterschiedliche Standardwerte:

• PostgreSQL verwendet standardmäßig Read Committed. Jede Anweisung innerhalb einer Transaktion sieht einen frischen Snapshot, der beim Start dieser Anweisung erstellt wird, sodass zwei SELECTs in derselben Transaktion unterschiedliche Daten liefern können, wenn eine andere Transaktion dazwischen committet hat.
• MySQL (InnoDB) verwendet standardmäßig Repeatable Read. Der Snapshot wird nur einmal erstellt, beim ersten Lesen der Transaktion, und jedes spätere einfache SELECT in dieser Transaktion sieht denselben Snapshot, selbst wenn eine andere Transaktion seitdem ein Update committet hat.

In PostgreSQL legen Sie die Stufe direkt nach BEGIN fest, bevor irgendeine Abfrage läuft:

1
2
3
4
5
6
7

-- PostgreSQL
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
-- ... später in derselben Transaktion ...
SELECT balance FROM accounts WHERE id = 1; -- garantiert derselbe Wert wie oben
COMMIT;

In MySQL ändert die SESSION-Form die Stufe für jede folgende Transaktion auf dieser Verbindung:

1
2

-- MySQL
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Wie Repeatable Read einen Phantom Read in PostgreSQL blockiert

Öffnen Sie zwei psql-Sitzungen gegen dieselbe Tabelle und führen Sie das Folgende aus. Es einmal zu sehen, bleibt besser im Kopf als die Tabelle auswendig zu lernen.

Sitzung A:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

Sitzung B, während die Transaktion von A noch offen ist:

1
2
3

BEGIN;
INSERT INTO accounts (id, balance) VALUES (9, 2000);
COMMIT;

Sitzung A, dieselbe offene Transaktion, führt dieselbe Abfrage erneut aus:

1
2
3
4

SELECT id, balance FROM accounts WHERE balance > 1000;
--  id | balance
-- ----+---------
--   3 |    1500

Die Zeile, die B eingefügt und committet hat, taucht nicht auf. Das Repeatable Read von PostgreSQL nimmt seinen Snapshot bei der ersten Abfrage der Transaktion von A und behält ihn bis zum COMMIT, sodass neue Zeilen aus anderen Transaktionen nie erscheinen. Das ist strenger, als der Standard verlangt: Die Tabelle oben erlaubt Phantoms auf dieser Stufe, aber die Implementierung von PostgreSQL erzeugt sie nicht. Führen Sie dieselbe Sequenz mit A unter Read Committed aus, liefert das zweite SELECT die neue Zeile, weil Read Committed pro Anweisung einen frischen Snapshot nimmt.

Serializable geht weiter. Es erkennt, wenn zwei gleichzeitige Transaktionen ein Ergebnis erzeugt haben, das in keiner sequenziellen Reihenfolge entstehen könnte, und bricht eine davon mit einem Serialisierungsfehler ab (PostgreSQL liefert den SQLSTATE 40001), statt die Anomalie committen zu lassen. Das ist die Stufe, die die klassische Race Condition schließt, bei der zwei Transaktionen jeweils einen Wert lesen, auf dieser Basis entscheiden und beide schreiben. Code, der SERIALIZABLE nutzt, muss bereit sein, es erneut zu versuchen:

1
2
3
4
5
6

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- ... Anweisungen ...
COMMIT;
-- bei Fehler 40001 ("could not serialize access due to read/write dependencies"):
-- Rollback ausführen und die ganze Transaktion erneut versuchen

Was strengere Isolationsstufen kosten

Der Preis für strengere Isolation ist Nebenläufigkeit:

• Schreibvorgänge bleiben bis zum Commit gesperrt, auf jeder Stufe. Ein UPDATE nimmt in dem Moment, in dem es ausgeführt wird, eine Zeilensperre und hält sie bis COMMIT oder ROLLBACK, unabhängig von der Isolationsstufe. Zusätzlich halten Repeatable Read und Serializable denselben Snapshot (oder bei Serializable Prädikatssperren auf den Zeilen, die Ihre Abfragen untersucht haben) für die ganze Transaktion. Machen Sie einen langsamen externen API-Aufruf zwischen BEGIN und COMMIT, bleibt jede Zeile, die Sie geschrieben haben, gesperrt, und jede Zeile, die Sie unter Serializable gelesen haben, bleibt nachverfolgt — so lange, wie dieser Aufruf dauert.
• Unter Serializable sind Wiederholungsversuche nicht optional. Es lehnt scheinbar gültige Transaktionen unter gleichzeitiger Last absichtlich ab. Fängt Ihre Anwendung Serialisierungsfehler nicht ab und versucht es nicht erneut, schlagen diese Transaktionen unter Last sporadisch fehl — der schlechteste Zeitpunkt, es herauszufinden. Repeatable Read kann denselben 40001-Fehler bei einem einfachen gleichzeitigen Update auslösen: Der Wiederholungscode ist also nicht nur für Serializable nötig, sondern für jede Transaktion oberhalb von Read Committed.
• Lange Transaktionen blockieren Schemaänderungen und lassen den Speicherplatz aufblähen. Das MVCC von PostgreSQL hält alte Zeilenversionen so lange vor, wie eine offene Transaktion sie noch brauchen könnte. Eine stundenlang offen gelassene Transaktion (ein verbreiteter Bug: eine in einem Web-Request öffnen und nie committen oder zurückrollen) verhindert, dass VACUUM diesen Speicherplatz zurückgewinnt, und ein ALTER TABLE auf einer betroffenen Tabelle wartet, bis sie fertig ist.
• Nicht jeder Schreibvorgang braucht eine explizite Transaktion. Eine einzelne UPDATE-Anweisung ist bereits atomar. Eine einzelne Anweisung in BEGIN/COMMIT einzupacken fügt einen Roundtrip ohne zusätzliche Garantie hinzu. Greifen Sie zu einer expliziten Transaktion, wenn die Korrektheit davon abhängt, dass zwei oder mehr Anweisungen zusammen gelingen oder fehlschlagen: die Banküberweisung, eine Registrierung, die sowohl eine users- als auch eine accounts-Zeile schreibt, eine Bestellung, die den Lagerbestand verringert und den Bestellsatz einfügt.

ORMs (Entity Framework, SQLAlchemy, ActiveRecord) öffnen normalerweise eine Transaktion pro Arbeitseinheit und committen sie für Sie. Arbeiten Sie nicht dagegen. Der Bug, auf den Sie achten sollten, ist das Gegenteil: Code, der .save() oder .commit() getrennt für zwei zusammenhängende Schreibvorgänge aufruft und damit die Atomarität verschenkt, die das ORM geliefert hätte.

Welche Isolationsstufe Sie verwenden sollten

Starten Sie bei Read Committed und gehen Sie nur für die konkreten Transaktionen höher, bei denen Sie das Rennen benennen können, das Sie schließen. Das ist dieselbe Disziplin wie einen Index hinzuzufügen erst, nachdem Sie die langsame Abfrage gefunden haben. Können Sie dieses Rennen nicht in einem Satz aufschreiben, brauchen Sie noch keine strengere Stufe.

Verwandte Artikel

• Wie Datenbankindizes Funktionieren und Wann Sie Nutzen
• SQL-JOIN-Typen Erklärt mit Beispielen
• Was ist eine Race Condition und wie behebt man sie