Transacciones y aislamiento en bases de datos

Una transacción es una unidad lógica de trabajo que debe ejecutarse como un todo: o se aplica completa, o no se aplica nada. Es la garantía que evita que una base de datos quede a medias tras un fallo o una escritura concurrente. Entender transacciones y aislamiento es imprescindible para escribir código de datos correcto — y para diagnosticar los bugs de concurrencia más sutiles.

ACID: el contrato de las transacciones

ACID es el acrónimo de las cuatro garantías que una transacción debe cumplir:

💡 La consistencia suele delegarse en las constraints de la base (CHECK, UNIQUE, NOT NULL, FOREIGN KEY). Tú no escribes el código de consistencia: declaras las reglas y la base las hace cumplir dentro de cada transacción.

COMMIT y ROLLBACK

Toda transacción termina con COMMIT (hacer los cambios permanentes) o ROLLBACK (descartarlos). La mayoría de bases en modo autocommit: cada statement se commitea al instante, salvo que declares explícitamente una transacción.

BEGIN;

INSERT INTO cuentas (id, titular, saldo) VALUES (1, 'Ana', 500);
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;

-- algo sale mal en el camino...
SELECT 1/0;  -- error

ROLLBACK;    -- todo lo anterior se descarta

BEGIN;

UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;

COMMIT;      -- persiste de forma definitiva

⚠️ En PostgreSQL el BEGIN en sí no inicia trabajo real con el servidor: la primera consulta dentro de la transacción sí lo hace. Para ver el estado: SELECT * FROM pg_stat_activity;.

SAVEPOINT: sub-transacciones

Si no quieres descartarlo todo, usa puntos de guardado:

BEGIN;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;

SAVEPOINT sp1;
INSERT INTO cuentas (id) VALUES (1);  -- viola PK, falla
ROLLBACK TO sp1;                      -- vuelve atrás solo hasta sp1

COMMIT;   -- el UPDATE se mantiene

Los tres problemas de concurrencia

Cuando dos transacciones corren a la vez, pueden aparecer estos fenómenos. Los niveles de aislamiento existen precisamente para elegir cuáles toleras.

Dirty read (lectura sucia)

Leer un dato que otra transacción aún no ha commiteado (y que puede deshacerse):

T1: UPDATE saldo SET monto = 0 WHERE id=1;   (sin commit)
T2: SELECT monto WHERE id=1;  --> ve 0, dato que T1 aún no confirma
T1: ROLLBACK;  --> T2 leyó algo que nunca existió

Non-repeatable read (lectura no repetible)

Dentro de una misma transacción, la misma consulta devuelve valores distintos porque otra transacción commiteó un UPDATE entre medias:

T1: SELECT saldo WHERE id=1;  --> 100
T2: UPDATE saldo SET saldo=200 WHERE id=1; COMMIT;
T1: SELECT saldo WHERE id=1;  --> 200 (¡cambió entre lecturas!)

Phantom read (lectura fantasma)

La misma consulta de rango devuelve un número distinto de filas porque otra transacción commiteó filas nuevas (INSERT/DELETE) que cumplen el filtro:

T1: SELECT count(*) FROM pedidos WHERE cliente=5;  --> 2
T2: INSERT INTO pedidos(cliente) VALUES (5); COMMIT;
T1: SELECT count(*) FROM pedidos WHERE cliente=5;  --> 3 (una fila fantasma)

Niveles de aislamiento

El estándar SQL define cuatro niveles, de menor a mayor aislamiento (y menor a mayor coste de concurrencia):

⚠️ PostgreSQL no implementa READ UNCOMMITTED: si lo pides, te sube silenciosamente a READ COMMITTED. Además, con MVCC, REPEATABLE READ en PostgreSQL sí previene phantom reads — está más cerca del SERIALIZABLE del estándar.

💡 README COMMITTED es el valor por defecto en PostgreSQL (y Oracle, SQL Server y MariaDB). Es el equilibrio práctico: cada statement ve una instantánea consistente y confirmada.

En SQL

-- PostgreSQL (por defecto: read committed)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- ...
COMMIT;

-- MySQL (por defecto: repeatable read)
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

MVCC: cómo PostgreSQL evita el bloqueo

PostgreSQL implementa el aislamiento con MVCC (Multi-Version Concurrency Control). En lugar de bloquear la lectura de una fila mientras se escribe, mantiene múltiples versiones de la fila: cada transacción ve una instantánea (snapshot) tomada al momento de empezar.

INSERT fila v1  ──>  UPDATE fila v2  ──>  fila v3 (activa)
                      ↑ T1 ve v1        ↑ T2 ve v2

• El lector nunca bloquea al escritor ni el escritor al lector.
• Cada fila guarda metadatos de visibilidad (xmin/xmax en PostgreSQL): la transacción que la creó y la que la invalidó.
• Las versiones viejas se limpian con VACUUM; un VACUUM descuidado infla la tabla.

