Was sind Datenbanktransaktionen?

• Transaktionen sind eine Struktur, um mehrere Operationen in einer Datenbank als eine atomare Einheit auszuführen, einschließlich Lesen, Schreiben, Aktualisieren und Löschen
• MySQL und Postgres steuern Transaktionen mit begin; und commit;; bei Fehlschlägen oder Fehlern werden Änderungen mit rollback; rückgängig gemacht
• Beide Datenbanken garantieren konsistente Lesevorgänge (consistent read), aber Postgres verwendet MVCC (Multi-Version Concurrency Control), während MySQL ein Undo Log verwendet
• Isolationsstufen (isolation levels) steuern Dateninterferenzen zwischen Transaktionen und werden in vier Stufen von Serializable bis Read Uncommitted unterteilt
• Postgres und MySQL behandeln gleichzeitige Schreibkonflikte auf unterschiedliche Weise: Postgres mit optimistischer Validierung, MySQL mit Sperren auf Zeilenebene (row-level locking)

---

Grundkonzept von Transaktionen

• Eine Transaktion ist eine Struktur, die mehrere SQL-Operationen in einer Datenbank zu einer atomaren Ausführungseinheit bündelt
  • Sie beginnt mit begin; und endet mit commit;, dazwischen können mehrere Queries ausgeführt werden
  • Zum Zeitpunkt von commit; werden alle Änderungen auf einmal angewendet
• Bei unerwarteten Ausfällen (Stromausfall, Festplattenfehler usw.) oder einer absichtlichen Abbruchaktion werden Änderungen mit rollback; zurückgesetzt
  • Postgres unterstützt die Wiederherstellung mit WAL (Write-Ahead Log)
• Während einer Transaktion geänderte Daten sind isoliert und in anderen Sessions nicht sichtbar
  • Bei rollback; werden alle Änderungen verworfen und die Datenbank in ihren ursprünglichen Zustand zurückversetzt

Konsistente Lesevorgänge (Consistent Reads)

• Eine Transaktion muss während ihrer Ausführung eine konsistente Datensicht aufrechterhalten, die nicht von externen Änderungen beeinflusst wird
• MySQL und Postgres unterstützen dies ab dem Modus REPEATABLE READ, setzen es aber unterschiedlich um
  • Postgres: Verwaltung der Versionen jeder Zeile über MVCC (Multi-Version Concurrency Control)
  • MySQL: Rekonstruktion früherer Versionen mithilfe des Undo Log

Multi-Version-Zeilenspeicherung in Postgres

• Bei jeder Aktualisierung einer Zeile wird eine neue Version erzeugt; die alte Version speichert die Transaktions-ID in xmax, die neue Version in xmin
• Bevor eine Transaktion committet wird, können andere Sessions die Änderungen nicht sehen
• Nach dem Commit wird die neue Version in der gesamten Datenbank wirksam
• Bei rollback; werden die Änderungen verworfen und die ursprünglichen Daten bleiben erhalten
• Alte Zeilenversionen werden mit dem Befehl VACUUM FULL bereinigt, um Speicherplatz zurückzugewinnen

Undo Log in MySQL

• MySQL überschreibt Zeilen direkt, protokolliert aber die vorherigen Werte im Undo Log, damit sie bei Bedarf wiederhergestellt werden können
• Jede Zeile besitzt xid (ID der zuletzt ändernden Transaktion) und ptr (Undo-Log-Pointer) als Metadaten
• Wenn mehrere Transaktionen gleichzeitig laufen, wählt jede Transaktion über das Undo Log gezielt die benötigte Version aus
• Für dieselbe Zeile können mehrere Undo-Log-Einträge existieren; anhand der Transaktions-ID wird die passende Version ausgewählt

Isolationsstufen (Isolation Levels)

• Dies ist eine Einstellung zur Steuerung von Dateninterferenzen zwischen Transaktionen; sie wird in der Reihenfolge Serializable → Repeatable Read → Read Committed → Read Uncommitted schrittweise gelockert
• Serializable: Alle Transaktionen verhalten sich so, als würden sie nacheinander ausgeführt
• Repeatable Read: Die erneute Ausführung derselben Query liefert dasselbe Ergebnis, aber Phantom Reads sind möglich
• Read Committed: Änderungen anderer bereits committeter Transaktionen können gelesen werden
• Read Uncommitted: Dirty Reads sind erlaubt; dies ist die niedrigste Schutzstufe, bietet aber hohe Performance

Gleichzeitige Schreibvorgänge (Concurrent Writes)

• Wie zwei Transaktionen behandelt werden, die dieselbe Zeile gleichzeitig ändern, unterscheidet sich je nach Datenbank

