SemaTrain Logo Ein Fachportal von SemaTrain

Transaktionen & ACID

Damit Daten konsistent bleiben: Sie lernen Transaktionen (BEGIN/COMMIT/ROLLBACK), verstehen ACID und erkennen typische Fehlerbilder (Teil-Updates, Race Conditions).

Hinweis: Syntax/Details variieren je nach DB (SQLite/PostgreSQL/MySQL). Die Konzepte bleiben gleich.

SQL Grundlagen Schulung – Kursbezug

Dieses Kapitel ist Teil des Lernpfads zur SQL Grundlagen Schulung. Termine & Buchung laufen über SemaTrain.de.

Safety „Alles oder nichts“ mit BEGIN/COMMIT/ROLLBACK.

Worum geht’s?

Schema-Kontext (Lernpfad)

Wir bleiben in den Beispielen nah am Schulungs-Use-Case „Termine buchen“.

Schema-Reminder (SQL)
-- kurse(kurs_id, kurs_name)
-- trainer(trainer_id, name)
-- termine(termin_id, kurs_id, trainer_id, start_datum, ort, format, dauer_tage, preis_eur)
-- (Ergänzung in diesem Kapitel)
-- termin_kapazitaet(termin_id, freie_plaetze)
-- buchungen(buchung_id, termin_id, name, email, created_at)

Lehr-/Lernziele

ACID in 60 Sekunden

Praxisbild: „Buchung“ = Platz reservieren + Buchung schreiben. Ohne Transaktion kann eins durchgehen und das andere fehlschlagen → Inkonsistenz.

Transaktion: BEGIN / COMMIT / ROLLBACK

Wichtig (Praxis): Nach dem „Platz reservieren“-UPDATE muss geprüft werden, ob wirklich 1 Zeile geändert wurde (sonst würde man trotz 0 Plätzen trotzdem ein INSERT machen → Overbooking).

Transaktions-Grundmuster (Buchung) (SQL)
-- Beispiel: Termin buchen (alles oder nichts)
-- Voraussetzung: termin_kapazitaet(termin_id, freie_plaetze) und buchungen(...)
BEGIN;

-- 1) Platz reservieren (nur wenn noch frei)
UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze - 1
WHERE termin_id = 2
  AND freie_plaetze > 0;

-- 2) Praxis-Safety: Prüfe "affected rows" (DB/Tool abhängig)
--    Wenn 0 Zeilen geändert wurden -> KEIN Platz frei -> ROLLBACK.
--    Wenn 1 Zeile geändert wurde -> weiter mit INSERT und COMMIT.

-- 3) Buchung schreiben (nur wenn Reservierung erfolgreich war)
INSERT INTO buchungen (termin_id, name, email)
VALUES (2, 'Max Mustermann', 'max@example.com');

COMMIT;   -- übernimmt beide Schritte (wenn alles ok)
-- ROLLBACK; -- Alternative: alles verwerfen
Affected Rows prüfen (SQLite / MySQL / PostgreSQL)
Affected Rows / RETURNING (Beispiele) (SQL)
-- SQLite: changes() liefert die Anzahl der geänderten Zeilen des letzten Statements
BEGIN;

UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze - 1
WHERE termin_id = 2 AND freie_plaetze > 0;

-- Wenn changes() = 0 -> kein Platz -> ROLLBACK
SELECT changes() AS affected_rows;

-- (Wenn affected_rows = 1) dann:
INSERT INTO buchungen(termin_id, name, email)
VALUES (2, 'Max Mustermann', 'max@example.com');

COMMIT;

-- ----------------------------------------------------------

-- MySQL: ROW_COUNT() nach UPDATE prüfen
START TRANSACTION;

UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze - 1
WHERE termin_id = 2 AND freie_plaetze > 0;

SELECT ROW_COUNT() AS affected_rows;

-- App/Client entscheidet:
-- affected_rows = 1 -> INSERT + COMMIT
-- affected_rows = 0 -> ROLLBACK

-- ----------------------------------------------------------

-- PostgreSQL: "UPDATE ... RETURNING" und daraus INSERT machen (robust + elegant)
BEGIN;

WITH reserved AS (
  UPDATE termin_kapazitaet
  SET freie_plaetze = freie_plaetze - 1
  WHERE termin_id = 2 AND freie_plaetze > 0
  RETURNING termin_id
)
INSERT INTO buchungen (termin_id, name, email)
SELECT termin_id, 'Max Mustermann', 'max@example.com'
FROM reserved;

