PostgreSQL
Bloqueos de fila, tabla y advisory locks, detección de deadlocks y estrategias para coordinar concurrencia explícita sin crear esperas indefinidas.
Los locks no son un fallo: son el mecanismo con el que PostgreSQL coordina operaciones incompatibles. El problema aparece cuando la aplicación bloquea más de lo necesario, mantiene locks demasiado tiempo o adquiere recursos en órdenes inconsistentes.
MVCC permite que lecturas normales convivan con escrituras, pero existen operaciones que necesitan exclusión explícita:
leer para decidir y después escribir
→ proteger la fila o detectar conflicto
modificar estructura
→ coordinar acceso a la tabla
garantizar una regla lógica
→ constraint, row lock o advisory lockUn lock tiene:
Sin coordinación, dos procesos pueden:
Los locks serializan únicamente los conflictos necesarios. No deberían utilizarse para convertir toda la aplicación en ejecución secuencial.
Puedes bloquear filas visibles con:
SELECT *
FROM inventory
WHERE branch_id = $1
AND product_id = $2
FOR UPDATE;La fila continúa siendo legible mediante SELECT normal desde otras transacciones, pero operaciones incompatibles esperan.
Es el modo más fuerte de los locks de fila comunes. Bloquea otros UPDATE, DELETE, SELECT FOR UPDATE y operaciones incompatibles.
Úsalo cuando vas a modificar la fila o necesitas impedir cambios relevantes mientras decides.
Similar, pero permite ciertas operaciones que solo requieren key-share. PostgreSQL lo usa para updates que no modifican keys referenciables.
Puede reducir contención cuando no necesitas proteger identidad referenciada.
Permite varios holders share, pero bloquea modificaciones incompatibles.
Protege principalmente la key frente a delete o cambios que rompan referencias. Las foreign keys pueden utilizar este tipo de coordinación.
No memorices solo nombres: revisa qué operaciones deben coexistir en tu flujo.
SELECT o.*
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = $1
FOR UPDATE OF o;OF o limita qué tabla se bloquea. Sin precisión, podrías bloquear filas relacionadas innecesarias.
En outer joins existen restricciones porque algunas filas no corresponden a una tuple real bloqueable.
Patrón inseguro:
SELECT status
→ lógica en aplicación
→ SELECT FOR UPDATE
→ UPDATEEl estado pudo cambiar antes del lock.
Patrón correcto:
SELECT ... FOR UPDATE
→ evaluar estado protegido
→ modificar
→ COMMITOtra opción es una sola sentencia condicional.
SELECT *
FROM inventory
WHERE id = $1
FOR UPDATE NOWAIT;Falla inmediatamente si el recurso está bloqueado.
Útil cuando:
La aplicación debe manejar SQLSTATE de lock no disponible y no presentarlo como 500 genérico.
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY priority DESC, created_at, id
FOR UPDATE SKIP LOCKED
LIMIT 10;Workers concurrentes pueden reclamar lotes distintos.
Flujo:
Esto produce una vista deliberadamente inconsistente del conjunto. Es apropiado para colas, no para reportes o reglas que necesitan ver todos los elementos.
Una fila problemática puede quedar bloqueada o fallar repetidamente y ser omitida por muchos workers.
Mitigaciones:
locked_at y owner.WITH selected AS (
SELECT id
FROM jobs
WHERE status = 'pending'
ORDER BY priority DESC, created_at, id
FOR UPDATE SKIP LOCKED
LIMIT 10
)
UPDATE jobs j
SET status = 'processing',
worker_id = $1,
locked_at = now()
FROM selected s
WHERE j.id = s.id
RETURNING j.*;La selección y cambio ocurren en una transacción. Procesar trabajo externo dentro de esa misma transacción puede mantener locks demasiado tiempo; normalmente se reclama, confirma y luego se procesa con idempotencia.
PostgreSQL utiliza varios modos de tabla, desde ACCESS SHARE hasta ACCESS EXCLUSIVE.
Ejemplos conceptuales:
ACCESS SHARE.ROW EXCLUSIVE además de row locks.CREATE INDEX normal y muchos DDL toman modos más fuertes.ALTER TABLE suele requerir ACCESS EXCLUSIVE.ACCESS EXCLUSIVE entra en conflicto con todos los demás modos, incluido SELECT.
Una migración puede esperar un lock detrás de una transacción larga. Mientras espera, otras consultas que necesitan modos incompatibles pueden acumularse detrás de ella.
Resultado:
transacción larga
↓ bloquea ALTER TABLE
ALTER TABLE esperando
↓
nuevos SELECT/UPDATE quedan en cola detrás del DDLAunque el cambio DDL sea rápido, su espera puede provocar incidente.
Mitiga con:
SET lock_timeout = '2s';
ALTER TABLE ...;Falla rápido y reintenta en una ventana segura.
Un deadlock es un ciclo de espera:
T1 posee A y espera B
T2 posee B y espera ANinguna puede avanzar. PostgreSQL detecta el ciclo después de deadlock_timeout y aborta una transacción con SQLSTATE 40P01.
El sistema no puede adivinar cuál operación de negocio importa más; elige una víctima.
T1:
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- después intenta id 2T2:
UPDATE accounts SET balance = balance - 10 WHERE id = 2;
-- después intenta id 1Prevención: adquirir cuentas en un orden estable:
SELECT id
FROM accounts
WHERE id IN ($1, $2)
ORDER BY id
FOR UPDATE;Después modifica ambas.
La aplicación debe reintentar la transacción completa de manera limitada. Sin embargo, si son frecuentes:
Reintentar sin corregir diseño puede ocultar alta contención.
Límite para esperar adquisición de un lock.
Límite total de ejecución del statement, incluida espera.
Tiempo antes de comprobar deadlock y, según logging, registrar waits. Es configuración del servidor; no suele ajustarse por query casualmente.
Configura timeouts con jerarquía coherente.
PostgreSQL ofrece locks sobre números definidos por la aplicación:
SELECT pg_advisory_xact_lock(hashtextextended('invoice:123', 0));Modalidades:
Útiles para:
PostgreSQL no conoce la relación entre la clave y los datos.
Si un writer olvida adquirirlo, la garantía desaparece.
Necesitas:
No reemplazan constraints cuando la regla puede expresarse declarativamente.
Cuando los conflictos son raros:
UPDATE products
SET price = $1,
version = version + 1
WHERE id = $2
AND version = $3
RETURNING version;Cero filas significa que otro proceso modificó la fila o no existe.
Ventajas:
Costes:
FOR UPDATE bloquea antes de ejecutar una decisión.
Conviene cuando:
No conviene mantener el lock mientras un humano edita o una API externa responde.
A menudo evita un SELECT lock:
UPDATE inventory
SET reserved_quantity = reserved_quantity + $1
WHERE branch_id = $2
AND product_id = $3
AND quantity - reserved_quantity >= $1
RETURNING *;Es una forma compacta de concurrencia pesimista implícita sobre la fila, con condición reevaluada apropiadamente.
Insertar un child puede tomar key-share sobre el parent. Eliminar o cambiar la key del parent puede esperar.
Triggers también pueden tocar tablas adicionales y crear órdenes de locking no visibles desde el statement original.
Para deadlocks, inspecciona todo el flujo, no solo las queries del handler.
Actividad:
SELECT
pid,
usename,
state,
wait_event_type,
wait_event,
xact_start,
query_start,
query
FROM pg_stat_activity
WHERE datname = current_database();Blockers directos:
SELECT
pid,
pg_blocking_pids(pid) AS blocking_pids,
query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;Locks:
SELECT locktype, mode, granted, pid, relation::regclass
FROM pg_locks
ORDER BY granted, pid;Correlaciona PID, transacción, aplicación, user y query.
SELECT pg_cancel_backend(pid);
SELECT pg_terminate_backend(pid);cancel intenta detener la query actual; terminate cierra la sesión y revierte su transacción.
Antes de actuar:
BEGIN;
SELECT id, balance
FROM accounts
WHERE id IN ($1, $2)
ORDER BY id
FOR UPDATE;
UPDATE accounts
SET balance = balance - $3
WHERE id = $1 AND balance >= $3;
UPDATE accounts
SET balance = balance + $3
WHERE id = $2;
COMMIT;El orden por ID evita que transferencias inversas adquieran locks en orden contrario. Debes verificar que el débito afectó una fila.
El planner bloquea filas devueltas según ejecución; no asumas un orden sin ORDER BY.
Las filas saltadas por offset pueden bloquearse dependiendo del plan; evita patrones complejos para queues.
Locks adquiridos después de un savepoint pueden liberarse al rollback hacia él según el tipo y operación.
Si devuelves la conexión al pool sin liberar, otro request hereda el lock.
Puede causar cola amplia incluso antes de adquirir el lock.
El ciclo puede involucrar parent/child y no ser evidente desde updates principales.
La lectura ya quedó obsoleta.
Aumenta contención y memoria de locks.
Bloquea otros requests durante una latencia no controlada.
Omite datos deliberadamente.
No ofrece garantía.
Puede causar tormenta y ocultar diseño.
Rompe workloads legítimos.
NOWAIT y manejo de error.SKIP LOCKED.regla expresable como constraint
→ constraint
cambio condicional de una fila
→ UPDATE atómico
conflicto raro con edición externa
→ optimistic version
decisión corta sobre fila estable
→ FOR UPDATE
queue concurrente
→ SKIP LOCKED + estado durable
recurso lógico sin fila coordinadora
→ advisory lock documentado
invariante compleja de conjunto
→ SERIALIZABLE o rediseñoSKIP LOCKED omite filas y necesita recuperación.FOR UPDATE debe ejecutarse antes de la decisión?SKIP LOCKED?Índices: familias, diseño y trade-offs explica cómo acelerar patrones de acceso sin aumentar innecesariamente el coste de escritura.