Transactions: begin/commit/rollback, ACID, and Isolation

Use PDO transactions (beginTransaction/commit/rollBack) to group multi-statement operations atomically, explain the ACID properties, understand isolation levels and the dirty/non-repeatable/phantom read anomalies they prevent, and handle deadlocks with a retry-with-backoff pattern.

Many real operations touch more than one row, or more than one table, and need to succeed or fail as a single unit — transferring money between two accounts means both the debit and the credit must happen, or neither should. A transaction groups a sequence of SQL statements so the database guarantees they're applied together atomically. This lesson covers PDO's transaction API, the four ACID guarantees a transactional database provides, the isolation levels that trade consistency for concurrency, the three classic read anomalies (dirty, non-repeatable, phantom reads) those levels are designed to prevent, and what to do when two transactions deadlock against each other.

Transactions with PDO

A transaction is a sequence of one or more SQL statements that the database treats as a single, indivisible unit of work. You start one with beginTransaction(), run your statements as normal, and then either commit() to make all of the changes permanent at once, or rollBack() to discard every change made since the transaction began, as if none of it ever happened. If your script throws an uncaught exception mid-transaction and never calls commit(), the connection closing (or an explicit rollback in a catch block) undoes the partial work — the database never ends up in a state where only half the operation happened.

The standard pattern wraps the transaction in a try/catch: begin the transaction, run the statements, commit on success, and roll back in the catch block before re-throwing or handling the error. This guarantees that any failure partway through — a constraint violation, a deadlock, an application-level check that fails — leaves the database exactly as it was before the transaction started, not in some inconsistent halfway state.

Transactions matter most whenever an operation involves more than one write that must succeed or fail together: moving money between two account rows, decrementing inventory while creating an order record, or updating a parent row alongside its child rows. Wrapping only the *whole* operation in one transaction — not each individual statement — is what gives you the atomicity guarantee.

• $pdo->beginTransaction() — starts a transaction; PDO switches out of autocommit mode. • $pdo->commit() — makes every change since begin permanent, all at once. • $pdo->rollBack() — discards every change since begin, as if it never happened. • Standard pattern: begin → try statements → commit; on exception → rollBack, then handle/re-throw.

ACID: The Four Guarantees

ACID is the acronym for the four properties a transactional database engine (like InnoDB for MySQL, or PostgreSQL) guarantees for every transaction. Atomicity means a transaction's statements are all-or-nothing: either every one of them takes effect, or none of them do — there's no partial application even if the process crashes mid-transaction. Consistency means a transaction can only move the database from one valid state to another valid state, respecting every constraint (foreign keys, unique constraints, check constraints) it's aware of; a transaction that would violate a constraint is rejected, not partially applied.

Isolation means concurrently running transactions don't see each other's uncommitted, in-progress changes — from any one transaction's point of view, it looks as if transactions ran one after another, even though the engine may actually be interleaving them for performance. Exactly how strictly that illusion is maintained is controlled by the isolation level (covered next); a weaker isolation level trades some of that illusion for better concurrency/performance.

Durability means once a transaction commits, its changes survive — even a server crash or power loss immediately afterward won't lose them, because the engine has already written the change to durable storage (typically a write-ahead log flushed to disk) before reporting success. Durability is what makes commit() a meaningful promise: once it returns, the data really is safe.

• Atomicity — all of a transaction's statements apply, or none do. • Consistency — a transaction only moves the DB between states that satisfy its constraints. • Isolation — concurrent transactions don't observe each other's in-progress, uncommitted work. • Durability — once committed, changes survive crashes; they're on durable storage.

Isolation Levels and Read Anomalies

Full isolation (as if every transaction ran strictly one at a time) is expensive for concurrency, so SQL databases offer several isolation levels that trade strictness for throughput, from weakest to strongest: Read Uncommitted, Read Committed, Repeatable Read, and Serializable. Each stronger level prevents one more category of anomaly that weaker levels allow, at the cost of more locking or more transaction retries under contention. MySQL's InnoDB defaults to Repeatable Read; PostgreSQL defaults to Read Committed.

A dirty read happens when one transaction reads a row that another transaction has modified but not yet committed — if the writer then rolls back, the reader saw data that never actually existed. Example: Transaction A updates a balance to $500 but hasn't committed; Transaction B reads $500; Transaction A rolls back, reverting to $1000 — B's read of $500 was 'dirty', it never really happened. Read Uncommitted allows this; Read Committed and above prevent it.