-- Wenn kein Platz frei: reserved ist leer -> INSERT fügt 0 Zeilen ein -> App kann danach COMMIT/ROLLBACK entscheiden
COMMIT;

Merksatz: UPDATE reserviert → prüfen → erst dann INSERT. In echten Apps passiert die Entscheidung (COMMIT/ROLLBACK) im Code anhand der betroffenen Zeilen.

Typischer Fehler: COMMIT vergessen

Manche Tools starten Transaktionen automatisch oder haben Autocommit aus. Ohne COMMIT bleiben Änderungen ggf. „pending“ und sind nach dem Schließen weg (oder blockieren Locks – DB/Setting abhängig).

Autocommit/Commit (Hinweis) (SQL)
-- In vielen Clients gibt es einen Autocommit-Schalter.
-- Faustregel: Wenn du BEGIN siehst, brauchst du am Ende COMMIT oder ROLLBACK.

BEGIN;
UPDATE termine
SET ort = 'Hamburg'
WHERE termin_id = 42;

-- COMMIT;   -- sonst ggf. nicht dauerhaft
-- ROLLBACK; -- falls es doch falsch war

SAVEPOINT (Teil-Rollback)

Wenn Sie innerhalb einer Transaktion einen Teil zurückdrehen wollen (z. B. „Zahlung“ scheitert).

SAVEPOINT / ROLLBACK TO (Buchung) (SQL)
BEGIN;

-- Platz reservieren
UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze - 1
WHERE termin_id = 2
  AND freie_plaetze > 0;

-- (Praxis) prüfen, ob reserviert wurde (affected rows). Wenn 0 -> ROLLBACK.

SAVEPOINT after_reserve;

-- Folgeschritt scheitert (Simulation: NOT NULL verletzt)
-- INSERT INTO buchungen(termin_id, name, email) VALUES (2, NULL, 'x@example.com'); -- würde fehlschlagen

-- Teil-Rollback: bis nach der Reservierung zurück
ROLLBACK TO after_reserve;

-- Kompensation: Platz wieder freigeben (innerhalb derselben TX)
UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze + 1
WHERE termin_id = 2;

COMMIT;

Isolation: Parallelität (Grundidee)

Isolation-Level steuern, was Transaktionen voneinander sehen dürfen. Details sind DB-spezifisch, aber die Problemklassen sind immer gleich.

Praktisches Muster: SELECT … FOR UPDATE (DB-abhängig)

PostgreSQL/MySQL unterstützen FOR UPDATE. SQLite arbeitet anders (Locking-Modell).

SELECT ... FOR UPDATE (Beispiel) (SQL)
-- Beispiel (PostgreSQL/MySQL): Zeile sperren, bevor man sie ändert
BEGIN;

SELECT freie_plaetze
FROM termin_kapazitaet
WHERE termin_id = 2
FOR UPDATE;

UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze - 1
WHERE termin_id = 2
  AND freie_plaetze > 0;

COMMIT;

Praxisaufgabe

  1. Lege zwei Tabellen an: termin_kapazitaet und buchungen (Minimalspalten wie unten).
  2. Buche einen Platz für einen Termin in einer Transaktion: reservieren + Buchung schreiben.
  3. Simuliere einen Fehler (z. B. Termin existiert nicht) und stelle sicher: ROLLBACK verhindert Teiländerungen.
  4. (Bonus) Nutze SAVEPOINT, um nur einen Teil zurückzurollen.
Lösungsvorschlag anzeigen
Lösung: Setup + Transaktion (Buchung, mit Safety-Check) (SQL)
-- Minimal-Setup (didaktisch klein)
-- Hinweis: Optional kann buchungen.termin_id als FOREIGN KEY auf termine(termin_id) referenzieren,
-- wenn die Tabelle termine existiert (FK ist hier nicht zwingend für das Transaktionsprinzip).

CREATE TABLE termin_kapazitaet (
  termin_id     INTEGER PRIMARY KEY,
  freie_plaetze INTEGER NOT NULL CHECK (freie_plaetze >= 0)
);

CREATE TABLE buchungen (
  buchung_id INTEGER PRIMARY KEY,
  termin_id  INTEGER NOT NULL,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP

  -- Optional (wenn termine existiert):
  -- ,FOREIGN KEY (termin_id) REFERENCES termine(termin_id)
);

-- Beispiel-Daten
INSERT INTO termin_kapazitaet(termin_id, freie_plaetze) VALUES (2, 1);