💡 Comparación mental: en MySQL/InnoDB el aislamiento REPEATABLE READ se apoya en undo logs y next-key locks; en PostgreSQL, en snapshots de MVCC. Mismo objetivo, mecánicas distintas.

Locks: bloqueos explícitos

MVCC resuelve el aislamiento de lecturas, pero para escribir sobre la misma fila sigues necesitando locks. SELECT ... FOR UPDATE bloquea una fila para tu transacción:

BEGIN;
SELECT saldo FROM cuentas WHERE id=1 FOR UPDATE;  -- bloquea la fila 1

UPDATE cuentas SET saldo = saldo - 100 WHERE id=1;
COMMIT;  -- al commitear se libera el lock

Patrón anti-bug de dinero: leer + decidir + escribir debe ir dentro de la misma transacción con FOR UPDATE, o dos transacciones pueden sobrescribirse:

-- MAL: dos transacciones leen 100, ambas escriben 50. Se pierde una actualización.
BEGIN;
SELECT saldo FROM cuentas WHERE id=1;       -- 100
-- (otra transacción también lee 100)
UPDATE cuentas SET saldo = 50 WHERE id=1;   -- escribe sobre valor viejo
COMMIT;

Row locks en PostgreSQL

Deadlocks: cómo evitarlos

Un deadlock ocurre cuando dos transacciones se esperan mutuamente:

T1: UPDATE fila A  →  espera la fila B
T2: UPDATE fila B  →  espera la fila A
→ nadie avanza, ambas esperan para siempre

La base lo detecta y aborta una de las dos (en PostgreSQL lanza ERROR: deadlock detected). Tú no lo “evitas” del todo: reduces la probabilidad.

Reglas de oro:

• Actualiza en el mismo orden siempre (ordena por id, por ejemplo).
• Mantén las transacciones cortas: menos tiempo sosteniendo locks.
• Evita entradas de usuario dentro de la transacción (esperas impredecibles).
• Maneja el error de deadlock reintentando la transacción completa.

-- el servidor detecta y aborta una; tu app debe reintentar
BEGIN;
SELECT ... FOR UPDATE;   -- deadlock aquí → ERROR, rollback automático
ROLLBACK;

Transacciones desde código

psycopg2 (PostgreSQL + Python)

Por defecto psycopg2 abre una transacción en el primer statement; tú decides cuándo cerrarla:

import psycopg2

conn = psycopg2.connect("dbname=tienda user=postgres")
cur = conn.cursor()

try:
    cur.execute("UPDATE cuentas SET saldo = saldo - 100 WHERE id = %s", (1,))
    cur.execute("UPDATE cuentas SET saldo = saldo + 100 WHERE id = %s", (2,))
    conn.commit()          # persiste ambos cambios
except Exception:
    conn.rollback()        # deshace ambos
    raise
finally:
    cur.close()
    conn.close()

💡 Psycopg2 está en modo autocommit=False por defecto. Si no llamas a commit(), la transacción queda abierta y las filas bloqueadas hasta que cierres la conexión. Establece conn.autocommit = True solo si quieres el comportamiento por statement.

SQLAlchemy

from sqlalchemy import create_engine, text

engine = create_engine("postgresql+psycopg2://user:pass@localhost/tienda")

with engine.begin() as conn:   # commit automático si no hay excepción
    conn.execute(text("UPDATE cuentas SET saldo = saldo - 100 WHERE id=:i"), {"i": 1})
    conn.execute(text("UPDATE cuentas SET saldo = saldo + 100 WHERE id=:i"), {"i": 2})
# rollback automático si algo lanza una excepción

Para control fino, gestiona la transacción manualmente:

with engine.connect() as conn:
    trans = conn.begin()
    try:
        conn.execute(text("UPDATE cuentas SET saldo = saldo - 100 WHERE id=:i"), {"i": 1})
        trans.commit()
    except Exception:
        trans.rollback()
        raise

¿Y en el ORM? (Django/ActiveRecord)

# Django: bloque de transacción atómica
from django.db import transaction

with transaction.atomic():
    cuenta.debito(100)      # todo o nada
    otra.credito(100)

⚠️ Cuidado con mezclar capas: si dejas una transacción abierta mientras haces una llamada HTTP, mantienes locks durante toda la latencia. Transacción = corta y sin I/O lento en medio.

Cheatsheet

Para profundizar

• PostgreSQL Documentation — Transaction Isolation: la referencia oficial con la matriz completa.
• PostgreSQL Documentation — Explicit Locking: row locks, deadlocks y advisory locks.
• MySQL Documentation — InnoDB Isolation Levels: cómo lo hace InnoDB, con next-key locks.
• Designing Data-Intensive Applications (Kleppmann): el capítulo 7 sobre transacciones es la mejor explicación conceptual.
• Martin Kleppmann — Isolation Levels Illustrated y snapshot isolation.
• Ruta completa: Bases de datos.