Isolation Levels in MySQL: Unterschiede wirklich verstehen

1. Warum Isolation Levels überhaupt existieren

Der Isolation Level einer Datenbank legt fest, wie stark parallel laufende Transaktionen einander sehen dürfen, während sie gleichzeitig auf denselben Daten arbeiten. Ohne jede Isolation würde jede Transaktion sofort jede Änderung jeder anderen Transaktion sehen, noch bevor diese committet wurde, was zu unvorhersehbaren und oft falschen Ergebnissen führt. Vollständige Isolation, bei der Transaktionen sich gegenseitig komplett unsichtbar sind, wäre zwar sicher, aber in der Praxis so langsam, dass parallele Verarbeitung kaum noch möglich ist.

Der SQL-Standard definiert deshalb vier Isolation Levels als Kompromiss zwischen Konsistenz und Durchsatz: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ und SERIALIZABLE. Jede Stufe verhindert eine zusätzliche Klasse von Anomalien, kostet dafür aber mehr Locking-Overhead und reduziert die mögliche Parallelität. MySQL implementiert alle vier Stufen in InnoDB, mit einer wichtigen Besonderheit gegenüber anderen Datenbanksystemen wie PostgreSQL oder Oracle: der Standard-Isolation-Level ist REPEATABLE READ, nicht READ COMMITTED.

Dieser Artikel geht jeden der vier Isolation Levels konkret durch, mit SQL-Beispielen zu den jeweiligen Anomalien, die sie zulassen oder verhindern, und erklärt im Detail, wie InnoDB seinen Default REPEATABLE READ über einen konsistenten Snapshot technisch umsetzt.

2. READ UNCOMMITTED: Dirty Reads im Detail

READ UNCOMMITTED ist der schwächste Isolation Level und erlaubt sogenannte Dirty Reads: Eine Transaktion sieht Änderungen einer anderen Transaktion, noch bevor diese committet wurde. Wird die fremde Transaktion später mit ROLLBACK zurückgerollt, hat die lesende Transaktion bereits mit Daten gearbeitet, die es in der Datenbank nie tatsächlich gegeben hat. Dieses Verhalten ist in fast allen produktiven Anwendungsfällen unerwünscht.

In der Praxis wird dieser Isolation Level fast nie bewusst eingesetzt, außer bei sehr spezifischen Monitoring- oder Debugging-Abfragen, bei denen ein grober Überblick über Daten wichtiger ist als deren Korrektheit, und bei denen Sperren durch andere Transaktionen unter keinen Umständen blockieren dürfen. InnoDB implementiert READ UNCOMMITTED technisch, indem beim Lesen keine Konsistenzprüfung über MVCC erfolgt, sondern direkt die aktuelle, möglicherweise noch nicht committete Version einer Zeile gelesen wird.

-- Session A: sets isolation level and starts a transaction
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- reads 500, an uncommitted value

-- Session B (concurrently, not yet committed):
-- START TRANSACTION;
-- UPDATE accounts SET balance = 500 WHERE id = 1;
-- -- no COMMIT yet, or later a ROLLBACK

-- Session A saw the uncommitted 500 even though
-- session B might roll back afterwards

3. READ COMMITTED: Verhalten und Einsatzgebiete

READ COMMITTED verhindert Dirty Reads: Eine Transaktion sieht ausschließlich Daten, die zum Zeitpunkt des jeweiligen Lesevorgangs bereits committet wurden. Der entscheidende Unterschied zu REPEATABLE READ liegt in der Häufigkeit des Snapshots: Bei READ COMMITTED erstellt InnoDB bei jeder einzelnen SELECT-Anweisung einen neuen Snapshot, nicht nur einmal zu Beginn der Transaktion. Führt man innerhalb derselben Transaktion zweimal dieselbe Abfrage aus, und eine andere Transaktion committet dazwischen eine Änderung, liefern beide Abfragen unterschiedliche Ergebnisse. Dieses Phänomen nennt man Non-Repeatable Read.

