PDO throws when beginTransaction() is called inside an open transaction; how do you give PHP code nested-transaction behaviour with savepoints?
answer
- one transaction per PDO connection
- There is already an active transaction
- no savepoint method: use exec()
- SAVEPOINT, ROLLBACK TO, RELEASE
- depth counter; names are identifiers
basics
~20 sA 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.
solid answer
~40 sA second `beginTransaction()` on the same connection throws `PDOException` "There is already an active transaction", and PDO has no savepoint methods. So I keep a depth counter: at depth 0 I call `beginTransaction()`, deeper I run `$pdo->exec("SAVEPOINT sp_$depth")`. On success the outer level calls `commit()` and inner levels `RELEASE SAVEPOINT`; on failure the outer level calls `rollBack()` and inner levels `ROLLBACK TO SAVEPOINT`, then rethrow. Rolling back to a savepoint leaves the transaction open, and releasing one commits nothing — only the outermost `commit()` does. Savepoint names are SQL identifiers, so they come from the counter, never from a placeholder or user input.
code
php · 26 lines<?php
declare(strict_types=1);
final class TransactionRunner
{
private int $depth = 0;
public function __construct(private readonly PDO $pdo) {}
public function run(callable $work): mixed
{
$sp = 'sp_' . $this->depth;
if ($this->depth === 0) { $this->pdo->beginTransaction(); } else { $this->pdo->exec("SAVEPOINT $sp"); }
$this->depth++;
try {
$result = $work($this->pdo);
} catch (Throwable $e) {
$this->depth--;
if ($this->depth === 0) { $this->pdo->rollBack(); } else { $this->pdo->exec("ROLLBACK TO SAVEPOINT $sp"); }
throw $e;
}
$this->depth--;
if ($this->depth === 0) { $this->pdo->commit(); } else { $this->pdo->exec("RELEASE SAVEPOINT $sp"); }
return $result;
}
}go deeper
Recall that one PDO connection holds one transaction, and a second beginTransaction() throws a PDOException.
Explain the three savepoint statements, that PDO has no methods for them, and that only the outermost commit() makes work permanent.
Write the depth-counting wrapper, keep the counter correct on both paths, and explain when an inner failure should be caught for partial recovery.
Judge whether nested units of work belong in the codebase at all, or whether a single use-case boundary keeps failure handling simpler.
## Why PDO has no nested transactions A `PDO` connection holds **at most one transaction**. Calling `beginTransaction()` while one is open throws: ``` PDOException: There is already an active transaction ``` The exception is thrown whatever the error mode, and PDO offers no method for savepoints — the `PDO` class has `beginTransaction()`, `commit()`, `rollBack()` and `inTransaction()`, and nothing that marks a point inside a transaction. The problem shows up as soon as code is composed: a `transferPoints()` function opens a transaction, and a `grantSignupBonus()` function it calls wants its own. ## Savepoints in plain SQL A **savepoint** is a named marker inside the current transaction. MySQL (InnoDB), PostgreSQL and SQLite all accept the same three statements, which you send with `PDO::exec()`: | SQL | Effect | |---|---| | `SAVEPOINT sp_1` | marks the current point in the open transaction | | `ROLLBACK TO SAVEPOINT sp_1` | undoes everything after the marker; the transaction stays open | | `RELEASE SAVEPOINT sp_1` | forgets the marker; the work is kept and still awaits the outer commit | Two properties matter: - **Rolling back to a savepoint does not end the transaction.** `inTransaction()` is still `true`, and the outer code can carry on and commit. - **Releasing a savepoint does not commit anything.** Only the outermost `commit()` makes the work permanent; an outer `rollBack()` discards the inner "successful" work too. ## A depth-counting wrapper Nesting by hand usually means a small helper that counts depth: 1. At depth 0, call `beginTransaction()`. Deeper, `exec("SAVEPOINT sp_N")`. 2. Run the callback. 3. On success at depth 0, `commit()`; deeper, `RELEASE SAVEPOINT sp_N`. 4. On failure at depth 0, `rollBack()`; deeper, `ROLLBACK TO SAVEPOINT sp_N`. Rethrow either way. The counter must be decremented on **both** paths, or the next call opens a savepoint where it should have opened a transaction. Framework database layers implement this same idea; outside a framework you write it yourself or use one of them. ## Naming the savepoints A savepoint name is an **SQL identifier**, not a value. Prepared-statement placeholders can only stand in for values, so the name cannot be bound with `?` or `:name`. Generate it from the depth counter (`'sp_' . $depth`), a string the code fully controls, and never build it from request input. ## Using it in the points transfer A transfer may try to add a promotional bonus that is allowed to fail on its own: - The outer run debits and credits the accounts. - An inner run inserts a bonus ledger row. If the bonus rule rejects it, the inner run rolls back to its savepoint, the outer code catches that specific exception and continues. - The outer `commit()` makes the transfer permanent without the bonus. If the inner exception is **not** caught, it propagates, the outer level rolls back, and the transfer is undone as well. Savepoints make partial recovery possible; the calling code decides whether to use it. ## Testing the wrapper A helper like this is small but easy to get subtly wrong, so it deserves its own tests against a real database: 1. An inner failure that the outer callback catches: the inner row is gone, the outer rows are committed. 2. An inner failure that is not caught: nothing is committed and `inTransaction()` is `false` afterwards. 3. Two sibling inner runs at the same depth, one failing: the other's work survives. 4. After every scenario the depth counter is back at 0, so the next `run()` opens a real transaction. ## Traps - **DDL inside a savepoint on MySQL** still causes an implicit commit, which ends the transaction and every savepoint in it. - **PostgreSQL aborts the whole transaction on any error** and refuses further statements until you roll back; rolling back to a savepoint is how you continue after an expected failure there. - **Mixing raw `START TRANSACTION`/`COMMIT` SQL with PDO's methods** — the manual warns that PDO may not know about a transaction it did not start itself; use `beginTransaction()`, `commit()` and `rollBack()` for the outer level and plain SQL only for savepoints. - **Treating a released savepoint as committed** — a later outer rollback discards it.
- If an inner run releases its savepoint and the outer run then fails, what happens to the inner work?It is discarded. `RELEASE SAVEPOINT` only removes the marker; the inner changes are still part of the outer transaction, and the outer `rollBack()` undoes everything since `beginTransaction()`, the released work included. Nothing is permanent until the outermost `commit()`.
- Why not simply skip beginTransaction() when inTransaction() is already true?Joining the outer transaction works for success, but on failure the inner code has only two bad options: call `rollBack()`, which undoes the caller's work too and makes the caller's later `commit()` throw, or do nothing and let its half-done changes be committed by the caller. A savepoint gives the inner code a boundary it can undo on its own.
Savepoints work like bookmarks in a draft you have not yet sent: you can tear out every page after a bookmark and keep writing, but nothing reaches the reader until the whole draft is sent, and throwing the draft away discards the bookmarked pages too.
saying these in an interview costs you the question
- Expecting a second beginTransaction() to start a nested transaction.
- Believing RELEASE SAVEPOINT commits the inner work permanently.
- Thinking ROLLBACK TO SAVEPOINT ends the whole transaction.
- Binding the savepoint name with a placeholder or building it from input.
- Forgetting to decrement the depth counter on the failure path.