Transaktionsverwaltung in SQL

Zusammenfassung

⭐ Laut Dozent: Transaktionsverwaltung in SQL ist Schwerpunkt — BEGIN/COMMIT/ROLLBACK und SELECT FOR UPDATE sicher beherrschen.

Die Transaktionsverwaltung in SQL kapselt eine Folge von SQL-Operationen als atomare Einheit, die die Datenbank von einem konsistenten Zustand in einen anderen konsistenten Zustand überführt und dabei das ACID-Prinzip garantiert. Die zentralen TCL-Befehle sind START/BEGIN TRANSACTION, COMMIT (WORK) für erfolgreichen Abschluss, ROLLBACK/ABORT (WORK) für Rücksetzung sowie SAVEPOINT für partielle Rücksetzung. Über SET TRANSACTION lassen sich Zugriffsmodus (READ ONLY / READ WRITE) und Isolationslevel (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) pro Transaktion einstellen, um Anomalien wie Dirty Read, Nonrepeatable Read, Phantom und Lost Update kontrolliert zuzulassen oder auszuschließen.

Kernkonzepte

Transaktion und ACID-Prinzip

Eine Transaktion ist eine Folge von Operationen, die die Datenbank von einem konsistenten in einen konsistenten Zustand überführt. Sie muss das ACID-Prinzip einhalten: Atomicity (Alles-oder-Nichts), Consistency (Konsistenzerhaltung), Isolation (jede Transaktion arbeitet scheinbar allein) und Durability (dauerhafte Speicherung nach Commit). Transaktionen kapseln Nutzer im Mehrbenutzerbetrieb voneinander ab. Ohne Transaktionen könnten parallele Operationen sich gegenseitig stören.

A-C-I-D: Alles oder nichts, Konsistent, Isoliert, Dauerhaft.

BEGIN / START TRANSACTION (BOT)

Der Befehl BEGIN TRANSACTION oder START TRANSACTION kennzeichnet den Beginn einer Transaktion (Begin Of Transaction, BOT). Ab diesem Punkt werden alle nachfolgenden SQL-Befehle als atomare Gesamtheit behandelt. In vielen DBMS startet auch implizit eine neue Transaktion nach jedem COMMIT oder ROLLBACK. Die konkrete Syntax hängt vom DBMS ab, das Schlüsselwort TRANSACTION ist teilweise optional.

BOT öffnet die Klammer, COMMIT oder ROLLBACK schließt sie.

COMMIT (WORK)

COMMIT signalisiert den erfolgreichen Abschluss einer Transaktion. Alle Änderungen der Transaktion werden festgeschrieben (persistent gemacht) und sind für andere Transaktionen sichtbar. Das Schlüsselwort WORK ist meist optional. Nach einem COMMIT ist der Zustand dauerhaft (Durability). Das DBMS prüft dabei Konsistenzbedingungen.

COMMIT = festgeschrieben und dauerhaft.

ROLLBACK / ABORT (WORK)

ROLLBACK oder ABORT bricht die Transaktion ab und setzt die Datenbasis auf den Zustand vor Beginn der Transaktion zurück. Anders als COMMIT muss das DBMS die erfolgreiche Ausführung eines ROLLBACK immer garantieren können. Es realisiert damit die Atomicity-Eigenschaft. Ein ROLLBACK kann durch den Nutzer, das DBMS (z.B. bei Deadlock) oder einen Systemfehler ausgelöst werden.

ROLLBACK = Zeitreise zurück zum Startpunkt der Transaktion.

SAVEPOINT

SAVEPOINT definiert innerhalb einer aktiven Transaktion einen Sicherungspunkt, auf den man teilweise zurücksetzen kann, ohne die gesamte Transaktion abzubrechen. Alle Änderungen bis zum Savepoint müssen vom DBMS gemerkt werden, sind aber noch nicht endgültig festgeschrieben. Ein ROLLBACK TO SAVEPOINT verwirft nur Änderungen seit dem Savepoint. Ein finales COMMIT oder ROLLBACK schließt die Transaktion trotzdem ab.

