Transactions
Group writes that are only correct together: beginTransaction, commit when every step worked, rollBack from the catch.
$pdo->beginTransaction(); /* writes */ $pdo->commit(); // or $pdo->rollBack() in the catchA transaction is a group of writes the database treats as one. Either all of them happen or none of them do. You open one with beginTransaction(), and from then on your writes are provisional. commit() makes them real and visible to everyone else. rollBack() throws them away as though they never ran.
The writes that are only correct together
Move money between two accounts and you have two writes: one subtracts, one adds. If the second fails, the first has already destroyed money. Create an order and its line items, and an order with no lines is a record nobody can fulfil. Whenever a half-finished state would be wrong, the writes belong in one transaction.
The shape
Open the transaction, do the writes inside a try, and commit() as the last statement in the try. In the catch, call rollBack() and then rethrow, because the caller still needs to know the operation failed. Without a rollBack(), an exception leaves the transaction open; it ends when the script does, and until then the rows it touched may be locked against other requests.
What does not roll back
beginTransaction() does not nest. Calling it while a transaction is already open raises an error, so check inTransaction() if a function may be called from inside another one. Writes that are not in the database at all, such as an email you sent or a file you wrote, roll back no further than the moment you sent them.
Example
When to use it
- Insert an order and its line items so neither can exist without the other.
- Transfer a balance between two accounts without ever losing or inventing an amount.
- Undo a half-finished import when row 400 of 1000 turns out to be invalid.
- Deduct stock and record the reservation as one indivisible step.
More examples
The real thing, on a PDO connection
An order with no lines is not a smaller order, it is a broken record, so the header and the lines go in together. Catching Throwable rather than PDOException matters: a TypeError in your own loop must roll back too. The rethrow is not optional, because the caller has to know not to show a confirmation page.
<?php
declare(strict_types=1);
function placeOrder(PDO $pdo, int $customerId, array $lines): int
{
$pdo->beginTransaction();
try {
$order = $pdo->prepare(
'INSERT INTO orders (customer_id, status) VALUES (:customer_id, :status)'
);
$order->execute(['customer_id' => $customerId, 'status' => 'new']);
$orderId = (int) $pdo->lastInsertId();
$line = $pdo->prepare(
'INSERT INTO order_lines (order_id, sku, quantity) VALUES (:order_id, :sku, :quantity)'
);
foreach ($lines as $row) {
$line->execute([
'order_id' => $orderId,
'sku' => $row['sku'],
'quantity' => $row['quantity'],
]);
}
$pdo->commit();
return $orderId;
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
}
A function that may already be inside a transaction
Only the function that opened the transaction may finish it, which is what the $owns flag records. Without it, an inner commit() would make the outer transaction's half-finished work permanent. The condition stock >= :quantity in the UPDATE is the other half of the idea: the database, not PHP, decides whether there is stock, so two simultaneous requests cannot both pass the check.
<?php
declare(strict_types=1);
/**
* beginTransaction() does not nest. inTransaction() lets this function
* be called on its own or from inside a larger transaction.
*/
function deductStock(PDO $pdo, string $sku, int $quantity): void
{
$owns = !$pdo->inTransaction();
if ($owns) {
$pdo->beginTransaction();
}
try {
$stmt = $pdo->prepare(
'UPDATE products SET stock = stock - :quantity WHERE sku = :sku AND stock >= :quantity'
);
$stmt->execute(['quantity' => $quantity, 'sku' => $sku]);
if ($stmt->rowCount() === 0) {
throw new RuntimeException("not enough stock for {$sku}");
}
if ($owns) {
$pdo->commit();
}
} catch (Throwable $e) {
if ($owns) {
$pdo->rollBack();
}
throw $e;
}
}
Discussion