Para evitar deadlocks quando atualizações concorrentes no PostgreSQL se sobrepõem, bloqueie o conjunto completo de linhas-alvo de cada transação na mesma ordem de chave imutável antes de atualizar. Mantenha a transação em um único cliente node-postgres e repita desde o início as falhas elegíveis.

Este guia é para um desenvolvedor que trabalha no Terminal do macOS e precisa que dois lotes concorrentes atualizem completamente linhas sintéticas de artigos. O exemplo usa a documentação atual do PostgreSQL, que identifica o PostgreSQL 18, e a documentação atual do node-postgres. O escopo é uma lista conhecida de IDs de artigos existentes, que permanecem inalterados durante o trabalho concorrente. PostgreSQL: Bloqueio explícito

Por que atualizações sobrepostas podem causar deadlock

Considere duas transações: A atualiza o artigo 1 e, em seguida, o artigo 2; B atualiza o artigo 2 e, em seguida, o artigo 1. Se cada uma obtiver o bloqueio da primeira linha antes de solicitar a segunda, ambas aguardam um bloqueio mantido pela outra. O PostgreSQL detecta o ciclo e cancela uma das transações; a aplicação não pode escolher qual delas. Atualizações comuns podem produzir essa situação sem instruções explícitas de bloqueio. PostgreSQL: Bloqueio explícito

A distinção útil é entre sobreposição e ciclo. A sobreposição pode exigir espera. Um ciclo impede que qualquer transação prossiga. O PostgreSQL recomenda uma ordem consistente de aquisição de bloqueios entre as aplicações, adquirindo primeiro o modo de bloqueio mais forte necessário. PostgreSQL: Bloqueio explícito

Para a carga de trabalho deste artigo, adote uma regra: adquira o bloqueio de todas as linhas de artigos solicitadas em ordem crescente de id antes de alterar qualquer artigo. Aplique essa regra à transação inteira, incluindo quaisquer instruções anteriores que acessem artigos.

Bloqueie todo o conjunto-alvo antes da atualização

Ordenar um array JavaScript não instrui uma atualização SQL em lote a visitar as linhas nessa ordem. A sintaxe UPDATE do PostgreSQL não tem uma cláusula ORDER BY direta. A documentação demonstra o bloqueio ordenado por meio de uma consulta de seleção e explica que uma ordem compartilhada pode evitar deadlocks entre operações de atualização. PostgreSQL: UPDATE

Use uma instrução de bloqueio separada e aguardada dentro da transação. Esta transação SQL ilustrativa tem como alvo os artigos 1, 2 e 3; a tabela de configuração aparece na próxima seção.

BEGIN ISOLATION LEVEL READ COMMITTED;

SELECT id
FROM deadlock_demo_articles
WHERE id = ANY(ARRAY[3, 1, 2]::integer[])
ORDER BY id
FOR UPDATE;

UPDATE deadlock_demo_articles
SET revision = revision + 1
WHERE id = ANY(ARRAY[3, 1, 2]::integer[]);

COMMIT;

A primeira instrução estabelece a ordem de aquisição. Em seguida, a atualização opera sobre as linhas já bloqueadas por essa transação. Normalmente, os bloqueios de linha permanecem até o fim da transação. Mantenha esse intervalo curto: prepare os dados antes de iniciar e evite aguardar interações pelo computador enquanto os bloqueios estiverem ativos. PostgreSQL: Bloqueio explícito

As condições a seguir definem o contrato de ordenação do exemplo:

  • Todos os lotes concorrentes usam ordem crescente de id dos artigos.
  • Os IDs permanecem inalterados durante o trabalho concorrente.
  • Cada lote identifica todo o seu conjunto-alvo antes de bloquear as linhas.
  • Nenhuma instrução anterior já bloqueou linhas de artigos em outra ordem.
  • A tabela de exemplo não tem gatilhos nem operações em tabelas relacionadas que introduzam dependências adicionais de bloqueio.

Esses são pré-requisitos para aplicar esse raciocínio a esta carga de trabalho. Uma transação que bloqueia primeiro o artigo 9 e depois solicita os artigos de 1 a 3 já descumpriu o contrato, mesmo que sua consulta final contenha ORDER BY id.

Execute um exemplo completo com node-postgres no Mac

Os pré-requisitos são um servidor PostgreSQL local em execução, um banco de dados temporário existente, Node.js com npm e psql disponível no Terminal. Configure a conexão com o banco de dados existente por meio das variáveis de ambiente do PostgreSQL. Os pools e psql do node-postgres usam as mesmas variáveis de conexão; defina PGHOST explicitamente, se necessário, para que ambos os comandos se conectem ao mesmo servidor. node-postgres: Conexão

Em uma nova pasta de trabalho, instale a dependência:

