← Dashboard

Kapitel 10 · Datenbanken

Isolation Levels

⭐ Klausur-Schwerpunkt · Hoch
⭐ Laut Dozent: Die Isolationslevel-Matrix (Slide 16) ist Schwerpunkt — welches Level welche Anomalie zulässt, sicher ableiten können.

Zusammenfassung

Isolation Levels (Isolationsebenen) sind eine Aufweichung der ACID-Isolationsbedingung, mit denen pro Transaktion definiert wird, welche Nebenläufigkeitsprobleme toleriert werden. Der SQL-Standard definiert vier Stufen: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ und SERIALIZABLE. Je höher die Stufe, desto mehr Probleme (Dirty Read, Nonrepeatable Read, Phantom) werden verhindert, aber desto niedriger ist die Parallelität und der Durchsatz.

Kernkonzepte

READ UNCOMMITTED

Schwächste Stufe der Isolation. Eine Transaktion darf auch noch nicht committete (also potenziell zurückgerollte) Daten anderer Transaktionen lesen. Damit sind Dirty Reads erlaubt und alle darauf folgenden Probleme möglich. Default ist hier read only, weil das Risiko für Inkonsistenzen durch dirty reads und writes sonst zu hoch wäre. Typischer Anwendungsfall: statistische Lese-Transaktionen, bei denen nur ein ungefährer Überblick benötigt wird und keine korrekten Werte gefordert sind.

READ UNCOMMITTED verhindert nichts. Es sieht sogar schmutzige, nicht bestätigte Werte.

READ COMMITTED

Es dürfen nur endgültig geschriebene, also committete Werte gelesen werden. Damit wird der Dirty Read verhindert. Nonrepeatable Read ist jedoch weiterhin möglich: Wenn dieselbe Transaktion zweimal denselben Datensatz liest, kann zwischen den beiden Lesevorgängen eine andere Transaktion den Wert verändert und committet haben. Auch Phantom-Reads sind möglich. Dies ist die Standardstufe vieler DBMS wie Oracle und PostgreSQL.

READ COMMITTED verhindert nur Dirty Read. Nonrepeatable Read und Phantom bleiben möglich.

REPEATABLE READ

Ein einmal gelesener Datensatz bleibt innerhalb derselben Transaktion beim erneuten Lesen unverändert, d.h. Nonrepeatable Read ist ausgeschlossen. Das Phantomproblem kann jedoch weiterhin auftreten, wenn eine parallele Transaktion Datensätze einfügt oder löscht, die ein Selektionsprädikat erfüllen, das sie zuvor nicht erfüllten. REPEATABLE READ ist der Default in MySQL/MariaDB. Der Schutz wird typischerweise über Sperren oder Snapshots realisiert.

REPEATABLE READ verhindert Dirty Read und Nonrepeatable Read, aber nicht das Phantomproblem.

SERIALIZABLE

Höchste Isolationsstufe. Garantiert Serialisierbarkeit: das Ergebnis der parallelen Ausführung entspricht immer dem Ergebnis einer seriellen Ausführung der Transaktionen. Die Transaktion sieht nur Änderungen, die zu Beginn der Transaktion bereits committet waren, plus die eigenen Änderungen. Alle vier Probleme (Dirty Read, Nonrepeatable Read, Phantom, Lost Update) werden verhindert. Preis: geringste Parallelität und potenziell viele Wartezeiten oder Rollbacks.

SERIALIZABLE verhindert alle Probleme, kostet aber Performance.

Die vier Nebenläufigkeitsprobleme

Slide 16 der DBI-Folien listet die vier Probleme im Mehrbenutzerbetrieb: (1) Dirty Read - Abhängigkeit von nicht freigegebenen Daten, (2) Nonrepeatable Read - inkonsistentes Lesen innerhalb derselben Transaktion, (3) Phantom-Problem - Berechnungen auf unvollständigen Daten durch neue Datensätze, (4) Lost Update - verlorengegangene Änderungen durch Überschreiben. Isolation Levels adressieren die ersten drei; Lost Update wird typischerweise durch Locking und SERIALIZABLE verhindert.

Vier Probleme, aufsteigend adressiert: Dirty, Nonrepeatable, Phantom, Lost Update.

SET TRANSACTION Syntax

In SQL wird der Isolation Level pro Transaktion konfiguriert. Die Syntax lautet: SET TRANSACTION [READ ONLY | READ WRITE] [ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}]. Beispiel: set transaction read write, isolation level serializable. Der Wechsel des Isolationslevels kann in bestimmten Anwendungsfällen den Systemdurchsatz erhöhen, z.B. durch höhere Parallelität reiner Lesetransaktionen.

SET TRANSACTION ISOLATION LEVEL steuert die Balance zwischen Konsistenz und Durchsatz.

Übersichtstabelle Level vs Problem

Merktabelle: READ UNCOMMITTED erlaubt alle drei Probleme (Dirty, Nonrepeatable, Phantom). READ COMMITTED verhindert Dirty Read, erlaubt Nonrepeatable und Phantom. REPEATABLE READ verhindert Dirty Read und Nonrepeatable Read, erlaubt Phantom. SERIALIZABLE verhindert alle drei. Diese Tabelle ist der zentrale Merkstoff für die Klausur und wird bei Slide 16/17 direkt abgefragt.

Von unten nach oben wird nacheinander Dirty, dann Nonrepeatable, dann Phantom verboten.

Motivation - Warum überhaupt Levels

Volle Serialisierbarkeit ist teuer: viele Sperren, wenig Parallelität, potenzielle Deadlocks. In vielen Anwendungen (z.B. Reports, Statistiken, Dashboards) ist ein bisschen Inkonsistenz akzeptabel, wenn dafür die Performance steigt. Deshalb bietet der SQL-Standard eine Aufweichung der ACID-I-Bedingung an. Die Wahl des Levels ist eine bewusste Abwägung zwischen Korrektheit und Durchsatz.

