Pradip Dharam

Imagine an online food-delivery application receiving two requests for the same order at almost the same time.

One request updates the order status.

Another request reads the status and decides whether another operation is allowed.

Both use database transactions.

The important question is not only which isolation level is configured.

It is:

What can each transaction see, and what happens when both transactions make decisions concurrently?

What Are Isolation Levels?

SQL defines four commonly discussed isolation levels:

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

They describe how much one transaction is isolated from concurrent transactions.

The SQL standard also describes phenomena such as dirty reads, non-repeatable reads, and phantom reads.

This gives us a common vocabulary.

But it does not fully describe how a real database handles concurrency.

Why the Isolation-Level Name Is Not Enough

Modern databases can use different mechanisms, including:

• MVCC
• Transaction snapshots
• Locks
• Write-conflict detection

Therefore, two databases using the same isolation-level name can behave differently in important situations.

For production systems, the database's actual documentation and concurrency model matter more than the label alone.

MVCC and Snapshot Isolation

Many databases use MVCC (Multi-Version Concurrency Control).

MVCC allows multiple versions of data to exist so readers can often work without blocking writers.

But MVCC is not the same thing as Snapshot Isolation.

MVCC is a concurrency-control technique.

Snapshot Isolation is an isolation model based on transactions reading from a consistent snapshot.

Similarly, Snapshot Isolation should not automatically be treated as SERIALIZABLE.

A Simple Write Skew Example

Suppose an application has two doctors on call.

The rule is:

At least one doctor must remain on call.

Initially:

• Doctor A is on call.
• Doctor B is on call.

Two transactions run concurrently.

Transaction 1 sees B on call and removes A.

Transaction 2 sees A on call and removes B.

If both commit, nobody remains on call.

Neither transaction needed to read uncommitted data.

Both may have seen a consistent snapshot.

Yet the business rule was violated.

This is write skew.

It shows why preventing dirty reads does not automatically prevent every concurrency anomaly.

Why Serializable Matters

SERIALIZABLE provides a stronger guarantee: the result should be equivalent to some serial execution of the transactions.

That can prevent anomalies such as write skew.

However, stronger isolation can increase:

• Blocking
• Transaction aborts
• Retries
• Contention
• Latency

So stronger isolation is not automatically the best choice for every workload.

Common Production Mistake

A common mistake is assuming:

"REPEATABLE READ means the same thing everywhere."

It does not.

Database implementations can differ in how snapshots, locks, conflicts, and concurrent writes are handled.

This matters in applications involving:

• Inventory
• Orders
• Reservations
• Account state
• Usage limits
• Background jobs

Practical Rule

When choosing an isolation level, don't ask only:

"Which level are we using?"

Ask:

1. What can this transaction observe?
2. How are concurrent writes handled?
3. Can write skew occur?
4. What happens when transactions conflict?
5. What happens to latency under contention?

These questions reveal whether the database's actual behavior matches the application's consistency requirements.

Key Takeaway

SQL isolation-level names provide a useful starting point, but they do not completely describe real database concurrency.

For production systems, understand the specific database's MVCC, snapshot, locking, and conflict behavior before relying on an isolation guarantee SQL names.

Common Questions / FAQ

Is MVCC the same as Snapshot Isolation?

No. MVCC is a concurrency-control technique; Snapshot Isolation is an isolation model that can use MVCC.

Does REPEATABLE READ prevent write skew?

Not necessarily. The answer depends on the specific database implementation.

Is SERIALIZABLE always better?

It provides stronger guarantees, but can increase contention, blocking, aborts, and latency.

Should every application use SERIALIZABLE?

No. Choose isolation based on the application's correctness requirements and workload.

Share your technical perspective on database isolation in the comments.