PDO — Savepoints, Streaming, and Connection Resilience
Go past the PDO basics into native vs. emulated prepared statements and why the difference matters for security and type fidelity, nested-transaction emulation with savepoints, streaming large result sets with unbuffered queries, driver-specific cursor behavior, and connecting resiliently in the face of transient failures.
The earlier PDO Database Access lesson covers the DSN, connecting, the charset and error mode, prepared statements with bound parameters, fetch modes, and a basic transaction. Those cover the vast majority of everyday PDO code, but a handful of PDO behaviors only matter once an application grows past 'a few queries on a request' — how PDO decides whether the server or PHP itself parses your prepared statement, how to fake a nested transaction when PDO only ever gives you one real transaction at a time, how to stream a result set too large to hold in memory, and what to do when the database connection itself is the thing that fails. This lesson picks up exactly where that one left off, under the same PDO Database Access module.
Native vs. Emulated Prepared Statements
PDO can prepare a statement in one of two fundamentally different ways, controlled by PDO::ATTR_EMULATE_PREPARES. With emulation off (native prepares), PDO sends the SQL text with placeholders to the database server as-is, the server parses and plans it once, and then PDO sends bound values separately, as pure data, on execute() — the server never sees your values as part of the SQL text at all. With emulation on, PDO itself substitutes the bound values into the SQL string client-side (safely quoting/escaping them) before sending one complete, literal SQL statement to the server — the server has no idea a placeholder was ever involved.
This matters for two reasons. First, type fidelity: under native prepares, the database driver preserves the actual column/parameter type, so an integer column bound as PDO::PARAM_INT really does come back as a PHP int; under emulation, everything is turned into a literal in the SQL text, and PDO's MySQL driver in particular has historically returned every column as a string regardless of the underlying type when emulation is on. Second, plan reuse: a server-side native prepared statement can be planned once and executed many times with different bound values (a real win when the same statement runs repeatedly in a loop or across many requests), whereas an emulated one is re-parsed as a brand-new literal SQL string on every single execution.
PDO's historical MySQL driver default is ATTR_EMULATE_PREPARES => true, largely for compatibility with older MySQL query-cache behavior; PostgreSQL and SQLite drivers lean toward native behavior already. The practical guidance for modern MySQL/MariaDB versions is to explicitly set it to false unless you have a specific, documented reason not to — it's one of those settings worth pinning explicitly rather than trusting the driver default.
It's a common misconception that emulated prepares are an SQL-injection risk on their own — they aren't: PDO still quotes/escapes every bound value correctly under emulation, so a bound parameter can't break out of its position in the SQL text either way. The real historical danger was different code entirely: developers who saw 'I'm using PDO with placeholders' as a blanket guarantee of safety and then fell back to raw string interpolation for the things PDO *can't* bind — table/column identifiers, and a bare LIMIT/OFFSET on some older database/driver combinations — which is a completely separate mistake from emulation being on or off.
• PDO::ATTR_EMULATE_PREPARES = false: native prepares -- values sent as real data, types preserved, plan reusable server-side. • PDO::ATTR_EMULATE_PREPARES = true: PDO substitutes values into the SQL text client-side, safely, before sending -- but usually returns everything as strings and can't reuse a server-side plan. • Emulated prepares are not inherently injection-prone -- PDO quotes bound values correctly either way. • The real injection risk is unrelated: raw string interpolation for identifiers/LIMIT that PDO can't bind at all, regardless of the emulation setting. • Set PDO::ATTR_EMULATE_PREPARES explicitly (usually false for MySQL/MariaDB) rather than relying on the driver default.
Transaction Savepoints: Emulating Nested Transactions
PDO gives you exactly one real, top-level transaction at a time — there's no built-in concept of nesting. Calling beginTransaction() a second time while one is already open is a bug waiting to happen: depending on the driver, it either throws ("There is already an active transaction") or, worse, is silently accepted in a way that doesn't behave like a real independent nested scope. This becomes a real problem the moment you have reusable functions that each want to guarantee 'my own writes are atomic,' called both standalone and from inside some larger operation that's already in a transaction.
SAVEPOINTs solve this at the SQL level, and MySQL/InnoDB, PostgreSQL, and SQLite all support them. A savepoint marks a point inside an already-open transaction that you can later roll back to — ROLLBACK TO SAVEPOINT name undoes everything since that savepoint without aborting the entire outer transaction, and RELEASE SAVEPOINT name discards the savepoint once you no longer need to roll back to it. PDO has no dedicated savepoint API; you issue them as plain SQL through $pdo->exec().
The pattern for reusable nested-transaction-safe code is to track nesting depth yourself: the outermost call uses the real beginTransaction()/commit()/rollBack(), and every call nested inside an already-open transaction uses a named savepoint instead, rolling back to (or releasing) just that savepoint on failure or success rather than touching the real transaction at all.
• PDO supports exactly one real transaction at a time -- calling beginTransaction() while one is open is not true nesting. • SAVEPOINT name / RELEASE SAVEPOINT name / ROLLBACK TO SAVEPOINT name -- supported by MySQL/InnoDB, PostgreSQL, and SQLite. • PDO has no dedicated savepoint API -- issue them as raw SQL via $pdo->exec(). • Track nesting depth in your own code: outermost call uses the real transaction, inner calls use savepoints.
PDO Attributes Worth Knowing Beyond the Basics
Past ATTR_ERRMODE and ATTR_DEFAULT_FETCH_MODE, a handful of other attributes matter in specific situations. PDO::ATTR_TIMEOUT sets a connection timeout in seconds (driver support varies — it's most reliably honored by MySQL). PDO::ATTR_PERSISTENT, passed in the connection options array, asks PHP to keep the underlying connection alive and reuse it for the next request handled by the same PHP-FPM worker process rather than reconnecting from scratch — a cheap win on connection overhead, but it means any session-level state (an open transaction, a SET statement, a temporary table) can leak into a completely unrelated later request if it isn't cleaned up before the request ends.
PDO::ATTR_STRINGIFY_FETCHES controls whether numeric column values are returned as PHP strings regardless of their underlying type (true) or as their native PHP type (false) — worth knowing about explicitly since it interacts with the same type-fidelity question that emulated prepares raise. PDO::ATTR_CASE (CASE_LOWER/CASE_UPPER/CASE_NATURAL) forces the case of column-name keys in fetched associative arrays, occasionally useful when working across databases with different default identifier casing conventions (Oracle's uppercase-by-default identifiers being the classic example).
• PDO::ATTR_TIMEOUT -- connection timeout in seconds; support and reliability vary by driver. • PDO::ATTR_PERSISTENT -- reuses a connection across requests on the same worker; watch for leaked session state. • PDO::ATTR_STRINGIFY_FETCHES -- forces numeric columns to come back as strings regardless of native type. • PDO::ATTR_CASE -- normalizes fetched column-name key casing (CASE_LOWER / CASE_UPPER / CASE_NATURAL).
Streaming Large Result Sets: Unbuffered Queries
By default, PDO's MySQL driver fully buffers a query's result set: it pulls every matching row from the server into PHP's own memory before your code sees the first row, which is why fetchAll() (and even iterating with fetch()) works predictably but scales badly — a query matching several million rows can exhaust PHP's memory limit before you've processed a single one. Setting PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false switches to unbuffered mode: rows stream from the server as you fetch them, so memory use stays roughly flat no matter how many rows the query matches.
The tradeoff is that an unbuffered query holds the underlying connection 'busy' streaming results until every row has been read (or you explicitly call closeCursor()) — you can't issue a second query on that same connection in the meantime, which usually means opening a dedicated connection just for the streaming query, or fully consuming/closing it before doing anything else on that connection. Unbuffered queries are the right tool specifically for large, one-pass exports and batch-processing jobs, not for everyday request-handling code.
• Default: PDO's MySQL driver buffers the full result set into PHP memory before your code sees any row. • PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = false -- streams rows from the server as fetched, keeping memory flat. • An unbuffered query holds its connection busy until fully consumed or closeCursor() is called. • Reserve unbuffered queries for large exports/batch jobs, not typical request-handling code.
Cursor Behavior and Database-Specific Differences
Cursor and streaming behavior isn't uniform across drivers, because it isn't uniform across the underlying database protocols. MySQL's protocol makes the buffered-vs-unbuffered choice an explicit client-side flag, as above. PostgreSQL's client library (libpq), by contrast, buffers a query's full result set on the client by default too, but PostgreSQL additionally supports real server-side cursors via DECLARE ... CURSOR and FETCH ... FROM — issued as raw SQL through PDO — which stream in batches directly from the server and are the more idiomatic way to page through a very large PostgreSQL result set without holding it all in memory on either side. SQLite, being an embedded, in-process database with no client/server round trip at all, doesn't have a meaningful buffered/unbuffered distinction in the same sense — result rows are read directly from the file as you fetch them.
The practical lesson is that 'streaming a large result set' isn't a single PDO feature — it's a driver-specific technique, and code that needs to run portably across MySQL and PostgreSQL has to branch on which mechanism it's using rather than assuming one PDO attribute works everywhere.
• MySQL: explicit buffered/unbuffered toggle via PDO::MYSQL_ATTR_USE_BUFFERED_QUERY. • PostgreSQL: real server-side cursors via DECLARE CURSOR / FETCH, issued as raw SQL -- the idiomatic streaming approach there. • SQLite: no client/server round trip at all -- rows are read straight from the file as fetched. • Streaming large result sets is driver-specific; there's no single portable PDO attribute that covers every database.
Handling Connection Failures and Retries Gracefully
A database connection can fail for reasons that have nothing to do with your query — a brief network blip, the database restarting during a deploy, a connection pooler momentarily out of slots, a managed database failing over to a replica. The new PDO(...) constructor throws a PDOException on any connection failure, and treating every such failure identically to a genuine, permanent misconfiguration (wrong credentials, database doesn't exist) throws away the chance to recover automatically from the transient ones.
A resilient connection routine retries a bounded number of times with a short, increasing delay between attempts (exponential backoff), distinguishing where practical between errors worth retrying (connection refused, timeout) and errors that will never succeed no matter how many times you try (authentication failure, unknown database) — retrying the latter just delays reporting a real configuration problem. The same principle applies mid-request: a query that fails specifically because the connection itself dropped (rather than because the query was wrong) is sometimes worth reconnecting and retrying once, though this needs care — a write whose commit status is genuinely unknown after a dropped connection should not be blindly retried without a way to detect it already happened (an idempotency key, a unique constraint that would reject a duplicate).
• Connection failures are often transient -- network blips, deploys, pooler exhaustion, replica failover. • Retry connection attempts with a bounded count and increasing backoff, rather than failing immediately on the first attempt. • Distinguish retryable failures (timeout, connection refused) from permanent ones (bad credentials, unknown database) -- don't retry the latter. • Never blindly retry a write after a dropped connection without an idempotency safeguard -- its commit status may be genuinely unknown.
A Nested-Transaction-Safe Helper Using Savepoints
The outermost call (depth 1) uses PDO's real transaction API; every nested call uses a named savepoint instead. A savepoint name derived from the current depth keeps concurrent nested calls from colliding on the same savepoint name. The finally block decrements depth on every path, including exceptions, so the counter never gets stuck.
<?php
declare(strict_types=1);
final class NestedTransaction
{
private static int $depth = 0;
public static function run(PDO $pdo, callable $work): mixed
{
self::$depth++;
$savepoint = 'sp_' . self::$depth;
if (self::$depth === 1) {
$pdo->beginTransaction();
} else {
$pdo->exec("SAVEPOINT {$savepoint}");
}
try {
$result = $work($pdo);
if (self::$depth === 1) {
$pdo->commit();
} else {
$pdo->exec("RELEASE SAVEPOINT {$savepoint}");
}
return $result;
} catch (Throwable $e) {
if (self::$depth === 1) {
$pdo->rollBack();
} else {
$pdo->exec("ROLLBACK TO SAVEPOINT {$savepoint}");
}
throw $e;
} finally {
self::$depth--;
}
}
}Code Examples
Both queries return the same logical value, but the PHP type that comes back differs depending on whether PDO handed the value to the server as real typed data (native) or substituted it into the SQL text as a literal before sending (emulated). Code that does strict type comparisons (=== 7) can quietly break if it was written and tested against one mode and later runs against the other. Expected result: int(7) string(1) "7"
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
adjustInventory() is written once and called both standalone and from inside recordOrder()'s already-open transaction. Because both go through NestedTransaction::run(), the outer call gets the real beginTransaction()/commit(), and every inner call transparently becomes a savepoint scoped to just that inner operation -- a failure in one item's inventory adjustment can roll back to its own savepoint without discarding the whole order.
NestedTransaction::run($pdo, function (PDO $pdo) use ($sku, $delta): void { ... });
With buffering off, PDO streams each row from the server as the foreach loop asks for it instead of pulling millions of rows into PHP memory up front -- peak memory stays roughly constant regardless of table size. closeCursor() explicitly frees the connection once the loop finishes, since an unbuffered query keeps the connection busy until every row is consumed or the cursor is closed.
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
Connection attempts that look like transient infrastructure hiccups get retried with growing delays between attempts, up to a cap; attempts that clearly indicate a permanent misconfiguration (bad credentials, a database that doesn't exist) are surfaced immediately instead of being retried pointlessly for five rounds before eventually failing anyway.
$permanentFailure = str_contains($e->getMessage(), 'Access denied') || ...;
<?php
declare(strict_types=1);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare('SELECT id FROM products WHERE id = ?');
$stmt->bindValue(1, 7, PDO::PARAM_INT);
$stmt->execute();
$native = $stmt->fetchColumn();
var_dump($native); // int(7) -- the server's own integer type is preserved
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$stmt = $pdo->prepare('SELECT id FROM products WHERE id = ?');
$stmt->bindValue(1, 7, PDO::PARAM_INT);
$stmt->execute();
$emulated = $stmt->fetchColumn();
var_dump($emulated); // string(1) "7" on many MySQL setups with emulation on<?php
declare(strict_types=1);
function recordOrder(PDO $pdo, int $customerId, array $items): int
{
return NestedTransaction::run($pdo, function (PDO $pdo) use ($customerId, $items): int {
$insertOrder = $pdo->prepare('INSERT INTO orders (customer_id) VALUES (:cid)');
$insertOrder->execute(['cid' => $customerId]);
$orderId = (int) $pdo->lastInsertId();
foreach ($items as $item) {
adjustInventory($pdo, $item['sku'], -$item['qty']);
}
return $orderId;
});
}
function adjustInventory(PDO $pdo, string $sku, int $delta): void
{
// Safe whether this runs standalone or nested inside recordOrder()'s
// already-open outer transaction -- it uses a savepoint either way.
NestedTransaction::run($pdo, function (PDO $pdo) use ($sku, $delta): void {
$stmt = $pdo->prepare('UPDATE inventory SET qty = qty + :delta WHERE sku = :sku');
$stmt->execute(['delta' => $delta, 'sku' => $sku]);
});
}<?php
declare(strict_types=1);
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$stmt = $pdo->query('SELECT id, payload FROM event_log ORDER BY id');
$count = 0;
foreach ($stmt as $row) {
processEvent($row);
$count++;
}
$stmt->closeCursor();
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); // restore the default
echo "Processed {$count} rows without ever holding the full result set in memory" . PHP_EOL;<?php
declare(strict_types=1);
function connectWithRetry(
string $dsn,
string $user,
string $pass,
array $options,
int $maxAttempts = 5
): PDO {
$attempt = 0;
while (true) {
$attempt++;
try {
return new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
$permanentFailure = str_contains($e->getMessage(), 'Access denied')
|| str_contains($e->getMessage(), 'Unknown database');
if ($permanentFailure || $attempt >= $maxAttempts) {
throw $e;
}
$delayMs = min(2000, 100 * (2 ** $attempt));
usleep($delayMs * 1000);
}
}
}Nested Calls, Savepoints, and the Real Transaction
Only the outermost call ever touches the real transaction -- every nested call scopes its own success/failure to a savepoint.
recordOrder() depth=1 BEGIN
|-- adjustInventory() depth=2 SAVEPOINT sp_2
| (success) RELEASE SAVEPOINT sp_2
|-- adjustInventory() depth=2 SAVEPOINT sp_2
| (throws!) ROLLBACK TO SAVEPOINT sp_2 <-- only this item undone
| (exception re-thrown)
v
recordOrder() catches -> depth=1 ROLLBACK <-- whole order undone too, in this designImportant Rules
Set PDO::ATTR_EMULATE_PREPARES explicitly (usually false for MySQL/MariaDB) rather than relying on the driver default.
Emulated prepares are not an injection risk by themselves -- the real risk is unrelated string interpolation for identifiers/LIMIT.
Never call beginTransaction() while one is already open -- use a SAVEPOINT for any nested scope instead.
Track transaction nesting depth in application code; PDO itself has no concept of nested transactions.
Use unbuffered queries (PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = false) only for large, one-pass exports/batch jobs, and always closeCursor() when done.
Streaming technique is driver-specific -- MySQL's buffered flag and PostgreSQL's server-side cursors are not interchangeable.
Retry transient connection failures with bounded, backed-off attempts; never retry a permanent misconfiguration or a write with unknown commit status.
Where This Shows Up
Nightly export jobs, data-warehouse ETL scripts, and CSV-generation endpoints are where unbuffered/streaming queries stop being an optimization and start being a requirement -- a naive fetchAll() against a millions-of-rows export table is a routine cause of out-of-memory crashes in production batch jobs. Savepoint-based nested transactions show up constantly in any codebase with reusable domain functions (inventory adjustment, audit logging, notification dispatch) that need to be individually transactional yet also composable inside a larger business transaction.
function saveItem(PDO $pdo) { $pdo->beginTransaction(); /*... */ $pdo->commit(); } // called from inside a caller that already has a transaction open function saveItem(PDO $pdo) { NestedTransaction::run($pdo, function (PDO $pdo) { /*... */ }); } // uses a savepoint when nested, a real transaction when standalone
Calling beginTransaction() while a transaction is already open either throws or silently produces behavior that isn't a real independent nested scope, depending on the driver -- a reusable function has no way to know in advance whether it's being called standalone or from inside a larger transaction, so it needs savepoint-aware logic rather than assuming it owns the top-level transaction.
$stmt = $pdo->query('SELECT * FROM event_log'); $rows = $stmt->fetchAll(); // 40 million rows, buffered by default
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); $stmt = $pdo->query('SELECT * FROM event_log'); foreach ($stmt as $row) { /* process one at a time */ } fetchAll() against a buffered query has to hold the entire result set in PHP memory at once -- on a genuinely large table this exhausts the memory limit before your code processes a single row. Switching to an unbuffered query and iterating keeps memory flat regardless of how many rows match.
What is the main practical difference between PDO::ATTR_EMULATE_PREPARES set to true vs. false? A. Emulated prepares are vulnerable to SQL injection; native prepares are not B. With native prepares, bound values are sent to the server as real typed data and the plan can be reused; with emulation, PDO substitutes values into the SQL text client-side and often returns everything as strings C. They produce identical behavior in every case D. Emulated prepares only work with SQLite
B. With native prepares, bound values are sent to the server as real typed data and the plan can be reused; with emulation, PDO substitutes values into the SQL text client-side and often returns everything as strings — Both modes safely quote/escape bound values, so emulation isn't an injection risk by itself. The real difference is type fidelity (native prepares preserve the server's actual column type) and plan reuse (a native server-side prepared statement can be executed repeatedly without re-parsing).
How do you emulate a nested transaction in PDO, since PDO only supports one real transaction at a time? A. Call beginTransaction() again for each nested scope B. Use SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT via raw SQL for inner scopes, reserving the real beginTransaction()/commit() for the outermost call C. Open a second PDO connection for every nested operation D. PDO has a dedicated $pdo->beginNestedTransaction() method for this
B. Use SAVEPOINT / RELEASE SAVEPOINT / ROLLBACK TO SAVEPOINT via raw SQL for inner scopes, reserving the real beginTransaction()/commit() for the outermost call — PDO has no built-in nested transaction support and no dedicated savepoint API -- the standard pattern is issuing SAVEPOINT/ROLLBACK TO SAVEPOINT/RELEASE SAVEPOINT as plain SQL for any scope nested inside an already-open transaction, tracking nesting depth in your own code.
Why would you set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false when reading a very large table? A. It makes the query run faster on the server B. It disables prepared statements for better compatibility C. It streams rows from the server as they're fetched instead of loading the entire result set into PHP memory first, keeping memory use flat regardless of row count D. It automatically paginates the query for you
C. It streams rows from the server as they're fetched instead of loading the entire result set into PHP memory first, keeping memory use flat regardless of row count — PDO's MySQL driver buffers the full result set into PHP memory by default. Turning buffering off streams rows as you fetch them, which is what makes it possible to process a multi-million-row table without exhausting the memory limit -- at the cost of keeping the connection busy until the result is fully consumed or closed.
Write a PHP function importLargeCsv(PDO $pdo, string $path): int that reads a CSV file line by line (fopen/fgetcsv) and inserts each row into a staging_rows table, using an unbuffered connection setting appropriately and a single prepared statement reused for every row, returning the count of rows inserted.
function importLargeCsv(PDO $pdo, string $path): int { $stmt = $pdo->prepare('INSERT INTO staging_rows (col_a, col_b) VALUES (:a, :b)'); $handle = fopen($path, 'r'); $count = 0; while (($row = fgetcsv($handle)) !== false) { $stmt->execute(['a' => $row[0], 'b' => $row[1]]); $count++; } fclose($handle); return $count; } // fgetcsv already streams the file one line at a time; the risk here was never about buffered/unbuffered SELECTs.
The unbuffered-query setting matters for SELECTs streaming results out of the database, not for INSERTs streaming data in -- think about which side of this operation is actually at risk of unbounded memory growth, and prepare the INSERT statement once, outside the loop.