BiteSizedChunks.comLearn one small thing at a time.
Course contentsShow
← Databases & SQL
Lesson 752 of 1,9537. Transaction ManagementFree lesson

Isolation Level Mapping

Map high-level SQL commands to internal MVCC engine operations.

In this lesson, you will learn how the SQL isolation levels you request via commands are mapped to the underlying internal Multi-Version Concurrency Control (MVCC) mechanics.

When you execute a command like SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, you are essentially instructing the database engine on how to construct a "snapshot" of the data and how strictly to enforce visibility rules. The SQL standard defines the what (the isolation level), but your database engine handles the how (the MVCC operations).

The Mapping Logic

The database engine translates your isolation level into a specific configuration of the Snapshot Generation Logic:

  • Read Committed: The engine generates a new snapshot at the beginning of each individual statement within your transaction. This ensures you see the latest committed data for every query, but the view can change if another transaction commits mid-process.
  • Repeatable Read / Snapshot Isolation: The engine generates a single snapshot at the start of the transaction. This snapshot is then used by every subsequent statement. Because the engine checks the transaction IDs of every row version against this initial snapshot, it guarantees that you see a consistent, frozen view of the database, regardless of external updates.

Internal Visibility Checks

Consider a simple SELECT query. Behind the scenes, the MVCC engine maps your isolation level to a visibility filter:

-- Your command:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM orders;

When this runs, the engine applies the Visibility Rules:

  1. Is the row's xmin (creator ID) committed?
  2. Was the row created before the start of your specific transaction snapshot?
  3. Has the row been deleted or updated by a transaction that committed before your snapshot started?

If you were in Read Committed, the engine would simply fetch a "fresh" snapshot for this query. Because you are in Repeatable Read, it ignores any row versions created after your transaction began, even if they have been committed by others.

By mapping SQL commands to these versioning rules, the database provides the illusion of isolation while allowing multiple transactions to work on the same rows simultaneously without interfering with one another.

Key Takeaway: SQL isolation levels serve as instructions that dictate whether the MVCC engine generates a new snapshot for every statement or persists one snapshot for the entire duration of a transaction.