Monitorización de bloqueos y conflictos

Imagina que curras en una oficina donde todas las puertas se quedan bloqueadas porque alguien se dejó las llaves dentro de una sala. Así funcionan los bloqueos en PostgreSQL. Si una query o transacción bloquea un recurso, las demás operaciones que intentan acceder a ese mismo recurso dependen de que termine la primera. Esto puede causar retrasos, situaciones conflictivas y, en el peor de los casos, parar el sistema entero.

¿Cuándo aparecen los bloqueos?

Los bloqueos (Locks) en PostgreSQL se usan para gestionar el acceso concurrente a los datos. Aparecen:

1. Cuando haces operaciones de escritura: UPDATE, DELETE, INSERT.
2. Cuando usas transacciones que mantienen el recurso más tiempo del necesario.
3. Cuando hay conflictos entre distintas transacciones que quieren el mismo recurso.

Una base de datos real es un "campo de batalla" por los recursos, y aunque pienses que tu sistema va perfecto, una transacción despistada puede "atascarlo" todo, como un merge fallido en Git.

Herramientas para analizar bloqueos: pg_locks

pg_locks es una vista del sistema de PostgreSQL que muestra los bloqueos actuales, tanto los que están retenidos como los que están esperando las transacciones. Responde a la pregunta: "¿Quién tiene el bloqueo y quién está esperando?"

Campos principales de pg_locks:

• locktype: tipo de bloqueo (por ejemplo, relation, transaction, page, tuple).
• database: identificador de la base de datos.
• relation: identificador de la tabla (si el bloqueo está relacionado con una tabla).
• mode: modo de bloqueo (por ejemplo, RowExclusiveLock, AccessShareLock).
• granted: flag que indica si el bloqueo ya está concedido (true) o si la transacción sigue esperando (false).

Nota: PostgreSQL usa lo que se llama "modo jerárquico de bloqueos". Esto significa que distintas operaciones pueden poner bloqueos menos estrictos (por ejemplo, AccessShareLock para leer datos) o más estrictos (ExclusiveLock para modificar la estructura de una tabla).

Ejemplo: ver todos los bloqueos actuales

SELECT *
FROM pg_locks;

Pero si simplemente sacas todo el contenido de pg_locks, va a haber demasiado ruido. ¡Vamos a probar algo más útil!

Ejemplo: bloqueos que aún no se han concedido (o sea, transacciones esperando)

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

¿Qué pasa aquí?

• Filtramos los registros donde granted = false, o sea, el bloqueo aún no está concedido.
• relation::regclass convierte el identificador de la tabla a su nombre para que sea más legible.

El resultado puede verse así:

Estas queries te ayudan a identificar qué tabla/recurso está bloqueado y qué transacción puede ser la causa.

Análisis de conflictos: pg_blocking_pids()

Los bloqueos ya son un rollo, pero ¿qué hacer si una transacción bloquea a otra? PostgreSQL tiene una forma cómoda de pillar al "culpable" usando la función pg_blocking_pids().

La función pg_blocking_pids() devuelve una lista de identificadores de procesos (pid) que están bloqueando la ejecución de la transacción actual.

Ejemplo: buscar transacciones que bloquean a otras

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

¿Qué pasa aquí?

• Usamos la vista pg_stat_activity para sacar los procesos activos en el sistema.
• La función pg_blocking_pids(pid) devuelve la lista de procesos que bloquean cada pid. Si la lista no está vacía (longitud mayor que 0), el proceso está bloqueado.

Ejemplo de resultado:

La transacción con pid = 4567 está bloqueada por los procesos 1234 y 5678. Ya tenemos a nuestros "culpables".

Terminar procesos que bloquean

Cuando ya sabes qué procesos están bloqueando, puedes pararlos usando la función pg_terminate_backend():

SELECT pg_terminate_backend(1234); -- "Matamos" el proceso 1234

¡Pero ojo! Forzar la parada de un proceso puede hacer que se deshagan los datos de la transacción actual. Usa esto como "botón nuclear" solo si no queda otra.

Aplicación práctica: escenario de análisis de bloqueos

Imagina que tienes una base de datos de una universidad con las tablas students y enrollments. Varias transacciones intentan escribir a la vez en la tabla enrollments y te encuentras con bloqueos.

1. Identificar bloqueos:

SELECT pid, locktype, relation::regclass AS table_name, mode, granted
FROM pg_locks
WHERE NOT granted;

2. Detectar procesos que bloquean:

SELECT pid, pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

3. Eliminar bloqueos:

Forzamos la terminación de uno de los procesos en conflicto:

SELECT pg_terminate_backend(1234); -- Terminamos el proceso 1234

Nota: Antes de "matar" un proceso, intenta entender por qué surgió el bloqueo. Igual te conviene revisar la lógica de las transacciones.

Errores típicos y cómo evitarlos

Los bloqueos suelen aparecer por una mala gestión de las transacciones. Por ejemplo:

Error: una transacción mantiene el bloqueo demasiado tiempo sin hacer nada (estado "idle in transaction").

Solución: vigila el estado de las transacciones con pg_stat_activity y termina las transacciones "colgadas".

SELECT pid, state, query
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Error: te olvidaste de usar índices en las queries, lo que provoca bloqueos a nivel de tabla entera.

Solución: optimiza las queries añadiendo índices para las condiciones más usadas.

Tabla de "quién espera a quién"

Para diagnosticar mejor, puedes montar un árbol de dependencias que muestre qué transacción bloquea a cuál:

WITH RECURSIVE blocking_tree AS (
  SELECT pid, pg_blocking_pids(pid) AS blocked_by
  FROM pg_stat_activity
  WHERE cardinality(pg_blocking_pids(pid)) > 0
  UNION ALL
  SELECT a.pid, pg_blocking_pids(a.pid)
  FROM pg_stat_activity a
  JOIN blocking_tree b ON a.pid = ANY(b.blocked_by)
)
SELECT pid, blocked_by FROM blocking_tree;

Resultado:

Aquí se ve que el proceso 5678 bloquea al proceso 1234, y ese a su vez bloquea al 4567.