Transacciones de Base de Datos en Python, JS y Java

Visión general

Una transacción de base de datos trata una secuencia de operaciones como una única unidad de trabajo: o todas se confirman, o ninguna lo hace. Esa garantía — resumida por las propiedades ACID — es lo que evita que una transferencia fallida debite una cuenta sin acreditar la otra.

Esta recipe muestra el patrón central en Python, JavaScript y Java, junto con las técnicas de producción que lo rodean: niveles de aislamiento, savepoints, reintentos por deadlock y monitoreo de locks. Los extras exclusivos de PostgreSQL como advisory locks, LISTEN/NOTIFY y drivers async tienen su propia sección más abajo.

Cuándo usarlo

Detonantes típicos:

• El dinero tiene que moverse entre cuentas sin quedar a medias — Money and Currency cubre la parte de la aritmética decimal.
• Una compra debería actualizar el inventario de forma atómica — Batch Processing resuelve la versión masiva.
• Creas registros relacionados en varias tablas en un solo paso lógico
• Necesitas consistencia de lectura para consultas de reporting
• Previenes condiciones de carrera en escrituras concurrentes

No envuelvas una transacción alrededor de un solo UPDATE o INSERT — una sentencia única ya es atómica, y un BEGIN/COMMIT explícito solo añade round-trips y overhead de locks.

Solución

Python (SQLAlchemy / psycopg2)

import psycopg2

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

try:
    cur.execute("BEGIN")
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))
    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2))
    conn.commit()
    print("Transfer committed")
except Exception as e:
    conn.rollback()
    print(f"Rolled back: {e}")
finally:
    cur.close()
    conn.close()

JavaScript (Node.js + pg)

const { Pool } = require('pg');
const pool = new Pool();

async function transfer(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // El check de saldo va dentro del UPDATE para que sea atómico
    const debit = await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1 RETURNING balance',
      [amount, fromId]
    );
    if (debit.rowCount === 0) {
      throw new Error('Fondos insuficientes o cuenta no encontrada');
    }
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
    await client.query('COMMIT');
    console.log('Transfer committed');
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('Rolled back:', e);
  } finally {
    client.release();
  }
}

Java (JDBC)

import java.sql.*;

