Transação longa / aberta

Uma transação longa ou aberta está acumulando locks, undo e bloat. Encontramos a transação responsável, determinamos seu tipo (idle in transaction, lote longo, impacto) e a resolvemos com segurança.

Qual é a transação aberta mais antiga?

Liste as transações abertas por horário de início. A mais antiga geralmente é a origem do problema. Verifique seu estado: está em execução ativa ou está 'idle in transaction' esperando um 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 esquecido, parada ociosa)
• Lote/DML ativo, mas muito longo
• Acumulando locks/bloat/undo enquanto a transação continua

Por que ocorre idle in transaction?

A sessão abriu uma transação, mas fica ociosa sem executar consultas; ou seja, o commit/rollback foi esquecido. Isso segura locks e undo sem necessidade. Basta uma correção na aplicação, ou é preciso encerrá-la com urgência?

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;

• A aplicação/connection pool não faz commit (correção permanente)
• Preciso encerrá-la agora mesmo

O que a transação longa ativa está fazendo?

A sessão está de fato em execução (ativa). É um único DML/relatório enorme, ou um bloqueador que está fazendo outras sessões esperarem e precisa ser cortado com urgência?

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;

• Um único DML/relatório enorme; precisa ser dividido
• Está fazendo outras esperarem; encerre com urgência

O que a transação longa está acumulando?

Determine o efeito colateral da transação longa: é principalmente um acúmulo de locks fazendo outras sessões esperarem, ou bloat por segurar versões antigas de linhas e impedir a limpeza (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;

• Um acúmulo de locks está fazendo outras sessões esperarem
• O acúmulo de versões antigas está impedindo a limpeza (undo/bloat)

Causa raiz: a aplicação não faz commit (idle in transaction)

O pool de conexões ou a aplicação abre uma transação e depois mantém a conexão ociosa sem commit/rollback. Isso segura locks e undo sem necessidade.

Solução: encerre a sessão responsável com segurança

Se uma sessão bloqueadora/longa está fazendo outras esperarem, ela deve ser encerrada de forma controlada. O encerramento dispara um rollback, que pode precisar de espaço/tempo; tenha cuidado.

Causa raiz: um único DML/relatório enorme

Um DML que processa milhões de linhas em uma única transação, ou um relatório muito longo, acumula locks e undo/bloat durante toda a sua duração.

Causa raiz: fila de acúmulo de locks

Por causa dos locks que segura, a transação longa criou uma fila de espera atrás de si; muitas sessões estão esperando pelo mesmo recurso.

Causa raiz: acúmulo de versões antigas (undo/bloat)

A transação longa impede a limpeza das versões antigas de linhas anteriores a ela. No Postgres, isso aparece como bloat de tabelas/índices; no Oracle, como pressão em undo.