Blog

Advisory lock que fica preso no pool: pg_advisory_lock de sessão, PgBouncer e o job que nunca mais roda

Uma plataforma de cobrança rodava a conciliação de pagamentos a cada 5 minutos, em 3 instâncias, e usava pg_try_advisory_lock para garantir que só uma delas processasse o lote. Funcionou por dois anos. Na semana em que o time colocou o PgBouncer em modo transação na frente do Postgres, para parar de esgotar conexões nos picos, a conciliação simplesmente parou: durante 19 horas todas as execuções registraram "outra instância já está rodando" e saíram sem erro, enquanto 11 mil pagamentos ficavam pendentes. Nenhuma instância estava rodando. A trava estava presa numa conexão do servidor que o PgBouncer continuava entregando, a cada poucos segundos, para requisições da API que nem sabiam que seguravam a chave. Dois dias depois, já com a trava liberada à mão, apareceu o defeito oposto: duas instâncias processaram o mesmo lote ao mesmo tempo. Este artigo explica por que advisory lock de sessão e pool de conexões não combinam, o que o PgBouncer faz com o estado da sessão, como encontrar quem segura a trava e como escolher entre trava de transação, conexão dedicada e lease em tabela.

2026-11-22 / Arquitetura / 12 min

01

O job que parou de rodar sem nenhum erro

O código do job era curto e parecia correto: tentar a trava, sair se outra instância a tivesse, processar e liberar num finally. O detalhe que mudou tudo é que as duas chamadas usavam pool.query, e cada pool.query pega qualquer conexão livre. Antes do PgBouncer, o pool do node-postgres com poucas conexões quase sempre devolvia a mesma, e quando não devolvia a conexão ociosa era fechada em 10 segundos, levando a trava junto. O problema existia, mas se curava sozinho.

// Job de conciliação: 3 instâncias, agendado a cada 5 minutos, usando o pool compartilhado.
// Cada pool.query pode cair numa conexão diferente do servidor.
const CHAVE = 'job:conciliacao';

export async function conciliar(pool) {
  const { rows } = await pool.query(
    'SELECT pg_try_advisory_lock(hashtextextended($1, 0)) AS obtida',
    [CHAVE],
  );
  if (!rows[0].obtida) {
    console.info('outra instância já está rodando a conciliação');
    return;
  }
  try {
    await processarLote(pool);
  } finally {
    // O unlock vai para outra conexão: o Postgres responde false e só registra um WARNING.
    await pool.query('SELECT pg_advisory_unlock(hashtextextended($1, 0))', [CHAVE]);
  }
}

// Postgres log:
// WARNING:  you don't own a lock of type ExclusiveLock

Com o PgBouncer em modo transação, cada comando fora de BEGIN é uma transação, e cada transação pode ser atendida por uma conexão diferente do servidor. A trava foi obtida na conexão S1; o unlock foi executado na S2. O Postgres respondeu false, registrou um WARNING que ninguém monitorava e a função retornou normalmente. A S1 voltou ao pool segurando a chave, e como o time tinha subido server_lifetime para 24 horas para evitar reconexões, ela continuou viva e ocupada atendendo a API.

  • Sintoma 1: o job sai sem erro em todas as instâncias, com a mensagem de que outra está rodando.
  • Sintoma 2: pg_locks mostra uma trava advisory concedida a um backend idle que roda consultas de outras rotas.
  • Sintoma 3, mais raro e pior: duas instâncias processando o mesmo lote, porque a trava é reentrante dentro da mesma sessão.

02

Advisory lock de sessão não conhece a transação

O Postgres oferece dois níveis de advisory lock. O de sessão, obtido com pg_advisory_lock ou pg_try_advisory_lock, pertence à conexão: sobrevive a COMMIT e ROLLBACK e só é liberado por pg_advisory_unlock na mesma sessão ou quando a conexão termina. O de transação, com pg_advisory_xact_lock ou pg_try_advisory_xact_lock, pertence à transação e some no fim dela; não existe função de unlock para ele.