A non-repeatable read happens when a transaction reads the same row twice and gets two different values, because another transaction committed a change to that row in between. Example: Transaction A reads a product's price as $50; Transaction B updates and commits the price to $60; Transaction A reads the same row again and now sees $60 — within what A expected to be one consistent view of the data. Repeatable Read and Serializable prevent this by holding the initial read stable for the duration of the transaction. A phantom read is the row-set version of the same problem: a transaction re-runs the same range query (e.g. 'all orders over $100') and gets a *different set of rows* the second time, because another transaction inserted or deleted rows matching that range in between. Only Serializable fully prevents phantom reads; MySQL's InnoDB Repeatable Read prevents most phantoms via gap locking but the SQL standard doesn't guarantee it at that level.

• Isolation levels (weakest → strongest): Read Uncommitted, Read Committed, Repeatable Read, Serializable. • Dirty read: seeing another transaction's uncommitted (possibly soon-to-be-rolled-back) changes. • Non-repeatable read: re-reading the same row within a transaction yields a different value. • Phantom read: re-running the same range query within a transaction yields a different set of rows. • Stronger isolation = fewer anomalies but more locking/contention and lower concurrency.

Deadlocks and Retrying

A deadlock happens when two transactions each hold a lock the other one needs, and each is waiting for the other to release its lock first — neither can ever proceed. Classic example: Transaction A locks row 1 then tries to lock row 2; Transaction B, running concurrently, locks row 2 then tries to lock row 1. Both wait forever unless something intervenes. The database engine detects this cycle and forcibly aborts one of the two transactions (rolling it back) so the other can proceed — it does not just hang forever.

Because the engine picks a 'victim' transaction to kill, your application code has to be ready for a transaction to fail specifically due to deadlock, and the correct response is almost always to retry the whole transaction from the beginning, not to treat it as a permanent error. A transaction retry pattern catches the deadlock-specific error, waits a short randomized backoff period (to reduce the chance both competing transactions retry at exactly the same instant and deadlock again), and re-attempts the entire transaction up to some maximum number of times before giving up and surfacing a real error.

Deadlocks are more likely when transactions touch multiple rows in different, inconsistent orders. Keeping transactions short, and always acquiring locks on rows in a consistent order across your codebase (e.g. always locking the lower account ID first in a funds transfer), significantly reduces how often they occur — retry logic is a safety net, not a substitute for that discipline.

• Deadlock: two transactions each hold a lock the other is waiting on — neither can proceed. • The database engine detects the cycle and kills one transaction (rollback) to break it. • Application response: catch the deadlock error and retry the whole transaction, not just the failed statement. • Retry with randomized backoff and a max-attempts cap; also acquire locks in a consistent row order to reduce deadlocks in the first place.

PDO Transaction Shape

Never leave a transaction open across an HTTP request/response boundary — keep it as short as possible. Throwable catches both Exception and Error, so a fatal type error mid-transaction still triggers a rollback. Nested transactions aren't truly supported by most drivers — PDO throws if you call beginTransaction() twice without a commit/rollback between.