Isolation Levels sind ein bewusster Trade-off zwischen Konsistenz und Performance.

Wichtige Details

Beispiele

Dirty Read - verhindert ab READ COMMITTED

T2 liest den geänderten A-Wert von T1, obwohl T1 später noch mit abort zurückrollt. T2 rechnet dann mit einem ungültigen Wert weiter und schreibt B - Inkonsistenz. Nur READ UNCOMMITTED erlaubt dieses Szenario.

T1: read(A,x); x:=x+100; write(x,A);
T2:                                 read(A,x); read(B,y); y:=y+x; write(y,B); commit;
T1: abort;

=> Ergebnis: T2 hat mit noch nicht committetem Wert gerechnet.

Nonrepeatable Read - verhindert ab REPEATABLE READ

T2 liest denselben Datensatz zweimal, während T1 dazwischen ein UPDATE committet. Damit sieht T2 in derselben Transaktion zwei unterschiedliche Werte für dasselbe Konto. Verhindert durch REPEATABLE READ oder SERIALIZABLE.

T2: SELECT guthaben FROM Konten WHERE iban=4711;   -- liefert 100
T1: UPDATE Konten SET guthaben = guthaben+100 WHERE iban=4711; COMMIT;
T2: SELECT guthaben FROM Konten WHERE iban=4711;   -- liefert 200

=> Zwei unterschiedliche Ergebnisse in einer Transaktion.

Phantom-Problem - nur durch SERIALIZABLE verhindert

T1 zählt Mitarbeiter, T2 fügt einen neuen Mitarbeiter ein und committet. T1 rechnet dann mit einem Betrag pro Mitarbeiter, der auf der alten Zählung basiert, aber der neue Meier bekommt trotzdem seinen Anteil. Nur SERIALIZABLE verhindert dieses Phantom.

T1: SELECT COUNT(*) INTO X FROM Mitarbeiter;   -- X = 10
T2: INSERT INTO Mitarbeiter VALUES ('Meier',50000,...); COMMIT;
T1: UPDATE Mitarbeiter SET Gehalt = Gehalt + 10000/X;   -- rechnet mit X=10, aber es sind jetzt 11
T1: COMMIT;

=> Inkonsistente Aufteilung des Gehaltsbudgets.

Lost Update - Lösung durch SERIALIZABLE bzw. Sperren

T1 und T2 lesen A=10, addieren jeweils einen Wert und schreiben zurück. Die Änderung von T1 wird durch T2 direkt überschrieben und geht verloren.

T1: read(A,x)         -- x=10
T2: read(A,x)         -- x=10
T1: x:=x+5
T2: x:=x+1
T1: write(x,A)        -- A=15
T2: write(x,A)        -- A=11 (T1s +5 verloren!)

=> +5 von T1 ist verschwunden.

Grafik: Level vs Problem

Isolation Level Dirty Read Nonrepeatable Read Phantom READ UNCOMMITTED ✗ ✗ ✗ READ COMMITTED ✓ ✗ ✗ REPEATABLE READ ✓ ✓ ✗ SERIALIZABLE ✓ ✓ ✓

Rot = Problem möglich | Grün = Problem verhindert | Von oben nach unten: wachsende Konsistenz, sinkende Parallelität

FAQ

Welches Isolation Level verhindert Dirty Reads?
Ab READ COMMITTED wird der Dirty Read verhindert. Also alle Level ausser READ UNCOMMITTED garantieren, dass nur committete Werte gelesen werden.
Warum verhindert REPEATABLE READ nicht das Phantomproblem?
REPEATABLE READ schützt nur bereits gelesene Datensätze vor Änderungen. Neue Datensätze, die durch INSERT einer parallelen Transaktion entstehen und ein Selektionsprädikat erfüllen, werden nicht verhindert. Erst SERIALIZABLE sperrt oder isoliert auch den Wertebereich.
Was ist der Default-Isolationslevel in MySQL/MariaDB?
REPEATABLE READ ist der Default in MySQL und MariaDB. Oracle und PostgreSQL verwenden dagegen standardmäßig READ COMMITTED.
Warum ist bei READ UNCOMMITTED der Default READ ONLY?
Weil bei Schreiboperationen ohne Isolation das Risiko für Inkonsistenzen durch Dirty Reads und Dirty Writes sehr hoch wäre. Der Modus ist praktisch nur für statistische Übersichten sinnvoll.
Wie stelle ich den Isolationslevel in SQL ein?
Mit dem Befehl SET TRANSACTION ISOLATION LEVEL <level>, z.B. SET TRANSACTION READ WRITE, ISOLATION LEVEL SERIALIZABLE. Der Level kann pro Transaktion gesetzt werden.
Verhindert SERIALIZABLE auch Lost Updates?
Ja. SERIALIZABLE garantiert eine serialisierbare Ausführung, damit können sich zwei Transaktionen ihre Schreiboperationen nicht gegenseitig überschreiben. Lost Update wird ebenfalls ausgeschlossen.
Was ist der Preis von SERIALIZABLE?
Geringere Parallelität, mehr Wartezeiten und potenziell Deadlocks oder Serialization-Errors mit Rollback. Der Durchsatz sinkt gegenüber niedrigeren Isolationsstufen.
Warum sind Isolation Levels eine Aufweichung von ACID?
Weil die volle I-Bedingung Serialisierbarkeit fordert. Alle Level unterhalb von SERIALIZABLE erlauben bewusst gewisse Inkonsistenzen, um bessere Performance zu erreichen.

Prüfungsfragen

Fortschritt
0 von 12