Lang laufende / offene Transaktion
Eine lang laufende oder offene Transaktion häuft Sperren, Undo und Bloat an. Wir finden die verantwortliche Transaktion, bestimmen ihren Typ (idle in transaction, langer Batch, Auswirkung) und lösen sie sicher auf.
Welche ist die älteste offene Transaktion?
Liste offene Transaktionen nach Startzeit auf. Die älteste ist meist die Ursache des Problems. Prüfe ihren Zustand: Läuft sie aktiv, oder ist sie 'idle in transaction' und wartet auf einen Commit?
SELECT s.sid, s.serial#, s.username, s.status, t.start_time, t.used_ublk*8/1024 AS undo_mb
FROM v$transaction t JOIN v$session s ON s.taddr=t.addr
ORDER BY t.start_time;- idle in transaction (Commit vergessen, inaktiv)
- Aktiver, aber sehr lang laufender Batch/DML
- Sperren/Bloat/Undo häufen sich an, während die Transaktion läuft
Warum tritt idle in transaction auf?
Die Session hat eine Transaktion geöffnet, ist aber inaktiv, ohne eine Abfrage auszuführen; Commit/Rollback wurde also vergessen. Das hält Sperren und Undo unnötig fest. Reicht eine Korrektur in der Anwendung, oder muss sie dringend beendet werden?
SELECT pid, now()-state_change AS idle_age, left(query,80) AS last_query
FROM pg_stat_activity WHERE state='idle in transaction' ORDER BY state_change;- Anwendung/Connection Pool committet nicht (dauerhafte Lösung)
- Ich muss sie sofort beenden
Was macht die aktive lange Transaktion?
Die Session läuft tatsächlich (aktiv). Ist es eine einzelne riesige DML/ein Bericht, oder ein Blocker, der andere Sessions warten lässt und dringend getrennt werden muss?
SELECT s.sid, s.serial#, sq.sql_text, s.last_call_et AS running_secs
FROM v$session s JOIN v$sql sq ON sq.sql_id=s.sql_id
WHERE s.status='ACTIVE' AND s.type='USER' ORDER BY s.last_call_et DESC;- Eine einzelne riesige DML/ein Bericht; muss aufgeteilt werden
- Sie lässt andere warten; dringend beenden
Was häuft die lange Transaktion an?
Bestimme die Nebenwirkung der langen Transaktion: Ist es vor allem ein Sperrstau, der andere Sessions warten lässt, oder Bloat, weil alte Zeilenversionen gehalten werden und die Bereinigung (VACUUM/Undo) blockiert ist?
SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0;
SELECT max(age(backend_xmin)) AS max_xmin_age FROM pg_stat_activity WHERE backend_xmin IS NOT NULL;- Ein Sperrstau lässt andere Sessions warten
- Angehäufte alte Versionen blockieren die Bereinigung (Undo/Bloat)
Ursache: Die Anwendung committet nicht (idle in transaction)
Connection Pool oder Anwendung öffnet eine Transaktion und hält die Verbindung dann inaktiv, ohne Commit/Rollback. Das hält Sperren und Undo unnötig fest.
Lösung: verantwortliche Session sicher beenden
Lässt eine blockierende/lange Session andere warten, muss sie kontrolliert beendet werden. Das Beenden löst ein Rollback aus, das Platz/Zeit brauchen kann; Vorsicht.
Ursache: eine einzelne riesige DML/ein Bericht
Eine DML, die Millionen Zeilen in einer Transaktion verarbeitet, oder ein sehr langer Bericht häuft über die gesamte Laufzeit Sperren und Undo/Bloat an.
Ursache: Warteschlange durch Sperrstau
Durch die gehaltenen Sperren hat die lange Transaktion eine Warteschlange hinter sich erzeugt; viele Sessions warten auf dieselbe Ressource.
Ursache: angehäufte alte Versionen (Undo/Bloat)
Die lang laufende Transaktion verhindert die Bereinigung alter Zeilenversionen, die älter sind als sie. In Postgres zeigt sich das als Tabellen-/Index-Bloat, in Oracle als Undo-Druck.