try {
    $pdo->beginTransaction();

    // ... multiple statements that must succeed together ...

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

Code Examples

The debit and credit are two separate UPDATE statements, but they're wrapped in one transaction so they succeed or fail together — if the credit somehow failed after the debit already ran, the rollback in the catch block undoes the debit too, so money is never subtracted from one account without appearing in the other. Expected result: On success: both balances updated atomically. On any failure: both rows are exactly as they were before the call — no partial transfer.

$pdo->beginTransaction(); ... $pdo->commit(); ... $pdo->rollBack();

The whole unit of work is wrapped in a retry loop. On a generic PDOException, it rolls back and checks whether the SQLSTATE code (40001) specifically indicates a deadlock. Only deadlocks are retried, and only up to $maxAttempts times, with an increasing randomized backoff between attempts so two competing transactions are unlikely to collide again immediately. Retrying re-runs the *entire* transaction from beginTransaction(), not just the statement that failed — a deadlock invalidates the whole in-progress transaction, so partial re-execution would be unsafe.

$isDeadlock = $e->getCode() === '40001';

<?php
declare(strict_types=1);

function transferFunds(PDO $pdo, int $fromAccountId, int $toAccountId, int $amountCents): void
{
    $pdo->beginTransaction();
    try {
        $debit = $pdo->prepare(
            'UPDATE accounts SET balance_cents = balance_cents - :amt
             WHERE id = :id AND balance_cents >= :amt'
        );
        $debit->execute(['amt' => $amountCents, 'id' => $fromAccountId]);

        if ($debit->rowCount() === 0) {
            // Either the account doesn't exist or has insufficient funds.
            throw new RuntimeException('Insufficient funds or invalid source account.');
        }

        $credit = $pdo->prepare(
            'UPDATE accounts SET balance_cents = balance_cents + :amt WHERE id = :id'
        );
        $credit->execute(['amt' => $amountCents, 'id' => $toAccountId]);

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

<?php
declare(strict_types=1);

function withDeadlockRetry(PDO $pdo, callable $work, int $maxAttempts = 3): mixed
{
    $attempt = 0;
    while (true) {
        $attempt++;
        $pdo->beginTransaction();
        try {
            $result = $work($pdo);
            $pdo->commit();
            return $result;
        } catch (PDOException $e) {
            $pdo->rollBack();

            // MySQL deadlock = SQLSTATE 40001; check driver-specific error code.
            $isDeadlock = $e->getCode() === '40001';

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

            // Randomized backoff so competing transactions don't retry in lockstep.
            usleep(random_int(50_000, 250_000) * $attempt);
        }
    }
}

// Usage:
withDeadlockRetry($pdo, function (PDO $pdo) use ($fromId, $toId, $amount) {
    transferFunds($pdo, $fromId, $toId, $amount);
});

Deadlock Between Two Transactions

The surviving transaction proceeds; the killed one should be retried by the application.

Transaction A                    Transaction B
--------------                   --------------
LOCK row 1                       LOCK row 2
  (holds row 1)                    (holds row 2)
try LOCK row 2  ---waits--->     try LOCK row 1  ---waits--->
       |                                |
       +---------- cycle: A waits on B, B waits on A ----------+
                            |
              engine detects deadlock, kills one
              transaction (rollback) to break the cycle

Important Rules

Wrap any multi-statement operation that must succeed or fail together in beginTransaction/commit/rollBack.

Always roll back in a catch block before re-throwing, so failures never leave partial writes.

Know your engine's default isolation level (InnoDB: Repeatable Read; PostgreSQL: Read Committed) and raise it only when the anomaly it prevents actually matters for the operation.

Treat a deadlock error as retryable, not fatal — retry the whole transaction with randomized backoff up to a max attempt count.

Reduce deadlocks by keeping transactions short and locking rows in a consistent order across the codebase.

Where This Shows Up

Any system handling money, inventory, or booking (seats, appointments, hotel rooms) lives and dies by correct transaction boundaries — an e-commerce checkout that decrements stock and creates an order record must do both atomically, or you risk overselling or 'ghost' orders with no matching inventory change. High-traffic systems that update the same hot rows frequently (a popular product's stock count, a shared counter) are also where deadlocks show up in practice, which is why production payment and inventory systems almost always have retry-with-backoff logic wrapped around their transactional writes rather than assuming deadlocks won't happen.

Running the debit UPDATE and credit UPDATE as two separate, unwrapped statements with no transaction.

Wrap both UPDATEs in beginTransaction()/commit(), with rollBack() in a catch block.

Without a transaction, a failure after the debit but before the credit leaves money subtracted from one account and never added to the other — a classic case of lost atomicity.

Catching a deadlock exception and simply logging it / returning an error to the user with no retry.

Catch the deadlock-specific SQLSTATE, roll back, and retry the entire transaction with backoff up to a small max attempt count.

A deadlock is often a transient condition caused by unlucky timing between two otherwise-correct transactions — retrying usually succeeds, whereas failing immediately turns a temporary contention blip into a user-facing error.

What does the 'Atomicity' guarantee in ACID actually promise? A. That every transaction runs faster than a single non-transactional statement. B. That a transaction's statements all take effect, or none of them do — there's no partial application. C. That two transactions can never run at the same time. D. That the database will automatically retry a failed transaction forever.

B. That a transaction's statements all take effect, or none of them do — there's no partial application. — Atomicity means all-or-nothing: either every statement in the transaction is applied, or the whole transaction has no effect at all, even if the process crashes partway through.

A transaction reads a product's price twice and gets two different values because another transaction committed a price change in between. What is this called? A. A dirty read B. A non-repeatable read C. A phantom read D. A deadlock

B. A non-repeatable read — Re-reading the same row within one transaction and getting a different value because another transaction committed a change in between is the definition of a non-repeatable read.

What is the recommended application response to a detected deadlock error? A. Permanently fail the request and show the raw database error to the user. B. Ignore it — deadlocks resolve themselves without any code changes. C. Roll back and retry the entire transaction from the start, with randomized backoff, up to a limited number of attempts. D. Immediately raise the database's global isolation level to Serializable.

C. Roll back and retry the entire transaction from the start, with randomized backoff, up to a limited number of attempts. — The database engine kills one of the two deadlocked transactions; the correct application response is to detect that specific error, roll back, and retry the whole transaction with backoff — not to fail permanently or ignore it.

Write a PHP function placeOrder(PDO $pdo, int $productId, int $qty) that, inside a single transaction, decrements products.stock by $qty only if enough stock is available, and inserts a row into orders. Roll back and throw if stock is insufficient.

Use an UPDATE with a WHERE clause that checks stock >= :qty, check rowCount() === 0 to detect insufficient stock, then INSERT the order row, then commit.