MySQL: Sperren auf Zeilenebene (Row-level Locking)

• Gemeinsame Sperren (S lock) erlauben mehreren Transaktionen gleichzeitiges Lesen
• Exklusive Sperren (X lock) erlauben nur einer Transaktion, eine Zeile zu ändern
• Im Modus SERIALIZABLE muss bei jeder Aktualisierung ein X lock erworben werden; bei Konflikten kann ein Deadlock entstehen
• MySQL erkennt Deadlocks und beendet eine der beiden Transaktionen

Postgres: Serializable Snapshot Isolation

• Postgres verwendet Predicate Locks, um Zugriffe auf Mengen von Zeilen nachzuverfolgen
  • Beispiel: eine Sperre für die Bedingung WHERE id BETWEEN 10 AND 20
• Der tatsächliche Zugriff wird nicht blockiert; stattdessen werden Konflikte erkannt und die Transaktion bei einer Verletzung beendet
• Durch optimistische Konfliktauflösung (optimistic conflict resolution) werden Deadlocks vermieden
• Wie bei MySQL wird bei einem Konflikt eine Transaktion beendet, und die Anwendung muss eine Retry-Logik implementieren

Fazit

• Transaktionen sind ein Kernelement von Datenbanken und garantieren Atomarität, Konsistenz, Isolation und Dauerhaftigkeit (ACID)
• Postgres und MySQL erreichen dasselbe Ziel mit unterschiedlichen internen Strukturen
• Wer die vier Isolationsstufen und das Verhalten von Transaktionen versteht, kann Datenbanken stabiler betreiben

[GN⁺]

Hacker-News-Kommentare

• Dieser Artikel wirkte etwas unzureichend
  Statt die Isolationsstufen anhand der im SQL-Standard definierten Phänomene (phenomena) zu erklären, wäre es meiner Meinung nach intuitiver, vom Konzept der Serialisierbarkeit (serializability) auszugehen
  Serialisierbarkeit kann als Verallgemeinerung von Thread-Sicherheit verstanden werden; geht sie verloren, entstehen Bugs, bei denen das Ergebnis je nach Ausführungsreihenfolge unterschiedlich ausfällt
  Die verschiedenen Isolationsstufen von Datenbanken sind letztlich nur abgeschwächte Formen dieser Garantie, und der Nutzer muss die fehlende Sicherheit auf andere Weise gewährleisten
  Die Phänomene sind nur Werkzeuge, um nicht-serielle Situationen zu visualisieren, stehen aber nicht in direkter Verbindung zur Serialisierbarkeit
  Zum Beispiel kann auch ein Kubernetes-Cluster mit gut entworfenen Controllern serialisierbar arbeiten

  • Hier der Autor. Danke für das gute Feedback
    Transaktionen, Isolationsstufen und MVCC auf einmal zu behandeln, inklusive Vergleichen zwischen mehreren Datenbanken, ist eine gewaltige Aufgabe
    Ich habe versucht, ein Gleichgewicht zwischen technischer Tiefe, Zugänglichkeit und Textlänge zu finden
  • Jemand teilt den Link Jepsen: Analyse des MariaDB Galera Cluster
    Dazu die Meinung, dass mehr Notation und Zitate hilfreich wären
  • Die meisten RDBMS bieten bei Bedarf serialisierbare Isolation
    Wenn man sie aber unnötig verwendet, steigen die Koordinationskosten zwischen Transaktionen, was Nebenläufigkeit und Durchsatz verringert
  • Darauf die Reaktion, dann solle man doch eine bessere Erklärung vorschlagen

• Man kann sich Transaktionen auch wie Snapshots in Copy-on-Write-Dateisystemen (btrfs, zfs) vorstellen, aber die Analogie mit Git-Branches ist meiner Meinung nach noch intuitiver
  BEGIN ist das Erzeugen eines Branches, UPDATE ein Commit, ROLLBACK das Löschen des Branches und COMMIT entspricht git merge
  Bei Konflikten versucht die DB, auf Zeilenebene zu mergen; wenn das scheitert, wird je nach Konfiguration zurückgerollt oder ein erzwungener Merge ausgeführt
  READ UNCOMMITTED priorisiert schnelles Mergen, SERIALIZABLE die Korrektheit
  So eine Analogie kann jemandem helfen, das Konzept von Transaktionen mit einem echten „Aha!“ zu verstehen

  • (Kurzer Kommentar) Eine Reaktion, die Concurrency andeutet

