En esta página

Transacciones y concurrencia

14 min lectura TextoCap. 5 — SQL en producción

Transacciones y concurrencia

Las transacciones son el mecanismo que hace que una base de datos sea confiable. Cuando múltiples usuarios acceden a los datos simultáneamente, las transacciones garantizan que cada uno vea un estado consistente y que las operaciones incompletas nunca queden a medias. Esta es la "A" (Atomicidad) y la "I" (Aislamiento) de ACID.

Transacciones básicas

En PostgreSQL, cada instrucción SQL individual se ejecuta en su propia transacción automáticamente (modo autocommit). Para agrupar múltiples instrucciones en una sola transacción, usas BEGIN y COMMIT:

-- Sin transacción explícita: cada instrucción es su propia transacción
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;  -- Transacción implícita #1
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;  -- Transacción implícita #2
-- Si falla la segunda instrucción: el dinero desaparece del saldo 1 pero no llega al saldo 2

-- CON transacción explícita: ambas operaciones son atómicas
BEGIN;
  UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
  UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;
-- Si falla cualquier instrucción: ambas se revierten. El dinero nunca desaparece.

BEGIN, COMMIT y ROLLBACK

-- Ejemplo completo: transferencia bancaria
CREATE TABLE cuentas (
  id      SERIAL       PRIMARY KEY,
  titular VARCHAR(150) NOT NULL,
  saldo   NUMERIC(12,2) NOT NULL DEFAULT 0 CHECK (saldo >= 0)
);

INSERT INTO cuentas (titular, saldo) VALUES
  ('Ana García', 1500.00),
  ('Luis Martínez', 500.00);

-- Transferencia exitosa
BEGIN;
  -- Verificar que hay saldo suficiente
  SELECT saldo FROM cuentas WHERE id = 1 FOR UPDATE;  -- Bloquear la fila
  
  UPDATE cuentas SET saldo = saldo - 300 WHERE id = 1;
  UPDATE cuentas SET saldo = saldo + 300 WHERE id = 2;
  
  -- Verificar el resultado antes de confirmar
  SELECT id, titular, saldo FROM cuentas WHERE id IN (1, 2);
  
COMMIT;  -- Confirmar si todo está bien

-- Transferencia que se cancela (ROLLBACK)
BEGIN;
  UPDATE cuentas SET saldo = saldo - 200 WHERE id = 1;
  -- Descubrimos que hay un error en los datos de destino
  SELECT * FROM cuentas WHERE id = 999;  -- No existe
  
ROLLBACK;  -- Deshacer todos los cambios desde el BEGIN
-- El saldo de la cuenta 1 vuelve a su valor anterior

ROLLBACK automático

-- Si ocurre un error, PostgreSQL invalida la transacción
BEGIN;
  UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
  
  -- Este UPDATE viola el CHECK (saldo >= 0)
  UPDATE cuentas SET saldo = saldo - 10000 WHERE id = 2;
  -- ERROR: new row violates check constraint "cuentas_saldo_check"
  
  -- Ahora la transacción está en estado de error (aborted)
  -- Ninguna instrucción más se ejecutará
  UPDATE cuentas SET saldo = 999 WHERE id = 1;  -- Ignorada
  
ROLLBACK;  -- Necesario para limpiar el estado de error

SAVEPOINT — Puntos de guardado parciales

Los SAVEPOINT permiten hacer rollback parcial dentro de una transacción, sin perder todo el trabajo:

BEGIN;
  INSERT INTO pedidos (cliente_id, total) VALUES (1, 150.00);
  
  SAVEPOINT despues_pedido;  -- Punto de guardado
  
  INSERT INTO pedidos_lineas (pedido_id, producto_id, cantidad, precio_unit)
  VALUES (currval('pedidos_id_seq'), 999, 1, 150.00);  -- producto_id=999 no existe
  -- ERROR: violación de clave foránea
  
  ROLLBACK TO SAVEPOINT despues_pedido;  -- Volver al savepoint, NO al BEGIN
  -- El pedido insertado antes del savepoint sigue existiendo
  
  -- Reintentar con el producto correcto
  INSERT INTO pedidos_lineas (pedido_id, producto_id, cantidad, precio_unit)
  VALUES (currval('pedidos_id_seq'), 1, 1, 150.00);  -- producto_id=1 sí existe
  
  RELEASE SAVEPOINT despues_pedido;  -- Liberar el savepoint (opcional)
COMMIT;

Niveles de aislamiento

