Development
How to Use PDO Transactions for Multi-Step Updates

A PDO transaction groups related database changes so they either commit together or roll back together. Use one when a business operation would become inconsistent if only some of its SQL statements succeeded.
The database must support transactions for the tables involved. This example assumes MySQL with InnoDB, PHP 8.1 or newer, and PDO configured to throw exceptions. A transaction cannot make a nontransactional table or an external API participate automatically.
Use a PDO transaction for a multi-step update
Consider reserving one unit of stock and recording its reservation. Both changes must succeed. An atomic conditional update avoids first reading an available quantity and then racing another request.
The schema needs an InnoDB products table with id and integer stock, plus an InnoDB reservations table with product_id and quantity. Add your application's user, order, and idempotency constraints before using this in a real checkout.
<?php
/** Reserve one stock unit and record the matching reservation atomically. */
function reserveOne(PDO $pdo, int $productId): void
{
$pdo->beginTransaction();
try {
$stock = $pdo->prepare(
'UPDATE products SET stock = stock - 1 WHERE id = ? AND stock > 0'
);
$stock->execute([$productId]);
if ($stock->rowCount() !== 1) {
throw new RuntimeException('Product is unavailable.');
}
$reservation = $pdo->prepare(
'INSERT INTO reservations (product_id, quantity) VALUES (?, 1)'
);
$reservation->execute([$productId]);
$pdo->commit();
} catch (Throwable $error) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $error;
}
}
Configure PDO::ATTR_ERRMODE to PDO::ERRMODE_EXCEPTION when constructing the connection. Validate identifiers and authorize the operation before entering this function. Prepared statements handle values, not authorization.
The function owns its transaction boundary. Calling it inside another transaction requires a deliberate design, because PDO does not provide transparent nested transactions. Keep transaction ownership clear at the application-service boundary.
Understand what rollback can undo
Rollback reverses transactional database changes. It cannot unsend an email, refund a remote charge, or delete a file already exposed elsewhere. Keep network calls outside a database transaction where possible, and record an outbox job inside the transaction when a later action must follow a committed change.
Some SQL statements cause implicit commits. Do not mix schema migrations into an ordinary business transaction and assume a later rollback will undo them. PHP documents these limitations in PDO transactions.
Keep contention predictable
Keep transactions short and acquire rows in a consistent order when updating several records. A transaction held open while waiting for a payment API can block unrelated requests.
When a decision needs a current row value, a locking read such as SELECT ... FOR UPDATE may be appropriate inside a transaction. Its behavior depends on indexes and isolation level. MySQL's InnoDB locking-read documentation explains the relevant semantics.
Deadlocks are possible even in correct applications. If you retry, retry the complete transaction a bounded number of times and only when its operations are safe to repeat. Do not retry every exception, especially an unknown commit outcome after a connection failure.
Test failure and concurrency
Force the reservation insert to fail and verify that stock remains unchanged. Test missing products, zero stock, and two simultaneous requests for the last unit. Confirm that exactly one reservation succeeds and stock never becomes negative.
Use the same storage engine and isolation settings as production. An in-memory substitute cannot prove InnoDB locking behavior. Also test the application's handling of deadlocks and connection loss without claiming that retries provide exactly-once business execution.
For organizing these boundaries, see incremental PHP refactoring. Discuss a database consistency review when multi-step updates are producing partial records.