public class TransactionExample {
    // Usa BigDecimal en lugar de double para dinero en producción.
    public static void transfer(Connection conn, int fromId, int toId, double amount) throws SQLException {
        conn.setAutoCommit(false);
        try (PreparedStatement debit = conn.prepareStatement("UPDATE accounts SET balance = balance - ? WHERE id = ?");
             PreparedStatement credit = conn.prepareStatement("UPDATE accounts SET balance = balance + ? WHERE id = ?")) {
            debit.setDouble(1, amount);
            debit.setInt(2, fromId);
            debit.executeUpdate();

            credit.setDouble(1, amount);
            credit.setInt(2, toId);
            credit.executeUpdate();

            conn.commit();
            System.out.println("Transfer committed");
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

Cómo funciona

BEGIN abre el bloque transaccional: desde ahí, cada fila que escribes queda bloqueada hasta que la transacción termina — cualquier otra sesión que intente tocarlas tiene que esperar. Tus cambios permanecen invisibles para otras sesiones (en el nivel de aislamiento por defecto) hasta el COMMIT.

COMMIT hace dos cosas: vuelca el Write-Ahead Log (WAL) para que los cambios sobrevivan a un fallo, y libera todos los locks que la transacción retenía. ROLLBACK descarta todos los cambios hechos desde el BEGIN y libera los mismos locks. Si la conexión se cae antes del COMMIT, la base de datos hace rollback automáticamente — una transacción a medias nunca puede persistir.

Las cuatro garantías ACID se mapean a este flujo:

• Atomicidad: todas las sentencias se confirman o ninguna lo hace. La transferencia de arriba no puede debitar la cuenta 1 sin acreditar la cuenta 2.
• Consistencia: las restricciones (foreign keys, check constraints) se validan al confirmar, así que una transacción no puede dejar el esquema en estado inválido.
• Aislamiento: dos transacciones corriendo a la vez no pueden espiar las escrituras no confirmadas de la otra — cuánto sí pueden ver es trabajo del nivel de aislamiento, que se cubre en la siguiente sección.
• Durabilidad: una vez que COMMIT retorna, los datos están en disco — un corte de luz no pierde nada confirmado.

Como las escrituras retienen locks hasta el final, la duración de la transacción controla directamente la contención. Una transacción que espera una llamada HTTP o input de usuario mientras retiene locks puede frenar a todas las sesiones que tocan las mismas filas.

Niveles de aislamiento SQL

-- Sintaxis PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- tus operaciones
COMMIT;

Nivel	Dirty Read	Non-Repeatable Read	Phantom Read	Anomalía de serialización	Rendimiento
READ UNCOMMITTED	Permitido	Permitido	Permitido	Permitida	Más rápido
READ COMMITTED	Prevenido	Permitido	Permitido	Permitida	Default (PG, Oracle)
REPEATABLE READ	Prevenido	Prevenido	Permitido	Permitida	Default (MySQL)
SERIALIZABLE	Prevenido	Prevenido	Prevenido	Prevenida	Más lento, más seguro

Las anomalías de la tabla describen lo que una segunda sesión puede observar mientras tu transacción sigue abierta. Un dirty read devuelve datos no confirmados que pueden ser revertidos después, así que el valor que viste nunca existió oficialmente. Un non-repeatable read significa que tu segundo SELECT sobre la misma fila devuelve un valor distinto — otra transacción coló un commit entre las dos lecturas. Un phantom read es la misma idea aplicada a un conjunto: una consulta WHERE repetida devuelve filas nuevas que coinciden con el filtro.

Subir de nivel de aislamiento compra corrección a cambio de throughput. SERIALIZABLE aborta cualquier transacción que no pueda demostrar como serializable, así que tu aplicación necesita lógica de reintento — la mayoría de cargas se queda en READ COMMITTED y resuelve las anomalías con locks explícitos. La documentación de aislamiento de PostgreSQL lista las anomalías exactas que previene cada nivel.

Variantes

Los patrones de abajo cubren las situaciones que aparecen cuando el flujo básico BEGIN/COMMIT ya funciona: rollbacks parciales, deadlocks, transacciones gestionadas por framework, sesiones descontroladas y control de concurrencia sin locks.

Savepoints para operaciones anidadas

Un savepoint marca una posición dentro de una transacción a la que puedes volver sin abortar la unidad completa — útil cuando un paso opcional puede fallar.

try:
    cur.execute("BEGIN")
    cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (100, 1))

    # Savepoint antes de operación riesgosa
    cur.execute("SAVEPOINT before_insert")
    try:
        cur.execute("INSERT INTO audit_log (action) VALUES ('transfer')")
    except Exception:
        cur.execute("ROLLBACK TO SAVEPOINT before_insert")
        # Continuar con transacción principal

    cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (100, 2))
    conn.commit()
except Exception as e:
    conn.rollback()
    print(f"Rolled back: {e}")

Lógica de reintento para deadlocks (Python)

PostgreSQL aborta una de las dos transacciones en deadlock con el error 40P01. Reintenta con backoff exponencial en lugar de fallar el request.

import time
from psycopg2 import errors

def with_retry(fn, max_retries=3, base_delay=0.1):
    for attempt in range(max_retries):
        try:
            return fn()
        except errors.DeadlockDetected:
            if attempt == max_retries - 1:
                raise
            delay = base_delay * (2 ** attempt)
            print(f"Deadlock detectado, reintentando en {delay}s...")
            time.sleep(delay)

def transfer(conn, from_id, to_id, amount):
    def _transfer():
        with conn.cursor() as cur:
            cur.execute("BEGIN")
            cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, from_id))
            cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, to_id))
            conn.commit()
    return with_retry(_transfer)

Transacción JavaScript con reintento