SAVEPOINT = Zwischenstand speichern, Teil-Rollback möglich.

SET TRANSACTION und Isolationslevel

Mit SET TRANSACTION lassen sich Zugriffsmodus (READ ONLY / READ WRITE) und Isolationslevel setzen. Es gibt vier Level: READ UNCOMMITTED (schwächste Stufe, Dirty Reads erlaubt), READ COMMITTED (nur committete Werte lesbar), REPEATABLE READ (kein nonrepeatable read, aber Phantoms möglich) und SERIALIZABLE (volle Serialisierbarkeit). Ein niedrigeres Level erhöht Durchsatz auf Kosten der Konsistenz. Der Isolationslevel kann pro Transaktion angegeben werden.

SET TRANSACTION = Isolation stufenweise aufweichen für mehr Parallelität.

Mehrbenutzer-Anomalien

Ohne ausreichende Isolation treten vier klassische Probleme auf: Dirty Read (Lesen nicht-committeter Daten), Nonrepeatable Read (gleiches SELECT liefert unterschiedliche Ergebnisse), Phantom Read (neue Tupel erscheinen bei erneuter Anfrage) und Lost Update (Überschreiben paralleler Änderungen). Diese Anomalien führen zu inkonsistenten Ergebnissen. Der gewählte Isolationslevel bestimmt, welche Anomalien noch auftreten können.

Vier Anomalien: Dirty, Nonrepeatable, Phantom, Lost Update.

Serialisierbarkeit und Historie

Eine Historie (Transaction Schedule) ist die sequenzielle Anordnung aller Aktionen paralleler Transaktionen. Eine serielle Historie führt Transaktionen hintereinander aus. Eine serialisierbare Historie ist zwar verzahnt, hat aber ein Ergebnis, das identisch zu irgendeiner seriellen Historie wäre. Ziel des DBMS ist es, korrekte (serialisierbare) parallele Pläne zu finden, um Effizienz und Konsistenz zu vereinen.

Korrekt = Serialisierbar: verzahnt, aber ergebnisgleich zu einer seriellen Ausführung.

Wichtige Details

TCL (Transaction Control Language) ist der SQL-Teilbereich für Transaktionsbefehle, neben DML, DDL und DCL.
Zentrale TCL-Schlüsselwörter: BEGIN/START TRANSACTION, COMMIT [WORK], ROLLBACK [WORK], ABORT, SAVEPOINT, SET TRANSACTION.
COMMIT WORK und ROLLBACK WORK: das Schlüsselwort WORK ist meist optional.
Vier Isolationslevel (aufsteigend): READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.
Anomalie-Matrix: Read Uncommitted erlaubt alle vier Anomalien; Read Committed verhindert Dirty Read; Repeatable Read verhindert zusätzlich Nonrepeatable Read und Lost Update; Serializable verhindert alles.
Zustandsübergänge einer Transaktion: potentiell, aktiv, wartend, abgeschlossen, persistent, gescheitert, aufgegeben, wiederholbar.
Erweiterte Operationen: define savepoint (Sicherungspunkt setzen) und backup transaction (auf jüngsten Savepoint zurücksetzen).
Bei Systemabsturz muss die R1-Recovery die Konsistenz wiederherstellen (Undo laufender Transaktionen, Redo committeter Transaktionen).
Syntax und Funktionalität der Transaktionsbefehle sind stark DBMS-abhängig (Oracle, MySQL, PostgreSQL etc.).
Default-Isolationslevel: MySQL/MariaDB nutzen standardmäßig REPEATABLE READ; Oracle nutzt READ COMMITTED.

Beispiele

Klassische Transaktion: Geldüberweisung

Überweisung von 50 Euro von Konto A zu Konto B als atomare Einheit. Fällt ein Schritt aus, muss die gesamte Transaktion zurückgerollt werden.

