In this lesson, you will learn how databases physically store multiple versions of a single record so that different transactions can view data as it existed at different points in time.
The Anatomy of a Row Version
To support Multi-Version Concurrency Control (MVCC), a database cannot simply overwrite a record when it is updated. If it did, a concurrent transaction would lose the "previous" version it needs to maintain a consistent snapshot. Instead, the database uses Row Versions.
Every row in your table includes a hidden row header containing metadata that allows the engine to track history. The two most critical pieces of data in this header are:
xmin(Transaction ID): A unique identifier for the transaction that created (inserted) this specific version of the row.xmax(Transaction ID): An identifier for the transaction that deleted or updated this row, effectively "closing" this version.
Pointers and Chains
When you update a row, the database doesn’t just modify the existing bytes. It marks the old version's xmax with the current Transaction ID (making it invisible to future queries) and creates a brand-new row version with a new xmin.
Think of it like a linked list of history:
Row v1: [xmin: 100, xmax: 101] (The original version)
Row v2: [xmin: 101, xmax: null] (The current version)
In this example, Transaction 100 created the data. Transaction 101 updated it, which simultaneously marked the first version as expired (xmax: 101) and created a new version (xmin: 101).
The Visibility Check
When a query runs, the database engine examines these headers. It asks a simple question for every version it finds: "Is the xmin committed, and is the xmax either empty or from a transaction that hasn't committed yet?"
If the answer is yes, the query includes that row in its result set. This mechanism allows a long-running report to ignore the "current" reality of the table and look back at the version that was active when the report started, ensuring a perfectly consistent view without blocking other writers.
Key Takeaway: Row versions use hidden transaction IDs (xmin/xmax) in the row header to act as time-stamps, allowing the database to maintain a chain of history and provide consistent snapshots to concurrent transactions.