Jan Mikoláš
Glossary
Database transaction
A transaction combines related database changes into one result. On its own, however, it does not cover a remote payment, email, or message sent to another service.
Short definition
Either the entire local step takes effect, or none of it does.
A database transaction begins before related reads and writes. If everything succeeds, COMMIT confirms the changes and makes them available to other transactions. On an error, conflict, or deliberate decision, ROLLBACK discards the changes made by that transaction so far. This commonly ensures that an order, its items, and an inventory reservation are created together rather than only the first of them.
A transaction is a feature of a specific database and its connection. It does not automatically create a transaction across HTTP, a message queue, Redis, or a carrier API. Its boundary therefore belongs around a small, consistent local operation—not a long import, network call, or wait for a person.
What it is used for
Where an incomplete write would damage application state
A transaction protects changes that must become visible together in the local relational database.
- creating an order, its items, price, and relationship to the customer
- reserving inventory together with changing the available quantity
- storing an imported order and its unique external identifier
- changing a complaint status together with an audit record
- transferring money between two local accounts held in one database
Practical example
Importing an order from a marketplace
The importer receives an order in a response that may be repeated after a timeout. In a short transaction, it creates the order and an integration record; the items would be created in the same local step. A unique constraint on marketplace and external_order_id prevents a second creation; on a duplicate, the application loads the existing order.
After the commit, a separate worker sends the information to other systems. If delivery fails, the completed order is not rolled back. The retry works with the stored outbox record and the idempotent meaning of the event.
try {
$connection->beginTransaction();
$connection->insert('marketplace_order', [
'marketplace' => 'example-market',
'external_order_id' => $externalOrderId,
'status' => 'new',
]);
$connection->insert('outbox_event', [
'type' => 'order.imported',
'payload' => json_encode(['externalOrderId' => $externalOrderId], JSON_THROW_ON_ERROR),
]);
$connection->commit();
} catch (\Throwable $exception) {
if ($connection->isTransactionActive()) {
$connection->rollBack();
}
throw $exception;
}How it works
From beginning a change to committing it
The exact behaviour varies by database and isolation level, but the fundamental decision remains the same.
- BEGIN The application opens a transaction, and the database creates a working context for its reads and changes.
- Checking state The necessary data is loaded and rules evaluated. A read alone, without a constraint or suitable lock, may not prevent a concurrent change.
- Local writes Rows, relationships, and audit records are created or modified. Constraints protect permitted state even against another process.
- COMMIT or ROLLBACK Changes are committed on success or rolled back on failure. Some conflicts must be caught and the operation retried safely.
- Follow-up work An event for an external system is usually stored as a local outbox record and sent later by a separate worker.
Main components and concepts
Atomicity is not enough without correct concurrency design.
The ACID concepts are a useful shorthand, but actual behaviour is determined by the database, isolation level, and query.
Atomicity and durability
Atomicity means that a local group of changes is not committed only halfway. Durability means that, according to its configuration and operational guarantees, the database protects committed data even against failure.
Isolation and MVCC
Concurrent transactions do not necessarily see the same data at the same time. PostgreSQL uses MVCC; higher isolation can limit anomalies but may require retrying the entire transaction after a serialisation conflict.
Constraint, lock, and retry
UNIQUE or FOREIGN KEY protects a specific invariant. A targeted lock can protect a critical row. Neither replaces handling errors and conflicts and retrying safely.
Transaction boundary
It should be short and include only the necessary database work. A long transaction holds resources, complicates cleanup of old row versions, and increases the chance of a conflict.
Nested transactions and savepoints
A nested application block is not always an independent database transaction. Some libraries use a savepoint that can roll back part of the work, but the outer transaction still determines the final commit.
Benefits and limitations
Stronger consistency requires careful control of concurrency and transaction duration.
Benefits
- protection from partially written local state
- a clear boundary for constraints, auditing, and error handling
- more predictable work across concurrent requests and workers
- the ability to roll back local changes after an expected error
Limitations and common mistakes
- a network call or email inside a transaction needlessly extends locks
- assuming that a database commit also confirms an external API
- a missing UNIQUE constraint for an idempotent import
- ignoring a deadlock or serialisation conflict without a deliberate retry
- an overly broad transaction spanning the entire HTTP request
When it makes sense
For a change with local, clearly expressed rules.
A transaction is natural for an order, inventory state, user permissions, or an internal process where several tables must agree. The database may use an implicit transaction for a single INSERT, but the design must still account for constraints and duplicate behaviour.
It is not suitable as a mechanism for long imports, slow file generation, or waiting for a carrier response. Such work is divided into small idempotent steps: the local state is committed, and a worker then handles the follow-up task. Distributed consistency cannot be achieved simply by stretching one SQL transaction across the entire system.
What to keep in mind
The boundary must match the actual data rule.
A good transaction is short, measurable, and able to fail clearly.
- include only data that must change state together in the transaction
- protect important invariants with a database constraint, not only a condition in PHP
- do not call a remote API, send email, or access a slow filesystem while holding a lock
- log and handle deadlocks, timeouts, and conflicts according to the operation
- design retries together with an idempotency key or unique constraint
Common questions
Transactions in a live application
Will a rollback also undo a sent email or API request?
No. Rollback reverts changes in the given database transaction. Remote side effects need a separate design, such as an outbox, retries, and an idempotent API.
Is every SQL operation a transaction?
The database can use an implicit transaction for a standalone statement. An explicit transaction is required when multiple dependent steps must take effect as a unit.
Do transactions solve every concurrency error?
No. Isolation, constraints, locks, and retries address different situations. Two transactions can still compete for the same business state.
Why should a transaction be short?
Holding resources for a long time increases the risk of waits, conflicts, and operational problems. A transaction should contain only the essential local database step.
How I work with databases in practice
I design data changes with clear rules and transaction boundaries.
In e-commerce and integration applications, I design transactions, constraints, imports, and traceability of state changes so critical data never changes only halfway.