Cómo Identificar y Resolver Bloqueos Mutuos y Contención de Bloqueos en PostgreSQL

Los bloqueos mutuos y la contención de bloqueos en PostgreSQL generalmente se manifiestan como una aplicación que de repente se siente atascada. Las solicitudes se acumulan, los workers permanecen en estado active o idle in transaction, y la base de datos en sí misma puede tener aún suficiente CPU libre. El problema no es la capacidad bruta. Una sesión está esperando que otra sesión libere un bloqueo, y a veces se forma una fila completa detrás de ella.

La forma más rápida de manejar esto es separar dos casos. La contención de bloqueos significa que una sesión está esperando, pero eventualmente puede continuar. Un bloqueo mutuo significa que dos o más sesiones están esperándose mutuamente en un ciclo, por lo que PostgreSQL tiene que cancelar una transacción. Ambos se depuran con las mismas herramientas básicas, pero la solución suele ser diferente.

Conceptos Básicos de Bloqueo en PostgreSQL

PostgreSQL utiliza bloqueos para proteger tablas, filas, transacciones y otros objetos internos mientras muchas sesiones trabajan al mismo tiempo. También utiliza MVCC, por lo que los lectores y escritores ordinarios a menudo no se bloquean entre sí. Es por eso que PostgreSQL puede manejar alta concurrencia bien, pero también es por lo que los problemas de bloqueo pueden ser confusos: el problema suele ser un patrón de sentencias específico, no "demasiados usuarios" en general.

Tipos de Bloqueos

PostgreSQL utiliza varios niveles de bloqueo, cada uno ofreciendo un grado diferente de protección. Entender estos es clave para diagnosticar problemas:

AccessShareLock: Utilizado por SELECT ordinario. Entra en conflicto principalmente con ACCESS EXCLUSIVE, razón por la cual muchas lecturas pueden ejecutarse mientras ocurren escrituras.
RowExclusiveLock: Común para INSERT, UPDATE y DELETE en una tabla. El nombre es fácil de malinterpretar; no significa que cada fila en la tabla esté bloqueada exclusivamente.
ShareUpdateExclusiveLock: Utilizado por operaciones como VACUUM sin FULL, ANALYZE y algunas operaciones de índice. Permite lecturas y escrituras normales pero entra en conflicto con varias operaciones de mantenimiento.
ShareLock / ShareRowExclusiveLock / ExclusiveLock: Modos de nivel de tabla más fuertes utilizados por operaciones específicas de DDL y relacionadas con restricciones.
AccessExclusiveLock: El bloqueo de tabla más restrictivo. ALTER TABLE, DROP TABLE, TRUNCATE y VACUUM FULL pueden tomar este tipo de bloqueo. Bloquea tanto lecturas como escrituras ordinarias.

Los bloqueos a nivel de fila son independientes de los modos de bloqueo a nivel de tabla. Un UPDATE toma un RowExclusiveLock a nivel de tabla y bloqueos de fila en las filas que modifica. Cuando la gente dice "esta fila está bloqueada", generalmente significa que otra transacción ha modificado o seleccionado esa fila FOR UPDATE y aún no ha hecho commit.

Modos de Bloqueo

Los modos de bloqueo indican el tipo de acceso que requiere una transacción. A menudo se representan con nombres como RowExclusiveLock, ShareLock, ExclusiveLock, etc.

Identificación de Contención de Bloqueos y Sesiones Bloqueantes

La contención de bloqueos ocurre cuando múltiples transacciones están esperando bloqueos retenidos por otras transacciones. Esto puede ralentizar significativamente tu aplicación. La vista del sistema pg_locks es tu herramienta principal para diagnosticar estos problemas.

Usando `pg_locks`

La vista pg_locks proporciona información sobre todos los bloqueos activos en el sistema de base de datos. Es invaluable para entender qué sesiones están reteniendo bloqueos y cuáles están esperando.

Aquí hay una consulta común para identificar sesiones bloqueantes:

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;

Explicación de la consulta:

Unimos pg_locks con pg_stat_activity dos veces: una para el proceso bloqueado y otra para el proceso bloqueante.
La cláusula WHERE NOT blocked_locks.granted filtra los bloqueos que están siendo esperados actualmente.
blocking_locks.pid != blocked_locks.pid asegura que no reportemos una sesión bloqueándose a sí misma.
Las condiciones de unión en pg_locks emparejan bloqueos en el mismo recurso.

Interpretando la Salida

