Database Transactions for Small Web Applications — Keep Multi-Step Writes All-or-Nothing
A small web application can execute two database statements successfully hundreds of times and still have a serious design gap. What happens when the first statement succeeds but the second one fails? An order might exist without its items, stock might decrease without an order, or a payment record might be stored without the corresponding ledger entry. The individual queries can all be valid while the result as a whole is not.
A database transaction addresses this specific problem by drawing a boundary around related work. Inside that boundary, the application asks the database to make all the changes permanent together or cancel them together. This sounds simple, but using transactions well requires more than placing beginTransaction() and commit() around arbitrary SQL.
The useful question is not "How many queries?"
A transaction is often described as a bundle of statements, but statement count is only a clue. The more useful question is: which changes represent one business decision?
Creating an order and its line items may be one decision. Reserving stock and recording that reservation may be one decision. Updating a profile and writing an optional analytics event probably are not one decision, because losing the analytics event should not necessarily reject the profile update.
The PostgreSQL transaction tutorial gives the underlying model: several steps become one all-or-nothing operation, and their intermediate states are not exposed as completed work. Although its examples use PostgreSQL, that basic model also applies to transactions in other relational databases. Vendor details still differ, so the database and client documentation remain important.
This boundary is a design choice. A transaction cannot decide which operations belong together. The application has to define that first.
Autocommit hides the gap between statements
PDO connections normally operate in autocommit mode, as explained in the PHP manual. MariaDB also documents autocommit as enabled by default. In practical terms, a successful statement becomes permanent without waiting for the next application statement.
That behavior is sensible for a single independent write. It becomes risky when two or more writes must either all happen or not happen. Consider this sequence without an explicit transaction:
- Decrease the available stock.
- Create the order.
- Create the order item.
If step two or three fails, autocommit does not rewind step one. Application code can attempt a compensating update, but that creates another operation that can fail and another concurrency case to reason about.
An explicit transaction changes the sequence. beginTransaction() starts the boundary, commit() accepts it, and rollBack() cancels its uncommitted database changes. MariaDB describes the equivalent SQL operations in its START TRANSACTION documentation.
A small PDO pattern
The following example is illustrative rather than code taken from a production system. It assumes $pdo is an existing PDO connection, the relevant MariaDB tables use a transactional storage engine, and the IDs and quantity have already been validated at the application boundary.
<?php
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$orderId = $validatedOrderId;
$productId = $validatedProductId;
$quantity = $validatedQuantity;
try {
$pdo->beginTransaction();
$reserve = $pdo->prepare(
'UPDATE products
SET stock = stock - :quantity
WHERE id = :product_id AND stock >= :quantity'
);
$reserve->execute([
'quantity' => $quantity,
'product_id' => $productId,
]);
if ($reserve->rowCount() !== 1) {
throw new RuntimeException('Product unavailable or stock insufficient');
}
$createOrder = $pdo->prepare(
'INSERT INTO orders (id, status) VALUES (:id, :status)'
);
$createOrder->execute([
'id' => $orderId,
'status' => 'pending',
]);
$createItem = $pdo->prepare(
'INSERT INTO order_items (order_id, product_id, quantity)
VALUES (:order_id, :product_id, :quantity)'
);
$createItem->execute([
'order_id' => $orderId,
'product_id' => $productId,
'quantity' => $quantity,
]);
$pdo->commit();
} catch (Throwable $error) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
throw $error;
}There are two distinct safeguards here. The transaction prevents a partial set of database writes from becoming permanent. The condition stock >= :quantity protects a business invariant within the stock update itself. If no row is updated, the code rejects the operation and rolls back.
Separating those ideas matters. A transaction can atomically commit an incorrect calculation. Atomicity means the selected operations stay together; it does not prove that the selected rule is correct.
Concurrency still needs deliberate rules
Transactions are sometimes treated as if they automatically make every read-then-write sequence safe. That is too broad. Suppose an application first reads a stock value, decides it is sufficient in PHP, and later sends an unconditional update. Another transaction may change the same product between those operations. The exact outcome depends on the statements, indexes, locks, and isolation level.
The guarded update above avoids one narrow race by putting the check and decrement in one database statement. Other workflows may need a locking read such as SELECT ... FOR UPDATE, a unique constraint, optimistic version checking, or a different data model. The correct tool depends on the invariant being protected.
MariaDB supports several isolation levels and documents REPEATABLE READ as the InnoDB default in its SET TRANSACTION reference. A stronger-sounding isolation level is not automatically a better global setting: it changes visibility and locking behavior and can reduce useful concurrency. It is safer to identify the anomaly a workflow must prevent before changing the level.
What a database transaction cannot roll back
The database boundary ends at the database. If application code sends an email, calls a payment API, publishes a message, or writes to another independent system before committing, a later database rollback does not recall that external side effect.
For a simple workflow, the application may perform a non-critical action only after a successful commit. For delivery that must survive crashes and retries, a durable outbox pattern can record the intended message in the same database transaction and let a separate worker deliver it. Even then, retry and duplicate-handling rules have to be designed; the word "transaction" does not make a network call exactly-once.
Transactions also do not replace constraints. A foreign key, unique constraint, or suitable CHECK constraint can protect data when writes arrive through a different code path. Application validation helps users, while database constraints defend invariants closer to the stored data. These layers solve related but different problems.
Four traps worth checking
1. The storage path must actually support transactions
PDO can expose transaction methods while an underlying runtime condition or table engine prevents the expected rollback behavior. The PHP manual specifically warns that PDO's capability check occurs at the driver level. For MariaDB applications, verify that every table participating in the unit of work uses an appropriate transactional engine, commonly InnoDB.
2. Schema changes may commit behind your back
Do not mix ordinary request writes with schema migration statements and assume one rollback covers both. MariaDB documents many statements, especially DDL such as CREATE TABLE, ALTER TABLE, and DROP TABLE, that cause an implicit commit. Its implicit-commit reference notes that the commit occurs before execution for listed statements, so even a failing statement may have already ended the previous transaction.
3. Long transactions have an operational cost
A transaction should normally contain the database work needed for its invariant, not slow HTTP requests, user interaction, file uploads, or unrelated computation. Keeping a transaction open can retain locks and make other work wait. Shorter is not a magic guarantee, but a narrow boundary is easier to reason about and usually reduces contention.
4. Ownership must be clear
A low-level helper that silently begins or commits a transaction can conflict with a transaction already owned by its caller. PDO also does not offer portable, transparent nested transactions. Decide which application layer owns the boundary. If partial rollback is genuinely needed, investigate the database's savepoint behavior explicitly rather than treating another beginTransaction() as nesting.
A review checklist for one write path
- Name the invariant in one sentence.
- List every database change required to preserve it.
- Keep optional logging or analytics outside the critical boundary unless it is truly required.
- Check the PDO driver and storage engines used by all participating tables.
- Make statement failures visible as exceptions or checked return values.
- Rollback only when a transaction is still active, then rethrow or handle the original failure deliberately.
- Consider concurrent requests, not only the single-request success path.
- Keep network calls and other irreversible side effects out of the open transaction.
- Test the failure after each critical statement in a disposable environment.
Conclusion
A transaction is most useful when it expresses one clear promise: these database changes belong together. It closes the partial-write gap left by autocommit, but it does not choose the right business rule, coordinate every external system, or remove concurrency decisions.
For a small PHP application, the practical starting point is modest: define the invariant, open the transaction immediately before the related database work, enforce critical conditions as close to the write as possible, commit only after every required statement succeeds, and roll back on failure. Then examine what remains outside that boundary. That final question is often where the next real design problem appears.