Transaktionsverwaltung in SQL

Zusammenfassung

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 ueberfuehrt und dabei das ACID-Prinzip garantiert. Die zentralen TCL-Befehle sind START/BEGIN TRANSACTION, COMMIT (WORK) fuer erfolgreichen Abschluss, ROLLBACK/ABORT (WORK) fuer Ruecksetzung sowie SAVEPOINT fuer partielle Ruecksetzung. Ueber 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 auszuschliessen.

Kernkonzepte

Transaktion und ACID-Prinzip

Eine Transaktion ist eine Folge von Operationen, die die Datenbank von einem konsistenten in einen konsistenten Zustand ueberfuehrt. 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 koennten parallele Operationen sich gegenseitig stoeren.

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 haengt vom DBMS ab, das Schluesselwort TRANSACTION ist teilweise optional.

BOT oeffnet die Klammer, COMMIT oder ROLLBACK schliesst sie.

COMMIT (WORK)

COMMIT signalisiert den erfolgreichen Abschluss einer Transaktion. Alle Aenderungen der Transaktion werden festgeschrieben (persistent gemacht) und sind fuer andere Transaktionen sichtbar. Das Schluesselwort WORK ist meist optional. Nach einem COMMIT ist der Zustand dauerhaft (Durability). Das DBMS prueft 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 zurueck. Anders als COMMIT muss das DBMS die erfolgreiche Ausfuehrung eines ROLLBACK immer garantieren koennen. Es realisiert damit die Atomicity-Eigenschaft. Ein ROLLBACK kann durch den Nutzer, das DBMS (z.B. bei Deadlock) oder einen Systemfehler ausgeloest werden.

ROLLBACK = Zeitreise zurueck zum Startpunkt der Transaktion.

SAVEPOINT

SAVEPOINT definiert innerhalb einer aktiven Transaktion einen Sicherungspunkt, auf den man teilweise zuruecksetzen kann, ohne die gesamte Transaktion abzubrechen. Alle Aenderungen bis zum Savepoint muessen vom DBMS gemerkt werden, sind aber noch nicht endgueltig festgeschrieben. Ein ROLLBACK TO SAVEPOINT verwirft nur Aenderungen seit dem Savepoint. Ein finales COMMIT oder ROLLBACK schliesst die Transaktion trotzdem ab.

SAVEPOINT = Zwischenstand speichern, Teil-Rollback moeglich.

SET TRANSACTION und Isolationslevel

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

SET TRANSACTION = Isolation stufenweise aufweichen fuer mehr Parallelitaet.

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 (Ueberschreiben paralleler Aenderungen). Diese Anomalien fuehren zu inkonsistenten Ergebnissen. Der gewaehlte Isolationslevel bestimmt, welche Anomalien noch auftreten koennen.

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 fuehrt Transaktionen hintereinander aus. Eine serialisierbare Historie ist zwar verzahnt, hat aber ein Ergebnis, das identisch zu irgendeiner seriellen Historie waere. Ziel des DBMS ist es, korrekte (serialisierbare) parallele Plaene zu finden, um Effizienz und Konsistenz zu vereinen.

Korrekt = Serialisierbar: verzahnt, aber ergebnisgleich zu einer seriellen Ausfuehrung.

Wichtige Details

TCL (Transaction Control Language) ist der SQL-Teilbereich fuer Transaktionsbefehle, neben DML, DDL und DCL.
Zentrale TCL-Schluesselwoerter: BEGIN/START TRANSACTION, COMMIT [WORK], ROLLBACK [WORK], ABORT, SAVEPOINT, SET TRANSACTION.
COMMIT WORK und ROLLBACK WORK: das Schluesselwort 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 zusaetzlich Nonrepeatable Read und Lost Update; Serializable verhindert alles.
Zustandsuebergaenge einer Transaktion: potentiell, aktiv, wartend, abgeschlossen, persistent, gescheitert, aufgegeben, wiederholbar.
Erweiterte Operationen: define savepoint (Sicherungspunkt setzen) und backup transaction (auf juengsten Savepoint zuruecksetzen).
Bei Systemabsturz muss die R1-Recovery die Konsistenz wiederherstellen (Undo laufender Transaktionen, Redo committeter Transaktionen).
Syntax und Funktionalitaet der Transaktionsbefehle sind stark DBMS-abhaengig (Oracle, MySQL, PostgreSQL etc.).
Default-Isolationslevel: MySQL/MariaDB nutzen standardmaessig REPEATABLE READ; Oracle nutzt READ COMMITTED.

Beispiele

Klassische Transaktion: Geldueberweisung

Ueberweisung von 50 Euro von Konto A zu Konto B als atomare Einheit. Faellt ein Schritt aus, muss die gesamte Transaktion zurueckgerollt 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 geaendert aber noch nicht committet hat. Rollt T1 spaeter zurueck, arbeitet T2 mit ungueltigen 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 zurueck. Die Aenderung von T1 geht verloren, weil T2 direkt ueberschreibt.

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 hoechstem 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 fuehrt automatisch ein ROLLBACK durch, denn ohne COMMIT gilt die Aenderung nicht als abgeschlossen und die Atomicity wird durch Rueckrollen gewahrt.

Worin unterscheiden sich ROLLBACK und Savepoint-Rollback?

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

Warum ist READ UNCOMMITTED gefaehrlich, aber trotzdem manchmal sinnvoll?

Es erlaubt Dirty Reads und liefert eventuell falsche Werte. Fuer rein statistische Auswertungen, bei denen ein grober Ueberblick reicht, ist es sinnvoll, weil es die Parallelitaet erhoeht.

Welche Anomalie verhindert Repeatable Read nicht?

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

Kann ein SELECT allein eine Transaktion sein?

Ja. Jede SQL-Anweisung laeuft in einer Transaktion. Bei Autocommit ist es eine impliziete 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 Ausfuehrungsreihenfolge der beteiligten Transaktionen gibt, die zum gleichen Datenbank-Endzustand fuehrt.

Was ist der Unterschied zwischen ABORT und ROLLBACK?

In der SQL-Praxis sind beide Synonyme und bewirken das Zuruecksetzen der Transaktion. ABORT wird oft im theoretischen Kontext benutzt, ROLLBACK ist das offizielle SQL-Schluesselwort.

Warum ist ein niedriger Isolationslevel manchmal die bessere Wahl?

Ein niedriger Isolationslevel wie READ COMMITTED erlaubt hoehere Parallelitaet und besseren Durchsatz, weil weniger Sperren gehalten werden. Fuer nicht-kritische Berichte ist das oft akzeptabel.

Pruefungsfragen

0 von 12 Fragen beantwortet