blocked_pid / blocking_pid: Los IDs de proceso (PIDs) de las sesiones involucradas.
blocked_user / blocking_user: Los usuarios asociados con estos PIDs.
blocked_statement / blocking_statement: Las consultas SQL que se están ejecutando o esperando actualmente.
blocked_mode / blocking_mode: Los modos de bloqueo solicitados y retenidos.

Si esta consulta devuelve filas, tienes contención de bloqueos. El blocking_pid está reteniendo un bloqueo que el blocked_pid está esperando.

Entendiendo y Resolviendo Bloqueos Mutuos

Un bloqueo mutuo ocurre cuando dos o más transacciones están cada una esperando un bloqueo retenido por otra transacción en el ciclo, creando una dependencia circular que ninguna puede resolver por sí misma. PostgreSQL detecta bloqueos mutuos y los resuelve automáticamente abortando una de las transacciones, típicamente la que está causando el bloqueo mutuo y ha realizado la menor cantidad de trabajo.

Escenarios Comunes de Bloqueo Mutuo

Dos transacciones actualizando diferentes filas en diferentes tablas en orden inverso:
- Transacción A: Actualiza la fila X en la Tabla 1, luego intenta actualizar la fila Y en la Tabla 2.
- Transacción B: Actualiza la fila Y en la Tabla 2, luego intenta actualizar la fila X en la Tabla 1. Si la Transacción A bloquea la fila X y la Transacción B bloquea la fila Y, se bloquearán mutuamente cuando intenten adquirir el bloqueo retenido por la otra.
UPDATE seguido de SELECT ... FOR UPDATE:
- Transacción A: Actualiza una fila.
- Transacción B: Ejecuta SELECT ... FOR UPDATE en la misma fila. Si el UPDATE aún está reteniendo un bloqueo exclusivo de fila cuando el SELECT FOR UPDATE intenta adquirir un bloqueo compartido, y existen otras dependencias, puede ocurrir un bloqueo mutuo.

Detectando Bloqueos Mutuos

PostgreSQL registra información de bloqueo mutuo en su registro del servidor. Típicamente verás mensajes 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.

PostgreSQL automáticamente elige una transacción para abortar para que las otras puedan continuar. No confíes en pg_stat_activity después del hecho para obtener la historia completa; para cuando mires, la sentencia cancelada puede haber desaparecido. El registro del servidor suele ser el mejor registro del ciclo.

Resolviendo Bloqueos Mutuos

Cuando se detecta un bloqueo mutuo y PostgreSQL lo resuelve abortando una transacción:

Identifica la Víctima: Revisa los registros de PostgreSQL en busca del mensaje deadlock detected. Especificará qué proceso fue abortado.
Reintenta la Transacción Abortada: La aplicación que recibe SQLSTATE 40P01 (deadlock_detected) debe reintentar toda la transacción cuando la operación sea segura de reintentar. Reintentar solo la última sentencia puede dejar el estado de la aplicación inconsistente.
Analiza la Causa: La clave para la resolución es prevenir futuros bloqueos mutuos. Esto implica entender por qué ocurrió el bloqueo mutuo (como se describe en escenarios comunes) y ajustar la lógica de la aplicación o el diseño de la base de datos.

Técnicas para Prevenir la Contención de Bloqueos y Bloqueos Mutuos

Prevenir es siempre mejor que curar. Implementar estrategias para minimizar la contención de bloqueos y evitar situaciones de bloqueo mutuo es crucial para una base de datos PostgreSQL de alto rendimiento.

1. Orden Consistente de Transacciones

Regla: Siempre accede y modifica los recursos (tablas, filas) en el mismo orden en todas las transacciones. Si múltiples transacciones necesitan actualizar TablaA y TablaB, asegúrate de que siempre actualicen TablaA antes que TablaB, o viceversa, de manera consistente.
Ejemplo: Si una transacción necesita actualizar registros en usuarios y pedidos, siempre realiza las operaciones en usuarios primero, luego en pedidos. Evita escenarios donde una transacción actualiza usuarios luego pedidos, mientras que otra actualiza pedidos luego usuarios.

2. Minimizar la Duración de las Transacciones

Regla: Mantén las transacciones lo más cortas posible. Cuanto más tiempo esté abierta una transacción, más bloqueos retiene, aumentando la probabilidad de contención.
Acción: Realiza solo las operaciones de base de datos necesarias dentro de una transacción. Mueve el trabajo no relacionado con la base de datos (por ejemplo, llamadas a API externas, cálculos complejos que no dependen del estado de la transacción) fuera del límite de la transacción.

3. Usar Niveles de Aislamiento Apropiados