El nivel de aislamiento controla qué puede "ver" una transacción de las transacciones concurrentes. PostgreSQL soporta cuatro niveles, cada uno con diferentes compromisos entre consistencia y rendimiento:

Los fenómenos de concurrencia

Antes de ver los niveles, entendamos qué problemas pueden ocurrir:

-- Configurar el nivel de aislamiento
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- O al inicio de la transacción:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Dirty Read: una transacción lee datos que otra transacción modificó pero aún no confirmó (committed). Si la otra hace ROLLBACK, leíste datos que nunca existieron.

Non-repeatable Read: una transacción lee la misma fila dos veces y obtiene valores diferentes porque otra transacción la modificó y confirmó entre las dos lecturas.

Phantom Read: una transacción ejecuta la misma consulta (con WHERE) dos veces y obtiene diferentes conjuntos de filas porque otra transacción insertó o eliminó filas entre las dos ejecuciones.

Serialization Anomaly: el resultado de ejecutar transacciones concurrentes no es equivalente a ningún orden serial de esas transacciones.

Los cuatro niveles de aislamiento

Nivel Dirty Read Non-repeatable Phantom Serialization
Read Uncommitted Posible* Posible Posible Posible
Read Committed Imposible Posible Posible Posible
Repeatable Read Imposible Imposible Imposible** Posible
Serializable Imposible Imposible Imposible Imposible

*PostgreSQL trata Read Uncommitted igual que Read Committed. **PostgreSQL previene phantom reads en Repeatable Read.

-- READ COMMITTED (predeterminado de PostgreSQL)
-- Cada instrucción ve los datos confirmados hasta ese momento
BEGIN;  -- Sesión 1
  SELECT saldo FROM cuentas WHERE id = 1;  -- Ve 1500
  -- En este momento, Sesión 2 hace UPDATE y COMMIT (saldo=1000)
  SELECT saldo FROM cuentas WHERE id = 1;  -- Ve 1000 (non-repeatable read)
COMMIT;

-- REPEATABLE READ
-- Ve una instantánea de los datos al inicio de la transacción
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT saldo FROM cuentas WHERE id = 1;  -- Ve 1500
  -- Sesión 2 hace UPDATE saldo=1000 y COMMIT
  SELECT saldo FROM cuentas WHERE id = 1;  -- Ve 1500 (misma snapshot)
COMMIT;

-- SERIALIZABLE
-- El nivel más estricto: garantiza el mismo resultado que si las transacciones
-- se ejecutaran una tras otra, nunca simultáneamente
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SELECT SUM(saldo) FROM cuentas;
  -- Sesión 2 inserta una cuenta nueva con saldo 500
  SELECT SUM(saldo) FROM cuentas;  -- Ve el mismo resultado (snapshot)
  -- Al hacer COMMIT, PostgreSQL verifica que no haya conflictos serializables
  -- Si detecta uno, lanza: ERROR: could not serialize access due to concurrent update
COMMIT;

¿Qué nivel usar?

  • Read Committed (predeterminado): adecuado para la mayoría de operaciones OLTP. Cada instrucción ve los datos más actuales.
  • Repeatable Read: cuando necesitas leer los mismos datos múltiples veces dentro de una transacción y esperas consistencia (reportes largos, operaciones financieras complejas).
  • Serializable: cuando la corrección matemática es crítica y aceptas reintentos por errores de serialización. Para sistemas financieros de alta consistencia.

Bloqueos de fila (Row-Level Locking)

PostgreSQL usa bloqueos para coordinar el acceso concurrente. La mayoría de veces son automáticos, pero puedes controlarlos explícitamente:

-- FOR UPDATE: bloquea las filas seleccionadas para actualización
-- Otras transacciones que intenten leer con FOR UPDATE esperarán
BEGIN;
  SELECT saldo FROM cuentas WHERE id = 1 FOR UPDATE;
  -- Ahora ninguna otra transacción puede modificar esta fila hasta nuestro COMMIT
  UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

-- FOR UPDATE NOWAIT: falla inmediatamente si no puede obtener el bloqueo
BEGIN;
  SELECT * FROM cuentas WHERE id = 1 FOR UPDATE NOWAIT;
  -- Si otra transacción ya bloqueó la fila:
  -- ERROR: could not obtain lock on row in relation "cuentas"
COMMIT;

