Transactions and Concurrency

A transfer moves 200 from account A to account B: one debit, one credit, two statements. Each statement on its own is exactly the kind of write you built in Performing CRUD Queries. Run them back to back with no grouping and MySQL's default autocommit mode makes each one durable the instant it finishes. If the debit commits and the server dies — or a CHECK constraint rejects the credit, or the connection drops — before the second statement lands, 200 has left A and never arrived at B. The money did not vanish in transit; it vanished because two writes that mean one operation were executed as two operations.

A transaction fixes that by making the group atomic: nothing is durable or visible to other connections until commit(), and rollBack() discards everything done since beginTransaction().

$pdo->beginTransaction();
try {
    $debit = $pdo->prepare(
        'UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?'
    );
    $debit->execute([200, $fromId, 200]);
    if ($debit->rowCount() !== 1) {
        throw new RuntimeException('Insufficient funds or unknown account');
    }

    $credit = $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');
    $credit->execute([200, $toId]);
    if ($credit->rowCount() !== 1) {
        throw new RuntimeException('Target account does not exist');
    }

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

Every line of that template has a reason, and all three methods are standard interview material:

• beginTransaction() switches autocommit off for this connection. Until commit, other sessions see the pre-transaction state — the debit is invisible until the credit has also succeeded. This invisibility is what makes the pair all-or-nothing.
• commit() makes it durable and releases the row locks the UPDATEs took.
• rollBack() undoes every statement since the begin — not just the last one. When the funds check throws, the earlier debit is reverted too; the transfer either happened entirely or left no trace.
• The catch guards with inTransaction() because some failures end the transaction server-side before your catch block runs; calling rollBack() with no active transaction raises an error of its own on some driver/PHP combinations and would mask the original exception.
• The guard must cover every early exit. A return or an exit inside the try block, before commit, leaves an open transaction on the connection: it eventually rolls back when the connection closes, but in a long-lived or persistent connection it keeps locks held. Every path out of a transaction ends in either commit() or rollBack() — there is no third option.

Three mechanics that surface in interviews and production:

1. Nesting does not exist. Calling beginTransaction() while a transaction is open throws PDOException: There is already an active transaction. MySQL does not nest transactions; if you need a partial rollback point — a validation failure inside a bigger unit of work that should not abort the whole thing — the mechanism is a savepoint ($pdo->exec('SAVEPOINT before_import') then ROLLBACK TO before_import), not a second begin.
2. DDL causes an implicit commit on MySQL. CREATE TABLE, ALTER TABLE, TRUNCATE — statements that change structure — commit everything you did before them. A migration step dropped inside your transfer transaction silently makes the first half permanent. Keep DDL out of transactions.
3. Keep the transaction short. Every milliless it is open is a millisecond the row locks are held. The classic production horror story is a transaction that calls a payment gateway or an HTTP API in the middle: the database now holds locks for the duration of a network call, blocking every other request that touches those rows — and blocking is where the next chapter's deadlocks are born.

An isolation level knob you may see alongside these calls is PDO::ATTR_AUTOCOMMIT; PDO normally manages autocommit for you, and beginTransaction()/commit() are the API you should use rather than touching it directly. What other sessions can see during your open transaction is a deliberate, tunable decision — isolation levels.

Atomicity answers "does my write group all succeed or none". Isolation answers the harder question: while my transaction is open, what can other transactions read, and what can they do to my in-flight work? PDO does not decide this for you — the database's isolation level does, and each level trades correctness for throughput.

The failure modes, in increasing severity:

• Dirty read — you read a row another transaction has modified but not committed. If that transaction rolls back, you have acted on data that never existed. Imagine reading a 900 balance from a transfer that then aborted.
• Non-repeatable read — you read the same row twice inside one transaction and get different values, because another transaction committed a change in between. Your snapshot of the world moved under your feet.
• Phantom — you run the same WHERE query twice and the set of matching rows changed, because another transaction inserted or deleted rows in that range.

The four standard levels, and what each permits:

* The SQL standard says phantoms remain possible at repeatable read; InnoDB's gap and next-key locking close most of them in practice, which is why MySQL's default is stronger than the standard's definition.

Set the level per transaction when you need something other than the default — after beginning the transaction:

$pdo->beginTransaction();
$pdo->exec('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');

Now the lost-update problem, the interview question this whole phase is built around. Two requests each read a balance of 1000, each add 100 in PHP, each write back 1100. The final balance is 1100, not 1200: the second write silently discarded the first one's addition. Read-modify-write in application code cannot be made safe by wrapping it in a transaction at repeatable read — both transactions read the same snapshot and both write.

Two correct fixes, with different costs:

// Fix 1: atomic arithmetic — the read and the write happen server-side, in one statement
$pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')
    ->execute([100, $accountId]);

// Fix 2: pessimistic locking — hold an exclusive lock from read to write
$pdo->beginTransaction();
$stmt = $pdo->prepare('SELECT balance FROM accounts WHERE id = ? FOR UPDATE');
$stmt->execute([$accountId]);
$balance = $stmt->fetchColumn();
if ($balance < 100) { $pdo->rollBack(); throw new RuntimeException('No funds'); }
$pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
    ->execute([100, $accountId]);
$pdo->commit();

Never compute balance = $balance + 100 in PHP after a plain SELECT — that is the lost update by construction. Prefer fix 1 whenever the rule fits in one expression; it needs no transaction at all and no lock beyond the statement's instant. Use SELECT ... FOR UPDATE when a decision depends on the read (the funds check must see the same value you then write), accepting that the lock is held until commit — which is exactly why the transaction must stay short. A third family of fixes, optimistic locking with a version column, is covered in the CRUD chapter: no locks, and a rowCount() === 0 result tells you to reload and retry.

FOR UPDATE also has a quieter cousin: at READ COMMITTED, plain reads do not lock anything, so between your SELECT and your UPDATE another transaction can change the row — the gap that FOR UPDATE exists to close. When two transactions both hold a lock and both then ask for the other's, neither can proceed. That cycle has a name, an error number, and a standard fix.

It is 2 a.m., the error log fills with the same line, and PHP is throwing it as an uncaught exception:

SQLSTATE[40001]: General error: 1213 Deadlock found when trying to get lock;
try restarting transaction

This is not a slow query and not a lock that will eventually clear. MySQL's InnoDB has detected a cycle in its waits-for graph — transaction 1 holds a lock transaction 2 wants, while transaction 2 holds a lock transaction 1 wants, so neither can ever proceed — and it resolved the standoff by picking a victim (usually the one that has done less work), rolling it back, and returning error 1213 to that connection. From PDO's side it surfaces as a PDOException with SQLSTATE 40001, which is the standard transaction-retry class code.

Reproduce the cycle with the transfer code from the first chapter, running two transfers at once — A→B and B→A:

Time   Transaction 1 (A → B)          Transaction 2 (B → A)
 1     UPDATE accounts ... WHERE id=A  
 2                                   UPDATE accounts ... WHERE id=B
 3     UPDATE accounts ... WHERE id=B  -- blocks: T2 holds B's row lock
 4                                   UPDATE accounts ... WHERE id=A -- blocks: T1 holds A's row lock
       = deadlock. InnoDB rolls one of them back.

Each transfer locks its source row first, then its destination. Two opposite transfers acquire the two rows in opposite orders — the textbook cycle. The fix is structural: always acquire row locks in a deterministic order — ascending account id is the usual convention.

// Canonical lock order: lower id is always locked first
[$first, $second] = $fromId < $toId ? [$fromId, $toId] : [$toId, $fromId];
$pdo->beginTransaction();
try {
    // lock both rows, in order, before deciding anything
    $stmt = $pdo->prepare('SELECT id FROM accounts WHERE id IN (?, ?) ORDER BY id FOR UPDATE');
    $stmt->execute([$first, $second]);
    // ... balance checks, debit, credit, all in id order ...
    $pdo->commit();
} catch (Throwable $e) {
    if ($pdo->inTransaction()) { $pdo->rollBack(); }
    throw $e;
}

With every transaction requesting the same order, the cycle cannot form: the second transaction simply waits at its first request and proceeds once the first commits. Three supporting practices keep the window small:

1. Short transactions. Deadlock probability rises with how many locks a transaction holds and for how long. External I/O — API calls, file writes, emails — belongs outside the transaction, after commit.
2. Indexed WHERE clauses. An UPDATE ... WHERE account_number = ? with no index scans and locks far more rows than the one you meant to touch (InnoDB takes next-key locks as it scans), inflating the set of transactions that can collide.
3. Bounded retries. The error message literally says "try restarting transaction" — that is the contract. Deadlock rollbacks undo nothing you must clean up; re-running the whole transaction is safe.

for ($attempt = 1; $attempt <= 3; $attempt++) {
    try {
        runTransfer($pdo, $fromId, $toId, 200);
        break;
    } catch (PDOException $e) {
        $isDeadlock = $e->getCode() === '40001';
        if (!$isDeadlock || $attempt === 3) { throw $e; }
        usleep(random_int(10_000, 50_000) * $attempt); // linear backoff with jitter
    }
}

Distinguish the neighbor error while you are here: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded is not a detected cycle — it is one transaction waiting too long for a held lock, and by default the waiter times out. Same remedy family (short transactions, retry), different diagnosis: 1213 means a cycle InnoDB broke up; 1205 means someone held a lock longer than innodb_lock_wait_timeout allows. Once writes are atomic and deadlock-aware, the next topic shifts to reads: assembling related tables into one result set with Modeling Relations with Joins.