async function withRetry(fn, maxRetries = 3, baseDelay = 100) {
    for (let attempt = 0; attempt < maxRetries; attempt++) {
        try {
            return await fn();
        } catch (err) {
            if (err.code === '40P01' && attempt < maxRetries - 1) {
                const delay = baseDelay * Math.pow(2, attempt);
                console.log(`Deadlock detectado, reintentando en ${delay}ms...`);
                await new Promise(resolve => setTimeout(resolve, delay));
                continue;
            }
            throw err;
        }
    }
}

async function safeTransfer(fromId, toId, amount) {
    return withRetry(async () => {
        const client = await pool.connect();
        try {
            await client.query('BEGIN');
            await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
            await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
            await client.query('COMMIT');
        } catch (err) {
            await client.query('ROLLBACK');
            throw err;
        } finally {
            client.release();
        }
    });
}

Transacción Java con Spring @Transactional

import org.springframework.transaction.annotation.Transactional;
import org.springframework.retry.annotation.Retryable;
import org.springframework.dao.DeadlockLoserDataAccessException;

@Service
public class TransferService {

    @Retryable(value = DeadlockLoserDataAccessException.class, maxAttempts = 3)
    @Transactional(isolation = Isolation.READ_COMMITTED)
    public void transfer(Long fromId, Long toId, BigDecimal amount) {
        Account from = accountRepository.findById(fromId)
            .orElseThrow(() -> new IllegalArgumentException("Cuenta no encontrada"));
        Account to = accountRepository.findById(toId)
            .orElseThrow(() -> new IllegalArgumentException("Cuenta no encontrada"));

        from.setBalance(from.getBalance().subtract(amount));
        to.setBalance(to.getBalance().add(amount));

        accountRepository.save(from);
        accountRepository.save(to);

        auditLogRepository.save(new AuditLog("transfer", fromId, toId, amount));
    }
}

Detectar sesiones idle-in-transaction

Las conexiones que abren una transacción y nunca confirman retienen locks indefinidamente. Encuéntralas y termínalas en PostgreSQL:

-- PostgreSQL: encontrar transacciones inactivas que retienen locks
SELECT
    pid,
    state,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY duration DESC;

-- Matar sesiones idle-in-transaction largas
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - query_start > interval '5 minutes';

Optimistic locking con columna de versión

Cuando la contención es baja, omite los locks de fila y detecta conflictos por versión. Un rowcount de 0 significa que otra transacción ganó la carrera.

def update_with_optimistic_lock(conn, user_id, new_name, expected_version):
    with conn.cursor() as cur:
        cur.execute("BEGIN")
        cur.execute(
            "UPDATE users SET name = %s, version = version + 1 "
            "WHERE id = %s AND version = %s",
            (new_name, user_id, expected_version)
        )
        if cur.rowcount == 0:
            conn.rollback()
            raise ConcurrentModificationError("El usuario fue modificado por otra transacción")
        conn.commit()

Técnicas específicas de PostgreSQL

El siguiente grupo de variantes solo existe en PostgreSQL: advisory locks que coordinan trabajo entre transacciones, LISTEN/NOTIFY para despertar otras sesiones, y un driver async cuya API de transacciones no se parece en nada a la de psycopg2. Versiones ejecutables de estos ejemplos — más las consultas de monitoreo y una variante Java JDBC — están en el repo complementario.

Advisory locks

Los advisory locks son locks definidos por la aplicación que viven en la base de datos — no protegen ninguna tabla, solo coordinan a quien se moleste en consultarlos. Elige la variante según el alcance: los de nivel transacción se liberan solos en COMMIT o ROLLBACK, los de nivel sesión hay que liberarlos explícitamente, y try-lock devuelve un resultado enseguida en lugar de esperar.

-- Nivel transacción: se libera solo al hacer commit o rollback
SELECT pg_advisory_xact_lock(12345);

-- Nivel sesión: tienes que liberarlo tú
SELECT pg_advisory_lock(12345);
-- ... sección crítica ...
SELECT pg_advisory_unlock(12345);

-- Sin bloqueo: devuelve true/false al momento
SELECT pg_try_advisory_lock(12345);

