← Dashboard

Kapitel 10 · Datenbanken

Isolation Levels

Zusammenfassung

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

Kernkonzepte

READ UNCOMMITTED

Schwaechste Stufe der Isolation. Eine Transaktion darf auch noch nicht committete (also potenziell zurueckgerollte) Daten anderer Transaktionen lesen. Damit sind Dirty Reads erlaubt und alle darauf folgenden Probleme moeglich. Default ist hier read only, weil das Risiko fuer Inkonsistenzen durch dirty reads und writes sonst zu hoch waere. Typischer Anwendungsfall: statistische Lese-Transaktionen, bei denen nur ein ungefaehrer Ueberblick benoetigt wird und keine korrekten Werte gefordert sind.

READ UNCOMMITTED verhindert nichts. Es sieht sogar schmutzige, nicht bestaetigte Werte.

READ COMMITTED

Es duerfen nur endgueltig geschriebene, also committete Werte gelesen werden. Damit wird der Dirty Read verhindert. Nonrepeatable Read ist jedoch weiterhin moeglich: Wenn dieselbe Transaktion zweimal denselben Datensatz liest, kann zwischen den beiden Lesevorgaengen eine andere Transaktion den Wert veraendert und committet haben. Auch Phantom-Reads sind moeglich. Dies ist die Standardstufe vieler DBMS wie Oracle und PostgreSQL.

READ COMMITTED verhindert nur Dirty Read. Nonrepeatable Read und Phantom bleiben moeglich.

REPEATABLE READ

Ein einmal gelesener Datensatz bleibt innerhalb derselben Transaktion beim erneuten Lesen unveraendert, d.h. Nonrepeatable Read ist ausgeschlossen. Das Phantomproblem kann jedoch weiterhin auftreten, wenn eine parallele Transaktion Datensaetze einfuegt oder loescht, die ein Selektionspraedikat erfuellen, das sie zuvor nicht erfuellten. REPEATABLE READ ist der Default in MySQL/MariaDB. Der Schutz wird typischerweise ueber Sperren oder Snapshots realisiert.

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

SERIALIZABLE

Hoechste Isolationsstufe. Garantiert Serialisierbarkeit: das Ergebnis der parallelen Ausfuehrung entspricht immer dem Ergebnis einer seriellen Ausfuehrung der Transaktionen. Die Transaktion sieht nur Aenderungen, die zu Beginn der Transaktion bereits committet waren, plus die eigenen Aenderungen. Alle vier Probleme (Dirty Read, Nonrepeatable Read, Phantom, Lost Update) werden verhindert. Preis: geringste Parallelitaet und potenziell viele Wartezeiten oder Rollbacks.

SERIALIZABLE verhindert alle Probleme, kostet aber Performance.

Die vier Nebenlaeufigkeitsprobleme

Slide 16 der DBI-Folien listet die vier Probleme im Mehrbenutzerbetrieb: (1) Dirty Read - Abhaengigkeit von nicht freigegebenen Daten, (2) Nonrepeatable Read - inkonsistentes Lesen innerhalb derselben Transaktion, (3) Phantom-Problem - Berechnungen auf unvollstaendigen Daten durch neue Datensaetze, (4) Lost Update - verlorengegangene Aenderungen durch Ueberschreiben. 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 Anwendungsfaellen den Systemdurchsatz erhoehen, z.B. durch hoehere Parallelitaet reiner Lesetransaktionen.

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

Uebersichtstabelle 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 fuer die Klausur und wird bei Slide 16/17 direkt abgefragt.

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

Motivation - Warum ueberhaupt Levels

Volle Serialisierbarkeit ist teuer: viele Sperren, wenig Parallelitaet, potenzielle Deadlocks. In vielen Anwendungen (z.B. Reports, Statistiken, Dashboards) ist ein bisschen Inkonsistenz akzeptabel, wenn dafuer die Performance steigt. Deshalb bietet der SQL-Standard eine Aufweichung der ACID-I-Bedingung an. Die Wahl des Levels ist eine bewusste Abwaegung 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 geaenderten A-Wert von T1, obwohl T1 spaeter noch mit abort zurueckrollt. T2 rechnet dann mit einem ungueltigen 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, waehrend T1 dazwischen ein UPDATE committet. Damit sieht T2 in derselben Transaktion zwei unterschiedliche Werte fuer 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 zaehlt Mitarbeiter, T2 fuegt einen neuen Mitarbeiter ein und committet. T1 rechnet dann mit einem Betrag pro Mitarbeiter, der auf der alten Zaehlung 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 - Loesung durch SERIALIZABLE bzw. Sperren

T1 und T2 lesen A=10, addieren jeweils einen Wert und schreiben zurueck. Die Aenderung von T1 wird durch T2 direkt ueberschrieben 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 moeglich | Gruen = Problem verhindert | Von oben nach unten: wachsende Konsistenz, sinkende Parallelitaet

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 schuetzt nur bereits gelesene Datensaetze vor Aenderungen. Neue Datensaetze, die durch INSERT einer parallelen Transaktion entstehen und ein Selektionspraedikat erfuellen, 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 standardmaessig READ COMMITTED.
Warum ist bei READ UNCOMMITTED der Default READ ONLY?
Weil bei Schreiboperationen ohne Isolation das Risiko fuer Inkonsistenzen durch Dirty Reads und Dirty Writes sehr hoch waere. Der Modus ist praktisch nur fuer statistische Uebersichten 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 Ausfuehrung, damit koennen sich zwei Transaktionen ihre Schreiboperationen nicht gegenseitig ueberschreiben. Lost Update wird ebenfalls ausgeschlossen.
Was ist der Preis von SERIALIZABLE?
Geringere Parallelitaet, mehr Wartezeiten und potenziell Deadlocks oder Serialization-Errors mit Rollback. Der Durchsatz sinkt gegenueber 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.

Pruefungsfragen

Fortschritt
0 von 12