From Transactions to MVCC: How Databases Actually Guarantee ACID

Let’s start from a place everyone understands.

You open a banking app and transfer money. You expect two things:

• The money leaves your account
• The same amount appears in the other account

You don’t think about failures, crashes, or concurrency. But underneath, the database is dealing with all of it simultaneously.

This is where the concept of transactions begins.

What is a Transaction?

A transaction is a group of operations that must be treated as a single unit.

Not “execute these steps,” but rather:

Either all of this happens, or none of it ever happened.

A simple example:

BEGIN; 

Deduct 100 from Account A 
Add 100 to Account B 

COMMIT;

This looks straightforward. But imagine the system crashes right after deducting money from A but before adding it to B.

Now the system is inconsistent.

To prevent this, databases rely on a set of guarantees known as ACID properties.

ACID: The Contract of Reliability

ACID defines what a “correct” transaction means.

Atomicity — All or Nothing

If any part of a transaction fails, the entire transaction is undone.

No partial updates. No half-finished states.

Consistency — Valid State Always

After a transaction, the database must follow all rules:

• No invalid foreign keys
• No duplicate unique values
• No broken constraints

This is where business rules and database constraints meet.

Isolation — Transactions Don’t See Each Other’s Mess

When multiple users are interacting with the database at the same time, their operations should not interfere in unpredictable ways.

Each transaction should behave as if it is running alone.

Durability — Once Committed, It Stays

After a transaction commits, the data must survive:

• Crashes
• Power failures
• Restarts

At this point, ACID sounds like a promise. The real question is:

How does the database actually enforce this?

This is where the internal machinery comes in.

The Hidden Engine Behind ACID

To implement ACID, databases rely on three core mechanisms:

• Undo Logs
• Write-Ahead Logging (WAL)
• MVCC (Multi-Version Concurrency Control)

And one optimization layer:

• Checkpointing

These are not separate features. They are tightly connected.

Let’s build them step by step.

Undo Logs: The Ability to Go Back in Time

Before a database changes any data, it first records what the data looked like.

This record is called an undo log.

Imagine updating a value:

A = 1000 → 900

Before changing it, the database stores:

Undo: A was 1000

Now two things become possible:

1. If the transaction fails → the database restores A = 1000
2. If another transaction needs an older view → it can still access 1000

This second point is subtle but powerful. Undo logs are not just for failure recovery. They are also used for serving older versions of data.

This becomes crucial when we talk about concurrency.

Write-Ahead Logging (WAL): The Ability to Move Forward

Undo logs help us go backward. WAL helps us go forward.

The rule is simple but critical:

The database must write changes to a log before applying them to actual data.

Let’s walk through it.

When updating A from 1000 to 900:

• The database writes a WAL entry:

A will become 900

• This log is flushed to disk
• Only then is the actual data updated in memory

Why this order?

Because memory is volatile. If the system crashes, memory is gone. But the log on disk remains.

So after a crash, the database can read the WAL and replay changes to restore the correct state.

Undo logs help you rewind incomplete work. WAL helps you replay completed work.

Together, they guarantee atomicity and durability.

The Concurrency Problem

So far, everything works well for a single transaction.

But real systems have thousands of concurrent users.

Now imagine this situation:

• One user is updating a row
• Another user is reading the same row

If we lock everything strictly:

• Reads block writes
• Writes block reads

The system becomes slow and unusable.

This is where MVCC comes in.

MVCC: Multiple Versions, No Waiting

Instead of overwriting data, the database creates new versions.

Let’s say:

A = 1000

A transaction updates it to 900.

Instead of replacing 1000, the database:

• Creates a new version: A = 900
• Keeps the old version: A = 1000 (in undo log)

Now we have two versions:

• New version (latest)
• Old version (previous state)

Each transaction sees a version based on when it started.

The Concept of Snapshot

When a transaction begins, it captures a snapshot of the database.

Think of it as:

This is my view of reality, and it won’t change while I’m running.

If a transaction started before the update:

• It continues to see A = 1000

If it started after:

• It sees A = 900

The database walks through the version chain (stored via undo logs) and picks the correct version.

This is how MVCC allows:

• Reads without blocking writes
• Writes without blocking reads

But There’s a Catch: Snapshot Isn’t Perfect

MVCC uses something called snapshot isolation.

Each transaction sees a consistent snapshot. But that snapshot may not reflect the latest reality.

This can lead to subtle anomalies.

Write Skew: When Reality Splits

Imagine a rule:

At least one doctor must be on call.

Initial state:

• Doctor A → ON
• Doctor B → ON

Two transactions start at the same time.

Transaction 1:

• Sees both ON
• Turns A OFF

Transaction 2:

• Sees both ON
• Turns B OFF

Both commit.

Final state:

• A OFF
• B OFF

The rule is violated, even though each transaction behaved correctly in isolation.

This is called write skew.

Snapshot isolation prevents many issues, but not all.

To fully prevent such anomalies, databases need serializable isolation, which comes at a performance cost.

Checkpointing: Making Recovery Practical

There’s one more piece.

WAL keeps growing as the system runs. If a crash happens, replaying the entire log would be slow.

Checkpointing solves this.

At intervals, the database:

• Flushes in-memory changes to disk
• Marks a position in the log as safe

After a crash, recovery starts from the last checkpoint instead of the beginning.

Think of it as saving progress in a game.

Bringing It All Together

Now connect everything:

• A transaction begins
• Undo logs record old values
• WAL records new changes
• Data is updated in memory
• MVCC allows multiple versions for concurrent access
• Commit is recorded in WAL
• Checkpoint ensures long-term efficiency

Each component supports a part of ACID:

• Atomicity → Undo logs
• Durability → WAL
• Isolation → MVCC
• Consistency → Constraints + all of the above

Where Relational and NoSQL Databases Differ

Relational databases were designed with ACID as a core principle.

They prioritize:

• Strong consistency
• Structured schemas
• Reliable transactions

NoSQL databases emerged to solve scalability challenges.

They often trade strict guarantees for:

• High availability
• Horizontal scaling
• Eventual consistency

But this is not a strict divide anymore.

Modern systems blur the line:

• Some NoSQL databases support ACID transactions
• Some relational systems allow relaxed consistency modes

The real decision depends on use case, not category.

Summary: Key Takeaways

If you compress everything into one picture:

• Transactions define what should happen together
• ACID defines what correctness means
• Undo logs allow going backward
• WAL allows going forward
• MVCC allows multiple realities to coexist
• Checkpoints ensure recovery stays fast

What looks like a simple “UPDATE” query is actually a carefully orchestrated system designed to survive failure, scale under load, and maintain correctness.

Once you see this whole framework together, databases stop feeling like black boxes. You start seeing the trade-offs, the guarantees, and the exact points where things can break.

And that’s where real engineering decisions begin.