Die vier Isolationsstufen

Wenn ein Interviewer Sie auffordert, "die vier Isolationsstufen zu nennen", wird in Wirklichkeit geprüft, ob Sie die Abwägung erklären können: Stärkere Isolation bedeutet weniger Anomalien, aber auch geringere Nebenläufigkeit.

Der SQL-Standard definiert vier Stufen, geordnet von der schwächsten zur stärksten:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

Jede Stufe lässt eine bestimmte Menge von Leseanomalien zu oder verhindert sie. Diese Lektion behandelt die Stufen; die nächste erklärt die Anomalien ausführlich.

Sie legen die Isolation pro Transaktion oder pro Sitzung fest. Die Syntax ist bei den verschiedenen Engines nahezu identisch.

Wenn Sie keine Stufe festlegen, hat jede Datenbank einen Standardwert. Die Standardwerte zu kennen, ist eine häufige Frage im Interview, daher behandeln wir sie am Ende.

READ UNCOMMITTED ist die schwächste Stufe. Eine Transaktion kann Zeilen lesen, die eine andere Transaktion geändert, aber noch nicht bestätigt hat. Diese werden als Dirty Reads bezeichnet.

Wenn die andere Transaktion zurückgesetzt wird, haben Sie Daten gelesen, die offiziell nie existiert haben. Das ist für alles, was korrekt sein muss, gefährlich.

Hinweis: Postgres behandelt READ UNCOMMITTED genauso wie READ COMMITTED und führt daher tatsächlich niemals Dirty Reads aus. SQL Server und MySQL unterstützen diese Stufe dagegen tatsächlich.

READ COMMITTED garantiert, dass Sie ausschließlich bestätigte Daten lesen. Es gibt keine Dirty Reads.

Jede Anweisung sieht jedoch den aktuellsten bestätigten Snapshot. Wenn Sie dieselbe Abfrage innerhalb einer Transaktion zweimal ausführen, kann eine dazwischen bestätigte Transaktion das Ergebnis verändern. Diese Anomalie wird als Non-Repeatable Read bezeichnet.

Dies ist der Standard in Postgres, Oracle und SQL Server und für die meisten Anwendungen ein sinnvoller Kompromiss.

REPEATABLE READ stellt sicher, dass Sie beim zweimaligen Lesen einer Zeile innerhalb derselben Transaktion beide Male denselben Wert erhalten. Zu Beginn der Transaktion wird ein konsistenter Snapshot erstellt.

Dirty Reads und Non-Repeatable Reads werden verhindert. Der Standard erlaubt weiterhin Phantom Reads: neue Zeilen, die Ihrer WHERE-Klausel entsprechen und bei einer erneuten Abfrage erscheinen.

Wichtig: Dies ist der Standard in MySQL/InnoDB. Die Implementierung von InnoDB verhindert durch Next-Key-Locks außerdem die meisten Phantom Reads.

SERIALIZABLE ist die strengste Stufe. Die Datenbank garantiert, dass das Ergebnis der nebenläufigen Ausführung von Transaktionen identisch mit dem Ergebnis wäre, wenn sie nacheinander in einer bestimmten seriellen Reihenfolge ausgeführt würden.

Dirty Reads, Non-Repeatable Reads und Phantom Reads werden verhindert. Der Preis dafür sind mehr Sperren oder – in Postgres – Abbrüche wegen Serialisierungsfehlern, die Sie wiederholen müssen.

Formulierung fürs Interview: "SERIALIZABLE vermittelt den Eindruck, dass jede Transaktion allein ausgeführt wurde, allerdings bei geringerer Nebenläufigkeit und möglichen Wiederholungen."

Am nützlichsten ist es, sich zu merken, welche Anomalien jede Stufe zulässt. "Ja" bedeutet, dass die Anomalie auftreten kann.

• READ UNCOMMITTED: Dirty Reads=Ja, Non-Repeatable Reads=Ja, Phantom Reads=Ja
• READ COMMITTED: Dirty Reads=Nein, Non-Repeatable Reads=Ja, Phantom Reads=Ja
• REPEATABLE READ: Dirty Reads=Nein, Non-Repeatable Reads=Nein, Phantom Reads=Ja (gemäß Standard)
• SERIALIZABLE: Dirty Reads=Nein, Non-Repeatable Reads=Nein, Phantom Reads=Nein

