Diagnóstico de esperas de bloqueo y deadlocks en PostgreSQL con pg_locks, pg_blocking_pids y lock_timeout

Objetivo

Convertir "la base de datos está colgada" en una sesión bloqueadora identificada, la sentencia que mantiene el bloqueo y una decisión sobre qué hacer, todo en pocos minutos.

Requisitos previos

Un rol con pg_read_all_stats o superusuario para ver las sentencias de otras sesiones; log_lock_waits = on, de modo que las esperas superiores a deadlock_timeout (por defecto un segundo) se escriban en el log del servidor con ambas partes identificadas.

Pasos

1. Liste las sesiones en espera: SELECT pid, wait_event_type, wait_event, state, xact_start, query FROM pg_stat_activity WHERE wait_event_type = 'Lock'.
2. Encuentre a sus bloqueadores: SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0. La documentación recomienda esta función en lugar de hacer un autojoin de pg_locks, porque una consulta de ese tipo tendría que codificar qué modos de bloqueo entran en conflicto y la vista no expone el orden de la cola de espera.
3. Consulte a los bloqueadores en pg_stat_activity: state (idle in transaction significa que el cliente mantiene la transacción abierta mientras hace otra cosa), xact_start y query, que es la última sentencia ejecutada, no necesariamente la que tomó el bloqueo.
4. Si hace falta, vea qué bloqueo está en disputa: SELECT locktype, relation::regclass, mode, granted, waitstart FROM pg_locks WHERE pid IN (...). ACCESS EXCLUSIVE, que toman la mayoría de las formas de ALTER TABLE, entra en conflicto con todos los demás modos, incluido un simple SELECT, por lo que una sentencia DDL en cola bloquea a todos los lectores posteriores de la tabla.
5. Decida: pg_cancel_backend(pid) detiene la sentencia en curso del bloqueador; pg_terminate_backend(pid) finaliza su sesión y revierte la transacción. Ambas aparecen como errores en la aplicación, así que registre a quién se interrumpió y por qué.
6. En caso de deadlocks, lea el log del servidor: el error indica ambos procesos y sus sentencias. La documentación afirma que PostgreSQL detecta los deadlocks automáticamente y aborta una de las transacciones, que no se puede predecir cuál, y que la mejor defensa es adquirir los bloqueos de varios objetos siempre en el mismo orden. Reintente la transacción abortada desde la aplicación.
7. Evite el siguiente: ejecute las migraciones y el mantenimiento con SET lock_timeout = '...' y un bucle de reintentos, configure idle_in_transaction_session_timeout para los roles de la aplicación y ordene por clave primaria las actualizaciones de varias filas en los procesos por lotes.

Resultado esperado

Cada cuelgue se atribuye a un titular del bloqueo y a una sentencia; el DDL ya no queda en cola indefinidamente detrás de transacciones largas; los errores de deadlock se reintentan en lugar de llegar a los usuarios.

Límites y base de verificación

pg_blocking_pids refleja el instante de la llamada, y las cadenas de bloqueo cambian rápidamente. Una espera por un bloqueo de fila aparece en pg_locks como una espera sobre un transactionid, lo cual resulta confuso sin la sentencia del bloqueador. El procedimiento sigue la documentación citada; no se afirma ningún dato de tiempos.