Database Transactions in PHP: ACID, Rollbacks & Real-World Patterns

<?php
/**
 * ACID Demo: Transferring funds between two bank accounts.
 * If ANY step fails, the entire operation is rolled back.
 * Both accounts stay consistent — no money is created or destroyed.
 */

$dsn = 'mysql:host=localhost;dbname=bank_app;charset=utf8mb4';
$dbUser = 'root';
$dbPassword = 'secret';

try {
    $pdo = new PDO($dsn, $dbUser, $dbPassword, [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // Throw exceptions on error
        PDO::ATTR_EMULATE_PREPARES   => false,                  // Use real prepared statements
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    $senderAccountId   = 101;
    $receiverAccountId = 202;
    $transferAmount    = 250.00;

    // ── Step 1: Start the transaction ──────────────────────────────────────
    // MySQL auto-commits every query by default. beginTransaction() turns
    // that OFF so nothing is saved until we explicitly call commit().
    $pdo->beginTransaction();

    // ── Step 2: Debit the sender ───────────────────────────────────────────
    // We use SELECT ... FOR UPDATE to lock this row during the transaction.
    // This prevents another concurrent request from reading a stale balance.
    $lockSender = $pdo->prepare(
        'SELECT balance FROM accounts WHERE account_id = :id FOR UPDATE'
    );
    $lockSender->execute([':id' => $senderAccountId]);
    $senderAccount = $lockSender->fetch();

    if (!$senderAccount) {
        throw new RuntimeException("Sender account {$senderAccountId} not found.");
    }

    if ($senderAccount['balance'] < $transferAmount) {
        throw new RuntimeException(
            "Insufficient funds. Balance: {$senderAccount['balance']}, Requested: {$transferAmount}"
        );
    }

    $debitSender = $pdo->prepare(
        'UPDATE accounts SET balance = balance - :amount WHERE account_id = :id'
    );
    $debitSender->execute([
        ':amount' => $transferAmount,
        ':id'     => $senderAccountId,
    ]);

    // ── Step 3: Credit the receiver ────────────────────────────────────────
    $creditReceiver = $pdo->prepare(
        'UPDATE accounts SET balance = balance + :amount WHERE account_id = :id'
    );
    $creditReceiver->execute([
        ':amount' => $transferAmount,
        ':id'     => $receiverAccountId,
    ]);

    // ── Step 4: Log the transaction for audit purposes ─────────────────────
    $logTransfer = $pdo->prepare(
        'INSERT INTO transfer_log (sender_id, receiver_id, amount, transferred_at)
         VALUES (:sender, :receiver, :amount, NOW())'
    );
    $logTransfer->execute([
        ':sender'   => $senderAccountId,
        ':receiver' => $receiverAccountId,
        ':amount'   => $transferAmount,
    ]);

    // ── Step 5: Everything succeeded — save all three changes atomically ───
    $pdo->commit();

    echo "Transfer successful: £{$transferAmount} moved from account "
       . "{$senderAccountId} to {$receiverAccountId}.\n";

} catch (RuntimeException $businessException) {
    // Business rule violation (insufficient funds, missing account)
    // Roll back any partial changes made in this transaction
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Transfer refused: " . $businessException->getMessage() . "\n";

} catch (PDOException $dbException) {
    // Database-level failure (connection dropped, constraint violated)
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Database error — transfer cancelled: " . $dbException->getMessage() . "\n";
}

<?php
/**
 * MySQLi Transaction: Creating an order and reducing inventory simultaneously.
 * Uses mysqli_report() to ensure errors throw exceptions rather than
 * returning false silently — a critical safety measure.
 */

// Force MySQLi to throw exceptions instead of returning false on failure.
// Place this BEFORE creating the connection — it affects the connection setup too.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

try {
    $mysqli = new mysqli('localhost', 'root', 'secret', 'shop_db');
    $mysqli->set_charset('utf8mb4'); // Always set charset explicitly

    $customerId  = 55;
    $productId   = 12;
    $orderQty    = 3;
    $priceEach   = 19.99;
    $totalAmount = $orderQty * $priceEach;

    // ── Start transaction ──────────────────────────────────────────────────
    $mysqli->begin_transaction();

    // ── Step 1: Check and lock inventory row ──────────────────────────────
    // FOR UPDATE locks this specific row until we commit or rollback.
    // Without this lock, two simultaneous orders could both read
    // stock_qty = 5 and both succeed, overselling the product.
    $stockCheck = $mysqli->prepare(
        'SELECT stock_qty FROM products WHERE product_id = ? FOR UPDATE'
    );
    $stockCheck->bind_param('i', $productId);
    $stockCheck->execute();
    $stockCheck->bind_result($currentStock);
    $stockCheck->fetch();
    $stockCheck->close(); // Close statement before opening another on same connection

    if ($currentStock === null) {
        throw new RuntimeException("Product ID {$productId} does not exist.");
    }

    if ($currentStock < $orderQty) {
        throw new RuntimeException(
            "Only {$currentStock} units in stock, but {$orderQty} requested."
        );
    }

    // ── Step 2: Insert the order record ───────────────────────────────────
    $insertOrder = $mysqli->prepare(
        'INSERT INTO orders (customer_id, total_amount, created_at)
         VALUES (?, ?, NOW())'
    );
    $insertOrder->bind_param('id', $customerId, $totalAmount);
    $insertOrder->execute();

    // Capture the auto-generated order ID immediately after insert
    $newOrderId = $mysqli->insert_id;
    $insertOrder->close();

    // ── Step 3: Insert the order line item ────────────────────────────────
    $insertLineItem = $mysqli->prepare(
        'INSERT INTO order_items (order_id, product_id, quantity, unit_price)
         VALUES (?, ?, ?, ?)'
    );
    $insertLineItem->bind_param('iiid', $newOrderId, $productId, $orderQty, $priceEach);
    $insertLineItem->execute();
    $insertLineItem->close();

    // ── Step 4: Decrement inventory ───────────────────────────────────────
    $updateStock = $mysqli->prepare(
        'UPDATE products SET stock_qty = stock_qty - ? WHERE product_id = ?'
    );
    $updateStock->bind_param('ii', $orderQty, $productId);
    $updateStock->execute();
    $updateStock->close();

    // ── All steps succeeded — commit everything at once ────────────────────
    $mysqli->commit();

    echo "Order #{$newOrderId} placed successfully. "
       . "£{$totalAmount} charged. Stock reduced by {$orderQty} units.\n";

} catch (RuntimeException $businessException) {
    $mysqli->rollback();
    echo "Order failed (business rule): " . $businessException->getMessage() . "\n";

} catch (mysqli_sql_exception $dbException) {
    // mysqli_report() causes MySQLi errors to throw mysqli_sql_exception
    $mysqli->rollback();
    echo "Order failed (database error): " . $dbException->getMessage() . "\n";

} finally {
    // The finally block always runs — clean up the connection
    if (isset($mysqli)) {
        $mysqli->close();
    }
}

<?php
/**
 * Savepoint Demo: Importing a batch of product records.
 * Valid rows are committed; individual bad rows are skipped via savepoint
 * rollback without losing all the good rows we've already processed.
 */

$dsn = 'mysql:host=localhost;dbname=catalogue_db;charset=utf8mb4';

try {
    $pdo = new PDO($dsn, 'root', 'secret', [
        PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);

    // Simulated batch of products coming in from a CSV import or API feed
    $productBatch = [
        ['sku' => 'BOOT-001', 'name' => 'Hiking Boot',   'price' => 89.99,  'stock' => 50],
        ['sku' => 'BOOT-002', 'name' => '',              'price' => 45.00,  'stock' => 20],  // INVALID: empty name
        ['sku' => 'BOOT-003', 'name' => 'Trail Sneaker', 'price' => -10.00, 'stock' => 15],  // INVALID: negative price
        ['sku' => 'BOOT-004', 'name' => 'Running Shoe',  'price' => 65.50,  'stock' => 30],
    ];

    $insertProduct = $pdo->prepare(
        'INSERT INTO products (sku, name, price, stock_qty, created_at)
         VALUES (:sku, :name, :price, :stock, NOW())'
    );

    $importedCount = 0;
    $skippedRows   = [];

    // Start the outer transaction — wraps the entire batch import
    $pdo->beginTransaction();

    foreach ($productBatch as $rowIndex => $product) {

        // ── Create a savepoint before attempting each row ──────────────────
        // If this specific row fails, we roll back ONLY to here,
        // preserving all successfully inserted rows before it.
        $savepointName = 'sp_product_row_' . $rowIndex;
        $pdo->exec("SAVEPOINT {$savepointName}");

        try {
            // Validate before hitting the DB — fail fast
            if (empty(trim($product['name']))) {
                throw new InvalidArgumentException("Row {$rowIndex}: Product name cannot be empty.");
            }
            if ($product['price'] <= 0) {
                throw new InvalidArgumentException("Row {$rowIndex}: Price must be positive, got {$product['price']}.");
            }

            $insertProduct->execute([
                ':sku'   => $product['sku'],
                ':name'  => $product['name'],
                ':price' => $product['price'],
                ':stock' => $product['stock'],
            ]);

            // Row succeeded — release the savepoint (no longer needed)
            $pdo->exec("RELEASE SAVEPOINT {$savepointName}");
            $importedCount++;

            echo "  ✓ Imported: {$product['sku']} — {$product['name']}\n";

        } catch (InvalidArgumentException | PDOException $rowException) {
            // Roll back ONLY this row's changes, not the whole batch
            $pdo->exec("ROLLBACK TO SAVEPOINT {$savepointName}");
            $skippedRows[] = "  ✗ Skipped {$product['sku']}: " . $rowException->getMessage();
            echo end($skippedRows) . "\n";
        }
    }

    // Commit all successfully inserted rows in one go
    $pdo->commit();

    echo "\nImport complete. {$importedCount} products saved.\n";
    if (!empty($skippedRows)) {
        echo count($skippedRows) . " rows skipped:\n";
        echo implode("\n", $skippedRows) . "\n";
    }

} catch (PDOException $fatalException) {
    // Something catastrophic failed (connection lost, etc.)
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Fatal import error: " . $fatalException->getMessage() . "\n";
}

<?php
/**
 * A production-ready transaction wrapper.
 * Encapsulates all the boilerplate so your business logic stays clean.
 * Returns whatever value your callback returns, so it's fully composable.
 */

class Database
{
    private PDO $pdo;

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    /**
     * Execute a callable inside a database transaction.
     *
     * @template T
     * @param callable(): T $operation  Any callable that performs DB work
     * @return T                        Returns whatever the callable returns
     * @throws Throwable                Re-throws any exception after rolling back
     */
    public function transactional(callable $operation): mixed
    {
        // Guard against nested beginTransaction() calls which would
        // silently commit the outer transaction in MySQL.
        $weStartedTransaction = !$this->pdo->inTransaction();

        if ($weStartedTransaction) {
            $this->pdo->beginTransaction();
        }

        try {
            $result = $operation($this->pdo); // Pass PDO in so the callable can use it

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

            return $result;

        } catch (Throwable $exception) {
            // Catch Throwable (not just Exception) to also catch Errors
            // e.g. TypeError, which can happen with strict typed code
            if ($weStartedTransaction && $this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            // Re-throw so the caller can handle or log the specific error
            throw $exception;
        }
    }

    public function getPdo(): PDO
    {
        return $this->pdo;
    }
}

// ─── Example: Using the wrapper to register a new user ────────────────────────

function registerNewUser(Database $db, string $email, string $hashedPassword, string $roleName): int
{
    return $db->transactional(function (PDO $pdo) use ($email, $hashedPassword, $roleName): int {

        // Insert the user account
        $insertUser = $pdo->prepare(
            'INSERT INTO users (email, password_hash, created_at) VALUES (:email, :hash, NOW())'
        );
        $insertUser->execute([':email' => $email, ':hash' => $hashedPassword]);
        $newUserId = (int) $pdo->lastInsertId();

        // Look up the role ID — throws PDOException if role doesn't exist
        $findRole = $pdo->prepare('SELECT role_id FROM roles WHERE role_name = :name');
        $findRole->execute([':name' => $roleName]);
        $role = $findRole->fetch();

        if (!$role) {
            // Throwing here triggers the rollback in transactional()
            throw new RuntimeException("Role '{$roleName}' does not exist.");
        }

        // Assign the role to the new user
        $assignRole = $pdo->prepare(
            'INSERT INTO user_roles (user_id, role_id) VALUES (:user_id, :role_id)'
        );
        $assignRole->execute([':user_id' => $newUserId, ':role_id' => $role['role_id']]);

        return $newUserId; // Return the new user's ID to the caller
    });
}

// ─── Bootstrapping ────────────────────────────────────────────────────────────
$pdo = new PDO('mysql:host=localhost;dbname=app_db;charset=utf8mb4', 'root', 'secret', [
    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

$database = new Database($pdo);

try {
    $userId = registerNewUser(
        $database,
        'alice@example.com',
        password_hash('securepassword', PASSWORD_BCRYPT),
        'editor'
    );
    echo "User registered with ID: {$userId}\n";

} catch (RuntimeException $e) {
    echo "Registration failed: " . $e->getMessage() . "\n";
} catch (PDOException $e) {
    echo "Database error during registration: " . $e->getMessage() . "\n";
}