PDO Transactions

What Is a Transaction?

In simple terms: Some operations consist of several steps that must all happen — or none of them. Placing an order reduces the stock of each product, creates the order, and adds its items. If the third step fails, the first two must be undone; otherwise, stock disappears for an order that does not exist. A transaction groups such steps into one unit: either everything is saved (commit), or everything is undone (rollback).

Technically: A database transaction is a sequence of statements executed as a single unit with the ACID guarantees. PDO controls transactions with beginTransaction(), commit(), rollBack(), and inTransaction(). In MySQL, transactions require the InnoDB storage engine (the default).

The Basic Pattern

<?php
$pdo->beginTransaction();

try {
    // several INSERT / UPDATE / DELETE statements
    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    throw $e;
}

How It Works

1. beginTransaction() turns off auto-commit: changes are not yet permanent.
2. If every statement succeeds, commit() saves them all at once.
3. If anything throws — a database error or a business rule violation in PHP — the catch block undoes all changes since beginTransaction().
4. The exception is re-thrown, so the caller still learns that the operation failed.
5. inTransaction() avoids calling rollBack() when no transaction is active (for example, if beginTransaction() itself failed).

Practical Example: Placing an Order

<?php
require __DIR__ . '/src/pdo.php';

final class InsufficientStockException extends RuntimeException {}