npm init -y
npm install pg

Salve a configuração completa a seguir como setup.sql. Ela cria uma nova tabela de demonstração e três artigos sintéticos. Execute-a uma vez no banco de dados temporário. Ela falha deliberadamente se essa tabela já existir, em vez de substituir o conteúdo existente.

BEGIN;

CREATE TABLE deadlock_demo_articles (
    id integer PRIMARY KEY,
    title text NOT NULL,
    revision integer NOT NULL DEFAULT 0
);

INSERT INTO deadlock_demo_articles (id, title)
VALUES
    (1, 'Paper Shapes'),
    (2, 'Color Notes'),
    (3, 'Desk Sketches');

COMMIT;

psql -v ON_ERROR_STOP=1 -f setup.sql

Salve o programa completo a seguir como update-articles.mjs. Todas as instruções de uma tentativa usam o cliente retornado por pool.connect(). O node-postgres exige explicitamente que se use um único cliente durante toda a transação; executar instruções da transação por meio de pool.query() não preserva esse requisito. node-postgres: Transações

import pg from 'pg';
import { setTimeout as sleep } from 'node:timers/promises';

const { Pool } = pg;
const pool = new Pool({ max: 2, connectionTimeoutMillis: 5000 });
pool.on('error', error => {
  console.error('Idle database client error:', error.message);
  process.exitCode = 1;
});

function normalizeIds(input) {
  if (!Array.isArray(input) || input.length === 0 ||
      input.some(id => !Number.isInteger(id) ||
        id < 1 || id > 2147483647)) {
    throw new Error('Use a nonempty array of positive PostgreSQL integer IDs');
  }
  return [...new Set(input)].sort((a, b) => a - b);
}

async function incrementRevisions(input) {
  const ids = normalizeIds(input);
  const maxAttempts = 3;

  for (let attempt = 1; attempt <= maxAttempts; attempt += 1) {
    const client = await pool.connect();
    let discard = false;

    try {
      await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');

      const locked = await client.query(
        `SELECT id
         FROM deadlock_demo_articles
         WHERE id = ANY($1::integer[])
         ORDER BY id
         FOR UPDATE`,
        [ids]
      );

      if (locked.rows.length !== ids.length) {
        throw new Error('A requested article is missing; batch cancelled');
      }

      const updated = await client.query(
        `UPDATE deadlock_demo_articles
         SET revision = revision + 1
         WHERE id = ANY($1::integer[])
         RETURNING id, revision`,
        [ids]
      );

      if (updated.rowCount !== ids.length) {
        throw new Error('Unexpected update count; batch cancelled');
      }

      await client.query('COMMIT');
      return updated.rows.sort((a, b) => a.id - b.id);
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch (rollbackError) {
        discard = true;
        throw new Error('Rollback failed; connection discarded', {
          cause: rollbackError
        });
      }

      const retryable = error.code === '40P01' || error.code === '40001';
      if (!retryable || attempt === maxAttempts) throw error;
    } finally {
      client.release(discard);
    }

    // Illustrative bounded backoff, after releasing the client.
    await sleep(50 * 2 ** (attempt - 1) + Math.floor(Math.random() * 50));
  }
}

try {
  const outcomes = await Promise.allSettled([
    incrementRevisions([3, 1, 2]),
    incrementRevisions([3, 2])
  ]);

  for (const outcome of outcomes) {
    if (outcome.status === 'rejected') throw outcome.reason;
  }

  const result = await pool.query(
    'SELECT id, title, revision FROM deadlock_demo_articles ORDER BY id'
  );
  console.table(result.rows);
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await pool.end();
}

node update-articles.mjs

O array é passado como um único parâmetro e convertido explicitamente para integer[]; a documentação do node-postgres descreve a conversão de arrays JavaScript em valores de array do PostgreSQL. A remoção de duplicatas faz com que a quantidade solicitada corresponda ao número de artigos distintos. É o ORDER BY do banco de dados, e não a ordem do array de entrada, que estabelece a ordem de aquisição dos bloqueios. node-postgres: Consultas

Em uma tabela recém-inicializada, sem outros escritores e com ambos os lotes concluídos com sucesso, as revisões esperadas são 1 para o artigo 1 e 2 para os artigos 2 e 3. Esses totais decorrem dos artigos incluídos em cada lote, independentemente de qual lote termine primeiro. Execuções posteriores do script incrementam novamente os valores existentes. Se faltar um ID solicitado, o lote é cancelado em vez de atualizar silenciosamente apenas as linhas encontradas.

