Análisis de errores típicos al trabajar con transacciones anidadas

Programar en Postgres es toda una aventura: a veces se convierte en una misión llamada "Encuentra tu error". En este bloque vamos a hablar de los errores típicos y trampas que puedes encontrar al trabajar con transacciones anidadas. ¡Vamos allá!

Uso incorrecto de comandos transaccionales dentro de funciones y procedimientos

Error: intentar usar COMMIT, ROLLBACK o SAVEPOINT dentro de una FUNCTION.

Por qué: En PostgreSQL las funciones (CREATE FUNCTION ... LANGUAGE plpgsql) siempre se ejecutan en el contexto de una sola transacción externa, y cualquier comando transaccional dentro de la función está prohibido. Si lo intentas, te saldrá un error de sintaxis.

Ejemplo de error:

CREATE OR REPLACE FUNCTION f_bad() RETURNS void AS $$
BEGIN
    SAVEPOINT sp1;  -- Error: comandos transaccionales prohibidos
END;
$$ LANGUAGE plpgsql;

Cómo hacerlo bien:

Para operaciones atómicas que deben ejecutarse "todo o nada", usa funciones sin comandos transaccionales explícitos. Si necesitas guardar cambios por etapas, usa procedimientos.

Error: intentar usar ROLLBACK TO SAVEPOINT en un procedimiento en PL/pgSQL.

Por qué: en PostgreSQL 17 solo se permiten los comandos COMMIT, ROLLBACK, SAVEPOINT, RELEASE SAVEPOINT dentro de procedimientos (CREATE PROCEDURE ... LANGUAGE plpgsql). Pero ROLLBACK TO SAVEPOINT en PL/pgSQL no se puede usar. Cualquier intento terminará en error de sintaxis.

Ejemplo de error:

CREATE PROCEDURE p_bad()
LANGUAGE plpgsql
AS $$
BEGIN
    SAVEPOINT sp1;
    -- ...
    ROLLBACK TO SAVEPOINT sp1; -- ¡Error! No se puede usar
END;
$$;

Cómo hacerlo bien:

Para un "rollback parcial" usa bloques BEGIN ... EXCEPTION ... END — estos crean automáticamente un savepoint; si hay un error dentro del bloque, todos los cambios se revierten al inicio del bloque.

CREATE PROCEDURE p_good()
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- operaciones que pueden lanzar error
        ...
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Rollback dentro del bloque BEGIN ... EXCEPTION ... END';
    END;
END;
$$;

Llamadas anidadas a procedimientos: limitaciones y errores típicos

Error: llamar a un procedimiento con COMMIT/ROLLBACK explícito dentro de una transacción de cliente ya abierta.

Por qué: los procedimientos con control transaccional solo funcionan bien en modo autocommit (un procedimiento — una transacción), si no, al intentar usar COMMIT o ROLLBACK dentro del procedimiento, sale error: la transacción ya está abierta en el cliente.

Ejemplo:

# En Python con psycopg2 por defecto autocommit=False
cur.execute("BEGIN;")
cur.execute("CALL my_proc();")   -- Error al intentar COMMIT dentro de my_proc

Cómo hacerlo bien:

• Antes de llamar a procedimientos, pon la conexión en modo autocommit.
• No llames a procedimientos desde funciones ni usando SELECT.

Error: los procedimientos con control transaccional (COMMIT, ROLLBACK) no funcionan si se llaman NO con CALL (por ejemplo, usando SELECT).

Por qué: Solo la llamada con CALL (o en un bloque DO anónimo) permite controlar transacciones. No se pueden llamar desde funciones.

Problemas de bloqueos y deadlocks (Deadlock)

Los bloqueos son como invitados no deseados: primero molestan, luego causan caos. El deadlock ocurre cuando las transacciones se esperan mutuamente para siempre. Aquí tienes un ejemplo típico:

1. La transacción A bloquea una fila en la tabla orders e intenta actualizar una fila en la tabla products.
2. La transacción B bloquea una fila en la tabla products e intenta actualizar una fila en la tabla orders.

