Transaktionen und ACID in der MySQL-Praxis

1. Was ACID in MySQL konkret bedeutet

Eine Transaktion in MySQL ist eine Gruppe von SQL-Anweisungen, die als eine einzige logische Einheit behandelt wird. Entweder werden alle Anweisungen der Transaktion dauerhaft in der Datenbank sichtbar, oder keine einzige davon. Dieses Alles-oder-Nichts-Prinzip ist keine akademische Formalität, sondern der einzige Weg, Geldüberweisungen, Lagerbestandsänderungen oder Bestellprozesse korrekt abzubilden, ohne dass ein Serverabsturz mitten in der Verarbeitung die Daten in einem halbfertigen Zustand hinterlässt.

Das Kürzel ACID steht für Atomicity, Consistency, Isolation und Durability. In der reinen Theorie klingen diese vier Begriffe abstrakt, in MySQL sind sie jedoch an konkrete Mechanismen der Speicher-Engine InnoDB gebunden. Wichtig zu wissen: Nur InnoDB unterstützt vollwertige Transaktionen mit allen vier ACID-Eigenschaften. Die ältere MyISAM-Engine kennt keine Transaktionen im eigentlichen Sinn, weshalb produktive MySQL-Installationen heute praktisch ausschließlich auf InnoDB setzen.

Dieser Artikel geht Schritt für Schritt durch die praktische Seite der Transaktion in MySQL: von den grundlegenden Befehlen über den oft missverstandenen Autocommit-Modus bis zu den internen Log-Strukturen, mit denen InnoDB jede der vier ACID-Garantien tatsächlich einhält.

2. START TRANSACTION, COMMIT und ROLLBACK im Detail

Der Einstieg in eine explizite Transaktion erfolgt mit START TRANSACTION (alternativ BEGIN). Ab diesem Punkt werden alle nachfolgenden Änderungen nicht sofort dauerhaft geschrieben, sondern in einem transaktionsinternen Zustand gehalten, den nur die aktuelle Session sieht. Erst COMMIT macht diese Änderungen für alle anderen Verbindungen sichtbar und dauerhaft. ROLLBACK verwirft stattdessen alle seit START TRANSACTION vorgenommenen Änderungen vollständig, als hätten sie nie stattgefunden.

In der Praxis kombiniert man diese Befehle fast immer mit Fehlerbehandlung auf Anwendungsebene: Die Anwendung öffnet eine Transaktion, führt mehrere zusammengehörige Statements aus, und committet nur, wenn alle erfolgreich waren. Schlägt ein Statement fehl, etwa wegen einer Constraint-Verletzung, ruft die Anwendung ROLLBACK auf. Ohne dieses Muster bleiben halb ausgeführte Änderungen in der Datenbank stehen, was bei mehreren zusammenhängenden Tabellen zu inkonsistenten Zuständen führt.

-- Classic order-processing transaction
START TRANSACTION;

UPDATE inventory
SET quantity = quantity - 2
WHERE product_id = 4711;

INSERT INTO orders (customer_id, product_id, quantity, status)
VALUES (91, 4711, 2, 'pending');

-- Check stock did not go negative before committing
SELECT quantity FROM inventory WHERE product_id = 4711;

-- If quantity is still valid, persist everything at once
COMMIT;

-- If the stock check failed, undo both statements
-- ROLLBACK;

3. Autocommit verstehen und kontrollieren

MySQL läuft standardmäßig mit autocommit=1. In diesem Modus wird jede einzelne SQL-Anweisung automatisch als eigene, in sich geschlossene Transaktion behandelt, ohne dass ein explizites START TRANSACTION nötig ist. Ein einzelnes UPDATE wird sofort nach Ausführung committet. Das ist praktisch für einfache Skripte, wird aber gefährlich, sobald mehrere Anweisungen logisch zusammengehören: Ohne explizite Transaktion kann zwischen zwei Autocommit-Statements ein Fehler auftreten, und die Datenbank bleibt in einem inkonsistenten Zwischenzustand.