• Was viele überrascht: Postgres und MySQL laufen standardmäßig nicht im serialisierbaren Modus, sondern mit read committed
  Der Performance-Unterschied ist nicht nur „ein bisschen“, sondern in der Praxis deutlich größer
  Mit read committed muss man auf Locking achten, und auch UNIQUE-Constraints sind nötig, um Race Conditions zu verhindern
  Trotzdem wird dieser Ansatz bevorzugt, statt die Performance-Einbußen und Retry-Probleme des serialisierbaren Modus in Kauf zu nehmen
  Referenz: Offizielle PostgreSQL-Dokumentation

  • Aktuelle MySQL- und MariaDB-Versionen (InnoDB) haben standardmäßig repeatable read
    Siehe MySQL-Dokumentation und MariaDB-Dokumentation
    MyISAM wird heute kaum noch verwendet
  • Das Problem bei SERIALIZABLE ist nicht nur die Performance, sondern auch, dass Transaktionen durch Konflikte, Deadlocks oder Timeouts fehlschlagen können
    Die Anwendung muss das erkennen und eine Retry-Strategie haben
  • Auch bei Oracle und SQL Server ist read committed der Standard
    Serialisierbarer Modus sieht im Lehrbuch toll aus, wird in der Praxis aber kaum genutzt

• Viele Datenbank-Tools priorisieren heute das Teilen von Echtzeit-Updates über ACID
  Bei Airtable etwa werden Feldänderungen sofort auf den Bildschirmen der Kollegen sichtbar, aber weil Transaktionen fehlen, besteht das Risiko von Dateninkonsistenzen
  Dazu siehe den Blogbeitrag von VisualDB

  • Eine Reaktion, dass das wie Produktwerbung unter dem Vorwand aussieht, einen Konkurrenten zu kritisieren

• Es macht wirklich Spaß, den PlanetScale-Blog zu lesen
  Ich frage mich, welche Tools für die Visualisierung verwendet wurden

  • Hier der Autor. Danke!
    Die Visualisierungen wurden mit js + gsap (https://gsap.com) erstellt

• Wenn dich das Thema interessiert, kann ich „Designing Data-Intensive Applications“ sehr empfehlen
  Das Buch behandelt nicht nur verschiedene Isolationsstufen, sondern auch die Unschärfe der ACID-Definition
  Ich habe gehört, dass die zweite Auflage bald erscheint

  • Die zweite Auflage ist bereits erschienen: O’Reilly-Link

• Transaktionen in MVCC-Systemen wie Postgres ähneln Snapshots in Copy-on-Write-Dateisystemen
  Beim BEGIN-Zeitpunkt wird ein Snapshot der Daten erstellt, und UPDATE wird nur auf die private Kopie angewendet
  Bei ROLLBACK wird die Kopie verworfen, bei COMMIT wird der neue Snapshot zur offiziellen Version
  Diese Analogie kann für jemanden der Moment sein, in dem das Transaktionskonzept wirklich klar wird
  P.S. Auch die Git-Branch-Analogie ist möglich

  • Ganz korrekt ist das nicht. Eine DB verwendet Branching und Locking zusammen
    Bei Fällen wie SELECT gefolgt von UPDATE kann ein Thread blockiert werden
    Ich will heute in MySQL ausprobieren, ob sich das in eine einzelne Query umwandeln lässt

• Früher wurde in Backend-Interviews oft nach Transaktionen gefragt
  Jeder hat sie benutzt, aber das Verständnisniveau unterscheidet sich je nach Erfahrung
  Auch wenn man nicht jede Isolationsstufe auswendig kennt, zeigt schon das Wissen, dass sie sich unterschiedlich verhalten, Neugier und Systemverständnis

  • Selbst Isolationsstufen mit demselben Namen verhalten sich je nach DB unterschiedlich, deshalb sollte man die Details pro Einzelfall prüfen

• Die Erklärung von „phantom read“ könnte missverständlich sein
  Bei repeatable read ändern sich die Werte bestehender Zeilen nicht, aber neue Zeilen können hinzugefügt werden
  Bestehende Zeilen werden dabei nicht verändert oder gelöscht, daher sollte das klarer formuliert werden

• Der Satz „hat nichts mit xmin/xmax zu tun“ wirkt unvollständig
  Dass die Visualisierung beim Commit auf den Tabellen-Header zeigt, wirkt ebenfalls merkwürdig
  Sind xmax/xmin nicht tatsächlich der zentrale Mechanismus, um festzustellen, ob ein Commit erfolgt ist?
  Wenn man auch Subtransaktionen berücksichtigt, wird es noch komplizierter
  Trotzdem haben die Visualisierung und die Erklärung insgesamt Spaß gemacht zu lesen

  • Ich fand es auch schade, dass das xmax/xmin-Konzept fehlt
    Es ist zentral für das Verständnis der Isolationsstufen und wirkte, als würde ein ganzer Abschnitt fehlen