Como Identificar e Resolver Deadlocks e Contenção de Locks no PostgreSQL

Deadlocks e contenção de locks no PostgreSQL geralmente se manifestam como uma aplicação que de repente parece travada. As requisições se acumulam, os workers ficam em estado active ou idle in transaction, e o próprio banco de dados pode ainda ter bastante CPU disponível. O problema não é capacidade bruta. Uma sessão está esperando que outra sessão libere um lock, e às vezes uma fila inteira se forma atrás dela.

A maneira mais rápida de lidar com isso é separar dois casos. Contenção de lock significa que uma sessão está esperando, mas pode eventualmente continuar. Um deadlock significa que duas ou mais sessões estão esperando umas pelas outras em um ciclo, então o PostgreSQL precisa cancelar uma transação. Você depura ambos com as mesmas ferramentas básicas, mas a correção geralmente é diferente.

Fundamentos de Lock no PostgreSQL

O PostgreSQL usa locks para proteger tabelas, linhas, transações e outros objetos internos enquanto muitas sessões trabalham ao mesmo tempo. Ele também usa MVCC, então leitores e escritores comuns geralmente não bloqueiam uns aos outros. É por isso que o PostgreSQL pode lidar bem com alta concorrência, mas também é por isso que problemas de lock podem ser confusos: o problema geralmente é um padrão de instrução específico, não "muitos usuários" em geral.

Tipos de Locks

O PostgreSQL utiliza vários níveis de lock, cada um oferecendo um grau diferente de proteção. Entendê-los é fundamental para diagnosticar problemas:

AccessShareLock: Usado por SELECT comum. Conflita principalmente com ACCESS EXCLUSIVE, razão pela qual muitas leituras podem ser executadas enquanto escritas estão ocorrendo.
RowExclusiveLock: Comum para INSERT, UPDATE e DELETE em uma tabela. O nome é fácil de interpretar erroneamente; não significa que toda linha na tabela está exclusivamente bloqueada.
ShareUpdateExclusiveLock: Usado por operações como VACUUM sem FULL, ANALYZE e algumas operações de índice. Permite leituras e escritas normais, mas conflita com várias operações de manutenção.
ShareLock / ShareRowExclusiveLock / ExclusiveLock: Modos de nível de tabela mais fortes usados por DDL específico e operações relacionadas a restrições.
AccessExclusiveLock: O lock de tabela mais restritivo. ALTER TABLE, DROP TABLE, TRUNCATE e VACUUM FULL podem adquirir esse tipo de lock. Ele bloqueia leituras comuns, bem como escritas.

Locks em nível de linha são separados dos modos de lock em nível de tabela. Um UPDATE adquire um RowExclusiveLock em nível de tabela e locks de linha nas linhas que ele altera. Quando as pessoas dizem "esta linha está bloqueada", geralmente significa que outra transação modificou ou selecionou aquela linha com FOR UPDATE e ainda não foi confirmada.

Modos de Lock

Os modos de lock indicam o tipo de acesso que uma transação requer. Eles são frequentemente representados por nomes como RowExclusiveLock, ShareLock, ExclusiveLock, etc.

Identificando Contenção de Lock e Sessões Bloqueadoras

A contenção de lock ocorre quando múltiplas transações estão esperando por locks mantidos por outras transações. Isso pode desacelerar significativamente sua aplicação. A visão do sistema pg_locks é sua principal ferramenta para diagnosticar esses problemas.

Usando `pg_locks`

A visão pg_locks fornece informações sobre todos os locks ativos no sistema de banco de dados. É inestimável para entender quais sessões estão mantendo locks e quais estão esperando.

Aqui está uma consulta comum para identificar sessões bloqueadoras:

SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_locks.locktype AS blocked_locktype,
    blocked_locks.virtualtransaction AS blocked_vtx,
    blocked_locks.mode AS blocked_mode,
    blocked_activity.query AS blocked_statement,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_locks.locktype AS blocking_locktype,
    blocking_locks.virtualtransaction AS blocking_vtx,
    blocking_locks.mode AS blocking_mode,
    blocking_activity.query AS blocking_statement
FROM
    pg_catalog.pg_locks blocked_locks
JOIN
    pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN
    pg_catalog.pg_locks blocking_locks
ON
    blocking_locks.locktype = blocked_locks.locktype AND
    blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND
    blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND
    blocking_locks.offset IS NOT DISTINCT FROM blocked_locks.offset AND
    blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND
    blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
JOIN
    pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE
    NOT blocked_locks.granted
    AND blocking_locks.pid != blocked_locks.pid;

Explicação da consulta:

