Pular para o conteúdo
Setup do Dev
Guias

Idle in transaction no PostgreSQL: como achar a conexão parada que segura a trava

Uma transação aberta e esquecida trava as outras sem aparecer como lenta. Testamos com o PDO como achar o processo, derrubá-lo e evitar que volte.

Por Setup do Dev
4 min de leitura
Compartilhar
Capa cinza-azulada com o elefante do PostgreSQL, a manchete "A conexão parada que trava as outras" e uma tabela do pg_stat_activity com o estado idle in transaction.

Uma consulta lenta aparece no log de consultas lentas, no pg_stat_statements e no painel de monitoramento. Uma transação aberta e esquecida, não: a conexão fica parada, sem rodar nada, e mesmo assim segura as travas que pegou. As outras consultas é que ficam esperando, e são elas que parecem lentas.

O estado tem nome no pg_stat_activity: idle in transaction. Reproduzimos o caso no PostgreSQL 18 da máquina da redação, com o PDO do PHP 8.5 e duas conexões, sem criar tabela nenhuma. A trava foi uma trava consultiva (pg_advisory_xact_lock), que se comporta como a de uma linha para o que interessa aqui.

O caso montado

A primeira conexão, batizada de importador, abre a transação, pega a trava e para, como um script que começou a gravar e foi esperar uma API lenta:

$a->exec("SET application_name = 'importador'");
$a->beginTransaction();
$a->query('SELECT pg_advisory_xact_lock(4242)');
// ... e o script vai fazer outra coisa

A segunda, o painel, pede a mesma trava. E fica esperando, sem erro e sem prazo, porque o lock_timeout padrão é 0, sem limite.

Como achar quem está parado

A consulta que resolve é esta:

SELECT pid, application_name, state, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS bloqueado_por,
       now() - xact_start AS transacao_ha
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%' OR wait_event_type = 'Lock';

No teste, ela devolveu as duas pontas. O importador em idle in transaction, esperando o cliente (ClientRead). O painel em active, esperando uma trava (Lock, advisory), com bloqueado_por igual a {6728}, o pid do importador. A função pg_blocking_pids é o atalho: ela diz quem bloqueia quem, sem precisar cruzar o pg_locks à mão.

Linha do tempo com duas conexões. O importador abre a transação, pega a trava 4242 e fica em idle in transaction. O painel pede a mesma trava e espera, com wait_event advisory e pg_blocking_pids apontando o pid do importador. Quando o importador faz commit, o painel pega a trava, 2,9 segundos depois.
O teste: o importador abriu a transação e parou; o painel ficou esperando a trava até o importador terminar

O LIKE no filtro tem motivo. A documentação do pg_stat_activity tem um segundo estado, idle in transaction (aborted), para a transação em que uma consulta já deu erro e que ninguém desfez. É o caso clássico do worker de fila que pega a exceção no catch, registra e vai esperar o próximo item sem chamar o rollBack(): a conexão fica parada do mesmo jeito.

O application_name parece detalhe, mas foi o que fez a linha ter sentido. Sem ele, a conexão parada é só um pid e um IP, e descobrir qual parte do sistema a abriu vira trabalho de detetive.

A armadilha do PDO

A coluna query do pg_stat_activity mostra a última consulta da conexão, e seria a pista de onde o código parou. Com o PDO no padrão, ela mostrou isto:

DEALLOCATE pdo_stmt_00000002

O PDO prepara cada consulta no servidor e, ao terminar, manda o DEALLOCATE, que vira “a última consulta”. Com PDO::ATTR_EMULATE_PREPARES ligado, a mesma coluna mostrou SELECT pg_advisory_xact_lock(4242). Não vale trocar o modo de preparo só por isso, mas vale saber que o DEALLOCATE ali não é o culpado: é o rastro do PDO, e o application_name passa a ser a pista principal.

Derrubar e evitar que volte

Para resolver na hora, SELECT pg_terminate_backend(6728) encerra a conexão e desfaz a transação. No teste, a trava foi liberada na hora, e o importador, ao tentar a consulta seguinte, recebeu: FATAL: terminando a conexão devido a um comando do administrador.

Para não depender de alguém olhando, existe o idle_in_transaction_session_timeout, que encerra sozinho a sessão parada dentro de uma transação por mais tempo que o limite. Com 2 segundos e uma pausa de 4, a conexão de teste caiu. Aqui veio a segunda surpresa: o PHP não recebeu o motivo, só server closed the connection unexpectedly, a mesma mensagem de um servidor que caiu. O motivo de verdade fica no log do PostgreSQL. Se essa mensagem começar a aparecer no log da aplicação depois de ligar o limite, o limite está funcionando.

A documentação dá um segundo motivo para ligá-lo: mesmo sem trava importante, uma transação aberta impede o VACUUM de limpar as linhas mortas que ela ainda poderia ver, e a tabela incha. Em todos os limites, o padrão é 0, desligado:

ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app SET lock_timeout = '5s';

Por usuário do banco, e não no postgresql.conf, para não pegar a sessão de quem faz manutenção. O lock_timeout é a outra metade: em vez de esperar sem prazo, o painel do teste desistiu em 2,0 segundos com SQLSTATE[55P03]: Lock not available, um erro que a aplicação pode tratar. O transaction_timeout, que limita a transação inteira, existe desde o PostgreSQL 17, mas a própria documentação desaconselha ligá-lo para todas as sessões.

No código, a causa costuma ser a mesma: algo lento entre o beginTransaction() e o commit(), como chamar uma API, mandar e-mail ou processar um arquivo. A regra que evita quase todos os casos é buscar e calcular tudo antes, e abrir a transação só para gravar. É o mesmo raciocínio da trava da sessão do PHP: segurar um recurso enquanto se espera por outra coisa.

Fontes: Documentação do PostgreSQL: configuração das conexões, Documentação do PostgreSQL: pg_stat_activity, Documentação do PostgreSQL: pg_blocking_pids

Produzido com auxílio de IA e revisado pela redação.

Comentários (0)

Ainda não há comentários. Comece a conversa.

Deixe seu comentário

Seu e-mail não será publicado. O primeiro comentário de cada pessoa passa por moderação.

Seja respeitoso

Neste artigo

  1. O caso montado
  2. Como achar quem está parado
  3. A armadilha do PDO
  4. Derrubar e evitar que volte

Mais lidas

Editorias

Leia também

Newsletter semanal

O essencial da tecnologia, toda sexta no seu e-mail

Uma curadoria da redação com as notícias que importam para quem desenvolve, joga e trabalha com tecnologia. Sem spam.

Ao assinar, você concorda com a política de privacidade. Cancele quando quiser.