In this lesson, you will learn how a database engine uses metadata to decide whether a specific session is allowed to "see" a particular version of a row.
The Problem of Perspective
In Multi-Version Concurrency Control (MVCC), a table doesn't just hold the current value of a row; it holds a history of versions. When your query runs, the database engine must filter this history. If it shows you a version that hasn't been committed yet, you get a "dirty read." If it shows you a version that was updated after your transaction started, you lose consistency.
The Visibility Logic
To maintain the illusion that your transaction is the only one running, the engine tags every row version with two hidden system columns:
xmin: The ID of the transaction that created this version.xmax: The ID of the transaction that deleted or superseded this version.
When your transaction (let's call it Tx_100) performs a SELECT query, the engine applies a "Visibility Rule" to every row it encounters:
-- A row is visible to Tx_100 if:
-- 1. The creator (xmin) is already committed.
-- 2. The creator (xmin) is NOT Tx_100 (unless we created it ourselves).
-- 3. The version has not been deleted (xmax is empty or belongs to an uncommitted transaction).
A Concrete Illustration
Imagine Tx_100 starts. Meanwhile, Tx_101 updates a balance from 20.
- Version A (Old):
xmin: 50,xmax: 101 - Version B (New):
xmin: 101,xmax: NULL
When Tx_100 runs its query, it checks the metadata. It sees Version B has an xmin of 101. Since 101 is greater than 100 (or perhaps it’s still active), the engine knows Tx_100 must ignore this new version. It then looks at Version A. Because Tx_100 started before 101 committed, the engine follows the visibility rules to ignore the deletion metadata, effectively "reconstructing" the row as it looked when Tx_100 began.
By comparing your transaction ID against the xmin and xmax of every row, the database ensures you only interact with a consistent snapshot of the data.
Key Takeaway: Visibility rules allow the engine to provide transaction isolation by comparing your transaction ID against the hidden "birth" and "death" timestamps of every row version.