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?
answer
- open before try, commit last inside
- catch Throwable, not only PDOException
- inTransaction() guard before rollBack()
- throw $e; after the rollback
- rowCount() !== 1 means no debit
basics
~10 sCall $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 sI 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
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
Recall the three calls and their order: beginTransaction(), the statements, commit(); rollBack() when something fails.
Explain why the catch takes Throwable, why rollBack() is guarded with inTransaction(), and why the exception must be rethrown after the rollback.
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.
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.
saying these in an interview costs you the question
- 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.