Regla: Entiende y elige el nivel de aislamiento de transacción correcto. PostgreSQL ofrece:
- READ UNCOMMITTED (simulado por READ COMMITTED en PostgreSQL)
- READ COMMITTED (predeterminado)
- REPEATABLE READ
- SERIALIZABLE
Acción: El READ COMMITTED predeterminado proporciona un buen rendimiento mientras previene lecturas sucias. REPEATABLE READ y SERIALIZABLE ofrecen una consistencia más fuerte pero pueden llevar a más errores de serialization_failure (que son esencialmente bloqueos mutuos para el aislamiento de instantáneas) y potencialmente más contención de bloqueos. Úsalos solo cuando sea absolutamente necesario.

4. Optimizar Consultas e Índices

Regla: Las consultas lentas retienen bloqueos por más tiempo. Asegúrate de que tus consultas sean eficientes y estén bien indexadas.
Acción: Usa EXPLAIN ANALYZE para identificar consultas lentas. Agrega índices apropiados para acelerar la recuperación de datos, especialmente para cláusulas WHERE y condiciones JOIN.

5. Usar `SELECT ... FOR UPDATE` con Moderación

Regla: SELECT ... FOR UPDATE bloquea filas durante la duración de la transacción. Esto es poderoso para prevenir condiciones de carrera, pero también puede ser una fuente importante de contención.
Acción: Úsalo solo cuando realmente necesites bloquear filas para evitar que sean modificadas por otras transacciones antes de que tu transacción complete su trabajo. Considera si los bloqueos de aviso podrían ser una mejor opción para ciertos escenarios.

6. Bloqueos de Aviso (Advisory Locks)

Regla: Para bloqueos a nivel de aplicación o necesidades de sincronización más complejas que no se asignan directamente a bloqueos de objetos de base de datos, los bloqueos de aviso de PostgreSQL pueden ser una herramienta poderosa.
Acción: Usa funciones como pg_advisory_lock(), pg_advisory_lock_shared() y pg_advisory_unlock() para implementar mecanismos de bloqueo personalizados. Estos bloqueos no son detectados automáticamente por el mecanismo de detección de bloqueos mutuos, por lo que la lógica de la aplicación debe gestionarlos cuidadosamente.

7. Operaciones por Lotes

Regla: En lugar de emitir muchas sentencias UPDATE o DELETE individuales, considera agruparlas en una sola sentencia o usar COPY para carga/actualización masiva cuando sea posible.
Acción: Una sola sentencia UPDATE podría adquirir bloqueos de manera más eficiente que un bucle de UPDATE individuales. Analiza el comportamiento de bloqueo de tus operaciones por lotes.

Un Flujo de Triage Práctico

Cuando un incidente está activo, comienza con las sesiones en espera, no con la teoría más antigua en tu cabeza:

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;

Si un PID bloqueante aparece una y otra vez, inspecciónalo:

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;

La frase a observar es idle in transaction. Esa sesión no está realizando activamente trabajo útil en la base de datos, pero aún puede estar reteniendo bloqueos. Esto a menudo proviene de código de aplicación que abre una transacción, realiza una consulta, llama a una API externa y solo hace commit después de que la API regresa. Mueve la llamada externa fuera de la transacción si puedes.

Cancela con cuidado. SELECT pg_cancel_backend(pid) pide que la consulta actual se detenga. SELECT pg_terminate_backend(pid) mata la sesión y revierte su transacción abierta. En un incidente de producción, terminar el bloqueador puede ser la decisión correcta, pero captura la consulta y la antigüedad de la transacción primero para que puedas arreglar la ruta del código más tarde.

Un hábito más ayuda después del incidente: guarda la consulta bloqueante, la consulta bloqueada y los límites de la transacción de los registros de la aplicación. La sentencia SQL sola a menudo no es suficiente. Un UPDATE users SET last_seen_at = now() inofensivo puede convertirse en un bloqueador si se encuentra dentro de una transacción que también espera una API de pago. Las soluciones de bloqueo mutuo generalmente viven a nivel del flujo de la transacción, no dentro de una sentencia aislada.

Las soluciones duraderas son generalmente simples: mantén las transacciones cortas, toca los recursos compartidos en un orden consistente, agrega índices que eviten que las actualizaciones escaneen demasiado, y haz que los reintentos de la aplicación manejen 40P01 y los fallos de serialización limpiamente. PostgreSQL puede detectar un bloqueo mutuo, pero no puede rediseñar el patrón de transacción por ti. Esa parte pertenece a la revisión de la aplicación y el esquema después de que el incidente se haya calmado.