Transacciones robustas con PDO en PHP: savepoints, reintentos y manejo de bloqueos

https://chat.whatsapp.com/GxQXDqcjIHOBhOeF6ZcOGh

Cuando una operación en tu aplicación implica varios cambios en la base de datos (crear una orden, reservar stock, registrar movimiento contable), necesitas que esas operaciones sean atómicas y resilientes ante errores transitorios (deadlocks, timeouts). Aquí tienes patrones prácticos para usar PDO con transacciones, savepoints y reintentos, con ejemplos claros.

Buenas prácticas iniciales

Wrapper de conexión y utilidad de transacción con reintentos

Este patrón encapsula la lógica de reintentos y manejo de errores SQLSTATE. La función acepta una closure que recibe el objeto PDO.

class DB
{
    private PDO $pdo;

    public function __construct(array $cfg)
    {
        $dsn = sprintf('%s:host=%s;port=%d;dbname=%s;charset=%s',
            $cfg['driver'] ?? 'mysql', $cfg['host'], $cfg['port'] ?? 3306, $cfg['dbname'], $cfg['charset'] ?? 'utf8mb4'
        );

        $this->pdo = new PDO($dsn, $cfg['user'], $cfg['pass'], [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
        ]);
    }

    /**
     * Ejecuta una closure dentro de una transacción con reintentos en errores transitorios.
     * La closure recibe el PDO y debe lanzar excepciones si algo falla.
     */
    public function transaction(callable $closure, int $maxRetries = 3)
    {
        $attempt = 0;
        $delayMs = 50; // backoff inicial

        while (true) {
            try {
                if (!$this->pdo->inTransaction()) {
                    $this->pdo->beginTransaction();
                }

                $result = $closure($this->pdo);

                if ($this->pdo->inTransaction()) {
                    $this->pdo->commit();
                }

                return $result;
            } catch (PDOException $e) {
                // Codigo SQLSTATE: '40001' (postgre serializable), '40P01' (deadlock), MySQL deadlock es 1213 (error code)
                $sqlState = $e->getCode();
                $errorCode = $e->errorInfo[1] ?? null; // driver-specific code (p.ej. MySQL)

                if ($this->pdo->inTransaction()) {
                    $this->pdo->rollBack();
                }

                $attempt++;

                $isTransient = in_array($sqlState, ['40001', '40P01']) || in_array($errorCode, [1213, 1205]);

                if ($isTransient && $attempt <= $maxRetries) {
                    // backoff exponencial con jitter
                    usleep(($delayMs + rand(0, 20)) * 1000);
                    $delayMs *= 2;
                    continue; // reintentar
                }

                throw $e; // error no transitorio o reintentos agotados
            }
        }
    }
}

Uso práctico: crear una orden y reservar stock

Ejemplo de una operación compuesta que crea una orden, decrementa stock y registra una entrada de inventario. Se usan savepoints para aislar una parte que puede fallar sin abortar todo el flujo.

// $db es instancia de DB
try {
    $result = $db->transaction(function (PDO $pdo) use ($orderData, $items) {
        // 1) Crear orden
        $stmt = $pdo->prepare('INSERT INTO orders (user_id, total, created_at) VALUES (:u, :t, NOW())');
        $stmt->execute([':u' => $orderData['user_id'], ':t' => $orderData['total']]);
        $orderId = $pdo->lastInsertId();

        // 2) Por cada item: decrementar stock. Usamos savepoint por si falla un item
        foreach ($items as $i => $it) {
            $pdo->exec("SAVEPOINT item_{$i}");

            $upd = $pdo->prepare('UPDATE products SET stock = stock - :q WHERE id = :id AND stock >= :q');
            $upd->execute([':q' => $it['qty'], ':id' => $it['product_id']]);

            if ($upd->rowCount() === 0) {
                // No hay stock suficiente: revertir este item pero continuar (o decidir abortar)
                $pdo->exec("ROLLBACK TO SAVEPOINT item_{$i}");
                // lanzar excepción si quieres abortar la transacción completa
                throw new RuntimeException('Stock insuficiente para producto ' . $it['product_id']);
            }

            // registrar linea de orden
            $ins = $pdo->prepare('INSERT INTO order_items (order_id, product_id, qty, price) VALUES (:o, :p, :q, :pr)');
            $ins->execute([':o' => $orderId, ':p' => $it['product_id'], ':q' => $it['qty'], ':pr' => $it['price']]);
            $pdo->exec("RELEASE SAVEPOINT item_{$i}");
        }

        // 3) Registrar asiento contable (puede lanzar excepción)
        $acc = $pdo->prepare('INSERT INTO ledger (order_id, amount, created_at) VALUES (:o, :a, NOW())');
        $acc->execute([':o' => $orderId, ':a' => $orderData['total']]);

        return $orderId;
    }, 4); // reintenta hasta 4 veces en errores transitorios

    // $result contiene el orderId si todo salió bien
} catch (Exception $e) {
    // Manejo de error: informar al usuario, reintentar fuera de la transacción, etc.
    throw $e; // o loggear y devolver respuesta amigable
}

Notas sobre savepoints y nested transactions

Manejo de errores: cómo detectar deadlocks y errores de serialización

Código SQLSTATE y códigos específicos por motor:

En PDO, $e->getCode() suele devolver el SQLSTATE; $e->errorInfo contiene información adicional con índice 1 como código del driver.

Consejos de rendimiento y seguridad

Si necesitas incrementar la robustez a escala, considera usar colas (ej. procesar pagos o reconciliaciones fuera de la transacción principal) y diseñar operaciones idempotentes que se puedan reintentar sin efectos secundarios duplicados.

Tip avanzado: cuando soportes múltiples motores (MySQL/Postgres), centraliza la detección de errores transitorios en una estrategia que evalúe tanto SQLSTATE como el código de error del driver, y aplica backoff exponencial con jitter para evitar avalanchas de reintentos. Y una advertencia de seguridad: nunca incluyas datos sin validar en queries dinámicos; usa siempre consultas preparadas para evitar inyección SQL.