skip to content

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%

answer

  1. one transaction per PDO connection
  2. There is already an active transaction
  3. no savepoint method: use exec()
  4. SAVEPOINT, ROLLBACK TO, RELEASE
  5. depth counter; names are identifiers

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.

solid answer

~40 s

A 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
<?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

for a junior

Recall that one PDO connection holds one transaction, and a second beginTransaction() throws a PDOException.

for a middle

Explain the three savepoint statements, that PDO has no methods for them, and that only the outermost commit() makes work permanent.

for a senior

Write the depth-counting wrapper, keep the counter correct on both paths, and explain when an inner failure should be caught for partial recovery.

for a principal

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.

  • 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.