Transacciones de Base de Datos

Visión general

Una transacción de base de datos es una secuencia de operaciones tratadas como una unidad lógica de trabajo. Las transacciones garantizan las propiedades ACID: Atomicidad, Consistencia, Aislamiento y Durabilidad. Son esenciales para operaciones financieras, gestión de inventario y cualquier mutación de datos multi-paso donde la completitud parcial dejaría los datos en un estado inválido.

Cuándo usarlo

Usa esta recipe cuando:

• Transfieres dinero entre cuentas. Consulta Money and Currency para aritmética decimal exacta.
• Actualizas inventario después de una compra. Consulta Batch Processing para operaciones masivas.
• Creas registros relacionados en múltiples tablas
• Aseguras consistencia de lectura para queries de reporting
• Prevenes condiciones de carrera en escrituras concurrentes

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 - 100 WHERE id = 1")
    cur.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 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');
    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');
    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 {
    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);
        }
    }
}

Niveles de aislamiento SQL

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

Nivel	Dirty Read	Non-Repeatable Read	Phantom Read	Rendimiento
READ UNCOMMITTED	Permitido	Permitido	Permitido	Más rápido
READ COMMITTED	Prevenido	Permitido	Permitido	Default (PG, Oracle)
REPEATABLE READ	Prevenido	Prevenido	Permitido	Default (MySQL)
SERIALIZABLE	Prevenido	Prevenido	Prevenido	Más lento, más seguro

Lo que funciona

• Mantén transacciones cortas: Las transacciones largas retienen locks y bloquean otros queries
• Usa el nivel de aislamiento más bajo que cumpla tus requerimientos de corrección
• Siempre maneja rollback: Usa try/catch/finally para asegurar rollback en error
• Usa optimistic locking para datos de alta contención (columnas de versión). Consulta Optimistic Locking para concurrencia basada en versiones.
• Testea escenarios concurrentes: Simula condiciones de carrera en tu suite de tests
• Evita input de usuarios dentro de transacciones: Recolecta datos antes de iniciar la transacción

Errores comunes

• Olvidar llamar commit() o rollback(), dejando conexiones idle en transacción
• Ejecutar queries largos 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

Buenas prácticas adicionales

6. Configura lock_timeout para transacciones de escritura. Previene que las transacciones esperen indefinidamente por locks:

SET lock_timeout = '5s';

7. Usa SELECT ... FOR UPDATE para pessimistic locking. Bloquea las filas que vas a actualizar para prevenir modificaciones concurrentes:

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;

8. Batchea updates dentro de una sola transacción. Agrupa múltiples updates para reducir overhead de transacción y volumen de WAL:

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

9. Usa SET TRANSACTION SNAPSHOT para lecturas consistentes. Exporta un snapshot de una transacción e impórtalo en otra para lecturas consistentes cross-transacción:

-- 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';

10. Loguea la duración de transacciones. Rastrea cuánto tardan las transacciones para identificar las long-running:

import time

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 advisory locks para coordinación a nivel aplicación. Evita locks a nivel de fila cuando necesitas coordinación cross-transacción:

-- Adquirir advisory lock
SELECT pg_advisory_lock(12345);
-- ... lógica de aplicación
SELECT pg_advisory_unlock(12345);

5. Monitorea pg_locks para contención. Identifica transacciones bloqueadas:

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;