Dieser Isolation Level ist in vielen Webanwendungen die pragmatische Wahl, wenn Lesevorgänge stets den aktuellsten committeten Stand widerspiegeln sollen, etwa bei Dashboards oder Reporting-Abfragen, die während einer laufenden Transaktion mehrfach lesen und dabei jedes Mal aktuelle Daten erwarten. Ein weiterer praktischer Effekt: READ COMMITTED reduziert die Anzahl der von InnoDB gesetzten Gap Locks deutlich gegenüber REPEATABLE READ, was die Wahrscheinlichkeit unerwarteter Lock Waits senkt.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

SELECT balance FROM accounts WHERE id = 1;  -- e.g. reads 500

-- Another session commits a change to this row in between

SELECT balance FROM accounts WHERE id = 1;  -- may now read a different value

COMMIT;

In der Praxis zeigt sich dieser Unterschied besonders deutlich bei Reporting-Abfragen, die mehrere Zwischensummen innerhalb derselben Transaktion berechnen. Unter READ COMMITTED können zwei aufeinanderfolgende Aggregationen über dieselbe Tabelle unterschiedliche Ergebnisse liefern, wenn zwischen den beiden Abfragen eine andere Transaktion Daten committet, was bei Finanzreports zu scheinbar inkonsistenten Zwischenergebnissen führen kann, obwohl jede einzelne Abfrage für sich korrekt ist.

4. REPEATABLE READ: der MySQL-Default

REPEATABLE READ ist der Standard-Isolation-Level in InnoDB und unterscheidet sich fundamental von der Implementierung in anderen Datenbanksystemen. Während der SQL-Standard für REPEATABLE READ nur garantiert, dass bereits gelesene Zeilen sich innerhalb derselben Transaktion nicht ändern, geht InnoDBs Implementierung weiter: Der komplette Datenbestand wird für die gesamte Dauer der Transaktion so gesehen, wie er zum Zeitpunkt der ersten Leseoperation aussah. Das verhindert nicht nur Non-Repeatable Reads, sondern in der Praxis auch die meisten Phantom Reads bei einfachen SELECT-Abfragen.

Wichtig ist die Unterscheidung zwischen lesenden und schreibenden Zugriffen: Ein einfaches SELECT ohne FOR UPDATE liest über den Snapshot und sieht damit konsequent den Stand vom Transaktionsbeginn. Ein UPDATE, DELETE oder SELECT ... FOR UPDATE arbeitet dagegen immer mit dem aktuellsten committeten Stand der betroffenen Zeilen, unabhängig vom Snapshot. Genau dieser Unterschied führt in der Praxis häufig zu Verwirrung, wenn ein Entwickler erwartet, dass ein UPDATE auf dem gleichen alten Stand arbeitet wie ein vorheriges SELECT innerhalb derselben Transaktion.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT balance FROM accounts WHERE id = 1;  -- reads 500, snapshot is fixed now

-- Another session commits balance = 300 in between

SELECT balance FROM accounts WHERE id = 1;  -- still reads 500 (same snapshot)

UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- this UPDATE operates on the latest committed value (300), not on 500

SELECT balance FROM accounts WHERE id = 1;  -- reads 250, own change is visible

COMMIT;

5. SERIALIZABLE: stärkste Isolation, höchste Kosten

SERIALIZABLE ist der strengste Isolation Level und garantiert, dass das Ergebnis paralleler Transaktionen identisch mit dem Ergebnis wäre, wenn alle Transaktionen streng nacheinander ausgeführt würden. InnoDB implementiert das, indem jede einfache SELECT-Anweisung implizit in eine SELECT ... LOCK IN SHARE MODE umgewandelt wird, sofern Autocommit deaktiviert ist. Damit setzt bereits ein einfacher Lesevorgang Shared Locks, die andere Transaktionen am Schreiben derselben Zeilen hindern.

Diese Strenge hat einen direkten Preis: Die Parallelität sinkt spürbar, weil lesende und schreibende Transaktionen sich gegenseitig blockieren, was unter hoher Last zu deutlich mehr Lock Waits und potenziell zu mehr Deadlocks führt. SERIALIZABLE eignet sich für Fälle, in denen absolute Korrektheit wichtiger ist als Durchsatz, etwa bei komplexen Finanzberechnungen mit mehreren voneinander abhängigen Lesevorgängen, bei denen selbst die von REPEATABLE READ gebotene Konsistenz nicht ausreicht.

