Locks, bloqueos y deadlocks

Tipos de lock

Los motores SQL adquieren bloqueos a diferentes granularidades:

• Row-level locks: la más fina. PostgreSQL y InnoDB los usan por defecto para INSERT/UPDATE/DELETE.
• Page-level locks: un bloque de filas contiguas. SQL Server los usa ocasionalmente como optimización.
• Table-level locks: toda la tabla. Se usan para DDL (ALTER, DROP) o con LOCK TABLE explícito.

Los modos de lock determinan qué operaciones se bloquean entre sí:

• EXCLUSIVE (X): nadie más puede leerla ni modificarla. Adquirido por UPDATE, DELETE, SELECT FOR UPDATE.
• SHARE (S): otros pueden leerla con S pero nadie puede modificarla. Adquirido por SELECT FOR SHARE.
• UPDATE (U): intermedio, permite lecturas pero bloquea otros UPDATE.

Matriz de compatibilidad

| Quieres → | Share | Update | Exclusive | |---------------|-------|--------|-----------| | Ya hay Share | ✓ | ✓ | ✗ espera | | Ya hay Update | ✓ | ✗ | ✗ | | Ya hay Exclusive | ✗ | ✗ | ✗ |

Esta matriz explica la mayoría de esperas: lectores (S) no se bloquean entre sí, pero esperan a los escritores (X). Dos escritores sobre la misma fila se bloquean mutuamente.

Row-level locks en acción

-- Session 1
BEGIN;
SELECT * FROM cuentas WHERE id = 1 FOR UPDATE;  -- lock X sobre fila 1

-- Session 2
BEGIN;
UPDATE cuentas SET saldo = 100 WHERE id = 1;
-- ESPERA hasta que la session 1 haga COMMIT o ROLLBACK

-- Session 1
UPDATE cuentas SET saldo = 500 WHERE id = 1;
COMMIT;  -- libera el lock

-- Session 2 se desbloquea, aplica su UPDATE con saldo final 100

Locks implícitos de operaciones comunes

• SELECT sin modificador: no adquiere lock en PostgreSQL (MVCC lee snapshot). En MySQL InnoDB con REPEATABLE READ tampoco.
• SELECT FOR UPDATE: adquiere X lock a nivel de fila.
• SELECT FOR SHARE (LOCK IN SHARE MODE en MySQL): adquiere S lock.
• INSERT: X lock sobre la fila nueva. Si hay UNIQUE constraint, puede adquirir "gap lock" (InnoDB) para prevenir duplicados concurrentes.
• UPDATE / DELETE: X lock sobre cada fila modificada.
• ALTER TABLE, DROP TABLE: X lock sobre toda la tabla (operación DDL).

Deadlocks: cuando dos transacciones se esperan mutuamente

Un deadlock ocurre cuando la transacción A espera un lock que B tiene, y B espera un lock que A tiene. Ninguna puede progresar sin la otra.

Session A                       Session B
BEGIN;                          BEGIN;
UPDATE cuentas                  UPDATE cuentas
  SET saldo = 500                 SET saldo = 200
  WHERE id = 1;                   WHERE id = 2;
-- A tiene X lock en fila 1      -- B tiene X lock en fila 2

UPDATE cuentas                  UPDATE cuentas
  SET saldo = 300                 SET saldo = 700
  WHERE id = 2;                   WHERE id = 1;
-- A espera lock en fila 2      -- B espera lock en fila 1
-- (que tiene B)                -- (que tiene A)

Ambas se bloquean mutuamente. El motor detecta el ciclo y mata una de las dos con un error:

ERROR:  deadlock detected
DETAIL:  Process 12345 waits for ShareLock on transaction 98765; blocked by process 23456.
         Process 23456 waits for ShareLock on transaction 98766; blocked by process 12345.
HINT:  See server log for query details.

La aplicación recibe el error y debe reintentar la transacción. No es un bug del motor, es el comportamiento correcto ante una situación irresoluble.

Diagnóstico de locks en PostgreSQL

Ver locks activos

SELECT l.locktype, l.relation::regclass, l.mode, l.granted,
       a.pid, a.usename, a.state, a.query,
       NOW() - a.xact_start AS duracion_tx
FROM pg_locks l
JOIN pg_stat_activity a ON a.pid = l.pid
WHERE NOT l.granted OR l.relation IS NOT NULL
ORDER BY a.xact_start;

