Understanding ACID: Notes From My Database Fundamentals Journey

While learning database fundamentals, I came across a concept that shows up almost everywhere when talking about transactions: ACID.

At first, the four properties sounded like theoretical database terminology. But once I started looking at what can actually go wrong during a transaction, they made much more sense.

So this is my attempt to summarize ACID in a few simple points.

First, What Is a Transaction?

A transaction is a group of database operations that should be treated as one logical unit of work.

Imagine transferring $100 from Account A to Account B.

The database needs to do at least two things:

Account A: -100
Account B: +100

These are two separate database operations, but logically they represent one operation: the transfer.

What happens if the first query succeeds and the second one fails?

That's exactly the kind of problem ACID tries to solve.

ACID stands for:

• A — Atomicity
• C — Consistency
• I — Isolation
• D — Durability

1. Atomicity — All or Nothing

Atomicity means a transaction either completes entirely or doesn't happen at all.

Back to our transfer:

A: 1000 → 900
B: 500  → 600

Suppose the database subtracts $100 from A, then something goes wrong before adding it to B.

Without atomicity:

A = 900
B = 500

We just lost $100.

With atomicity, if any part of the transaction fails, the database rolls the transaction back.

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 'A';

UPDATE accounts
SET balance = balance + 100
WHERE id = 'B';

COMMIT;

If something fails before COMMIT, the transaction can be rolled back.

The important idea is simple:

Either everything happens, or nothing happens.

2. Consistency — Keep the Database Valid

Consistency means a transaction should move the database from one valid state to another valid state.

Suppose our system has a rule:

balance >= 0

If Account A has only $50, transferring $100 should not leave us with:

balance = -50

The transaction must respect the rules and constraints that define valid data.

These could include:

• Primary keys
• Foreign keys
• Unique constraints
• Check constraints
• Application-level business rules

One important distinction I learned here is that consistency doesn't magically understand your business.

The database only knows about the rules you define.

If you don't define or enforce a rule somewhere, the database cannot protect it for you.

3. Isolation — Transactions Shouldn't Step on Each Other

This was probably the most interesting part for me.

Databases don't normally process one transaction at a time.

Many transactions can run concurrently.

Imagine two users trying to buy the last item in stock.

Both transactions read:

stock = 1

Transaction A thinks:

Great, I can buy it.

Transaction B thinks the same thing.

Now both try to update the stock.

Without proper isolation, concurrent transactions can interfere with each other and produce incorrect results.

Isolation controls how much one transaction can see or interfere with the work of another transaction.

This is where concepts such as these start appearing:

• Dirty Reads
• Non-repeatable Reads
• Phantom Reads
• Lost Updates

And this leads to isolation levels such as:

Read Uncommitted
Read Committed
Repeatable Read
Serializable

Higher isolation usually gives stronger correctness guarantees, but it can also reduce concurrency.

So isolation is partly about finding the right balance between:

Correctness <------> Concurrency

4. Durability — Committed Means Committed

Suppose a transaction completes successfully and the database tells us:

COMMIT successful

Then the server immediately loses power.

What happens to our transaction?

Durability means that once the database confirms a transaction has been committed, that data should survive crashes and restarts.

Databases achieve this using mechanisms such as transaction logs and Write-Ahead Logging (WAL).

A simplified idea is:

Write the change to a durable log
        ↓
Confirm the transaction
        ↓
Persist/update the actual data pages

So even if the machine crashes at an unfortunate moment, the database can use its log during recovery to reconstruct committed changes.

That's an important distinction:

COMMIT doesn't simply mean "the value changed in memory."

It means the database has reached a point where it can guarantee that the committed transaction won't disappear after a crash.

Putting It All Together

Going back to our $100 transfer:

A → Atomicity
Both balance updates happen, or neither happens.

C → Consistency
The transaction must leave the database in a valid state.

I → Isolation
Other concurrent transactions shouldn't corrupt the transfer.

D → Durability
Once committed, the transfer survives a crash.

The way I currently think about ACID is this:

ACID is not really about memorizing four definitions. It's about protecting transactions from four different categories of failure.

Atomicity protects us from partial execution.

Consistency protects us from invalid states.

Isolation protects us from concurrency problems.

Durability protects us from losing committed data.

Once I started thinking about ACID in terms of the problems each property is trying to prevent, the concept became much easier to understand.