cur.execute("SELECT pg_advisory_lock(%s)", (99999,))
try:
    cur.execute("DELETE FROM old_logs WHERE created_at < NOW() - INTERVAL '30 days'")
    conn.commit()
finally:
    cur.execute("SELECT pg_advisory_unlock(%s)", (99999,))
    conn.commit()

LISTEN/NOTIFY para eventos entre procesos

NOTIFY empuja un mensaje ligero a cada sesión que hace LISTEN en un canal — típicamente para despertar workers justo después de que un commit aterrice. La entrega ocurre al confirmar, así que una transacción revertida no notifica a nadie.

-- Proceso 1: suscribirse al canal
LISTEN order_created;

-- Proceso 2: disparar dentro o después de una transacción
NOTIFY order_created, '{"order_id": 42, "customer": "alice"}';

import select
import psycopg2

conn = psycopg2.connect("postgresql://user:pass@localhost/mydb")
conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
cur = conn.cursor()
cur.execute("LISTEN order_created")

while True:
    if select.select([conn], [], [], 1) == ([], [], []):
        continue
    conn.poll()
    while conn.notifies:
        notify = conn.notifies.pop(0)
        print(f"{notify.channel}: {notify.payload}")

Transacciones async con asyncpg

asyncpg envuelve el manejo de transacciones en un context manager asíncrono — sin BEGIN/COMMIT explícitos, y el bloque revierte ante cualquier excepción. La lectura FOR UPDATE de abajo fija la fila del balance para que una transferencia concurrente no se cuele entre el check y el débito.

import asyncio
import asyncpg

async def transfer_funds(conn, from_account, to_account, amount):
    async with conn.transaction():
        row = await conn.fetchrow(
            "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE",
            from_account
        )
        if row['balance'] < amount:
            raise ValueError("Fondos insuficientes")

        await conn.execute(
            "UPDATE accounts SET balance = balance - $1 WHERE id = $2",
            amount, from_account
        )
        await conn.execute(
            "UPDATE accounts SET balance = balance + $1 WHERE id = $2",
            amount, to_account
        )
        # Log de auditoría dentro de la misma transacción
        await conn.execute(
            "INSERT INTO transfers (from_account, to_account, amount) VALUES ($1, $2, $3)",
            from_account, to_account, amount
        )

async def main():
    conn = await asyncpg.connect('postgresql://user:pass@localhost/mydb')
    try:
        await transfer_funds(conn, 1, 2, 100.00)
    finally:
        await conn.close()

asyncio.run(main())

Context manager para transacciones

Cuando las llamadas BEGIN/COMMIT crudas se repiten por todo el codebase, un context manager pequeño pone el nivel de aislamiento, el commit y el rollback en un solo lugar:

from contextlib import contextmanager
import psycopg2

@contextmanager
def transaction(conn_str, isolation_level='READ COMMITTED'):
    conn = psycopg2.connect(conn_str)
    conn.set_isolation_level(
        getattr(psycopg2.extensions, f'ISOLATION_LEVEL_{isolation_level.replace(" ", "_").upper()}')
    )
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

with transaction("postgresql://user:pass@localhost/mydb") as conn:
    with conn.cursor() as cur:
        cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 1")
        cur.execute("INSERT INTO audit_log (action, amount) VALUES ('deposit', 100)")

Lo que funciona

• Mantén las transacciones cortas: cada segundo dentro de una transacción es un segundo que alguien más espera por tus locks
• Usa el nivel de aislamiento más bajo que aún te dé la corrección que necesitas
• Siempre maneja el rollback: try/catch/finally para que un error nunca deje una transacción colgada
• Usa optimistic locking para filas de alta contención (columnas de versión) — consulta Optimistic Locking
• Prueba bajo concurrencia: un test de un solo hilo nunca atrapa una condición de carrera
• Recolecta el input antes de abrir la transacción: no retengas locks mientras esperas a un usuario

Errores comunes