FunçãoDuraçãoLiberada porSe a chave está ocupadaSeguro com PgBouncer em modo transação
pg_advisory_lock(k)Sessãopg_advisory_unlock na mesma sessão ou fim da conexãoEspera, limitado só por lock_timeoutNão
pg_try_advisory_lock(k)Sessãopg_advisory_unlock na mesma sessão ou fim da conexãoDevolve false na horaNão
pg_advisory_xact_lock(k)TransaçãoCOMMIT ou ROLLBACKEspera, limitado só por lock_timeoutSim
pg_try_advisory_xact_lock(k)TransaçãoCOMMIT ou ROLLBACKDevolve false na horaSim

Duas propriedades da trava de sessão explicam os dois defeitos. A primeira: pg_advisory_unlock chamado numa sessão que não tem a trava não é erro, é false com WARNING, então o código não percebe que nada foi liberado. A segunda: a trava é reentrante e contada. Se a mesma sessão chamar pg_try_advisory_lock duas vezes, as duas retornam true e são necessários dois unlocks. Isso foi feito para código que aninha chamadas, mas atrás de um pooler significa que qualquer cliente que cair na conexão que já segura a chave também a obtém.

03

O que o PgBouncer em modo transação faz com a trava

No modo transação, a conexão do servidor fica com o cliente só enquanto a transação dura. No COMMIT, ela volta ao pool e o próximo cliente que abrir uma transação pode recebê-la. Tudo que é estado de sessão vai junto: SET sem LOCAL, prepared statements nomeados, tabelas temporárias e advisory locks de sessão. O diagrama mostra a sequência do incidente.

instância 1 (job)            PgBouncer           conexões do servidor
------------------           ---------           --------------------
pg_try_advisory_lock(k)  ----------------------> S1   true  (S1 segura k, contador 1)
processa o lote ...
pg_advisory_unlock(k)    ----------------------> S2   false + WARNING (S2 não tem k)

API: BEGIN ... COMMIT    ----------------------> S1   cliente qualquer herda k sem saber

instância 2, 5 min depois:
pg_try_advisory_lock(k)  ----------------------> S3   false  "outra instância já está rodando"

instância 3, outro dia:
pg_try_advisory_lock(k)  ----------------------> S1   true   (mesma sessão, contador 2)
instância 1, ao mesmo tempo:
pg_try_advisory_lock(k)  ----------------------> S1   true   (contador 3: dois jobs juntos)

O PgBouncer tem uma consulta de reset, por padrão DISCARD ALL, que inclui pg_advisory_unlock_all. Mas em modo transação ela só roda se server_reset_query_always estiver ligado, e ligar isso não resolve: a trava passaria a ser liberada no fim de cada transação, ou seja, logo depois do pg_try_advisory_lock, e o job processaria o lote sem exclusão nenhuma. No modo sessão a consulta de reset roda quando o cliente desconecta, o que libera a trava, mas o pool da aplicação continua podendo mandar lock e unlock por conexões diferentes.

[pgbouncer]
pool_mode = transaction

; Em modo transação, a consulta de reset não roda entre transações:
server_reset_query = DISCARD ALL
server_reset_query_always = 0
; Ligar isso não corrige: a trava passaria a durar uma única transação, e o job perderia a exclusão durante o processamento.

; Conexão do servidor reaproveitada por um dia inteiro, levando junto qualquer trava de sessão esquecida.
server_lifetime = 86400
ConfiguraçãoOnde a trava de sessão ficaQuando é liberada se o unlock falhar
pool.query do node-postgres direto no PostgresNuma conexão qualquer do pool da aplicaçãoQuando a conexão fica ociosa por idleTimeoutMillis (10 s por padrão) ou nunca, se o pool está sempre ocupado
PgBouncer em modo sessãoNa conexão do servidor ligada ao clienteQuando o cliente desconecta e o DISCARD ALL roda
PgBouncer em modo transaçãoNa conexão do servidor que atendeu aquela transaçãoSó quando o PgBouncer fecha a conexão do servidor, por server_lifetime ou server_idle_timeout
Conexão dedicada direta (pg.Client)Na sessão do próprio jobNo unlock ou quando o job encerra a conexão

