Transaction longue / ouverte
Une transaction longue ou ouverte accumule verrous, undo et bloat. Nous trouvons la transaction fautive, déterminons son type (idle in transaction, long batch, impact) et la résolvons en toute sécurité.
Quelle est la plus ancienne transaction ouverte ?
Listez les transactions ouvertes par heure de début. La plus ancienne est généralement la source du problème. Vérifiez son état : s'exécute-t-elle activement, ou est-elle 'idle in transaction' en attente d'un 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 oublié, inactive)
- Batch/DML actif mais très long
- Accumulation de verrous/bloat/undo pendant que la transaction se poursuit
Pourquoi l'état idle in transaction survient-il ?
La session a ouvert une transaction mais reste inactive sans exécuter de requête ; autrement dit, le commit/rollback a été oublié. Cela retient inutilement verrous et undo. Une correction applicative suffit-elle, ou faut-il la terminer d'urgence ?
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;- L'application/le pool de connexions ne valide pas (correction permanente)
- Je dois la terminer immédiatement
Que fait la transaction longue active ?
La session s'exécute réellement (active). S'agit-il d'un seul DML/rapport énorme, ou d'un bloqueur qui fait attendre d'autres sessions et doit être coupé d'urgence ?
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;- Un seul DML/rapport énorme ; il faut le découper
- Il fait attendre les autres ; terminez-la d'urgence
Qu'accumule la transaction longue ?
Déterminez l'effet secondaire de la transaction longue : s'agit-il surtout d'une file de verrous faisant attendre d'autres sessions, ou d'un bloat dû à la conservation d'anciennes versions de lignes qui bloque le nettoyage (VACUUM/undo) ?
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;- Une file de verrous fait attendre d'autres sessions
- L'accumulation d'anciennes versions bloque le nettoyage (undo/bloat)
Cause racine : l'application ne valide pas (idle in transaction)
Le pool de connexions ou l'application ouvre une transaction puis garde la connexion inactive sans commit/rollback. Cela retient inutilement verrous et undo.
Solution : terminez la session fautive en toute sécurité
Si une session bloquante/longue fait attendre les autres, elle doit être terminée de façon contrôlée. La terminaison déclenche un rollback, qui peut nécessiter espace/temps ; prudence.
Cause racine : un seul DML/rapport énorme
Un DML qui traite des millions de lignes en une seule transaction, ou un rapport très long, accumule verrous et undo/bloat pendant toute sa durée.
Cause racine : file d'attente de verrous
Du fait des verrous qu'elle détient, la transaction longue a créé une file d'attente derrière elle ; de nombreuses sessions attendent la même ressource.
Cause racine : accumulation d'anciennes versions (undo/bloat)
La transaction longue empêche le nettoyage des anciennes versions de lignes antérieures à elle. Dans Postgres, cela se traduit par du bloat de tables/d'index, dans Oracle par une pression undo.