Mit jedem Schritt nach oben wird eine weitere Anomalie verhindert. Diese Entwicklung ist die vollständige Antwort.

Eine wichtige Unterscheidung auf Senior-Niveau: Der SQL-Standard definiert die Stufen danach, welche Anomalien sie verhindern müssen, nicht danach, wie sie das tun. Reale Engines verhindern häufig mehr.

• Postgres REPEATABLE READ verwendet Snapshot Isolation und verhindert ebenfalls Phantom Reads, kann aber weiterhin von Write Skew betroffen sein.
• MySQL/InnoDB REPEATABLE READ verhindert Phantom Reads durch Next-Key-Locking.
• Postgres SERIALIZABLE verwendet SSI (Serializable Snapshot Isolation) und bricht Transaktionen bei Konflikten ab, statt umfangreiche Sperren einzusetzen.

Wenn Sie das erwähnen, zeigen Sie, dass Sie wissen: Der Standard ist eine Untergrenze und beschreibt nicht exakt das Verhalten jeder Engine.

Die Standardwerte werden sehr häufig abgefragt. Merken Sie sich diese:

• PostgreSQL: READ COMMITTED
• Oracle: READ COMMITTED (niemals Dirty Reads)
• SQL Server: READ COMMITTED
• MySQL (InnoDB): REPEATABLE READ

Die Ausnahme bei MySQL ist eine beliebte Fangfrage. Wenn Sie gefragt werden, "wie lautet die Standard-Isolationsstufe?", klären Sie immer zuerst, um welche Engine es geht.

Wie entscheiden Sie sich? Stellen Sie Risiko und Durchsatz gegenüber.

• Verwenden Sie READ COMMITTED für typisches OLTP; diese Stufe ist schnell und verhindert Dirty Reads.
• Verwenden Sie REPEATABLE READ, wenn eine Transaktion dieselben Daten mehrfach liest und sie stabil bleiben müssen (Berichte, mehrstufige Berechnungen).
• Verwenden Sie SERIALIZABLE für Logik, bei der Korrektheit entscheidend ist und keine Anomalie akzeptabel ist. Planen Sie dabei eine Wiederholungslogik für Abbrüche ein.

READ UNCOMMITTED sollte in Produktion fast nie verwendet werden.

Nachdem Sie die Stufen aufgezählt haben, stellen Interviewer gerne schnelle Anschlussfragen. Halten Sie präzise Antworten bereit:

• "Welche Stufe verhindert Dirty Reads, erlaubt aber Non-Repeatable Reads?" READ COMMITTED.
• "Welche einzige Anomalie erlaubt REPEATABLE READ gemäß dem Standard weiterhin?" Phantom Reads.
• "Warum sollte man nicht immer SERIALIZABLE verwenden?" Die Nebenläufigkeit wird reduziert, und bei Serialisierungsfehlern können Wiederholungen der Transaktion erforderlich sein.
• "Kostet eine höhere Stufe mehr?" Ja, entweder durch Sperren oder durch den Aufwand für Abbruch und Wiederholung.

Wenn Sie diese Fragen sofort beantworten können, beweisen Sie, dass Sie die Stufenfolge verinnerlicht und nicht nur auswendig gelernt haben.

Eine dieser Aussagen betrifft den am häufigsten abgefragten Fakt zu den Standard-Isolationsstufen.

Die vier Isolationsstufen bilden eine Skala von der schwächsten zur stärksten Stufe: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. Mit jedem Schritt nach oben wird eine weitere Anomalie (Dirty Read, Non-Repeatable Read, Phantom Read) verhindert – auf Kosten der Nebenläufigkeit.

Merken Sie sich die Standardwerte (überall READ COMMITTED außer MySQL mit REPEATABLE READ) und beachten Sie, dass reale Engines häufig mehr verhindern, als der Standard verlangt. Als Nächstes untersuchen wir die drei Leseanomalien, die diese Stufen verhindern sollen.