04

Encontrar quem segura a chave

A trava aparece em pg_locks com locktype advisory. Uma chave bigint é dividida em duas colunas de 32 bits, classid e objid, com objsubid igual a 1; a forma com dois inteiros usa objsubid igual a 2. Calcular a chave no próprio SQL, do mesmo jeito que o job calcula, evita conferir números à mão. O cruzamento com pg_stat_activity é o que entrega o diagnóstico.

-- Chave da trava calculada do mesmo jeito que o job calcula.
-- Uma chave bigint aparece dividida em classid (32 bits altos) e objid (32 bits baixos), com objsubid = 1.
WITH alvo AS (SELECT hashtextextended('job:conciliacao', 0) AS k)
SELECT l.pid,
       l.granted,
       a.client_addr,
       a.state,
       now() - a.backend_start AS idade_conexao,
       now() - a.state_change  AS parado_ha,
       left(a.query, 50)       AS ultima_consulta
  FROM pg_locks l
  JOIN pg_stat_activity a ON a.pid = l.pid
 CROSS JOIN alvo
 WHERE l.locktype = 'advisory'
   AND l.objsubid = 1
   AND l.classid::bigint = (alvo.k >> 32) & 4294967295
   AND l.objid::bigint   = alvo.k & 4294967295;

  pid  | granted | client_addr | state | idade_conexao | parado_ha | ultima_consulta
-------+---------+-------------+-------+---------------+-----------+---------------------------------
 48121 | t       | 10.0.3.7    | idle  | 19:42:10      | 00:00:02  | SELECT id, status FROM pedidos W
-- Resultado: a conexão do PgBouncer segura a trava há 19 horas e acabou de atender outra rota.

-- Libera a trava encerrando o backend que a segura (está idle, nenhuma transação é cortada).
SELECT pg_terminate_backend(48121);

Três colunas contam a história. client_addr é o endereço do PgBouncer, não de uma instância do job. idade_conexao é de horas, mas parado_ha é de segundos, porque a conexão é reutilizada o tempo todo. E ultima_consulta é de outra rota da aplicação. Uma trava de sessão segura por um backend nessas condições é um vazamento, não um job em andamento. Encerrar esse backend com pg_terminate_backend libera a trava; o PgBouncer detecta a conexão encerrada e abre outra. Como o estado é idle, nenhuma transação é cortada, mas confira isso antes, porque se estiver active a requisição em curso recebe erro.

  • Alerta útil: trava advisory concedida a backend com state idle há mais tempo que a duração máxima do job.
  • Outro sinal barato: contar no log do Postgres o WARNING you don't own a lock of type ExclusiveLock. Ele só aparece quando um unlock foi para a sessão errada.
  • Dê application_name às conexões dos jobs. Atrás do PgBouncer em modo transação, o nome que aparece é o da primeira conexão, o que já indica que a trava não está onde deveria.

05

A correção padrão: trava de transação na mesma conexão

Para a maioria dos jobs, a correção é trocar a trava de sessão por pg_try_advisory_xact_lock e fazer o trabalho dentro da mesma transação, num client obtido com pool.connect. A trava passa a ter a mesma vida da transação: COMMIT ou ROLLBACK a liberam, e se o processo morrer a conexão cai, a transação é desfeita e a trava some. Funciona igual com ou sem PgBouncer, porque em modo transação a conexão do servidor fica presa ao cliente justamente até o fim da transação.

import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 10 });