Juntamos pg_locks com pg_stat_activity duas vezes: uma para o processo bloqueado e outra para o processo bloqueador.
A cláusula WHERE NOT blocked_locks.granted filtra para locks que estão atualmente sendo esperados.
A condição blocking_locks.pid != blocked_locks.pid garante que não reportemos uma sessão bloqueando a si mesma.
As condições de junção em pg_locks correspondem a locks no mesmo recurso.

Interpretando a Saída

blocked_pid / blocking_pid: Os IDs de processo (PIDs) das sessões envolvidas.
blocked_user / blocking_user: Os usuários associados a esses PIDs.
blocked_statement / blocking_statement: As consultas SQL que estão atualmente em execução ou esperando.
blocked_mode / blocking_mode: Os modos de lock solicitados e mantidos.

Se esta consulta retornar linhas, você tem contenção de lock. O blocking_pid está mantendo um lock que o blocked_pid está esperando.

Entendendo e Resolvendo Deadlocks

Um deadlock ocorre quando duas ou mais transações estão cada uma esperando por um lock mantido por outra transação no ciclo, criando uma dependência circular que nenhuma delas pode resolver sozinha. O PostgreSQL detecta deadlocks e os resolve automaticamente abortando uma das transações, tipicamente aquela que está causando o deadlock e fez menos trabalho.

Cenários Comuns de Deadlock

Duas transações atualizando linhas diferentes em tabelas diferentes em ordem inversa:
- Transação A: Atualiza a linha X na Tabela 1, então tenta atualizar a linha Y na Tabela 2.
- Transação B: Atualiza a linha Y na Tabela 2, então tenta atualizar a linha X na Tabela 1. Se a Transação A bloquear a linha X e a Transação B bloquear a linha Y, elas entrarão em deadlock quando tentarem adquirir o lock mantido pela outra.
UPDATE seguido por SELECT ... FOR UPDATE:
- Transação A: Atualiza uma linha.
- Transação B: Executa SELECT ... FOR UPDATE na mesma linha. Se o UPDATE ainda estiver mantendo um lock exclusivo de linha quando o SELECT FOR UPDATE tentar adquirir um lock compartilhado, e outras dependências existirem, um deadlock pode ocorrer.

Detectando Deadlocks

O PostgreSQL registra informações de deadlock em seu log do servidor. Você tipicamente verá mensagens como:

ERROR:  deadlock detected
DETAIL:  Process 1234 waits for ShareLock on transaction 5678; blocked by process 5679.
Process 5679 waits for ExclusiveLock on tuple (0,1) of relation 12345; blocked by process 1234.
HINT:  See server log for detail.

O PostgreSQL automaticamente escolhe uma transação para abortar para que as outras possam continuar. Não confie em pg_stat_activity após o fato para a história completa; no momento em que você olhar, a instrução cancelada pode ter desaparecido. O log do servidor é geralmente o melhor registro do ciclo.

Resolvendo Deadlocks

Quando um deadlock é detectado e o PostgreSQL o resolve abortando uma transação:

Identifique a Vítima: Verifique os logs do PostgreSQL para a mensagem deadlock detected. Ela especificará qual processo foi abortado.
Tente Novamente a Transação Abortada: A aplicação que receber o SQLSTATE 40P01 (deadlock_detected) deve tentar novamente a transação inteira quando a operação for segura para repetir. Tentar novamente apenas a última instrução pode deixar o estado da aplicação inconsistente.
Analise a Causa: A chave para a resolução é prevenir deadlocks futuros. Isso envolve entender por que o deadlock ocorreu (conforme descrito em cenários comuns) e ajustar a lógica da aplicação ou o design do banco de dados.

Técnicas para Prevenir Contenção de Lock e Deadlocks

Prevenir é sempre melhor que remediar. Implementar estratégias para minimizar a contenção de lock e evitar situações de deadlock é crucial para um banco de dados PostgreSQL de alto desempenho.

1. Ordenação Consistente de Transações

Regra: Sempre acesse e modifique recursos (tabelas, linhas) na mesma ordem em todas as transações. Se múltiplas transações precisam atualizar TabelaA e TabelaB, garanta que elas sempre atualizem TabelaA antes de TabelaB, ou vice-versa, de maneira consistente.
Exemplo: Se uma transação precisa atualizar registros em usuarios e pedidos, sempre execute operações em usuarios primeiro, depois pedidos. Evite cenários onde uma transação atualiza usuarios depois pedidos, enquanto outra atualiza pedidos depois usuarios.

2. Minimize a Duração da Transação

Regra: Mantenha as transações o mais curtas possível. Quanto mais tempo uma transação estiver aberta, mais locks ela mantém, aumentando a chance de contenção.
Ação: Execute apenas operações de banco de dados necessárias dentro de uma transação. Mova trabalhos não relacionados ao banco de dados (por exemplo, chamadas de API externas, cálculos complexos não dependentes do estado da transação) para fora do limite da transação.