Man kann den Autocommit-Modus für die gesamte Session mit SET autocommit = 0 deaktivieren. Dann startet MySQL implizit eine neue Transaktion bei der ersten Anweisung, und jede weitere Anweisung bleibt Teil dieser Transaktion, bis ein explizites COMMIT oder ROLLBACK erfolgt. Wichtig ist: Sobald START TRANSACTION explizit aufgerufen wird, ist der aktuelle Autocommit-Wert für den Rest der Transaktion irrelevant, MySQL committet erst beim expliziten COMMIT.

Ein häufig übersehener Effekt: Bestimmte DDL-Anweisungen wie CREATE TABLE, ALTER TABLE oder DROP TABLE lösen in MySQL ein implizites COMMIT aus, selbst innerhalb einer laufenden Transaktion. Wer DDL und DML in derselben Transaktion mischt, verliert damit unbemerkt die Möglichkeit, die DML-Änderungen per ROLLBACK rückgängig zu machen.

-- Check and control autocommit for the current session
SELECT @@autocommit;

-- Disable autocommit: subsequent statements form one implicit transaction
SET autocommit = 0;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Nothing is durable yet, both rows are only visible in this session
COMMIT;

-- Re-enable autocommit for subsequent single-statement transactions
SET autocommit = 1;

4. Atomicity: das Undo Log als Sicherheitsnetz

Atomicity garantiert, dass eine Transaktion entweder vollständig oder gar nicht ausgeführt wird. InnoDB setzt das über das Undo Log um, einen internen Puffer, der zu jeder Änderung den vorherigen Zustand einer Zeile speichert. Bevor InnoDB eine Zeile physisch verändert, wird der alte Wert ins Undo Log geschrieben. Kommt ein ROLLBACK, liest InnoDB das Undo Log rückwärts und stellt jede Zeile exakt auf ihren Ausgangszustand zurück.

Das Undo Log erfüllt eine zweite Funktion, die eng mit Atomicity und Isolation verzahnt ist: Es liefert die Datengrundlage für Consistent Reads unter MVCC, dem Multi-Version Concurrency Control von InnoDB. Andere Transaktionen, die eine Zeile lesen, während sie gerade verändert wird, sehen über das Undo Log die alte, konsistente Version, statt auf halb geänderte Daten zuzugreifen. Erst nach dem COMMIT und dem Verstreichen aller aktiven älteren Transaktionen wird der Undo-Log-Eintrag vom Purge-Thread endgültig entfernt.

Auch bei einem Server-Crash mitten in einer Transaktion greift dieses Prinzip: Beim nächsten Start liest InnoDB im Rahmen der Crash Recovery sowohl das Redo Log als auch das Undo Log, wendet noch nicht committete Änderungen zurück und stellt so sicher, dass keine unvollständige Transaktion überlebt.

5. Consistency: Constraints im Transaktionskontext

Consistency bedeutet, dass eine Transaktion die Datenbank von einem gültigen in einen anderen gültigen Zustand überführt, definiert über Constraints wie Primary Keys, Foreign Keys, Unique-Indizes und Check Constraints. Verletzt eine Anweisung innerhalb der Transaktion eine dieser Regeln, schlägt genau diese Anweisung fehl. Die Transaktion selbst bleibt offen, es liegt an der Anwendung, dann entweder mit einem korrigierten Statement weiterzumachen oder ein ROLLBACK auszulösen.

Foreign-Key-Constraints sind ein besonders relevanter Fall, weil sie tabellenübergreifend wirken. Fügt eine Transaktion einen Datensatz ein, der auf einen nicht existierenden Fremdschlüssel verweist, verhindert InnoDB das Einfügen sofort, unabhängig vom Isolation Level. Das schützt vor verwaisten Datensätzen, hat aber einen wichtigen Nebeneffekt: InnoDB muss beim Prüfen des Fremdschlüssels intern eine Sperre auf die referenzierte Zeile setzen, was in stark parallelen Workloads zu Lock-Konflikten führen kann.

-- Consistency enforced through constraints inside a transaction
START TRANSACTION;

-- Fails immediately if customer_id 99999 does not exist
-- in the referenced customers table
INSERT INTO orders (customer_id, total_amount)
VALUES (99999, 149.90);