• Olvidar llamar commit() o rollback(), dejando conexiones idle en transacción
• Ejecutar consultas largas dentro de transacciones, causando contención de locks
• Usar SERIALIZABLE en todas partes sin entender el costo de rendimiento
• No manejar excepciones de deadlock (código de error 40P01 en PostgreSQL)
• Anidar transacciones sin savepoints
• Llamar APIs externas o esperar input de usuario mientras tienes una transacción abierta

Tips de producción

Estos cinco ajustes y técnicas mantienen las transacciones sanas bajo carga real — aplícalos antes de necesitarlos, no después de la primera tormenta de locks.

1. Configura lock_timeout para transacciones de escritura. Sin él, una transacción puede hacer cola en un lock indefinidamente mientras los requests se apilan detrás; con él, la consulta aborta y falla rápido:

SET lock_timeout = '5s';

2. Usa SELECT ... FOR UPDATE para pessimistic locking. Bloquea las filas que vas a actualizar para que las transacciones concurrentes esperen en lugar de conflictuar después:

BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Lógica de aplicación aquí
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

3. Agrupa updates dentro de una sola transacción. Agrupar escrituras reduce el overhead de transacción y el volumen de WAL comparado con confirmar cada fila por separado:

cur.execute("BEGIN")
for item in items:
    cur.execute("UPDATE inventory SET stock = stock - %s WHERE id = %s", (item.qty, item.id))
conn.commit()

4. Exporta un snapshot para lecturas consistentes entre transacciones. pg_export_snapshot permite que dos conexiones compartan la misma vista en un punto del tiempo — útil para generar reportes en paralelo:

-- Transacción 1
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT pg_export_snapshot();
-- Retorna '00000003-00000001-1'

-- Transacción 2
BEGIN ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION SNAPSHOT '00000003-00000001-1';

5. Registra la duración de las transacciones. Las transacciones lentas son invisibles hasta que las mides:

import time
import logging

logger = logging.getLogger(__name__)

start = time.monotonic()
try:
    cur.execute("BEGIN")
    # ... operaciones
    conn.commit()
finally:
    elapsed = time.monotonic() - start
    if elapsed > 1.0:
        logger.warning(f"Transacción lenta: {elapsed:.2f}s")

Tips de Rendimiento

1. Mantén transacciones por debajo de 100ms cuando sea posible. Las transacciones cortas reducen la contención de locks y mejoran el throughput.

2. Usa COPY en lugar de INSERT para cargas masivas. COPY es considerablemente más rápido y genera menos WAL:

BEGIN;
COPY users FROM '/path/to/users.csv' WITH (FORMAT csv, HEADER true);
COMMIT;

3. Configura synchronous_commit = off para escrituras no críticas. Reduce latencia al no esperar el flush de WAL. Usa solo para datos que pueden regenerarse:

SET LOCAL synchronous_commit = off;

4. Usa tablas UNLOGGED para datos de staging. Omitir las escrituras de WAL acelera las importaciones masivas y las tablas temporales — solo no guardes ahí nada que no puedas regenerar:

CREATE UNLOGGED TABLE temp_import (id INT, data TEXT);

5. Monitorea pg_locks y pg_stat_database para contención. La consulta de abajo encuentra transacciones bloqueadas; pg_stat_database rastrea commits, rollbacks y deadlocks por base de datos, y pg_stat_statements añade tiempos por consulta encima:

SELECT
    bl.pid AS blocked_pid,
    kl.pid AS blocking_pid,
    a.query AS blocked_query,
    ka.query AS blocking_query
FROM pg_locks bl
JOIN pg_stat_activity a ON bl.pid = a.pid
JOIN pg_locks kl ON bl.locktype = kl.locktype
    AND bl.database IS NOT DISTINCT FROM kl.database
    AND bl.relation IS NOT DISTINCT FROM kl.relation
    AND bl.pid != kl.pid
JOIN pg_stat_activity ka ON kl.pid = ka.pid
WHERE NOT bl.granted;

-- Tasas de transacciones por base de datos
SELECT datname, xact_commit, xact_rollback, deadlocks
FROM pg_stat_database
WHERE datname = current_database();