3. Use Níveis de Isolamento Apropriados

Regra: Entenda e escolha o nível de isolamento de transação correto. O PostgreSQL oferece:
- READ UNCOMMITTED (simulado por READ COMMITTED no PostgreSQL)
- READ COMMITTED (padrão)
- REPEATABLE READ
- SERIALIZABLE
Ação: O padrão READ COMMITTED fornece bom desempenho enquanto previne leituras sujas. REPEATABLE READ e SERIALIZABLE oferecem consistência mais forte, mas podem levar a mais erros serialization_failure (que são essencialmente deadlocks para isolamento de snapshot) e potencialmente mais contenção de lock. Use-os apenas quando absolutamente necessário.

4. Otimize Consultas e Índices

Regra: Consultas lentas mantêm locks por mais tempo. Garanta que suas consultas sejam eficientes e bem indexadas.
Ação: Use EXPLAIN ANALYZE para identificar consultas lentas. Adicione índices apropriados para acelerar a recuperação de dados, especialmente para cláusulas WHERE e condições JOIN.

5. Use `SELECT ... FOR UPDATE` com Moderação

Regra: SELECT ... FOR UPDATE bloqueia linhas pela duração da transação. Isso é poderoso para prevenir condições de corrida, mas também pode ser uma fonte importante de contenção.
Ação: Use-o apenas quando você realmente precisar bloquear linhas para evitar que sejam modificadas por outras transações antes que sua transação complete seu trabalho. Considere se locks consultivos podem ser mais adequados para certos cenários.

6. Locks Consultivos (Advisory Locks)

Regra: Para locking em nível de aplicação ou necessidades de sincronização mais complexas que não mapeiam diretamente para locks de objetos de banco de dados, os locks consultivos do PostgreSQL podem ser uma ferramenta poderosa.
Ação: Use funções como pg_advisory_lock(), pg_advisory_lock_shared() e pg_advisory_unlock() para implementar mecanismos de locking personalizados. Esses locks não são detectados automaticamente pelo mecanismo de detecção de deadlock, então a lógica da aplicação deve gerenciá-los cuidadosamente.

7. Operações em Lote

Regra: Em vez de emitir muitas instruções UPDATE ou DELETE individuais, considere agrupá-las em uma única instrução ou usar COPY para carregamento/atualização em massa quando possível.
Ação: Uma única instrução UPDATE pode adquirir locks de forma mais eficiente do que um loop de UPDATEs individuais. Analise o comportamento de locking de suas operações em lote.

Um Fluxo de Triagem Prático

Quando um incidente está ativo, comece com as sessões em espera, não com a teoria mais antiga em sua cabeça:

SELECT
    now() - a.query_start AS waiting_for,
    a.pid,
    a.usename,
    a.state,
    a.wait_event_type,
    a.wait_event,
    pg_blocking_pids(a.pid) AS blocked_by,
    a.query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

Se um PID bloqueador aparecer repetidamente, inspecione-o:

SELECT
    pid,
    usename,
    state,
    now() - xact_start AS transaction_age,
    now() - query_start AS query_age,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity
WHERE pid = 12345;

A frase a observar é idle in transaction. Essa sessão não está ativamente fazendo trabalho útil no banco de dados, mas ainda pode estar mantendo locks. Isso geralmente vem de código de aplicação que abre uma transação, executa uma consulta, chama uma API externa e só confirma após a API retornar. Mova a chamada externa para fora da transação se puder.

Cancele com cuidado. SELECT pg_cancel_backend(pid) pede que a consulta atual pare. SELECT pg_terminate_backend(pid) mata a sessão e reverte sua transação aberta. Em um incidente de produção, terminar o bloqueador pode ser a decisão correta, mas capture a consulta e a idade da transação primeiro para que você possa corrigir o caminho do código depois.

Mais um hábito ajuda após o incidente: salve a consulta bloqueadora, a consulta bloqueada e os limites da transação dos logs da aplicação. A instrução SQL sozinha geralmente não é suficiente. Um UPDATE usuarios SET ultimo_acesso = now() inofensivo pode se tornar um bloqueador se estiver dentro de uma transação que também espera por uma API de pagamento. Correções de deadlock geralmente vivem no nível do fluxo da transação, não dentro de uma instrução isolada.

As correções duráveis são geralmente simples: mantenha as transações curtas, toque em recursos compartilhados em uma ordem consistente, adicione índices que impeçam que atualizações escaneiem demais e faça com que as tentativas da aplicação lidem com 40P01 e falhas de serialização de forma limpa. O PostgreSQL pode detectar um deadlock, mas não pode redesenhar o padrão de transação para você. Essa parte pertence à revisão da aplicação e do esquema após o incidente estar calmo.