Al final, ninguna transacción puede continuar. Es como dos coches intentando girar en una esquina estrecha al mismo tiempo, y el resultado es un atasco.

Ejemplo:

-- Transacción A
BEGIN;
UPDATE orders SET status = 'Procesando' WHERE id = 1;

-- Transacción B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;

-- Ahora la transacción A intenta actualizar la misma fila en `products`,
-- y la transacción B intenta cambiar la fila en `orders`.
-- ¡Deadlock!

¿Cómo evitarlo?

1. Actualiza los datos siempre en el mismo orden. Por ejemplo, primero orders, luego products.
2. Evita transacciones demasiado largas.
3. Usa LOCK con cabeza, indicando el nivel mínimo de bloqueo necesario.

Uso incorrecto de SQL dinámico (EXECUTE)

El SQL dinámico, si lo usas sin cuidado, puede ser una fuente de dolores de cabeza. El error más común es la inyección SQL. Por ejemplo:

EXECUTE 'SELECT * FROM orders WHERE id = ' || user_input;

Si user_input contiene algo como 1; DROP TABLE orders;, puedes despedirte de la tabla orders.

¿Cómo evitarlo? Usa consultas preparadas:

EXECUTE 'SELECT * FROM orders WHERE id = $1' USING user_input;

Así proteges tu aplicación de inyecciones SQL.

Rollback de la transacción tras un mal manejo de errores

Si los errores no se manejan bien, la transacción puede quedarse en un estado inválido. Por ejemplo:

BEGIN;

INSERT INTO orders (order_id, status) VALUES (1, 'Pendiente');

BEGIN;
-- Alguna operación que lanza error
INSERT INTO non_existing_table VALUES (1);
-- Error, pero la transacción no se cerró

COMMIT; -- Error: la transacción actual está abortada

Por el error, todo el código se queda atascado.

¿Cómo evitarlo? Usa bloques EXCEPTION para hacer rollback correctamente:

BEGIN
    INSERT INTO orders (order_id, status) VALUES (1, 'Pendiente');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Ocurrió un error, la transacción será revertida.';
END;

Cómo evitar errores: consejos y recomendaciones

• Al escribir un procedimiento complejo, empieza siempre con pseudocódigo. Escribe todos los pasos y los posibles puntos de error.
• Usa SAVEPOINT para rollback aislado de transacciones. Pero no olvides liberarlos después de usarlos.
• Evita transacciones largas — cuanto más larga la transacción, más probabilidad de bloqueos.
• Para llamadas anidadas a procedimientos, asegúrate de que los contextos de transacción externo e interno estén bien sincronizados.
• Comprueba siempre el rendimiento de tus procedimientos con EXPLAIN ANALYZE.
• Registra los errores en tablas o archivos de texto — esto facilita el debug.

Ejemplos de errores y su corrección

Ejemplo 1: Error al llamar a un procedimiento anidado

Código con error:

BEGIN;

CALL process_order(5);

-- Dentro de process_order ocurrió un ROLLBACK
-- Toda la transacción queda inválida
COMMIT; -- Error

Código corregido:

BEGIN;

SAVEPOINT sp_outer;

CALL process_order(5);

-- Rollback solo si hay error
ROLLBACK TO SAVEPOINT sp_outer;

COMMIT;

Ejemplo 2: Problema de Deadlock

Código con error:

-- Transacción A
BEGIN;
UPDATE orders SET status = 'Procesando' WHERE id = 1;
-- Espera a `products`

-- Transacción B
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
-- Espera a `orders`

Corrección:

-- Ambas consultas se ejecutan en el mismo orden:
-- Primero `products`, luego `orders`.
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
UPDATE orders SET status = 'Procesando' WHERE id = 1;
COMMIT;

Estos errores muestran por qué trabajar con transacciones requiere atención y experiencia. Pero, como sabes, cuanto más practicas, menos posibilidades tienes de recibir un ROLLBACK en la vida real (y en tu carrera).