-- If the foreign key check fails, the statement is rejected,
-- the transaction stays open for the application to decide
ROLLBACK;

6. Isolation: was eine Transaktion von anderen sieht

Isolation bestimmt, wie stark parallel laufende Transaktionen sich gegenseitig beeinflussen dürfen. MySQL kennt vier Isolation Levels, READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ und SERIALIZABLE, wobei InnoDB standardmäßig mit REPEATABLE READ arbeitet. Dieser Artikel fokussiert bewusst auf die anderen drei ACID-Eigenschaften, ein separater Artikel dieser Serie widmet sich ausschließlich den Isolation Levels und ihren praktischen Unterschieden im Detail.

Für das Verständnis von Transaktionen reicht an dieser Stelle die Kernidee: InnoDB nutzt für REPEATABLE READ einen konsistenten Snapshot, der beim ersten Lesevorgang der Transaktion erstellt wird. Alle nachfolgenden Lesevorgänge derselben Transaktion sehen die Daten so, wie sie zu diesem Zeitpunkt aussahen, selbst wenn parallele Transaktionen inzwischen Änderungen committen. Schreiboperationen sehen dagegen immer den aktuellsten committeten Stand, was in der Praxis zu Verwirrung führt, wenn Lese- und Schreibverhalten innerhalb derselben Transaktion nicht konsistent erscheinen.

7. Durability: Redo Log und Doublewrite Buffer

Durability garantiert, dass eine einmal committete Transaktion auch einen Serverabsturz unmittelbar danach übersteht. InnoDB erreicht das über das Redo Log, eine sequenziell geschriebene Log-Datei, in die jede Änderung geschrieben wird, bevor sie in den eigentlichen Datenseiten im Buffer Pool angewendet wird. Dieses Prinzip heißt Write-Ahead Logging: Das Log wird zuerst persistiert, die eigentlichen Datenseiten können später und effizienter im Hintergrund geschrieben werden.

Wie streng dieses Verhalten ist, steuert der Parameter innodb_flush_log_at_trx_commit. Der Wert 1, die Standardeinstellung, schreibt das Redo Log bei jedem COMMIT sofort auf die Festplatte und garantiert damit volle Durability, allerdings mit spürbarem I/O-Overhead. Der Wert 2 schreibt das Log an das Betriebssystem, flusht es aber nur einmal pro Sekunde, was bei einem Betriebssystemabsturz Daten verlieren kann, bei einem reinen MySQL-Crash jedoch nicht. Der Wert 0 flusht ebenfalls nur einmal pro Sekunde und riskiert damit den Verlust der letzten Sekunde an Transaktionen auch bei einem MySQL-Crash.

Der Doublewrite Buffer ergänzt das Redo Log um einen weiteren Schutzmechanismus: Bevor InnoDB eine Datenseite an ihrem endgültigen Ort schreibt, wird sie zunächst vollständig in einen zusammenhängenden Pufferbereich geschrieben. Bricht der Schreibvorgang mitten in einer Seite ab, etwa durch einen Stromausfall, kann InnoDB die intakte Kopie aus dem Doublewrite Buffer verwenden, statt eine korrupte, halb geschriebene Datenseite zu riskieren.

-- Inspect and tune durability behaviour
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'innodb_doublewrite';

-- Full durability (default): fsync on every commit
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- Relaxed durability for bulk-load jobs, restore after the job
-- SET GLOBAL innodb_flush_log_at_trx_commit = 2;

8. Savepoints für partielles Rollback

Nicht jede fehlgeschlagene Anweisung innerhalb einer Transaktion soll zwingend die komplette Transaktion verwerfen. Mit SAVEPOINT name markiert man einen Punkt innerhalb der laufenden Transaktion, zu dem man später gezielt zurückspringen kann, ohne die gesamte Transaktion zu verlieren. ROLLBACK TO SAVEPOINT name macht nur die Änderungen seit diesem Savepoint rückgängig, alle davor liegenden Änderungen der Transaktion bleiben erhalten und können am Ende committet werden.

