Transaction IDs and Hidden Columns
Every transaction in PostgreSQL gets a unique, monotonically increasing Transaction ID (XID). Every row version in the database has two hidden system columns that track its lifecycle:
xmin: The XID of the transaction that created (inserted or updated-to) this row version.xmax: The XID of the transaction that deleted or updated-away this row version. Zero if the row is still current.
You can actually query these hidden columns directly (SELECT xmin, xmax, id, val FROM mvcc_demo), which for a freshly inserted row returns something like:
xmin | xmax | id | val
------+------+----+----------
742 | 0 | 1 | original
The row was created by transaction 742, and xmax=0 means it hasn't been deleted.
Taking a Snapshot
When your transaction starts, the database takes a snapshot of the current state. The snapshot records:
- The current XID (your transaction's ID).
- The list of all XIDs that are currently in-progress (not yet committed or rolled back).
The visibility rule for any row version is:
"I can see this row version if its
xmintransaction committed before my snapshot was taken, AND eitherxmaxis zero, or thexmaxtransaction had not yet committed when my snapshot was taken."
In plain English: you see rows that existed and were committed when you started, and you don't see rows that were deleted or replaced before you started.
A Concrete MVCC Scenario
| Time | Transaction A (XID 100) | Transaction B (XID 101) | Row versions in heap |
|---|---|---|---|
| T1 | BEGIN | {xmin=99, xmax=0, val='original'} |
|
| T2 | SELECT val → sees 'original' |
BEGIN | |
| T3 | UPDATE val='updated' |
{xmin=99, xmax=101, val='original'} + {xmin=101, xmax=0, val='updated'} |
|
| T4 | COMMIT | ||
| T5 | SELECT val → still sees 'original' |
(B committed, but A's snapshot predates it) | |
| T6 | COMMIT |
Transaction A consistently sees 'original' throughout its lifetime, even after B commits. This is Snapshot Isolation — A operates in a consistent bubble of time.