skip to content

Commit & Rollback Flow

PDO groups statements with beginTransaction, commit and rollBack, while lastInsertId and rowCount report what a write did. Interviewers check the catch-and-roll-back pattern and DDL traps.

part ofPHPread it →
on this pageshow

explore

questions

5

With PDO, how do you run the two UPDATEs of a loyalty-points transfer as one transaction that rolls back and still reports the failure?

level: middleimportance: must knowfreq 62%

answer

  1. open before try, commit last inside
  2. catch Throwable, not only PDOException
  3. inTransaction() guard before rollBack()
  4. throw $e; after the rollback
  5. rowCount() !== 1 means no debit

basics

~10 s

Call $pdo->beginTransaction() before a try block, run both UPDATEs and commit() inside it, and in catch (Throwable $e) call rollBack() when inTransaction() is true, then rethrow so the caller still sees the failure.

solid answer

~40 s

I call `$pdo->beginTransaction()` just before `try`, so a failure to open leaves nothing to undo. Inside the `try` I run the debit and the credit and call `commit()` as the last line. The `catch` takes `Throwable`, because a `TypeError` or my own "insufficient points" exception must undo the debit as much as a `PDOException` does. There I call `rollBack()` only if `$pdo->inTransaction()` is still true, since `rollBack()` throws when nothing is active, and then `throw $e;` so the controller logs it and returns an error instead of reporting a successful transfer. The debit is a conditional `UPDATE ... WHERE points >= ?`, and I check `rowCount() === 1` to detect a short balance.

code

php · 24 lines
php
<?php
declare(strict_types=1);

function transferPoints(PDO $pdo, int $from, int $to, int $points): void
{
    $pdo->beginTransaction();
    try {
        $debit = $pdo->prepare(
            'UPDATE loyalty_accounts SET points = points - ? WHERE customer_id = ? AND points >= ?'
        );
        $debit->execute([$points, $from, $points]);
        if ($debit->rowCount() !== 1) {
            throw new RuntimeException('Insufficient points or unknown customer');
        }
        $pdo->prepare('UPDATE loyalty_accounts SET points = points + ? WHERE customer_id = ?')
            ->execute([$points, $to]);
        $pdo->commit();
    } catch (Throwable $e) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $e;
    }
}

go deeper

for a junior

Recall the three calls and their order: beginTransaction(), the statements, commit(); rollBack() when something fails.

for a middle

Explain why the catch takes Throwable, why rollBack() is guarded with inTransaction(), and why the exception must be rethrown after the rollback.

for a senior

Show what goes wrong without the explicit rollback: a worker that keeps its PDO object holds locks and fails the next beginTransaction(); a swallowed error keeps writing inside a doomed transaction.

for a principal

Weigh a shared transaction helper that owns begin, rollback and rethrow against hand-written blocks: one audited pattern versus code where every author re-derives the guard.