Savepoints sind besonders in komplexen Batch-Verarbeitungen nützlich, etwa beim Import mehrerer Datensätze innerhalb einer Transaktion: Scheitert der Import eines einzelnen Datensatzes an einer Constraint-Verletzung, kann man gezielt zu einem Savepoint vor diesem Datensatz zurückspringen, den fehlerhaften Datensatz überspringen und mit dem nächsten fortfahren, ohne bereits erfolgreich verarbeitete Datensätze zu verlieren.

START TRANSACTION;

INSERT INTO import_log (batch_id, item) VALUES (1, 'item-a');
SAVEPOINT before_item_b;

INSERT INTO import_log (batch_id, item) VALUES (1, 'item-b-invalid');
-- This insert violates a constraint, roll back only this part
ROLLBACK TO SAVEPOINT before_item_b;

INSERT INTO import_log (batch_id, item) VALUES (1, 'item-c');

-- item-a and item-c persist, item-b was discarded
COMMIT;

9. Typische Fehler in der Transaktionspraxis

Der häufigste Fehler in der Praxis ist die lang laufende Transaktion. Eine Transaktion, die eine Weile geöffnet bleibt, etwa weil zwischen zwei Statements ein langsamer API-Aufruf zu einem externen Dienst erfolgt, hält den zugehörigen Undo-Log-Snapshot am Leben und verhindert, dass InnoDB alte Zeilenversionen bereinigt. Bei hoher Schreiblast wächst das Undo-Tablespace dadurch unkontrolliert an, und die Datenbank verlangsamt sich spürbar für alle Sessions, nicht nur für die verantwortliche Transaktion.

Ein zweiter häufiger Fehler ist das Vergessen von Fehlerbehandlung: Ein Entwickler öffnet eine Transaktion, ein Statement schlägt fehl, die Anwendung fängt die Exception ab, ruft aber weder COMMIT noch ROLLBACK auf. Die Transaktion bleibt in der Datenbank hängen, hält Locks und Snapshots, bis die Verbindung schließlich getrennt wird. In Connection-Pool-Umgebungen kann eine so verwaiste Transaktion eine gepoolte Verbindung dauerhaft blockieren.

Ein dritter Fehler betrifft implizite Commits durch DDL: Wer versucht, in einer Transaktion sowohl Tabellenstruktur zu ändern als auch Daten zu manipulieren, und im Fehlerfall auf ein vollständiges ROLLBACK vertraut, wird überrascht: Die DDL-Anweisung hat bereits committet, bevor der Fehler überhaupt auftrat. DDL und DML sollten deshalb grundsätzlich in getrennten Transaktionen laufen.

Die Wahl von innodb_flush_log_at_trx_commit ist ein direkter Kompromiss zwischen Durability und Durchsatz. Für jede Transaktion, die Geld, Bestellungen oder rechtlich relevante Daten betrifft, ist der Standardwert 1 die einzig vertretbare Wahl. Für reine Massenimporte, deren Datenquelle bei einem Fehler ohnehin erneut eingespielt wird, kann eine temporäre Lockerung den Durchsatz spürbar erhöhen.

10. Zusammenfassung

Eine Transaktion in MySQL ist mehr als START TRANSACTION und COMMIT. Atomicity wird über das Undo Log garantiert, das den Ausgangszustand jeder Zeile für ein mögliches Rollback vorhält. Consistency erzwingt InnoDB über Constraints wie Foreign Keys, die jede verletzende Anweisung sofort ablehnen. Isolation bestimmt über den REPEATABLE-READ-Snapshot, was eine Transaktion von parallelen Änderungen sieht. Durability sichert das Redo Log gemeinsam mit dem Doublewrite Buffer ab, gesteuert über innodb_flush_log_at_trx_commit.

Wer Autocommit versteht und bewusst einsetzt, wer Savepoints für komplexe Batch-Verarbeitungen nutzt, und wer lang laufende Transaktionen sowie implizite Commits durch DDL vermeidet, betreibt Transaktionen in MySQL so, wie InnoDB sie entworfen hat: robust, nachvollziehbar und ohne versteckte Nebeneffekte auf parallele Sessions.

11. FAQ: Transaktionen und ACID in der MySQL-Praxis