Long-Running Transactions in MySQL vermeiden

1. Warum lange Transaktionen zum Problem werden

Eine Long-Running Transaction ist keine Transaktion mit einer festen Zeitgrenze, sondern jede Transaktion, die deutlich länger offen bleibt, als es die eigentliche fachliche Operation erfordert. Ein Report, der versehentlich innerhalb derselben Transaktion wie ein Import läuft, ein vergessenes COMMIT in einer interaktiven Session, oder ein Batch-Job, der zehntausend Zeilen in einem einzigen Durchgang verarbeitet: Alle drei erzeugen dieselbe Klasse von Problemen, auch wenn die Ursache jeweils unterschiedlich ist.

Das Tückische an einer Long-Running Transaction ist, dass sie zunächst keine sichtbaren Fehler produziert. Die Anwendung läuft weiter, Abfragen liefern Ergebnisse, und erst nach Minuten oder Stunden zeigen sich die Folgeschäden: wachsende Tablespace-Dateien, blockierte parallele Schreibzugriffe und eine Replikation, die immer weiter zurückfällt. Die folgenden Abschnitte zeigen, wie InnoDB durch lange Transaktionen technisch belastet wird und mit welchen konkreten Werkzeugen und Mustern man das Problem in den Griff bekommt.

2. Wie InnoDB Undo-Logs und Purge organisiert

InnoDB nutzt für Multi-Version Concurrency Control ein Undo-Log, das für jede Änderung an einer Zeile die vorherige Version festhält. Dieses Undo-Log ermöglicht es parallelen Transaktionen, dank MVCC einen konsistenten Snapshot ihres Datenbestands zu lesen, ohne durch Schreibzugriffe anderer Transaktionen beeinflusst zu werden. Ein Hintergrundprozess, der Purge-Thread, räumt Undo-Log-Einträge auf, sobald keine aktive Transaktion mehr einen älteren Snapshot benötigt, in dem der jeweilige Eintrag noch sichtbar sein müsste.

Eine einzelne Long-Running Transaction verhindert genau diesen Aufräumvorgang. Solange die alte Transaktion offen bleibt, muss InnoDB jeden Undo-Log-Eintrag vorhalten, der für ihren Snapshot relevant sein könnte, selbst wenn Tausende anderer Transaktionen in der Zwischenzeit committen. Das Ergebnis ist ein stetig wachsender History List Length, sichtbar über SHOW ENGINE INNODB STATUS, was den Tablespace aufbläht und die Lesegeschwindigkeit für alle nachfolgenden Abfragen messbar verschlechtert, weil InnoDB längere Versionsketten durchlaufen muss.

-- Check current InnoDB history list length (undo log growth indicator)
SHOW ENGINE INNODB STATUS\G
-- Look for: "History list length NNNN" in the TRANSACTIONS section

-- Alternative: query via information_schema (MySQL 8.0.30+)
SELECT NAME, COUNT
FROM information_schema.INNODB_METRICS
WHERE NAME = 'trx_rseg_history_len';

3. Long-Running Transactions mit innodb_trx aufspüren

Die Tabelle information_schema.innodb_trx listet alle aktuell offenen InnoDB-Transaktionen mit Startzeitpunkt, Status und der zugehörigen Thread-ID. Über die Spalte trx_started lässt sich die Laufzeit jeder Transaktion berechnen, sodass Long-Running Transactions gezielt herausgefiltert werden können, statt manuell durch die Prozessliste zu scrollen. In Kombination mit information_schema.processlist erhält man zusätzlich den zuletzt ausgeführten SQL-Befehl der jeweiligen Verbindung.

Diese Abfrage sollte fester Bestandteil jedes Monitoring-Setups sein, das Long-Running Transactions proaktiv erkennen will, statt erst zu reagieren, wenn Nutzer sich über Timeouts beschweren. In produktiven Umgebungen empfiehlt sich ein Schwellwert von wenigen Minuten, ab dem eine Transaktion als auffällig gilt, abhängig vom typischen Workload der jeweiligen Anwendung.

-- Find transactions running longer than 60 seconds
SELECT
  trx_id,
  trx_state,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
  trx_mysql_thread_id,
  trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY duration_seconds DESC;

-- Cross-reference with the full connection state
SELECT p.id, p.user, p.host, p.db, p.time, p.state, p.info
FROM information_schema.processlist p
JOIN information_schema.innodb_trx t ON t.trx_mysql_thread_id = p.id
WHERE t.trx_started < NOW() - INTERVAL 60 SECOND;

4. Sperren und Lock-Waits mit performance_schema analysieren