-- FOR UPDATE SKIP LOCKED: salta filas bloqueadas (útil para colas de tareas)
-- Procesador de tareas: tomar la siguiente tarea disponible sin esperar
BEGIN;
  SELECT * FROM tareas
  WHERE estado = 'pendiente'
  ORDER BY creado_en
  LIMIT 1
  FOR UPDATE SKIP LOCKED;
  -- Toma la primera tarea que no esté siendo procesada por otro worker
  UPDATE tareas SET estado = 'procesando' WHERE id = <id_obtenido>;
COMMIT;

-- FOR SHARE: bloqueo compartido (permite lectura, previene escritura)
BEGIN;
  SELECT * FROM productos WHERE id = 1 FOR SHARE;
  -- Otras transacciones pueden leer, pero no modificar esta fila
COMMIT;

Deadlocks

Un deadlock ocurre cuando dos transacciones se esperan mutuamente para liberar un bloqueo, creando un círculo vicioso. PostgreSQL los detecta automáticamente y cancela una de las transacciones:

-- Escenario de deadlock:
-- Transacción 1: bloquea cuenta A, luego intenta bloquear cuenta B
-- Transacción 2: bloquea cuenta B, luego intenta bloquear cuenta A
-- → Deadlock: ninguna puede continuar

-- ❌ Código que puede causar deadlock
-- Sesión 1:
BEGIN;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;  -- Bloquea cuenta 1
-- Espera... Sesión 2 ejecuta su primera línea
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;  -- Intenta bloquear cuenta 2 (bloqueada por S2)

-- Sesión 2 (concurrente):
BEGIN;
UPDATE cuentas SET saldo = saldo - 50 WHERE id = 2;   -- Bloquea cuenta 2
UPDATE cuentas SET saldo = saldo + 50 WHERE id = 1;   -- Intenta bloquear cuenta 1 (bloqueada por S1)
-- ERROR: deadlock detected

-- ✅ Solución: siempre bloquear recursos en el MISMO ORDEN
-- Tanto la Sesión 1 como la Sesión 2 deben acceder primero a id=1, luego a id=2

CREATE OR REPLACE FUNCTION transferir(
  p_de INTEGER,
  p_a  INTEGER,
  p_monto NUMERIC
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
  v_cuenta_menor INTEGER := LEAST(p_de, p_a);
  v_cuenta_mayor INTEGER := GREATEST(p_de, p_a);
BEGIN
  -- Siempre bloquear en orden ascendente por ID
  PERFORM id FROM cuentas WHERE id = v_cuenta_menor FOR UPDATE;
  PERFORM id FROM cuentas WHERE id = v_cuenta_mayor FOR UPDATE;
  
  UPDATE cuentas SET saldo = saldo - p_monto WHERE id = p_de;
  UPDATE cuentas SET saldo = saldo + p_monto WHERE id = p_a;
END;
$$;

Monitorear la actividad y los bloqueos

-- Ver transacciones activas
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duracion,
  query,
  state
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duracion DESC;

-- Ver bloqueos activos
SELECT
  blocked_locks.pid     AS pid_bloqueado,
  blocked_activity.query AS consulta_bloqueada,
  blocking_locks.pid    AS pid_bloqueador,
  blocking_activity.query AS consulta_bloqueadora
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.granted
  AND NOT blocked_locks.granted
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid;

-- Cancelar una consulta problemática (amigablemente)
SELECT pg_cancel_backend(pid);   -- Cancela la consulta, mantiene la conexión

-- Terminar una conexión (más drástico)
SELECT pg_terminate_backend(pid);

[!tip] Mantén las transacciones cortas: una transacción larga mantiene bloqueos durante mucho tiempo, bloqueando a otros usuarios. El patrón recomendado es: hacer toda la preparación de datos ANTES del BEGIN, ejecutar solo las instrucciones DML dentro de la transacción, y hacer COMMIT lo antes posible. Si necesitas procesar millones de filas, considera dividirlo en lotes (batches) con transacciones más pequeñas.

[!warning] ¡Configura lock_timeout y statement_timeout! En producción, añade estas configuraciones a nivel de sesión o de rol para evitar que transacciones colgadas bloqueen el sistema indefinidamente: SET lock_timeout = '5s'; (falla si no puede obtener un bloqueo en 5 segundos) y SET statement_timeout = '30s'; (cancela consultas que tarden más de 30 segundos). También configúralos en postgresql.conf o en los parámetros del rol de la aplicación.

En la siguiente lección aprenderemos sobre seguridad y backups: cómo gestionar roles, permisos, Row Level Security y cómo hacer y restaurar copias de seguridad de forma profesional.

nextSteps:

  • slug: seguridad-y-backups label: "Siguiente: Seguridad y backups"