-- Buchung in einer Transaktion (mit Safety-Check)
BEGIN;

UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze - 1
WHERE termin_id = 2
  AND freie_plaetze > 0;

-- SQLite: changes() / MySQL: ROW_COUNT() / Postgres: UPDATE ... RETURNING
-- Wenn affected_rows = 0 -> kein Platz -> ROLLBACK.
SELECT changes() AS affected_rows; -- (SQLite-Beispiel)

-- Nur wenn affected_rows = 1:
INSERT INTO buchungen(termin_id, name, email)
VALUES (2, 'Max Mustermann', 'max@example.com');

COMMIT;

-- Bei Fehlern / kein Platz:
-- ROLLBACK;

Kurz-Takeaways

Quiz: Transaktionen & ACID

1. (LZ3) Was bedeutet „Atomicity“ in ACID?

2. (LZ1) Welche Aussage stimmt?

3. (LZ2) Wofür nutzt man SAVEPOINT?

4. (LZ4) Was ist ein typisches Risiko ohne Isolation/Locks bei parallelen Updates?

Praxisaufgabe

Mini-Projekt: Kursbuchung „alles-oder-nichts“ (Platz reservieren + Buchung schreiben)

Sie modellieren eine typische Praxisaktion: Eine Person bucht einen Termin. Dabei müssen zwei Schritte zusammenpassen: (1) Platz reservieren und (2) Buchung schreiben. Wenn einer scheitert, darf keine Teiländerung übrig bleiben.

Beitrag zu den Lehr-/Lernzielen: LZ1 (BEGIN/COMMIT/ROLLBACK), LZ2 (SAVEPOINT), LZ3 (ACID/Fehlerbilder), LZ4 (Isolation-Grundidee).

Stufe A – Minimalmodell

Stufe B – Transaktion „Buchen“

Stufe C – Bonus: SAVEPOINT („Folgeschritt“ scheitert)

Musterlösung anzeigen
Projektlösung: Kursbuchung (Transaktion + SAVEPOINT) (SQL)
-- ==========================================================
-- Stufe A – Minimalmodell
-- ==========================================================
CREATE TABLE termin_kapazitaet (
  termin_id     INTEGER PRIMARY KEY,
  freie_plaetze INTEGER NOT NULL CHECK (freie_plaetze >= 0)
);

CREATE TABLE buchungen (
  buchung_id INTEGER PRIMARY KEY,
  termin_id  INTEGER NOT NULL,
  name       TEXT NOT NULL,
  email      TEXT NOT NULL,
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
  -- Optional (wenn termine existiert):
  -- ,FOREIGN KEY (termin_id) REFERENCES termine(termin_id)
);

INSERT INTO termin_kapazitaet(termin_id, freie_plaetze) VALUES (2, 1);

-- ==========================================================
-- Stufe B – Buchung als Transaktion („alles oder nichts“)
-- ==========================================================
BEGIN;

UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze - 1
WHERE termin_id = 2
  AND freie_plaetze > 0;

-- Safety: Prüfe affected rows (SQLite-Beispiel)
SELECT changes() AS affected_rows;

-- Nur wenn affected_rows = 1:
INSERT INTO buchungen(termin_id, name, email)
VALUES (2, 'Max Mustermann', 'max@example.com');

COMMIT;

-- Wenn kein Platz / Fehler:
-- ROLLBACK;

-- ==========================================================
-- Stufe C – SAVEPOINT: Folgeschritt scheitert -> Teil-Rollback
-- ==========================================================
BEGIN;

UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze - 1
WHERE termin_id = 2
  AND freie_plaetze > 0;

-- (Praxis) check affected rows, sonst direkt ROLLBACK.

SAVEPOINT after_reserve;

-- Folgeschritt scheitert (Simulation):
-- INSERT INTO buchungen(termin_id, name, email) VALUES (2, NULL, 'x@example.com'); -- NOT NULL -> Fehler

ROLLBACK TO after_reserve;

-- Platz zurückgeben (Kompensation)
UPDATE termin_kapazitaet
SET freie_plaetze = freie_plaetze + 1
WHERE termin_id = 2;

COMMIT;

-- Verify
SELECT * FROM termin_kapazitaet WHERE termin_id = 2;
SELECT * FROM buchungen WHERE termin_id = 2 ORDER BY created_at DESC;

Praxishinweis: In echten Anwendungen entscheidet die App anhand von affected rows (ob das UPDATE 1 Zeile geändert hat), ob COMMIT oder ROLLBACK ausgeführt wird.