Eine Long-Running Transaction ist nicht nur ein Problem für den Undo-Log, sondern hält häufig auch Zeilensperren, die andere Transaktionen blockieren. Der performance_schema liefert über die Tabellen data_locks und data_lock_waits eine detaillierte Sicht darauf, welche Transaktion auf welche Sperre einer anderen Transaktion wartet. In älteren MySQL-Versionen übernahm information_schema.innodb_lock_waits diese Aufgabe, wurde aber zugunsten des performance_schema als veraltet markiert.

Wer regelmäßig blockierte Abfragen im Log findet, sollte nicht nur die blockierte, sondern gezielt die blockierende Transaktion identifizieren. Häufig ist genau diese blockierende Transaktion die eigentliche Long-Running Transaction, die längst hätte committen sollen. Ein gezieltes KILL der blockierenden Verbindung ist im Notfall vertretbar, sollte aber immer von einer Ursachenanalyse gefolgt werden, warum die Transaktion so lange offen blieb.

-- Which transaction blocks which other transaction (MySQL 8.0+)
SELECT
  waiting_trx.trx_id AS waiting_trx_id,
  waiting_trx.trx_mysql_thread_id AS waiting_thread,
  blocking_trx.trx_id AS blocking_trx_id,
  blocking_trx.trx_mysql_thread_id AS blocking_thread,
  blocking_trx.trx_started AS blocking_since
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx waiting_trx
  ON waiting_trx.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.innodb_trx blocking_trx
  ON blocking_trx.trx_id = w.blocking_engine_transaction_id;

5. Anwendungsseitige Ursachen für unnötig lange Transaktionen

Die häufigste Ursache für eine Long-Running Transaction liegt nicht in der Datenbank, sondern im Anwendungscode. Ein klassisches Muster: Eine Transaktion wird geöffnet, danach folgt ein externer HTTP-Aufruf, etwa an einen Zahlungsdienstleister oder einen E-Mail-Versand, und erst nach der Antwort wird committet. Solange der externe Dienst antwortet, bleibt die Datenbanktransaktion offen, obwohl die eigentliche Datenbankoperation längst abgeschlossen wäre. Externe Netzwerkaufrufe gehören grundsätzlich niemals innerhalb einer offenen Transaktion.

Eine zweite häufige Ursache ist ORM-Verhalten, das implizit eine Transaktion offen hält, während im Anwendungscode noch aufwendige Berechnungen oder Schleifen mit vielen Einzeloperationen laufen. Auch interaktive Datenbank-Sessions von Entwicklern, die mit START TRANSACTION beginnen und dann minutenlang debuggen, bevor sie committen oder zurückrollen, zählen in der Praxis zu den häufigsten Quellen produktionsschädlicher Long-Running Transactions, gerade in Staging- und Test-Umgebungen, die versehentlich denselben Server wie die Produktion nutzen.

6. Timeout-Strategien: innodb_lock_wait_timeout und Applikationslogik

Der Serverparameter innodb_lock_wait_timeout begrenzt, wie lange eine Transaktion auf eine Sperre wartet, bevor sie mit einem Fehler abgebrochen wird. Der Standardwert von 50 Sekunden ist für viele Webanwendungen zu hoch, weil ein Nutzer nicht 50 Sekunden auf eine blockierte Seite warten sollte. Eine niedrigere applikationsspezifische Einstellung, etwa über SET SESSION innodb_lock_wait_timeout = 5, erzwingt schnelles Feedback und verhindert, dass sich blockierte Verbindungen im Verbindungspool stauen.

Zusätzlich zum reinen Lock-Wait-Timeout sollte die Anwendungsschicht selbst eine maximale Transaktionsdauer erzwingen, etwa über einen Watchdog, der offene Transaktionen nach einer definierten Zeit protokolliert und im Extremfall die Verbindung schließt. Diese doppelte Absicherung, Datenbankparameter und Applikationslogik gemeinsam, verhindert zuverlässig, dass eine einzelne fehlerhafte Long-Running Transaction unbemerkt über Stunden offen bleibt.

-- Session-level lock wait timeout, tighter than the global default
SET SESSION innodb_lock_wait_timeout = 5;

-- Check current global and session values
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

-- Global setting for the whole server (requires SUPER privilege)
SET GLOBAL innodb_lock_wait_timeout = 15;

7. Batch-Verarbeitung: große Transaktionen sinnvoll aufteilen

Ein Massen-Update oder -Import, der zehntausende Zeilen in einer einzigen Transaktion verarbeitet, ist eine der häufigsten selbstgemachten Long-Running Transactions. Die Lösung ist Batching: Die Gesamtmenge wird in kleinere Blöcke aufgeteilt, jeder Block läuft in einer eigenen kurzen Transaktion mit eigenem COMMIT. Das reduziert die maximale Sperrdauer pro Transaktion drastisch und hält den Undo-Log klein, weil Purge zwischen den Blöcken regelmäßig aufräumen kann.