// Trava de transação: mesma conexão do início ao fim e liberação garantida no COMMIT ou no ROLLBACK.
export async function executarComTrava(nome, tarefa) {
  const client = await pool.connect();
  let descartar = false;
  try {
    await client.query('BEGIN');
    // Limite para a transação inteira não virar uma transação longa esquecida.
    await client.query("SET LOCAL statement_timeout = '10min'");
    const { rows } = await client.query(
      'SELECT pg_try_advisory_xact_lock(hashtextextended($1, 0)) AS obtida',
      [nome],
    );
    if (!rows[0].obtida) {
      // Outra instância está com a trava: encerra a transação e sai sem erro.
      await client.query('ROLLBACK');
      return { executou: false };
    }
    // A tarefa recebe o client para que todo o trabalho fique dentro da mesma transação.
    const resultado = await tarefa(client);
    await client.query('COMMIT');
    return { executou: true, resultado };
  } catch (erro) {
    try {
      await client.query('ROLLBACK');
    } catch {
      // Se nem o ROLLBACK funcionou, a conexão está em estado desconhecido: descarta em vez de devolver ao pool.
      descartar = true;
    }
    throw erro;
  } finally {
    client.release(descartar);
  }
}

// uso / usage / uso:
// await executarComTrava('job:conciliacao', (client) => processarLote(client));

Dois cuidados. O primeiro é que o trabalho precisa usar o client recebido; se a tarefa chamar pool.query, ela roda fora da transação e fora da proteção. O segundo é o custo: o job vira uma transação aberta pelo tempo do processamento. Para alguns segundos ou poucos minutos isso é aceitável, e statement_timeout com SET LOCAL limita o pior caso. Para dezenas de minutos, uma transação longa segura o horizonte do vacuum, prende locks de linha e ocupa uma conexão do pool, e aí vale uma das alternativas da próxima seção.

06

Jobs longos: conexão dedicada ou lease em tabela

Quando o job leva muito tempo e não deve rodar numa única transação, a trava de sessão continua sendo uma boa ferramenta, desde que a sessão seja do job. Uma conexão própria, aberta direto no Postgres, sem PgBouncer, segura a trava enquanto o trabalho roda com o pool normal. O fim da conexão é a garantia final: se o processo morrer, o Postgres encerra a sessão e libera a trava. Para quedas de rede em que o processo some sem fechar o socket, configure keepAlive no cliente e tcp_keepalives_idle no servidor, senão o backend pode ficar vivo esperando um cliente que não existe mais.

import pg from 'pg';

// Job longo: conexão própria, direto no Postgres (porta 5432), sem passar pelo PgBouncer.
export async function executarComTravaDeSessao(nome, tarefa) {
  const client = new pg.Client({
    connectionString: process.env.DATABASE_URL_DIRETA,
    keepAlive: true,
    // O nome aparece em pg_stat_activity e identifica o dono da trava no diagnóstico.
    application_name: 'trava:' + nome,
  });
  await client.connect();
  try {
    const { rows } = await client.query(
      'SELECT pg_try_advisory_lock(hashtextextended($1, 0)) AS obtida',
      [nome],
    );
    if (!rows[0].obtida) return { executou: false };
    try {
      // O trabalho pode usar o pool normal: a trava só precisa da conexão dedicada viva.
      return { executou: true, resultado: await tarefa() };
    } finally {
      await client.query('SELECT pg_advisory_unlock(hashtextextended($1, 0))', [nome]);
    }
  } finally {
    // Encerrar a conexão libera qualquer trava de sessão que tenha sobrado, mesmo se o unlock falhar.
    await client.end();
  }
}

Quando não há acesso direto ao Postgres, como em alguns serviços gerenciados que só expõem o pooler, ou quando o job atravessa vários processos, a saída é uma trava em tabela com prazo de validade. Ela não depende de conexão aberta, sobrevive a qualquer pooler e expira sozinha se o dono morrer. Em troca, o job precisa renovar o prazo enquanto trabalha e parar se a renovação falhar.