In der Praxis wird SERIALIZABLE selten dauerhaft für eine ganze Anwendung gesetzt, sondern gezielt für einzelne kritische Transaktionen aktiviert, etwa über SET TRANSACTION ISOLATION LEVEL SERIALIZABLE direkt vor der betroffenen Transaktion. So bleibt der Rest der Anwendung bei REPEATABLE READ mit höherer Parallelität, während nur die wenigen Stellen mit besonders hohem Konsistenzanspruch von der strengeren Garantie profitieren.

6. Consistent Reads unter REPEATABLE READ: MVCC im Detail

Der Mechanismus hinter dem Consistent-Read-Verhalten von REPEATABLE READ heißt Multi-Version Concurrency Control, kurz MVCC. Jede Zeile in InnoDB trägt intern versteckte Metadaten, unter anderem eine Transaktions-ID, die angibt, welche Transaktion die Zeile zuletzt verändert hat. Beim ersten Lesevorgang einer Transaktion erstellt InnoDB eine Read View, die festhält, welche Transaktionen zu diesem Zeitpunkt bereits committet waren und welche noch aktiv sind. Alle folgenden Lesevorgänge derselben Transaktion nutzen diese Read View, um zu entscheiden, welche Version einer Zeile sichtbar ist.

Ist die aktuellste Version einer Zeile von einer Transaktion geschrieben worden, die laut Read View noch nicht committet war, folgt InnoDB der Verkettung im Undo Log zurück, bis eine Version gefunden wird, die zum Zeitpunkt der Read View bereits sichtbar war. Dieser Mechanismus erklärt, warum lesende Transaktionen unter diesem Isolation Level niemals durch schreibende Transaktionen blockiert werden: Sie lesen einfach eine ältere, im Undo Log gespeicherte Version, statt auf die aktuelle Zeile zu warten.

Dieses Verhalten hat einen wichtigen praktischen Nebeneffekt: Eine sehr lange laufende Transaktion mit REPEATABLE READ zwingt InnoDB, alle für ihre Read View relevanten Undo-Log-Einträge vorzuhalten, selbst wenn diese Zeilen längst durch neuere Transaktionen verändert wurden. Das lässt das Undo-Tablespace wachsen und ist einer der Hauptgründe, warum lange offene Transaktionen unter REPEATABLE READ vermieden werden sollten.

7. Isolation Level setzen: Session, Global, Transaktion

MySQL erlaubt, den Isolation Level auf drei Ebenen zu konfigurieren. Global mit SET GLOBAL TRANSACTION ISOLATION LEVEL ... ändert den Default für alle neuen Sessions, bestehende Sessions bleiben unverändert. Auf Session-Ebene mit SET SESSION TRANSACTION ISOLATION LEVEL ... gilt der Wert für alle folgenden Transaktionen dieser Verbindung. Auf Transaktions-Ebene mit SET TRANSACTION ISOLATION LEVEL ..., direkt vor START TRANSACTION, gilt der Wert nur für die einzelne nächste Transaktion.

In der Praxis empfiehlt sich, den Isolation Level nicht global für die gesamte Datenbank zu ändern, sondern gezielt pro Anwendungsfall auf Transaktions-Ebene zu setzen. Eine Reporting-Query, die READ COMMITTED benötigt, sollte diesen Level explizit für ihre eigene Transaktion anfordern, ohne den Default für alle anderen Anwendungsteile zu beeinflussen, die möglicherweise auf das REPEATABLE-READ-Verhalten angewiesen sind.

-- Check the current session and global isolation level
SELECT @@transaction_isolation, @@global.transaction_isolation;

-- Set isolation level only for the very next transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ... statements using READ COMMITTED semantics ...
COMMIT;

-- Back to the session default (usually REPEATABLE READ)
START TRANSACTION;
-- ... statements using REPEATABLE READ semantics again ...
COMMIT;

8. Praktische Auswirkungen: Phantom Reads und Lost Updates

