Pradip Dharam

 

A Real-World Scenario

Imagine an e-commerce application serving thousands of users.

A customer opens a product page while another transaction is updating the product's price, inventory, or availability.

The application should not randomly read a mixture of different database states.

A transaction may need to see a consistent view of the data while other transactions continue reading and writing.

This is one of the problems addressed by Multi-Version Concurrency Control (MVCC).

But there is an important source of confusion:

Different databases use different names and semantics for transaction isolation.

The name REPEATABLE READ does not automatically mean identical behavior across databases.

---

What Is MVCC?

MVCC stands for Multi-Version Concurrency Control.

Instead of having only one visible version of a row, an MVCC database can maintain multiple versions or enough historical information to reconstruct the version visible to a particular transaction or statement.

This allows readers to work with a consistent view while other transactions modify data.

Conceptually:

Transaction A
     |
     | reads
     v
Row Version 1

Transaction B
     |
     | updates
     v
Row Version 2

Depending on the database and isolation level, Transaction A may continue seeing the version that belongs to its snapshot while Transaction B works with a newer version.

This can significantly improve read/write concurrency compared with approaches that rely heavily on blocking readers and writers.

---

What Is Snapshot Isolation?

Snapshot isolation means that a transaction reads from a consistent snapshot of committed data rather than continuously seeing every change committed by other transactions during its lifetime.

A simplified example:

Time ─────────────────────────────>

T1 starts
   |
   |---- Snapshot created
   |
   |        T2 updates row
   |             |
   |             v
   |        New row version
   |
   |---- T1 reads
            |
            v
      Sees its snapshot

The important point is that MVCC is a concurrency-control mechanism, while snapshot isolation is an isolation behavior/guarantee.

They are related, but they are not synonyms.

An MVCC implementation can support different isolation behaviors.

---

Why Database Names Become Confusing

The SQL standard defines isolation concepts, but individual database systems have their own implementations and terminology.

As a result, engineers sometimes see:

PostgreSQL → Repeatable Read

Oracle → Serializable

MySQL/InnoDB → Repeatable Read

IBM Db2 → Repeatable Read / Cursor Stability and other isolation options

and conclude that these names can be directly mapped across databases.

That assumption is dangerous.

Isolation-level names should always be interpreted together with the database's actual transaction semantics.

---

PostgreSQL

PostgreSQL uses MVCC and provides isolation levels including READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

A PostgreSQL REPEATABLE READ transaction operates using a transaction-level snapshot.

This means repeated consistent reads within the transaction see the same snapshot, subject to PostgreSQL's concurrency rules.

PostgreSQL's SERIALIZABLE level goes further by providing serializable transaction behavior using Serializable Snapshot Isolation rather than simply relying on traditional locking alone.

This distinction matters.

Repeatable Read and Serializable are not interchangeable in PostgreSQL.

---

Oracle

Oracle also uses multiversion concurrency control.

Oracle's default READ COMMITTED isolation provides statement-level read consistency.

A statement sees a consistent view of committed data as of the relevant point in time for that statement.

Oracle also provides SERIALIZABLE, which provides stronger transaction-level consistency semantics.

This is one reason terminology can become confusing when comparing Oracle with PostgreSQL.

The same word may describe a different set of guarantees depending on the database engine.

---

MySQL and InnoDB

For MySQL, the storage engine matters.

InnoDB uses MVCC for consistent nonlocking reads.

InnoDB's default isolation level is commonly REPEATABLE READ.

A transaction can establish a consistent read view, allowing consistent nonlocking reads to observe the appropriate version of rows rather than simply reading the latest physical version.

However, InnoDB also has locking reads and write operations whose behavior differs from ordinary consistent nonlocking reads.

That distinction is important in production.

“Repeatable Read” does not mean every operation behaves like a simple snapshot read.

---

IBM Db2

IBM Db2 uses its own isolation-level terminology and concurrency mechanisms.

Depending on the isolation level and configuration, Db2 can provide different combinations of locking and read consistency behavior.

This is another example of why database migration requires more than changing SQL syntax.

An application relying on a particular transaction behavior should verify how the target database implements:

• consistent reads
• locking reads
• write conflicts
• phantom prevention
• transaction snapshots
• serialization

---

Why This Matters in Production

Suppose an application moves from one database engine to another.

The migration team sees:

REPEATABLE READ

in both systems.

It is tempting to assume:

“The application will behave the same.”

That can be wrong.

Differences in transaction semantics can affect:

• stale reads

• write conflicts

• lock contention

• serialization failures

• transaction latency

• retry behavior

• connection utilization

• application-level consistency

At low traffic, these differences may remain invisible.

Under high concurrency, they can become production problems.

---

MVCC Has Operational Costs

MVCC improves concurrency, but it is not free.

Maintaining old row versions or the information required to reconstruct them creates additional storage and cleanup work.

Long-running transactions can be particularly problematic because old versions may need to remain available for transactions that still require them.

This can contribute to:

• storage growth

• cleanup pressure

• increased I/O

• longer maintenance operations

• degraded database performance

PostgreSQL, for example, relies heavily on vacuuming to reclaim space and manage obsolete row versions.

So an application that keeps transactions open unnecessarily can create database-level operational problems even when query latency initially looks normal.

---

Common Mistakes

1. Treating isolation-level names as portable

REPEATABLE READ in one database should not automatically be assumed to provide identical behavior in another.

2. Assuming MVCC eliminates locking

MVCC reduces the need for readers and writers to block each other, but databases still use locks for many operations.

3. Assuming snapshot reads see the latest data

A consistent snapshot can intentionally return an older committed version.

That is a feature of the isolation model, not necessarily stale-data corruption.

4. Ignoring long-running transactions

Long transactions can prevent old versions from being cleaned up efficiently and can increase database resource pressure.

5. Testing only at low concurrency

Isolation problems often become visible when transactions overlap heavily.

Concurrency testing matters.

---

Practical Engineering Guidance

When choosing or changing a database:

Do not ask only:

“Which isolation level has the same name?”

Ask instead:

“What exact read and write guarantees does the application require?”

Then verify:

• What snapshot does a transaction read from?

• When is the snapshot created?

• Can concurrent writes cause transaction failures?

• Which operations acquire locks?

• How are serialization conflicts handled?

• What happens to long-running transactions?

• What retry behavior is required?

This is much safer than mapping isolation levels by name.

---

Key Takeaway

MVCC is a mechanism for managing multiple versions of data, while snapshot isolation is a concurrency behavior built around consistent views of data. Database isolation-level names are not portable contracts, so always verify the actual semantics of the specific database engine.

---

Common Questions

Is MVCC the same as snapshot isolation?

No. MVCC is a concurrency-control technique. Snapshot isolation is an isolation behavior that can be implemented using MVCC.

Is PostgreSQL Repeatable Read the same as MySQL Repeatable Read?

Not necessarily. Both use MVCC and provide repeatable-read semantics, but their detailed transaction and conflict behavior differs.

Does MVCC prevent locking?

No. MVCC can reduce reader/writer blocking, but locking is still used for many database operations.

Is Serializable always implemented using locks?

No. Database engines can implement serializable behavior using different mechanisms, including locking-based approaches and techniques such as Serializable Snapshot Isolation.

Why should isolation behavior be tested during database migration?

Because identical isolation-level names do not guarantee identical transaction semantics. Differences may only become visible under concurrent production workloads.

---

Database isolation is one of those areas where a familiar name can hide very different behavior.

What is your perspective on isolation-level naming and database behavior? Share your technical perspective in the comments.