Wichtig bei Batch-Verarbeitung ist Idempotenz: Wird der Prozess nach einem Teilfehler abgebrochen, muss er sicher fortsetzbar sein, ohne bereits verarbeitete Zeilen doppelt zu bearbeiten. Ein Fortschrittsmarker, etwa die zuletzt verarbeitete ID, macht den Batch-Prozess robust gegen Abbrüche und ersetzt die falsche Sicherheit einer einzigen großen Transaktion durch echte Wiederaufnehmbarkeit.

<?php
declare(strict_types=1);

/**
 * Processes a large dataset in small, short-lived transactions
 * instead of one long-running transaction.
 */
function batchUpdatePrices(PDO $pdo, int $batchSize = 500): void
{
    $lastId = 0;

    while (true) {
        $pdo->beginTransaction();

        $stmt = $pdo->prepare(
            'SELECT id, price FROM product
             WHERE id > :last_id
             ORDER BY id ASC
             LIMIT :batch_size
             FOR UPDATE'
        );
        $stmt->bindValue('last_id', $lastId, PDO::PARAM_INT);
        $stmt->bindValue('batch_size', $batchSize, PDO::PARAM_INT);
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (count($rows) === 0) {
            $pdo->commit();
            break; // no more rows left
        }

        $update = $pdo->prepare('UPDATE product SET price = price * 1.05 WHERE id = :id');
        foreach ($rows as $row) {
            $update->execute(['id' => $row['id']]);
            $lastId = (int) $row['id'];
        }

        $pdo->commit(); // short transaction, released immediately
    }
}

8. Monitoring und Alerting für Long-Running Transactions

Reaktives Debugging reicht bei Long-Running Transactions nicht aus, weil der Schaden bis zur Entdeckung bereits entstanden ist. Ein Monitoring-Job, der in regelmäßigem Abstand information_schema.innodb_trx abfragt und bei Überschreiten eines Schwellwerts alarmiert, erkennt das Problem, bevor Nutzer es über Timeouts bemerken. Werkzeuge wie Percona Monitoring and Management oder ein einfacher Cronjob mit E-Mail-Versand erfüllen diesen Zweck gleichermaßen.

Zusätzlich sollte die History List Length aus SHOW ENGINE INNODB STATUS als Metrik in ein Monitoring-Dashboard einfließen. Ein kontinuierlich steigender Wert über mehrere Stunden ist ein zuverlässiges Frühwarnsignal für eine irgendwo hängende Long-Running Transaction, selbst wenn die konkrete Transaktion im Moment der Beobachtung noch nicht als auffällig lange läuft, aber periodisch immer wieder auftritt und nie ganz abgebaut wird.

9. Symptome, Ursachen und Lösungen im Überblick

Die folgende Tabelle ordnet typische Symptome von Long-Running Transactions ihren häufigsten Ursachen und den passenden Gegenmaßnahmen zu, als schnelle Referenz für die Fehlersuche im Livebetrieb.

Diese Zuordnung ersetzt keine tiefergehende Diagnose, liefert aber im akuten Fall einen schnellen Ausgangspunkt, um von einem beobachteten Symptom zur wahrscheinlichsten Ursache einer Long-Running Transaction zu gelangen, bevor größerer Schaden entsteht.

10. Zusammenfassung

Long-Running Transactions sind gefährlich, weil sie zunächst unauffällig bleiben, während sie im Hintergrund den Undo-Log aufblähen, Sperren halten und Replikation verzögern. Die Tabelle information_schema.innodb_trx ist das erste Werkzeug, um offene Transaktionen mit ihrer Laufzeit sichtbar zu machen, ergänzt durch performance_schema.data_lock_waits für die Analyse konkreter Blockaden. Die häufigste Ursache liegt im Anwendungscode: externe Netzwerkaufrufe innerhalb offener Transaktionen und Massen-Updates ohne Batching.

Wirksame Gegenmaßnahmen kombinieren Datenbankkonfiguration und Anwendungsdesign: ein niedrigerer innodb_lock_wait_timeout, konsequentes Batching großer Datenmengen in kurze Transaktionen, und kontinuierliches Monitoring der History List Length als Frühwarnsystem. Wer diese drei Ebenen zusammen betrachtet, verhindert, dass eine einzelne vergessene Transaktion zum Flaschenhals für das gesamte System wird.

11. FAQ: Long-Running Transactions vermeiden