function placeOrder(PDO $pdo, int $customerId, array $items): int
{
    $pdo->beginTransaction();

    try {
        $lookup = $pdo->prepare('SELECT id, price, stock FROM products WHERE sku = ? FOR UPDATE');
        $reduceStock = $pdo->prepare('UPDATE products SET stock = stock - ? WHERE id = ?');

        $lines = [];
        $totalPaise = 0;

        foreach ($items as $sku => $quantity) {
            $lookup->execute([$sku]);
            $product = $lookup->fetch();

            if ($product === false) {
                throw new InvalidArgumentException("Unknown product $sku");
            }
            if ($product['stock'] < $quantity) {
                throw new InsufficientStockException("Not enough stock for $sku ({$product['stock']} available)");
            }

            $reduceStock->execute([$quantity, $product['id']]);

            $lines[] = [$product['id'], $quantity, $product['price']];
            $totalPaise += (int) round((float) $product['price'] * 100) * $quantity;
        }

        $pdo->prepare('INSERT INTO orders (customer_id, status, total) VALUES (?, ?, ?)')
            ->execute([$customerId, 'pending', number_format($totalPaise / 100, 2, '.', '')]);
        $orderId = (int) $pdo->lastInsertId();

        $insertItem = $pdo->prepare('INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)');
        foreach ($lines as [$productId, $quantity, $unitPrice]) {
            $insertItem->execute([$orderId, $productId, $quantity, $unitPrice]);
        }

        $pdo->commit();
        return $orderId;
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

$pdo = pdo();

$orderId = placeOrder($pdo, 2, ['PEN-01' => 2, 'NB-A5' => 1]);
$total = $pdo->query("SELECT total FROM orders WHERE id = $orderId")->fetchColumn();
echo "Order placed for customer 2: ₹$total\n";

try {
    placeOrder($pdo, 3, ['LAMP-01' => 1, 'BAG-02' => 1]);
} catch (InsufficientStockException $e) {
    echo "Order failed: " . $e->getMessage() . "\n";
}

$lampStock = $pdo->query("SELECT stock FROM products WHERE sku = 'LAMP-01'")->fetchColumn();
echo "Desk Lamp stock after the failed order: $lampStock\n";

Output

Order placed for customer 2: ₹100.00
Order failed: Not enough stock for BAG-02 (0 available)
Desk Lamp stock after the failed order: 6

Step-by-Step Explanation

1. Locking: SELECT ... FOR UPDATE reads each product and locks its row until the transaction ends. Another request trying to buy the same product waits, so two customers cannot both buy the last unit.
2. Validation inside the transaction: stock is checked on the locked, current values.
3. First order: 2 Gel Pens (2 × ₹20) and 1 Notebook (₹60) — stock is reduced, the order and its items are inserted, and commit() saves everything: total ₹100.00.
4. Second order: the Desk Lamp's stock is reduced from 6 to 5 — but then the Backpack has no stock. The exception triggers rollBack(), which also undoes the Desk Lamp's reduction. Its stock is still 6.
5. Calculations use integer paise to avoid floating-point rounding errors; the total is stored as a DECIMAL string.
6. $orderId comes from lastInsertId() and is cast to int, so inserting it directly into the follow-up SELECT is safe here; values from users would still require placeholders.

Without the transaction, the failed order would have left the Desk Lamp's stock permanently reduced.

Things to Know About Transactions

No Nested Transactions

Calling beginTransaction() while a transaction is active throws a PDOException ("There is already an active transaction"). Structure code so that one outer layer (for example, a service method) owns the transaction, and inner functions run inside it without starting their own. MySQL savepoints (SAVEPOINT name / ROLLBACK TO name) can roll back part of a transaction, executed as plain SQL statements.

Implicit Commits

Some statements commit the current transaction automatically in MySQL — notably DDL such as CREATE TABLE, ALTER TABLE, DROP TABLE, and TRUNCATE. Never mix schema changes with data changes inside one transaction expecting a rollback to undo them.

Keep Transactions Short

Locked rows block other requests until commit() or rollBack(). Do not perform slow work inside a transaction:

Isolation Level

InnoDB's default isolation level is REPEATABLE READ: within a transaction, repeated reads of the same rows return the same data. Explicit locks (FOR UPDATE, or FOR SHARE for reading without allowing changes) give precise control where it matters.

Handling Deadlocks

When two transactions each wait for a row locked by the other, MySQL detects a deadlock, aborts one of them (error 1213), and lets the other continue. The aborted transaction can simply be retried:

<?php
function withDeadlockRetry(callable $operation, int $maxAttempts = 3): mixed
{
    for ($attempt = 1; ; $attempt++) {
        try {
            return $operation();
        } catch (PDOException $e) {
            $isDeadlock = ($e->errorInfo[1] ?? null) === 1213;

            if (!$isDeadlock || $attempt >= $maxAttempts) {
                throw $e;
            }

            usleep(random_int(50, 200) * 1000);   // short random pause before retrying
        }
    }
}

// $orderId = withDeadlockRetry(fn() => placeOrder($pdo, 2, ['PEN-01' => 1]));

errorInfo[1] contains MySQL's own error code (see PDO Error Handling). Because placeOrder() rolls back on failure, retrying it is safe. Accessing rows in a consistent order (for example, sorted by product ID) reduces the chance of deadlocks.

Common Mistakes

Forgetting to Roll Back

Effect: The transaction stays open; locks are held until the connection closes, and later statements run inside the failed transaction.

Fix: Always roll back in a catch (or finally) block.

Checking Stock Before the Transaction

Effect: Overselling: the stock can change between the check and the update.

Fix: Lock and check inside the transaction (FOR UPDATE), or use conditional updates (see MySQL Update Data).

Swallowing the Exception After Rollback

Effect: The caller believes the operation succeeded.

Fix: Re-throw, or return a clear failure result.

Long-Running Transactions

Effect: Other requests wait or time out (error 1205, lock wait timeout).

Fix: Keep transactions short; move external calls outside.

Using MyISAM Tables

Effect: No transaction support — rollbacks silently do nothing.

Fix: Use InnoDB.

Best Practices

• Wrap every multi-step change that must be all-or-nothing in a transaction.
• Use the beginTransaction() / commit() / rollBack() pattern with try/catch.
• Lock rows you read for decisions with SELECT ... FOR UPDATE.
• Keep transactions short, without external calls or emails inside.
• Retry transactions aborted by deadlocks a limited number of times.

Official reference: https://www.php.net/manual/en/pdo.transactions.php