START TRANSACTION;
  read(A,a);
  a := a - 50;
  write(A,a);
  read(B,b);
  b := b + 50;
  write(B,b);
COMMIT WORK;

Dirty-Read-Ablauf erkennen

T2 liest einen Wert, den T1 geändert aber noch nicht committet hat. Rollt T1 später zurück, arbeitet T2 mit ungültigen Daten weiter.

T1: read(A,x)
T1: x := x+100
T1: write(x,A)      -- nicht committet!
T2: read(A,x)       -- liest dirty!
T2: read(B,y)
T2: y := y+x
T2: write(y,B)
T2: commit
T1: abort           -- Inkonsistenz: B basiert auf verworfenem A

Lost Update erkennen

T1 und T2 lesen A=10 parallel. Beide berechnen neuen Wert und schreiben zurück. Die Änderung von T1 geht verloren, weil T2 direkt überschreibt.

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

SET TRANSACTION mit Isolationslevel

Explizite Konfiguration einer Transaktion als lese/schreibend mit höchstem Isolationslevel serializable, um alle Anomalien zu vermeiden.

SET TRANSACTION READ WRITE,
  ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
  INSERT INTO Professoren VALUES (2141,'Meitner','C4',205);
  INSERT INTO Vorlesungen VALUES (5275,'Kernphysik',3,2141);
COMMIT WORK;

Grafik: Verzahnte Transaktionen und Isolation

FAQ

Was passiert, wenn eine Transaktion ohne explizites COMMIT endet und die Verbindung abbricht?

Das DBMS führt automatisch ein ROLLBACK durch, denn ohne COMMIT gilt die Änderung nicht als abgeschlossen und die Atomicity wird durch Rückrollen gewahrt.

Worin unterscheiden sich ROLLBACK und Savepoint-Rollback?

Ein normales ROLLBACK verwirft die gesamte Transaktion. Ein ROLLBACK TO SAVEPOINT verwirft nur die Änderungen seit dem Savepoint; die Transaktion bleibt aktiv und kann weiterarbeiten oder final committen.

Warum ist READ UNCOMMITTED gefährlich, aber trotzdem manchmal sinnvoll?

Es erlaubt Dirty Reads und liefert eventuell falsche Werte. Für rein statistische Auswertungen, bei denen ein grober Überblick reicht, ist es sinnvoll, weil es die Parallelität erhöht.

Welche Anomalie verhindert Repeatable Read nicht?

Repeatable Read verhindert Dirty Read und Nonrepeatable Read, aber nicht das Phantom-Problem: neue Tupel können zwischen zwei Selects erscheinen.

Kann ein SELECT allein eine Transaktion sein?

Ja. Jede SQL-Anweisung läuft in einer Transaktion. Bei Autocommit ist es eine implizite Ein-Anweisungs-Transaktion; explizit kann man auch nur lesende Transaktionen mit SET TRANSACTION READ ONLY starten.

Was bedeutet Serialisierbarkeit konkret?

Eine parallele Historie ist serialisierbar, wenn es eine serielle Ausführungsreihenfolge der beteiligten Transaktionen gibt, die zum gleichen Datenbank-Endzustand führt.

Was ist der Unterschied zwischen ABORT und ROLLBACK?

In der SQL-Praxis sind beide Synonyme und bewirken das Zurücksetzen der Transaktion. ABORT wird oft im theoretischen Kontext benutzt, ROLLBACK ist das offizielle SQL-Schlüsselwort.

Warum ist ein niedriger Isolationslevel manchmal die bessere Wahl?

Ein niedriger Isolationslevel wie READ COMMITTED erlaubt höhere Parallelität und besseren Durchsatz, weil weniger Sperren gehalten werden. Für nicht-kritische Berichte ist das oft akzeptabel.

Prüfungsfragen

0 von 12 Fragen beantwortet