Lange laufende und im Transaktions-Leerlauf befindliche Sitzungen in PostgreSQL: was sie blockieren und wie man sie begrenzt

Worum es geht

Eine Transaktion, die vor Langem begonnen hat, hält zwei Dinge fest: jede von ihr erworbene Sperre, die erst bei Commit oder Rollback freigegeben wird, und einen Snapshot, der ihren backend_xmin fixiert. Die Dokumentation zu idle_in_transaction_session_timeout besagt, dass eine offene Transaktion selbst ohne bedeutende Sperren verhindert, dass kürzlich verstorbene Tupel weggeräumt werden, die möglicherweise nur für sie sichtbar sind, sodass ein langer Leerlauf zum Table Bloat beitragen kann. pg_stat_activity zeigt pro Sitzung den state (active, idle, idle in transaction, idle in transaction (aborted)), xact_start und backend_xmin.

Warum es wichtig ist

Eine einzige Sitzung im Zustand idle in transaction ist die häufige Ursache für drei scheinbar unabhängige Symptome: Tabellen, die trotz laufendem Autovacuum immer weiter wachsen, eine Migration, die bei ALTER TABLE hängen bleibt und dann jede dahinter stehende Abfrage blockiert, und Warnungen vor einem Transaktions-ID-Wraparound, gegen die das Kapitel zum routinemässigen Vacuuming unter anderem das Beenden lange laufender offener Transaktionen empfiehlt, die über age(backend_xmin) gefunden werden. Der Client weiss meist nicht, dass er etwas offen hält: Ein Framework hat bei der ersten Abfrage eine Transaktion geöffnet, dann hat der Code einen HTTP-Aufruf gemacht oder auf eine Nutzerin oder einen Nutzer gewartet.

So wird es angewendet

• Auffinden: SELECT pid, usename, state, now() - xact_start AS age, age(backend_xmin), left(query, 80) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start listet die ältesten Transaktionen zuerst.
• idle_in_transaction_session_timeout pro Anwendungsrolle setzen (ALTER ROLE app SET idle_in_transaction_session_timeout = '30s'); die Sitzung wird beendet, und der Client erhält einen Verbindungsfehler, was bei einer vergessenen Transaktion das richtige Ergebnis ist.
• transaction_timeout (PostgreSQL 17 und neuer) für Rollen hinzufügen, deren Transaktionen kurz sein müssen, und statement_timeout für einzelne Anweisungen; die Dokumentation weist darauf hin, dass ein transaction_timeout, der kürzer als oder gleich lang wie die anderen beiden ist, diese länger eingestellten Werte wirkungslos macht.
• Die Standardwerte für Reporting- und Migrationsrollen beibehalten, die legitim lange laufen, und ihnen eigene Verbindungen und einen eigenen Pool geben.
• Diese Werte pro Rolle oder pro Sitzung setzen; die Dokumentation rät davon ab, statement_timeout und transaction_timeout in postgresql.conf zu setzen, da das alle Sitzungen betrifft, auch Wartungsvorgänge.
• Aus dem Monitoring heraus auf den ältesten xact_start und auf age(backend_xmin) alarmieren, nicht nur auf Bloat.

Stolpersteine

Replikationsslots und vorbereitete Transaktionen halten den xmin-Horizont auf dieselbe Weise zurück und sind von diesen Timeouts nicht erfasst; auch pg_replication_slots und pg_prepared_xacts prüfen. Ein Standby mit hot_standby_feedback exportiert seine lange laufenden Abfragen an den Primary. Ein Pooler im Transaktionsmodus kann eine beendete Serververbindung als rätselhaften Fehler bei einem unbeteiligten Client zutage treten lassen. Eine Beendigung macht die Arbeit rückgängig; ein Batch-Job braucht Commits in Abschnitten, kein längeres Timeout.