PHP PDO Transactions: commit and rollBack Example

A transaction makes several queries behave like one: either all of them succeed, or none of them do. That matters any time a half-finished change would leave bad data behind, such as moving money between accounts, creating an order with its items, or importing a file. Here is how to do it properly with PHP PDO, including the traps that make transactions silently not work.

The pattern to copy

try {
    $pdo->beginTransaction();

    // ...your queries...

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

This only works if PDO throws exceptions on errors. That is the default from PHP 8.0, but set PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION anyway, as shown in my PDO prepared statements guide.

A real example: transferring money

function transfer(PDO $pdo, int $from, int $to, int $pence): void
{
    $pdo->beginTransaction();
    try {
        $take = $pdo->prepare(
            'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?'
        );
        $take->execute([$pence, $from, $pence]);
        if ($take->rowCount() !== 1) {
            throw new RuntimeException('Insufficient funds');
        }

        $give = $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');
        $give->execute([$pence, $to]);

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

Storing money as whole pence (an integer) avoids floating point rounding errors. The balance >= ? check inside the UPDATE stops two simultaneous requests from overdrawing the account.

Order with line items

A common case is inserting a parent row, reading its ID, then inserting children:

$pdo->beginTransaction();
try {
    $pdo->prepare('INSERT INTO orders (user_id) VALUES (?)')->execute([$userId]);
    $orderId = (int) $pdo->lastInsertId();

    $item = $pdo->prepare('INSERT INTO order_items (order_id, sku, qty) VALUES (?, ?, ?)');
    foreach ($cart as $line) {
        $item->execute([$orderId, $line['sku'], $line['qty']]);
    }
    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

More on reading the new ID in PDO insert and lastInsertId().

Why your rollback does nothing

1. The table uses MyISAM

MyISAM tables do not support transactions. rollBack() returns without error but the rows stay. Check with SHOW TABLE STATUS and convert with ALTER TABLE accounts ENGINE=InnoDB;.

2. A DDL statement committed it for you

In MySQL, statements like CREATE TABLE, ALTER TABLE, DROP TABLE and TRUNCATE cause an implicit commit. Everything before them is saved, and on PHP 8 a later commit() or rollBack() throws "There is no active transaction". Keep schema changes out of transactions on MySQL.

3. Errors are not exceptions

With the old silent error mode, a failed query returns false, your code carries on, and commit() saves a partial change. Use exception mode.

Useful helpers

$pdo->inTransaction() returns true while a transaction is open. Use it before calling rollBack() in shared error handlers.
PDO does not support nested transactions. Calling beginTransaction() twice throws. If you need partial rollbacks, use SQL savepoints: SAVEPOINT s1 and ROLLBACK TO SAVEPOINT s1.
Keep transactions short. Do not call an external API or send an email while a transaction holds row locks.

A reusable wrapper

function tx(PDO $pdo, callable $fn): mixed
{
    $pdo->beginTransaction();
    try {
        $result = $fn($pdo);
        $pdo->commit();
        return $result;
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

$orderId = tx($pdo, fn(PDO $db) => createOrder($db, $cart));

FAQs

Do I need a transaction for a single INSERT?

No. A single statement is already atomic in InnoDB. Use transactions when two or more statements must succeed together.

Does PDO roll back automatically if the script crashes?

Yes. If the connection closes with an open transaction, the database rolls it back. Relying on that is sloppy, though; roll back explicitly.

Are transactions faster for bulk inserts?

Usually much faster, because the database commits once instead of once per row.

Can I use transactions with SQLite and PostgreSQL?

Yes, the same PDO methods work. PostgreSQL even allows DDL inside a transaction, unlike MySQL.

See every PDO guide in one place on the PHP PDO tutorial hub.