Database Transactions

A transaction groups several database operations into a single all-or-nothing unit: either every statement in it succeeds and gets saved permanently, or something goes wrong and every statement in it is undone, as if none of them had run at all. This matters anywhere an operation involves more than one write that has to stay in sync — the textbook example being moving money between two accounts, where debiting one row and crediting another must either both happen or neither happen. This page covers PDO's transaction methods, why they matter, and how to structure the try/catch around them correctly.

Why a multi-step operation needs a transaction

Imagine transferring $50 from Account A to Account B without a transaction: first an UPDATE subtracts $50 from Account A, then a second UPDATE adds $50 to Account B. If the first update succeeds but the server crashes, the connection drops, or the second query throws an error before it runs, $50 has simply vanished — deducted from one account and never credited to the other. A transaction prevents exactly this: neither update is made permanent until both have succeeded.

beginTransaction(), commit(), and rollBack()

PDO exposes transaction control as three methods on the connection object. ->beginTransaction() marks the start of the group of statements; ->commit() makes every change since then permanent; ->rollBack() discards every change since ->beginTransaction() as though none of it happened.

Transferring funds between two accounts

PHP

<?php
function transferFunds(PDO $pdo, int $fromAccountId, int $toAccountId, float $amount): void
{
    $pdo->beginTransaction();

    try {
        $debit = $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?');
        $debit->execute([$amount, $fromAccountId]);

        $credit = $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');
        $credit->execute([$amount, $toAccountId]);

        $pdo->commit();
    } catch (PDOException $e) {
        $pdo->rollBack();
        throw $e; // let the caller know the transfer failed
    }
}

(both balances updated together, or neither changes at all)

Notice the shape: beginTransaction() first, the risky work inside a try block, commit() as the last line of the try, and rollBack() in the catch. If either UPDATE throws — say, because the connection drops mid-transfer, or a database constraint rejects a negative balance — execution jumps straight to catch and commit() never runs, so rollBack() undoes the debit that already happened.

A caught exception that doesn't roll back leaves the transaction open

It's easy to write a catch block that logs the error and moves on without calling rollBack():

try { $debit->execute([$amount, $fromAccountId]); $credit->execute([$amount, $toAccountId]); $pdo->commit(); } catch (PDOException $e) { error_log($e->getMessage()); // rollBack() is missing here }

Skipping rollBack() leaves the transaction open with the debit applied but not committed, which can hold database locks and leave the connection in an inconsistent state for whatever runs next. Every catch block around a transaction should call rollBack() before doing anything else.

Checking whether a transaction is already active

Calling ->beginTransaction() while a transaction is already open throws an exception, which matters if transactional code might be called from more than one place. ->inTransaction() reports whether a transaction is currently open, which is useful for guarding against starting one twice.

Guarding against a nested beginTransaction() call

PHP

<?php
if (!$pdo->inTransaction()) {
    $pdo->beginTransaction();
}

PDO doesn't support true nested transactions

Calling `beginTransaction()` twice without a commit or rollback in between raises an error on most drivers rather than creating a nested transaction. If your application logic can end up calling a transactional function from inside another one, structure the outer function to own the transaction and have inner functions just run queries, or check `->inTransaction()` before starting a new one.

A brief note on isolation levels

Isolation level controls how much one transaction can see of another transaction's uncommitted changes while both are running at the same time. Most applications never need to touch this and can rely on the database's default (commonly REPEATABLE READ for MySQL's InnoDB, READ COMMITTED for PostgreSQL) — but it's worth knowing the setting exists for the rare case where an application needs stricter guarantees, such as preventing two concurrent transactions from both reading the same "available seats" count and independently deciding, both incorrectly, that a booking should proceed.

Setting isolation level explicitly (driver/database-specific)

SQL

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Transactions and prepared statements work together as usual

A transaction doesn't change how individual statements are written — every query inside one should still use prepared statements with bound parameters exactly as it would outside a transaction. The transaction only changes when the changes become permanent, not how safely each statement handles its input.

Call beginTransaction() before the first write in a group of related changes.
Run every statement inside the transaction through a prepared statement, as always.
Call commit() as the last step once every statement has succeeded.
Call rollBack() in the catch block for any exception, before doing anything else in that block.
Use inTransaction() to avoid accidentally starting a transaction inside another one.

Tip

Reach for a transaction whenever an operation involves more than one write that must succeed or fail together — fund transfers, order placement that both creates an order row and decrements stock, or any "all of this or none of this" requirement. For a single `INSERT`, `UPDATE`, or `DELETE` on its own, a transaction adds nothing, since a single statement is already atomic by itself.