Detectar bloqueos entre sesiones

SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query,
       blocking.state AS blocking_state
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

pg_blocking_pids(pid) devuelve los PIDs que bloquean al pid dado. Es la forma canónica de diagnosticar un deadlock en vivo.

Matar una transacción bloqueante

SELECT pg_terminate_backend(12345);  -- mata la sesion
-- o mas suave:
SELECT pg_cancel_backend(12345);     -- cancela solo la query actual

Evitar deadlocks

Regla 1: Accede a las filas en el mismo orden

El deadlock ocurre porque A toma filas en orden 1→2 y B las toma 2→1. Si ambas siguen el mismo orden, no puede haber deadlock:

-- Mal: orden variable segun IDs proporcionados
UPDATE cuentas SET saldo = ... WHERE id = :a;
UPDATE cuentas SET saldo = ... WHERE id = :b;

-- Bien: siempre en orden ascendente de ID
UPDATE cuentas SET saldo = ... WHERE id = LEAST(:a, :b);
UPDATE cuentas SET saldo = ... WHERE id = GREATEST(:a, :b);

Regla 2: Transacciones cortas

Cada segundo que una transacción mantiene un lock, es un segundo más en el que puede formar un ciclo. Minimiza el tiempo BEGIN → COMMIT.

• No hagas I/O externo (llamadas HTTP, envío de mails) dentro de la transacción.
• No hagas cálculos CPU-intensivos dentro.
• Haz el mínimo de statements antes del COMMIT.

Regla 3: Usa SKIP LOCKED para colas

Si tienes N workers procesando una cola, SELECT FOR UPDATE SKIP LOCKED evita que todos se peleen por las mismas filas:

BEGIN;
SELECT id FROM tareas
WHERE estado = 'pendiente'
ORDER BY fecha
LIMIT 1
FOR UPDATE SKIP LOCKED;
-- trabajo...
UPDATE tareas SET estado = 'completada' WHERE id = ?;
COMMIT;

Cada worker se lleva una fila libre sin bloquearse con los demás.

Regla 4: Reintentos ante deadlock

Tu aplicación debe reintentar la transacción cuando recibe un deadlock error. Pseudocódigo:

def con_retry(max_intentos=5):
    def decorador(func):
        def wrapper(*args, **kwargs):
            for i in range(max_intentos):
                try:
                    return func(*args, **kwargs)
                except DeadlockError:
                    if i == max_intentos - 1:
                        raise
                    time.sleep(0.05 * (2 ** i))  # backoff exponencial
        return wrapper
    return decorador

Locks de nivel de tabla: LOCK TABLE

A veces necesitas bloquear toda la tabla explícitamente (migraciones, maintenance):

BEGIN;
LOCK TABLE cuentas IN ACCESS EXCLUSIVE MODE;
-- Nadie puede leer ni escribir en cuentas hasta COMMIT
ALTER TABLE cuentas ADD COLUMN nueva_col INT;
COMMIT;

Modos disponibles en PostgreSQL (de menos a más restrictivo): ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE.

Usa NOWAIT para que falle inmediatamente si no puede adquirir el lock:

LOCK TABLE cuentas IN ACCESS EXCLUSIVE MODE NOWAIT;
-- ERROR si hay lock en curso, sin esperar

Advisory locks (PostgreSQL)

Para coordinar aplicaciones sin usar filas reales, PostgreSQL tiene advisory locks: bloqueos arbitrarios identificados por un número:

-- Solo un worker ejecuta esta logica a la vez
SELECT pg_try_advisory_lock(42);  -- true si lo obtiene
-- hacer trabajo critico
SELECT pg_advisory_unlock(42);

Útil para singleton jobs, cron distribuidos o cerrojos entre microservicios que comparten la BD.

Monitorizar deadlocks en producción

PostgreSQL registra cada deadlock en el log si log_lock_waits = on y deadlock_timeout se alcanza. Revisa:

SELECT datname, deadlocks
FROM pg_stat_database
WHERE deadlocks > 0;

Incremento anómalo de deadlocks sugiere:

• Transacciones demasiado largas.
• Orden inconsistente de acceso a tablas.
• Falta de índices (locks más amplios por falta de precisión).

Logs con log_lock_waits = on muestran query, duración y filas afectadas, facilitando el rediseño.