-- Alternativa sem conexão aberta: trava com prazo de validade em uma tabela.
CREATE TABLE travas_job (
  nome      text PRIMARY KEY,
  dono      text NOT NULL,
  expira_em timestamptz NOT NULL
);

-- Adquire se não existe ou se a trava anterior venceu; devolve uma linha só quando conseguiu.
INSERT INTO travas_job (nome, dono, expira_em)
VALUES ($1, $2, now() + interval '2 minutes')
ON CONFLICT (nome) DO UPDATE
   SET dono = EXCLUDED.dono, expira_em = EXCLUDED.expira_em
 WHERE travas_job.expira_em < now()
RETURNING dono;

-- Renovação periódica: zero linhas atualizadas significa que a trava foi perdida e o job deve parar.
UPDATE travas_job
   SET expira_em = now() + interval '2 minutes'
 WHERE nome = $1 AND dono = $2;

-- Liberação: só o dono atual apaga.
DELETE FROM travas_job WHERE nome = $1 AND dono = $2;
  1. Liste os pontos que usam pg_advisory_lock ou pg_try_advisory_lock e verifique se lock e unlock passam pela mesma conexão.
  2. Troque por pg_try_advisory_xact_lock os jobs que cabem numa transação curta.
  3. Mova os jobs longos para uma conexão dedicada direta ou para lease em tabela com renovação.
  4. Alerte sobre travas advisory concedidas a backends idle e sobre o WARNING de unlock na sessão errada.
  5. Antes de mudar o pool_mode do PgBouncer, procure também SET de sessão, prepared statements nomeados e tabelas temporárias: todos quebram pelo mesmo motivo.

FAQ

Perguntas frequentes

Posso usar pg_advisory_xact_lock num job que leva 40 minutos?

Funciona, mas o custo aparece em outro lugar. Uma transação aberta por 40 minutos impede o vacuum de limpar tuplas mortas geradas depois do seu início em todo o banco, segura os locks de linha que o job pegar e mantém uma conexão ocupada. Para jobs desse tamanho, prefira uma conexão dedicada com trava de sessão, ou lease em tabela, e processe em lotes com commits curtos.

Como escolher a chave de um advisory lock?

As chaves são globais no banco, então dois sistemas que usam o mesmo número se bloqueiam sem saber. Use um nome legível com prefixo do domínio, como job:conciliacao, e converta com hashtextextended(nome, 0), que gera um bigint e torna colisões improváveis. Evite hashtext, que tem só 32 bits. Se quiser separar por espaço de nomes, a forma com dois inteiros, pg_try_advisory_xact_lock(classe, id), deixa a primeira chave para o tipo de recurso.

Em modo sessão do PgBouncer, ou sem PgBouncer, o problema desaparece?

Diminui, mas não desaparece. Sem o pooler na frente, o pool da própria aplicação ainda pode mandar lock e unlock por conexões diferentes, como no pool.query do exemplo. A trava presa dura menos porque a conexão ociosa é fechada, mas sob carga constante ela pode nunca ficar ociosa. A regra vale em qualquer arquitetura: trava de sessão só com uma conexão obtida explicitamente e usada do lock ao unlock.

A trava vive onde a sessão vive

Advisory lock de sessão pertence à conexão, não ao processo nem à transação. Atrás de um pool, e principalmente do PgBouncer em modo transação, essa conexão passa de mão em mão, e a trava vai junto: o unlock falha em silêncio, o job para de rodar e, por ser reentrante, outro cliente pode obter a mesma chave. Trava de transação na mesma conexão resolve a maioria dos casos; conexão dedicada direta ou lease em tabela cobrem os jobs longos. E vale procurar travas concedidas a backends idle antes que a fila de pendências avise por você.