Ein Phantom Read entsteht, wenn eine Transaktion eine Bereichsabfrage zweimal ausführt und beim zweiten Mal zusätzliche Zeilen sieht, die eine andere Transaktion inzwischen eingefügt und committet hat. Unter READ COMMITTED tritt dieses Phänomen regelmäßig auf, weil jede Abfrage einen neuen Snapshot erstellt. Unter REPEATABLE READ verhindert InnoDB Phantom Reads bei einfachen SELECT-Abfragen durch den festen Snapshot, bei SELECT ... FOR UPDATE greifen zusätzlich Next-Key Locks, die neue Einfügungen im gesperrten Bereich blockieren, solange die Transaktion offen ist.

Ein Lost Update entsteht, wenn zwei Transaktionen denselben Wert lesen, unabhängig voneinander berechnen und schreiben, wobei die zweite Schreiboperation die erste überschreibt, ohne von ihr zu wissen. Kein Isolation Level allein verhindert dieses Muster zuverlässig, wenn die Anwendung ein einfaches Read-Modify-Write ohne explizite Sperren durchführt. Die zuverlässige Lösung ist entweder SELECT ... FOR UPDATE, das die gelesene Zeile bis zum COMMIT sperrt, oder Optimistic Locking über eine Versionsspalte, die bei jedem UPDATE geprüft und inkrementiert wird.

9. Isolation Level und Replikation

Der gewählte Isolation Level wirkt sich auch auf die Replikation aus, insbesondere im Zusammenspiel mit dem Binlog-Format. Bei statement-basierter Replikation (SBR) wird die tatsächliche SQL-Anweisung an die Replikate übertragen, die dort erneut ausgeführt wird. Unter READ COMMITTED kann dieselbe Anweisung auf Primary und Replikat zu unterschiedlichen Ergebnissen führen, weil sich der sichtbare Datenbestand zwischen den beiden Ausführungszeitpunkten unterscheiden kann. MySQL empfiehlt deshalb für READ COMMITTED grundsätzlich row-basierte Replikation (RBR), bei der die tatsächlich geänderten Zeilen übertragen werden, nicht die Anweisung selbst.

REPEATABLE READ funktioniert dagegen sowohl mit SBR als auch mit RBR zuverlässig, da der Snapshot-Mechanismus deterministische Ergebnisse für dieselbe Transaktion garantiert. In den meisten modernen MySQL-Installationen ist RBR ohnehin die Standardeinstellung, wodurch dieser Unterschied in der Praxis seltener zum Problem wird, aber bei der Migration älterer Systeme mit SBR sollte der gewählte Isolation Level explizit geprüft werden.

Ein zusätzlicher praktischer Hinweis für Umgebungen mit GTID-basierter Replikation: Der Isolation Level selbst wird nicht über GTIDs synchronisiert, sondern bleibt eine reine Sitzungs- oder Verbindungseinstellung. Wer auf Replikaten bewusst READ COMMITTED für Reporting-Lasten nutzen will, während der Primary bei REPEATABLE READ bleibt, kann das gefahrlos tun, solange RBR aktiv ist, da beide Ebenen unabhängig voneinander konfiguriert werden.

10. Zusammenfassung

Die vier Isolation Levels in MySQL bilden ein Spektrum zwischen Durchsatz und Konsistenz. READ UNCOMMITTED erlaubt Dirty Reads und eignet sich fast nie für produktiven Einsatz. READ COMMITTED erstellt bei jeder Abfrage einen neuen Snapshot und eignet sich für Fälle, die stets aktuelle Daten sehen sollen. REPEATABLE READ, der Default in InnoDB, hält einen festen Snapshot über die gesamte Transaktion und verhindert dadurch die meisten Anomalien bei einfachen Lesevorgängen. SERIALIZABLE garantiert vollständige Korrektheit auf Kosten spürbar reduzierter Parallelität.

Der entscheidende technische Mechanismus hinter REPEATABLE READ ist MVCC: InnoDB nutzt das Undo Log, um lesenden Transaktionen ältere, konsistente Zeilenversionen zu liefern, ohne sie durch parallele Schreiboperationen zu blockieren. Wer den passenden Isolation Level pro Anwendungsfall bewusst wählt, statt sich blind auf den Default zu verlassen, vermeidet sowohl unnötige Lock Waits als auch subtile Datenkonsistenzfehler.

11. FAQ: Isolation Levels in MySQL