Diagnosticar esperas de lock e deadlocks no PostgreSQL com pg_locks, pg_blocking_pids e lock_timeout

Objetivo

Transformar "o banco de dados está travado" em uma sessão bloqueadora identificada, a instrução que mantém o lock e uma decisão sobre o que fazer, em poucos minutos.

Pré-requisitos

Um role com pg_read_all_stats ou superusuário, para ver as instruções de outras sessões; log_lock_waits = on, para que esperas maiores que deadlock_timeout (padrão: um segundo) sejam gravadas no log do servidor com as duas partes identificadas.

Passos

1. Liste quem está esperando: SELECT pid, wait_event_type, wait_event, state, xact_start, query FROM pg_stat_activity WHERE wait_event_type = 'Lock'.
2. Encontre os bloqueadores: SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0. A documentação recomenda essa função em vez de fazer um join de pg_locks consigo mesma, porque uma consulta desse tipo teria de codificar quais modos de lock entram em conflito, e a view não expõe a ordem da fila de espera.
3. Consulte os bloqueadores em pg_stat_activity: state (idle in transaction significa que o cliente mantém a transação aberta enquanto faz outra coisa), xact_start e query, que é a última instrução executada, não necessariamente a que obteve o lock.
4. Se necessário, veja qual lock está em disputa: SELECT locktype, relation::regclass, mode, granted, waitstart FROM pg_locks WHERE pid IN (...). ACCESS EXCLUSIVE, obtido pela maioria das formas de ALTER TABLE, entra em conflito com todos os outros modos, inclusive um simples SELECT; assim, uma instrução DDL na fila bloqueia todos os leitores posteriores da tabela.
5. Decida: pg_cancel_backend(pid) interrompe a instrução em execução do bloqueador; pg_terminate_backend(pid) encerra a sessão e faz rollback da transação. Ambos aparecem como erros na aplicação, então registre quem foi interrompido e por quê.
6. Para deadlocks, leia o log do servidor: o erro nomeia os dois processos e suas instruções. A documentação afirma que o PostgreSQL detecta deadlocks automaticamente e aborta uma das transações, que não é possível prever qual delas, e que a melhor defesa é adquirir locks sobre vários objetos sempre na mesma ordem. Faça a aplicação repetir (retry) a transação abortada.
7. Evite a próxima ocorrência: execute migrações e manutenção com SET lock_timeout = '...' e um laço de retry, configure idle_in_transaction_session_timeout para os roles da aplicação e ordene por chave primária as atualizações de várias linhas em jobs em lote.

Resultado esperado

Cada travamento é atribuído a um detentor do lock e a uma instrução; o DDL deixa de ficar na fila indefinidamente atrás de transações longas; erros de deadlock são repetidos em vez de chegarem aos usuários.

Limites e base de verificação

pg_blocking_pids reflete o momento da chamada, e as cadeias de bloqueio mudam rapidamente. Uma espera por lock de linha aparece em pg_locks como espera por um transactionid, o que confunde sem a instrução do bloqueador. O procedimento segue a documentação citada; nenhum tempo de execução é afirmado.