## What a PDO transaction is A **transaction** groups several SQL statements so the database applies all of them or none of them. A PDO connection starts in **autocommit mode**: every statement is committed the moment it succeeds. Four methods on the `PDO` object change that: | Method | Returns | What it does | |---|---|---| | `beginTransaction()` | `bool` | turns autocommit off until the transaction ends; throws `PDOException` if one is already open or the driver has no transaction support | | `commit()` | `bool` | makes the work permanent and returns the connection to autocommit; throws `PDOException` if no transaction is active | | `rollBack()` | `bool` | discards the work and returns to autocommit; throws `PDOException` if no transaction is active | | `inTransaction()` | `bool` | reports whether a transaction is currently open | The "already active" and "no active transaction" exceptions are thrown whatever `PDO::ATTR_ERRMODE` says; they are not governed by the error mode. ## The loyalty-points transfer Moving points from one customer to another is two writes: a debit and a credit. If the debit succeeds and the credit fails, points vanish. The transaction makes the pair atomic, and the code around it has three jobs: 1. Call `beginTransaction()` **just before** the `try`. If it throws, nothing was opened and there is nothing to undo. 2. Run the statements inside `try`, and call `commit()` as the **last** line of the `try`. 3. In `catch (Throwable $e)`, roll back if `inTransaction()` is still true, then **rethrow** with `throw $e;`. ## Why each part matters - **Catch `Throwable`, not only `PDOException`.** The transfer can also fail in PHP code between the two statements: a `TypeError`, a `RuntimeException` you throw when a balance check fails, a `ValueError` from a helper. Any of them must undo the debit. `Throwable` covers both `Exception` and `Error` subclasses. - **Guard with `inTransaction()`.** `rollBack()` throws when nothing is active, for example after a statement caused an implicit commit on MySQL. An unguarded call would replace the original, informative exception with a "There is no active transaction" one. - **Rethrow.** The rollback repairs the database, not the caller's view of the world. If the `catch` only logs, the function returns normally and the controller reports a successful transfer. Rethrowing lets the caller's error handling, logging and HTTP 500 response do their job. - **Keep the error mode on exceptions.** `PDO::ERRMODE_EXCEPTION` is the default since PHP 8.0. Under the old silent mode, a failed `execute()` returns `false` instead of throwing, the `catch` never runs, and `commit()` happily commits the half that worked. ## What PDO does if you forget If a transaction is still open when the `PDO` object is destroyed — its last reference goes away or the script ends — PDO rolls it back. That safety net does not make the explicit rollback optional: - In a **long-running worker**, the same `PDO` object serves many jobs, so an open transaction outlives the failed job, keeps its row locks, and the next `beginTransaction()` throws "There is already an active transaction". - Within a single request, code that swallows the exception and carries on keeps writing **inside** the doomed transaction, so later writes are rolled back too, or committed by some unrelated `commit()` further down. ## Checking that the debit really happened A conditional debit such as `UPDATE loyalty_accounts SET points = points - ? WHERE customer_id = ? AND points >= ?` never goes negative, but it can match zero rows. `PDOStatement::rowCount()` tells you: anything but `1` means the balance was too low or the customer does not exist, so throw and let the `catch` roll back. ## When commit() itself fails `commit()` sits inside the `try` for a reason. The commit is where some failures first surface: the connection may have dropped while the application was working, or a database that checks deferred constraints at commit time may reject the whole transaction. Because the call is inside the `try`, that `PDOException` takes the same path as any other failure — the guarded rollback runs if a transaction is still open, and the exception reaches the caller. Code that calls `commit()` after the `try`/`catch` loses that guarantee and needs a second handler. A related habit is to return values from inside the `try` only after `commit()` has succeeded, so the caller never receives a ledger id or a new balance for work that was then undone. ## What reviewers look for - `beginTransaction()` outside the `try`, `commit()` as the last statement inside it. - `catch (Throwable $e)`, an `inTransaction()` guard, `rollBack()`, `throw $e;`. - No `echo` or `return false` in the `catch` that turns a failure into a success. - Exception error mode left at its default. Where the transaction boundary belongs in a larger application (per use case rather than per repository call) and how isolation levels affect concurrent transfers are separate topics; this pattern is the PDO mechanics underneath them.

  • Why is beginTransaction() placed before the try block rather than inside it?
    If `beginTransaction()` throws, for example because a transaction is already open on the connection, no new transaction exists. Inside the `try`, the `catch` would then try to roll back something this function never started, possibly undoing a caller's work or throwing "There is no active transaction" over the real error. Outside the `try`, the failure propagates untouched.
  • What happens to an open PDO transaction if the script dies with an uncaught error?
    When the `PDO` object is destroyed with a transaction still open, PDO rolls it back, so a crashed request does not commit half a transfer. The safety net fires only when the object goes away; in a long-running worker that reuses the connection, the transaction stays open, holds its locks and blocks the next `beginTransaction()`.
  • Why does the credit not need its own rowCount() check in the same way as the debit?
    It does need one if the target customer may not exist: an `UPDATE` that matches no row is not an error, so without a check the points would leave one account and land nowhere. The debit check is the one interviewers expect because it also enforces the balance rule; a careful version checks both and throws on either.

It is like a bank teller moving cash between two drawers with a pencil entry first: if anything interrupts the move, the teller erases the entry and puts the cash back, then still tells the manager that the transfer failed.

  • Catching only PDOException, so a TypeError skips the rollback.
  • Logging in the catch block and returning, which reports a failed transfer as success.
  • Calling rollBack() unguarded, so a no-active-transaction exception hides the real error.
  • Relying on PDO's automatic rollback at script end instead of rolling back explicitly.
  • Believing a failed execute() always throws, even under ERRMODE_SILENT.
open as a page

With PDO, how do you get the auto-increment id of the row you just inserted, and what does lastInsertId() return?

level: juniorimportance: should knowfreq 48%

basics

~20 s

Call $pdo->lastInsertId() on the connection right after the INSERT. It returns the generated id as a string, scoped to that connection, so other requests cannot change it; on PostgreSQL you may pass the sequence name.

open as a page

With PDO, what does PDOStatement::rowCount() report after an UPDATE, and why can it return 0 for an existing row on MySQL?

level: middleimportance: should knowfreq 40%

basics

~20 s

rowCount() returns the rows affected by the statement's last DELETE, INSERT or UPDATE. On MySQL an UPDATE that writes the values a row already holds changes nothing, so it reports 0 unless the connection sets Pdo\Mysql::ATTR_FOUND_ROWS.

open as a page

With PDO on MySQL in PHP 8, why does rollBack() throw 'There is no active transaction' after an ALTER TABLE ran inside the transaction?

level: seniorimportance: should knowfreq 30%

basics

~20 s

MySQL implicitly commits the open transaction when DDL such as ALTER TABLE runs. Since PHP 8.0, pdo_mysql reads the server's real state, so inTransaction() is false and rollBack() throws; the earlier writes are already committed.

open as a page

PDO throws when beginTransaction() is called inside an open transaction; how do you give PHP code nested-transaction behaviour with savepoints?

level: seniorimportance: should knowfreq 28%

basics

~20 s

A PDO connection holds one transaction, and a second beginTransaction() throws PDOException. Nest by hand: open the real transaction at depth 0 and issue SAVEPOINT, ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT through exec() at deeper levels.

open as a page