ACID: What Does a Transaction Guarantee?
Imagine creating an order and reducing the available stock by one.
Keeping the order when the second step fails would leave the data in
the wrong state. A database transaction gives these changes a
boundary. ACID names four properties of that boundary: atomicity,
consistency, isolation, and durability.
This is not a command reference. The SQL quick reference
covers BEGIN, COMMIT, and ROLLBACK syntax.
The transaction boundary
In this example, one row is added to orders and the available
value in stock is reduced. When both changes belong to one
PostgreSQL transaction, they become visible together on completion;
if the transaction is rolled back, neither change remains. This
assumes the stock row exists and the necessary stock rules have
also been defined.
The boundary matters: completing the order and stock changes in separate transactions does not cause ACID to combine them.
Atomicity — All or nothing
Atomicity means that the database effect of a transaction happens in full or not at all. If the stock update fails after the order row is inserted, the transaction cannot complete; rolling back the failed transaction also removes the new order row.
It does not promise that every SQL command inside a transaction will succeed. The application must finish or abort the transaction appropriately when an error occurs.
Consistency — Defined rules
Here, consistency means that committed data complies with defined
integrity constraints. For example, if stock.available >= 0 is
enforced with a CHECK constraint, a transaction that would leave
negative stock cannot commit.
The database cannot enforce a business rule it has not been given. A rule such as “one open reservation per customer and product” needs an appropriate constraint or application logic. Consistency in the CAP article has a different meaning.
Isolation — Concurrent transactions
Two customers may try to buy the last item at once. Isolation governs how concurrent transactions see unfinished changes and how their results can interact.
At PostgreSQL’s default Read Committed level, each query sees
data committed before that query began. Two reads inside one
transaction can therefore produce different results if another
transaction commits between them. Starting a transaction alone
does not solve every concurrency problem. Some scenarios require
a stronger isolation level or suitable locking; applications
using Serializable must also be ready to retry transactions
that fail due to serialization conflicts.
Durability — Surviving a crash
Durability means that a transaction acknowledged as committed
by the database remains recorded after a subsequent crash.
After the order’s COMMIT succeeds, a server restart should
not lose the order under normal durability settings.
A query result on screen cannot prove this property; it depends on storage and logging. PostgreSQL has settings that relax durability, so assess the guarantee together with the actual configuration.
What ACID does not guarantee
- A database transaction does not automatically undo an email sent at the same time or a change in another service.
- It does not invent business rules the application never defined.
- The default isolation level does not make every concurrent execution equivalent to a serial one.
A network partition between data replicas is a separate problem. Continue with CAP and BASE.