Cada lote é uma transação separada. Se um for concluído com sucesso e o outro falhar permanentemente, o lote bem-sucedido continuará confirmado. Promise.allSettled() permite que ambas as tentativas terminem antes de o programa fechar o pool. O pool.query() final é executado fora de qualquer uma das transações. A API do pool documenta como liberar clientes retirados do pool, destruir um cliente quando necessário e encerrar o pool após a conclusão do trabalho do script. node-postgres: Pool

As verificações de normalização e os cálculos ilustrativos de revisão foram executados com Node.js. O PostgreSQL não estava disponível no ambiente de pesquisa, portanto o SQL e o comportamento de concorrência do banco de dados foram conferidos com base na documentação, em vez de executados.

Preserve o nível de isolamento exigido pela sua tarefa

O programa usa explicitamente READ COMMITTED para atualizações com IDs fixos. Nesse nível, uma instrução de bloqueio pode aguardar uma atualização e, em seguida, bloquear a versão atualizada da linha, desde que ela ainda corresponda à condição. Cada instrução obtém um novo snapshot. Portanto, uma linha excluída antes da aquisição pode estar ausente; o programa trata esse caso verificando a quantidade. PostgreSQL: Isolamento de transações

Não reduza o nível de isolamento de uma transação existente apenas para usar este exemplo. Em REPEATABLE READ, tentar bloquear ou atualizar uma linha alterada desde o snapshot da transação pode causar uma falha de serialização. SERIALIZABLE também exige tratamento de novas tentativas e impede anomalias de serialização em transações confirmadas; isso não substitui a ordenação consistente de bloqueios de linha comuns. PostgreSQL: Isolamento de transações

Bloquear linhas conhecidas não garante que os elementos abrangidos por um predicado permaneçam os mesmos. Por exemplo, este exemplo não implementa “atualizar todos os artigos cujo título corresponda a uma condição, incluindo novas inserções concorrentes”. Essa é uma tarefa de consistência diferente. Defina os requisitos de isolamento e coordenação antes de adaptar a receita de IDs fixos.

Mantenha também imutável a coluna usada para ordenação. A documentação do PostgreSQL explica que, em READ COMMITTED, a ordenação ocorre antes do bloqueio; valores alterados enquanto a instrução aguarda podem fazer com que as linhas retornadas pareçam fora de ordem. O uso de IDs inalterados evita essa complicação específica. PostgreSQL: SELECT

Repita transações completas e investigue falhas recorrentes

O PostgreSQL identifica falhas de deadlock com o SQLSTATE 40P01 e falhas de serialização com 40001. Repita a transação completa, incluindo quaisquer decisões dependentes do banco de dados que determinem suas instruções ou valores. Repetir apenas a atualização final não atende a esse requisito. PostgreSQL: Tratamento de falhas de serialização

O exemplo permite três tentativas no total. O atraso exponencial curto e a variação aleatória são escolhas ilustrativas da aplicação, não padrões do PostgreSQL. O cliente é liberado antes do atraso; outros erros interrompem a execução imediatamente, e o esgotamento das tentativas é informado. O PostgreSQL alerta que as novas tentativas não garantem a conclusão eventual. PostgreSQL: Tratamento de falhas de serialização

Use este guia de decisão ao adaptar a implementação:

Situação Ação
Outro lote já mantém o bloqueio de uma linha solicitada Permita que a aquisição ordenada aguarde, se a tarefa exigir todas as linhas solicitadas.
Um ID solicitado não existe Reverta a transação e informe a condição de alvo ausente.
O SQLSTATE é 40P01 ou 40001 Reverta a transação e, em seguida, repita a transação elegível inteira, dentro de um limite definido.
As falhas continuam após a adoção do bloqueio ordenado Inspecione instruções anteriores, outros escritores, gatilhos e tabelas adicionais para encontrar desvios do contrato de ordenação.
A operação acessa várias tabelas Defina uma ordem compartilhada para as tabelas, além da ordem das linhas, para todos os caminhos concorrentes.
Um lote precisa ser confirmado atomicamente Mantenha uma única transação; confirmar blocos independentes mudaria a tarefa.

Evite adicionar SKIP LOCKED a este lote que precisa atualizar todos os alvos: ele omite linhas que não podem ser bloqueadas imediatamente. O PostgreSQL descreve seu uso em consumidores de filas e considera que ele não é adequado para uma visão geral consistente. NOWAIT informa um erro em vez de aguardar um bloqueio de linha conflitante; isso altera o comportamento de espera, mas não conclui o lote. PostgreSQL: SELECT

A implementação está pronta para ser adaptada quando você puder especificar todo o conjunto-alvo, manter inalteradas as chaves de ordenação e aplicar a mesma regra de aquisição entre transações concorrentes. Mantenha a verificação de linhas ausentes e as novas tentativas limitadas da transação inteira para que a contenção ou dados incompletos produzam um resultado explícito.

Fontes