--- title: "PostgreSQL Internals - Module 3: MVCC & Transaction Isolation Levels" description: "Understand how PostgreSQL implements MVCC using xmin/xmax, snapshot isolation, and visibility rules - with all four transaction isolation levels explained." author: "Samba Siva" published: 2026-04-06T07:47:18.634Z updated: 2026-11-06T08:22:35.336Z tags: - "PostgreSQL" - "Technical" - "PGInternals" url: "https://sambasivareddy.in/blog/postgresql-internals-module-3-mvcc-transaction-isolation-levels" --- # PostgreSQL Internals - Module 3: MVCC & Transaction Isolation Levels In the previous blogs, we explored the concepts related to PostgreSQL Architecture and Storage layer. Now that we know what **_xmin, xmax, ctid and cid_** look like on disk, we use them to understand how concurrency works in PostgreSQL. This module is about the single most important question PostgreSQL asks millions of times per second: **"Is this tuple visible to this transaction?"** * * * ## Part 1: What is a Transaction ID (XID)? Every transaction in PostgreSQL that writes something gets a transaction ID, a 32-bit unsigned integer which increases monotonically. ``` -- See your current transaction's XID -- (PG 13+ name: pg_current_xact_id(); txid_current() still works) SELECT txid_current(); -- Returns 9093 -- Note: this call ASSIGNS an XID if the transaction didn't have one yet -- A real XID is only assigned when the transaction writes something -- (or calls txid_current()). Every transaction has a "virtual XID"; -- read-only ones never get a real XID. -- Run this in a fresh transaction, before any write: SELECT txid_current_if_assigned(); -- NULL - no XID assigned yet ``` ### The XID Space - 0 = Invalid TransactionID - 1 = Bootstrap TransactionID (system init) - 2 = Frozen TransactionID (always visible) - 3...232\-1 = Normal Transaction IDs, assigned sequentially - 232 \- 1 = 4,294,967,295 (~4 billion). After that the counter wraps back to 3. > **Note:** XIDs are compared modulo 2^32, so every transaction sees about 2^31 (~2.1 billion) XIDs as “in the past” and 2^31 as “in the future”. If a tuple’s xmin gets older than ~2^31 transactions, it would suddenly look like it’s in the future. This is the **Transaction Wraparound** problem, which we will discuss later in this blog. * * * ## Part 2: The Snapshot - PostgreSQL's view of NOW A snapshot records which transactions are still running at the moment it’s taken, and it is used to **make all the tuple visibility decisions** for the statements that use it. When it’s taken depends on the isolation level (Part 4): at the first statement of the transaction in `REPEATABLE READ`, at every statement in `READ COMMITTED`. ### What a Snapshot contains ``` typedef struct SnapshotData { TransactionId xmin; /* oldest XID still running */ TransactionId xmax; /* one past the highest completed XID */ TransactionId *xip; /* in-progress XIDs[] between xmin & xmax */ uint32 xcnt; /* number of in-progress XIDs */ } SnapshotData; ``` In simple terms: - xmin = any XID below this is finished (either committed or aborted). - xmax = any XID **at or above** this had not finished when the snapshot was taken. So invisible. - xip\[\] = XIDs between xmin and xmax that are **still in progress at the time of taking this snapshot**. So these are also invisible. ``` -- See the current snapshot SELECT pg_current_snapshot(); -- Returns something like: 1041:1045:1041,1043 -- xmin = 1041 (oldest still running, so it also appears in xip) -- xmax = 1045 (one past the highest completed XID) -- xip = {1041, 1043} (currently running) ``` ### Snapshot Construction - Step by step PostgreSQL builds this snapshot by scanning the **ProcArray**, the shared memory array of all active backends we covered in Module 1. `xmax = latestCompletedXid + 1`, `xmin` = the oldest XID still running, and `xip[]` = the running XIDs below `xmax`. Your own XID is skipped. ``` Transactions at snapshot time: XID 1040 — committed XID 1041 — IN PROGRESS ← goes into xip[] XID 1042 — YOUR transaction (own XID is skipped, never in xip[]) XID 1043 — committed ← latestCompletedXid XID 1044 — not started yet Snapshot result: xmin = 1041 (oldest still running) xmax = 1044 (latestCompletedXid + 1) xip = [1041] ``` * * * ## Part 3: Visibility Rules - Exact Algorithm Given a tuple with **xmin**, **xmax**, and a **snapshot**, here is the **complete visibility check** PostgreSQL runs. This will be done in the `HeapTupleSatisfiesMVCC()` in `src/backend/access/heap/heapam_visibility.c`. The function asks two questions in order: **did the insert happen, for me?** and then **did the delete happen, for me?** For each XID it checks, always in this order: is it me, is it still running per my snapshot, and only then what CLOG (`pg_xact`) says. ### Algorithm ``` FUNCTION is_visible(tuple, snapshot): ── 1. Did the insert happen, for me? (xmin) ── IF hint XMIN_INVALID: RETURN false ← inserter aborted IF hint XMIN_COMMITTED not set: IF xmin == my_xid: IF cmin >= snapshot.curcid: RETURN false ← inserted by this or a later command ELSE IF running_for_me(xmin): RETURN false ← snapshot FIRST ELSE IF clog(xmin) == COMMITTED: set hint XMIN_COMMITTED ELSE: set hint XMIN_INVALID; RETURN false ← aborted or crashed ELSE IF not frozen AND running_for_me(xmin): RETURN false ← committed, but not for my snapshot ── 2. Did the delete happen, for me? (xmax) ── IF hint XMAX_INVALID OR xmax is lock-only: RETURN true IF xmax == my_xid: RETURN cmax >= snapshot.curcid ← deleted by this/later command → still visible IF running_for_me(xmax): RETURN true IF hint XMAX_COMMITTED not set: IF clog(xmax) != COMMITTED: set hint XMAX_INVALID; RETURN true set hint XMAX_COMMITTED RETURN false ← delete committed before my snapshot FUNCTION running_for_me(xid): xid < snapshot.xmin → false xid >= snapshot.xmax → true otherwise → xid in snapshot.xip[] ``` Two things to notice: - **The snapshot is checked before CLOG.** A transaction is marked committed in CLOG slightly before it leaves the ProcArray, so checking CLOG first could show a commit that this snapshot must still treat as running. - **Your own changes are decided by command ID (**`cid`**), not commit status.** A command never sees rows it inserted itself, and still sees rows it deleted itself. This runs for **every tuple** a query touches. The hint bits are what make it fast: once set, CLOG is never consulted again for that tuple, but the snapshot check still runs. > For a line-by-line walk through the real function, see [_Reading PostgreSQL: MVCC Internals Explained from Source Code_](https://sambasivareddy.in/blog/reading-postgresql-mvcc-internals-from-snapshots-to-heaptuplesatisfiesmvcc). * * * ## Part 4: Isolation Levels PostgreSQL Isolation levels are implemented by controlling **_when the snapshot is taken_** in transactions. Different Isolation Levels: - Read Uncommitted (accepted, but behaves exactly like Read Committed in PostgreSQL) - Read Committed (Default) - Repeatable Read - Serializable ### READ COMMITTED ``` SET TRANSACTION ISOLATION LEVEL READ COMMITTED; ``` A **new snapshot is taken at the start of each statement**. That means, even in a single transaction, different statements see different snapshots, and so different data. **Example:** ``` T1: BEGIN; (XID = 1054) T1: SELECT balance FROM accounts WHERE id=1; --Sees $100(Snapshot A) T2: BEGIN; (XID = 1055) T2: UPDATE accounts SET balance=200 WHERE id=1; T2: COMMIT; (Committed XID 1055) T1: SELECT balance FROM accounts WHERE id=1; --Sees $200(Snapshot B) T1: COMMIT; ``` T1 got two different answers for the same query within one transaction. This is a **non-repeatable read**, allowed under READ COMMITTED. ### REPEATABLE READ ``` SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; ``` The snapshot is taken **once at the first statement** and held for the entire transaction. **Example:** ``` T1: BEGIN; (XID = 1056) T1: SELECT balance FROM accounts WHERE id=1;--Snapshot taken,sees $100 T2: BEGIN; (XID = 1057) T2: UPDATE accounts SET balance=200 WHERE id=1; T2: COMMIT; T1: SELECT balance FROM accounts WHERE id=1; -- Still sees $100 T1: COMMIT; ``` T1 is completely isolated from T2’s commit. **This prevents non-repeatable reads AND phantom reads**; PostgreSQL’s REPEATABLE READ is actually stronger than the SQL standard requires. One catch: if T1 tries to **update** a row that T2 changed after T1’s snapshot, T1 can’t silently overwrite it. It fails with: ``` ERROR: could not serialize access due to concurrent update ``` and the application has to retry the transaction. ### SERIALIZABLE Uses **Serializable Snapshot Isolation (SSI)**: on top of REPEATABLE READ’s snapshot, it tracks read/write dependencies between transactions to detect serialization anomalies and aborts one of the “dangerous” transactions. Much deeper than just snapshot timing. ``` SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- May raise: ERROR: could not serialize access due to read/write dependencies among transactions ``` ### Anamolies 1. Dirty Read - You read uncommitted data from another transaction 1. Not possible in any isolation level. The visibility rules are designed so that uncommitted tuples are never visible to other transactions. 2. Non-Repeatable Read - Same row returns different values due to updates in another transaction 1. Possible in READ COMMITTED 3. Phantom Read - Same query returns different rows due to inserts/deletes in another transaction 1. Possible in READ COMMITTED 4. Serialization Anomaly (e.g. write skew) - Two transactions each read data, then write based on what they read, and the combined result could not have happened if they had run one after the other 1. Possible in READ COMMITTED and REPEATABLE READ. Only SERIALIZABLE prevents it, which is why it exists. * * * ## Part 5: XID WrapAround Problem XIDs are 32-bit and compared modulo 2^32, so each transaction sees about 2^31 (~2.1 billion) XIDs as past and 2^31 as future. Once a tuple’s xmin is more than ~2^31 transactions old, it flips to looking like it’s **in the future**, and PostgreSQL would treat the row as invisible. **Rows would suddenly disappear, which is effectively data loss.** ### How PostgreSQL solves it: Freezing VACUUM marks old tuples as **frozen**. Since PostgreSQL 9.4 it does this by setting the **HEAP\_XMIN\_FROZEN** bits (`HEAP_XMIN_COMMITTED | HEAP_XMIN_INVALID`) in `t_infomask` and leaves `t_xmin` as it is. Before 9.4, it overwrote `t_xmin` with **FrozenTransactionID (2)**. Either way, frozen tuples are **always visible** to everyone and no snapshot check is needed. ``` Normal tuple: xmin=1000000 → visible only if XID 1M is finished per my snapshot Frozen tuple: xmin=1000000 + HEAP_XMIN_FROZEN → ALWAYS visible, no snapshot check ``` ### When does a tuple get frozen? - A tuple is **eligible** for freezing when its **xmin** is older than **vacuum\_freeze\_min\_age** transactions (default: 50 million). - When a table’s **age(relfrozenxid)** passes **vacuum\_freeze\_table\_age** (default: 150 million), VACUUM runs **aggressively**: it scans every page that isn’t already all-frozen. - When it passes **autovacuum\_freeze\_max\_age** (default: 200 million), autovacuum **forces** an anti-wraparound vacuum on that table, even if autovacuum is disabled. ``` -- Monitor freeze health SELECT datname, age(datfrozenxid) AS xid_age, 2^32 - age(datfrozenxid) AS xids_until_wraparound FROM pg_database ORDER BY xid_age DESC; -- Per-table freeze age SELECT relname, age(relfrozenxid) AS table_xid_age, pg_size_pretty(pg_total_relation_size(oid)) AS size FROM pg_class WHERE relkind = 'r' ORDER BY age(relfrozenxid) DESC LIMIT 10; ``` If anti-wraparound vacuums can’t keep up, PostgreSQL starts **warning** when about **40 million** XIDs are left before wraparound, and **refuses to assign new XIDs** when about **3 million** are left. It doesn’t shut down: read-only queries still work, but anything that needs a new XID fails until the database is vacuumed. This is the wraparound safety circuit. > If it hits **2^32 - 1**, PostgreSQL will refuse to accept new transactions and shut down and this we can call as the wraparound safety circuit. ### The all\_frozen VM bit When VACUUM freezes every tuple on a page, it sets the **all\_frozen** bit in the visibility map. Later aggressive VACUUMs skip that page entirely since they know there's nothing left to freeze. This is why keeping tables well-vacuumed doesn't just prevent bloat, it keeps freeze overhead manageable. * * * ## Part 6: HOT Updates - Avoiding Index Churn Normal UPDATE writes a new tuple version and requires updating **every index** that covers the table. HOT (Heap Only Tuple) avoids this when: 1. The updated columns are **not** part of any index 2. There is **free space** on the same page as the old tuple ``` CREATE TABLE orders (id int PRIMARY KEY, status text, notes text); -- index only on id INSERT INTO orders VALUES (1, 'pending', 'rush'); UPDATE orders SET notes = 'very rush'; -- HOT: notes not indexed, same page ``` What happens physically: ``` Right after the UPDATE: ItemId[1]: xmin=100, xmax=101, t_infomask2=HEAP_HOT_UPDATED, t_ctid=(0,2) ← index points here ItemId[2]: xmin=101, xmax=0, t_infomask2=HEAP_ONLY_TUPLE ← new tuple, no index entry After page pruning (once the old version is dead to everyone): ItemId[1]: lp_flags=REDIRECT → 2 ← index still points here! ItemId[2]: xmin=101, xmax=0, t_infomask2=HEAP_ONLY_TUPLE ``` The index entry **still points to ItemId\[1\]**. When an index scan reaches it, it follows the HOT chain (through `t_ctid` before pruning, through the redirect after) to ItemId\[2\], the live version. * * * ## Part 7: Hands-On Lab ``` -- Setup CREATE TABLE mvcc_demo (id int, val text); INSERT INTO mvcc_demo VALUES (1, 'original'); -- Note the XID SELECT xmin, xmax, ctid, * FROM mvcc_demo; -- xmin | xmax | ctid | id | val -- 9581 | 0 | (0,1) | 1 | original -- ── Terminal 1 ── BEGIN; SELECT txid_current(); -- XID = 9582 UPDATE mvcc_demo SET val='updated' WHERE id=1; -- Before committing, open Terminal 2 -- ── Terminal 2 (while Terminal 1 is open) ─── -- Under READ COMMITTED (default): SELECT xmin, xmax, ctid, * FROM mvcc_demo; -- xmin | xmax | ctid | id | val -- 9581 | 9582 | (0,1) | 1 | original -- xmax=9582 is in progress → old tuple still visible -- ── Terminal 1 ── COMMIT; -- ── Terminal 2 ── SELECT xmin, xmax, ctid, * FROM mvcc_demo; -- NOW sees: xmin=9582, xmax=0, val='updated' -- xmin | xmax | ctid | id | val -- 9582 | 0 | (0,2) | 1 | updated -- New snapshot taken (READ COMMITTED), sees committed data -- ── Inspect both tuple versions with pageinspect ──── SELECT lp, t_xmin, t_xmax, t_ctid, t_infomask, t_data FROM heap_page_items(get_raw_page('mvcc_demo', 0)); -- Both tuples visible here: the dead one (xmax set) and the live one -- lp | t_xmin | t_xmax | t_ctid | t_infomask | t_data -- 1 | 9581 | 9582 | (0,2) | 1282 | \x0100000013 -- 2 | 9582 | 0 | (0,2) | 10498 | \x0100000024 -- ── Watch XID age ─── SELECT age(relfrozenxid), relname FROM pg_class WHERE relname = 'mvcc_demo'; -- age | relname -- 3 | mvcc_demo -- ── Trigger a manual freeze ── VACUUM FREEZE mvcc_demo; SELECT lp, t_xmin, t_xmax, t_infomask FROM heap_page_items(get_raw_page('mvcc_demo', 0)); -- lp | t_xmin | t_xmax | t_infomask -- 1 | | | -- 2 | 9582 | 0 | 11010 ``` This is the key insight: **since PostgreSQL 9.4, freezing does not rewrite t\_xmin to 2 (FrozenTransactionId). It sets the HEAP\_XMIN\_FROZEN bits on the tuple and leaves t\_xmin intact.** That’s why you still see t\_xmin = 9582 instead of 2. You can see it in t\_infomask: it went from 10498 (`0x2902`) to 11010 (`0x2B02`). The added `0x0200` (`HEAP_XMIN_INVALID`), together with the existing `0x0100` (`HEAP_XMIN_COMMITTED`), is `HEAP_XMIN_FROZEN` (`0x0300`). On 9.3 and older, you would see t\_xmin set to 2. * * * ## Part 8: Conclusion In this module, we explored how MVCC and transaction isolation levels work in PostgreSQL, covering: 1. What a transaction ID (XID) is 2. How a snapshot is built and how it decides the visibility of a tuple 3. The isolation levels, what each one allows, and why SERIALIZABLE exists 4. XID wraparound and how freezing prevents it 5. HOT updates and how they avoid index churn Finally, we got our hands dirty with a lab to see all of this on a real page. * * * ## What's Next **→** Module 4: WAL, Checkpoints and Crash Recovery You now understand exactly how PostgreSQL decides what you can see. Module 4 goes into **WAL (Write-Ahead Logging)**, the mechanism that makes every write durable, enables crash recovery, and powers replication. We'll cover the WAL record format, LSNs, checkpoints, and what happens byte-by-byte when you COMMIT.