When we execute a database transaction, a surprisingly large number of things can go wrong such as :
The server can crash
Two users can modify the same data at the same time
An operation can fail halfway through
The power can go out immediately after a transaction finishes
Yet when we work with relational databases, we expect our data to remain reliable. One of the foundations behind that reliability is ACID.
**ACID **stands for:
Atomicity • Consistency • Isolation • Durability
let's understand what problem each property actually solves.
1 - What Is a Transaction?
Imagine we have two bank accounts:
Alice: $1,000
Bob: $500
Alice wants to transfer $100 to Bob. At the database level, we might execute something like:
Technically, these are two UPDATE operations.
But from the application's perspective, they represent one logical operation:
Transfer $100 from Alice to Bob.
That's exactly why transactions exist, they allow multiple database operations to be treated as a single unit of work.
But now consider what happens if something goes wrong
What If the Database Crashes?
Imagine this sequence:
Alice lost $100, but Bob never received it.
Our database is now in a state that should never have existed.
This is where the first ACID property becomes important.
> A — Atomicity
Atomicity means:
A transaction happens completely or doesn't happen at all.
In other words is all or nothing
Our transfer has only two acceptable outcomes.
Success:
Or failure:
We should never permanently keep half of the transaction.
If something prevents the transaction from completing, its changes can be rolled back.
> C — Consistency
Consistency is probably one of the most misunderstood ACID properties.
A simple definition is:
A transaction takes the database from one valid state to another valid state.
But what exactly is a valid state?
That depends on the rules of our system.
For example:
Relational databases provide several mechanisms for defining these rules:
For example:
Now imagine a transaction tries to produce this:
Alice balance = -$500
If our database defines that as invalid through a constraint, that operation cannot successfully leave the database in that state.
There is an important detail here:
The database doesn't magically understand our business.
We still need to correctly define our schema, constraints, and application rules.
ACID doesn't save us from bad business logic.
> I — Isolation
Real databases don't execute only one transaction at a time.
Imagine thousands of users simultaneously:
Databases need concurrency.
Suppose two transactions access the same account:
What should each transaction be allowed to see?
What happens if one reads data that another transaction hasn't committed yet?
This is the problem addressed by Isolation.
Isolation controls how concurrent transactions interact and how much of each other's intermediate state they can observe.
Without sufficient isolation, concurrency can produce anomalies such as:
-> And this leads us to another important database concept.
Transaction Isolation Levels
SQL databases provide different isolation levels.
Conceptually, we can think about them as a spectrum:
As isolation becomes stronger, the database provides stronger guarantees about concurrent execution.
Serializable aims to make the final result equivalent to some valid serial execution of those transactions — as if they had executed one after another.
But stronger isolation isn't free.
Databases have to balance:
Depending on the database engine, this can involve mechanisms such as:
*MVCC, locks, snapshots, conflict detection, and transaction retries.
*
That's why choosing an isolation level isn't just a configuration detail. It can be an important architectural decision.
> D — Durability
Suppose our transaction succeeds:
COMMIT;
The database tells our application:
Transaction successful ✓
And then...
The machine loses power.
Should our transaction disappear? No.
That's what Durability guarantees.
Once a transaction has been successfully committed, its effects should survive system failures. The important distinction here is between volatile memory and persistent storage.
Changes existing only in RAM are not enough to provide durability.
Database engines therefore implement persistence and recovery mechanisms.
One particularly important mechanism used by databases such as PostgreSQL is Write-Ahead Logging (WAL).
The simplified idea is:
Transaction
↓
Write information to WAL
↓
COMMIT
↓
Database crash
↓
Recovery
↓
Committed data survives ✓
The actual implementation is considerably more sophisticated, but the fundamental principle behind write-ahead logging is extremely important:
Information necessary to recover a change is logged before the corresponding data pages need to be permanently written.
Putting ACID Together
Let's return to Alice and Bob
Alice ─────── $100 ───────> Bob
Each ACID property answers a different question about that transaction.
The next time you write:
COMMIT;
Remember that there's much more happening behind that simple command.
Prefer watching instead?
I also made a video explaining ACID transactions
Top comments (0)