A transaction lets several database changes become one accepted unit. Saving an order while failing to reduce stock leaves an inconsistent system; changing both under a supported transaction prevents that partial result. PDO supplies beginTransaction(), commit(), rollBack(), and inTransaction(), but the database determines which operations are transactional and how concurrent sessions interact. This reference builds a conditional stock update and order insert, then explains cleanup, isolation, implicit commits, and retries. The goal is to understand the complete business operation rather than copying a try/catch wrapper around every SQL statement.
Choose the transaction boundary around the business rule
Ask which changes must succeed together. Creating an order, saving its items, and reducing stock may form one unit. Sending a confirmation email is an external operation and does not automatically join the database transaction. Keep the transaction short and avoid waiting for user input or a slow network service while holding locks. Validate predictable field errors before beginning when possible. A transaction protects supported database changes on its connection; it does not undo filesystem writes, sent mail, or changes on another connection. If those operations must coordinate, use an outbox, job state, or another reviewed design. A clear boundary makes failure handling understandable and reduces lock contention.
Use a supported engine and know DDL limits
PDO is an interface to the database, not an independent transaction engine. MySQL transactional behavior depends on the storage engine; InnoDB supports transactions while some other table types do not provide the same rollback guarantees. Many MySQL DDL statements cause implicit commits. Do not mix schema creation into an order transaction and expect rollback to undo everything. SQLite and PostgreSQL have their own behaviors and concurrency models. Check beginTransaction() and driver exception behavior under your supported environment. A transaction started successfully does not prove every later statement is rollback-safe. Document table engines, isolation settings, and any statements with special semantics as part of the application's database contract.
Avoid the read-then-write stock race
Two sessions can both read stock=1 and each decide one unit is available. A later unconditional subtraction can oversell. An atomic conditional UPDATE such as stock = stock - ? WHERE id = ? AND stock >= ? lets the database check and change the row together. Use a positive validated quantity and inspect the changed-row result. If no row changed, report unavailable stock under your privacy and not-found policy. The constraint stock >= 0 adds defense for other writers. A locking SELECT ... FOR UPDATE can fit more complex workflows under engine-specific rules, but it still needs a complete transaction plan. Choose the simplest operation that expresses the invariant atomically.
Commit only after every required statement succeeds
beginTransaction() disables ordinary per-statement commit behavior for the supported work. Run the conditional stock update, insert the order, collect its generated ID on the same connection, and commit. Only then report success to the user. If a statement fails, roll back if the connection still has an active transaction and rethrow or translate the failure at the application boundary. A rollback can itself fail after a broken connection, so production error handling should preserve the original diagnostic and record cleanup failures separately. Do not catch an exception, ignore it, and commit whatever happened earlier. That turns an all-or-nothing design back into silent partial success.
Isolation controls concurrent observations
Atomicity does not mean every transaction sees every other change instantly. Isolation levels determine snapshots, locking, and which anomalies are possible. MySQL, PostgreSQL, and SQLite differ in defaults and supported behavior. A transaction can still encounter deadlocks or serialization failures when operations contend. Keep lock order consistent and reduce unnecessary work while locks are held. Do not assume a preliminary SELECT remains valid until a later UPDATE under all isolation levels. For inventory, a conditional update directly enforces the availability rule. If your business depends on aggregate conditions spanning many rows, review the isolation and locking strategy explicitly instead of believing beginTransaction() alone protects the condition.
Retry the whole unit only when safe
Some transient concurrency failures can justify a bounded retry of the entire transaction. Retrying only the last statement can reuse a stale assumption or a transaction the server already aborted. Classify errors using driver codes rather than matching random message text. Add a small bounded backoff and an overall request deadline. Ensure the operation is idempotent or has a unique business key, especially when a lost connection makes the commit outcome uncertain. Do not repeat an external payment or email as part of an unreviewed retry. Log attempt counts and outcomes. A retry policy should be designed for the operation, not a universal catch-all loop that repeats every database exception forever.
Practice task and worked solution
Extend the order function to two products. Validate both quantities and update product rows in a consistent ID order to reduce deadlock risk. The solution runs all updates and item inserts on one PDO connection and commits only after every product succeeds. Test one unavailable product and verify the earlier stock change is rolled back. Add a stable request key protected by a unique constraint so a client retry cannot create a duplicate order. Explain where a confirmation job would be recorded and why actual mail should not be sent inside a blindly retryable transaction. Document the supported database engine and isolation level.
Continue learning
Return to PDO, CRUD, and exceptions for the surrounding application boundaries.
The PDO transaction manual explains transaction support and implicit commits.
Transactions with PDO Examples
Each example shows the code, the output it produces and what each part does.
Reduce stock and save an order together
Assume a PDO MySQL connection, InnoDB products and orders tables, and a positive validated quantity. The example changes a disposable development fixture.
When product 12 has at least two units, stock decreases by two and one order is committed. The generated order ID depends on the fixture. When stock is insufficient, the transaction fails and no order from this unit is committed.
<?php
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$productId = 12;
$quantity = 2;
$pdo->beginTransaction();
try {
$stock = $pdo->prepare(
'UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?'
);
$stock->execute([$quantity, $productId, $quantity]);
if ($stock->rowCount() !== 1) throw new RuntimeException('Stock unavailable');
$order = $pdo->prepare('INSERT INTO orders (product_id, quantity) VALUES (?, ?)');
$order->execute([$productId, $quantity]);
$orderId = $pdo->lastInsertId();
$pdo->commit();
echo 'Order saved: ' . $orderId;
} catch (Throwable $error) {
try {
if ($pdo->inTransaction()) $pdo->rollBack();
} catch (Throwable $cleanupError) {
error_log('order_transaction_rollback_failed');
}
throw $error;
}Enough stock: Order saved: database-generated ID
Insufficient stock: Stock unavailable exception; transaction rolled backTransactions with PDO FAQ
Does rollback undo mail?
No. External effects are outside the database transaction: emails, payment API calls, uploaded files and messages to other services happen immediately. Perform them after a successful commit, or record them in an outbox table inside the transaction and send them afterwards.
Does a transaction prevent every race?
No. A transaction makes a group of changes all-or-nothing, but two transactions can still read the same stock level and both decide to sell the last item. Query design and isolation still matter: use conditional updates such as UPDATE ... WHERE stock >= ?, row locks with SELECT ... FOR UPDATE, or unique constraints.
Can CREATE TABLE commit implicitly?
Yes. In MySQL, many DDL operations, such as CREATE TABLE, ALTER TABLE and DROP TABLE, commit any open transaction before they run. A later rollback then can't undo the earlier changes, so keep schema changes out of data transactions.
Can I retry every exception?
Only under a classified, bounded, safe operation policy. Deadlocks and lock timeouts are often safe to retry a few times, but constraint violations and syntax errors will fail again, and retrying a step that already charged a card can repeat it. Retry only known-transient errors, with a limit and a delay.
Transactions with PDO Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
An order item insert fails after stock changes. What is correct?
Transactions with PDO Quiz
2 questions to check what you learned.
What best prevents a simple stock oversell race?
- A SELECT followed by an unchecked subtraction
- A browser button disabled alone
- A random sleep
- An atomic conditional stock update
Which changes join one PDO transaction automatically?
- Supported operations on the transaction's database connection
- All sent emails
- Every connection in the process
- All filesystem changes