MVCC postgres decoded

Under the hood, PostgreSQL uses Multi-Version Concurrency Control (MVCC). MVCC is a system designed to solve a massive problem in highly concurrent databases: How do we let hundreds of users read data at the exact same time someone else is updating it, without anyone crashing or getting corrupted, half-written data?

Older databases solved this using brute-force locks: If User A was updating a table, User B had to wait in line just to read it. MVCC solves this elegantly with a simple philosophy:

“Readers never block writers, and writers never block readers.”

Let’s understand the basics first, Every tuple (row) in a table internally can have multiple versions. we don’t see these versions directly as a client, but internally postgres maintains them. Postgres doesn’t overwrite the existing bits on the disk; it marks the old version as “expired” and inserts a brand-new version.

Row 1 can have multiple versions ( some could be dead, some in progress, some are committed)

Dead versions of tuples are periodically cleaned up postgres using “VACCUM” (this is out of scope for this article). Hold on this thought, we will come back how this MVCC pans out.

Note: Transaction ids generated in postgres are numeric in nature and are sequential.

Isolation Levels

Before we dive into the code, we need to understand how Postgres isolates transactions. Here is the one-line summary of the three main isolation levels:

• Read Committed (Default): You always see the latest committed data at the exact moment your current query starts.
• Repeatable Read: You see a frozen snapshot of the database from the moment your transaction ran its first query.
• Serializable: Same as Repeatable Read, but the database will mathematically guarantee your transactions behave as if they ran sequentially one after another.

1. The Setup: Our Linked List

Let’s set up our scenario. We have a simple table to track user posts:

CREATE TABLE users (
    user_id VARCHAR(50),
    posts INT
);

Lets imagine for each tuple, we have linked list of versions.

Each “Node” (row version) has three hidden pieces of metadata:

• **xmin (The Creator):** The ID of the transaction that inserted this node.
• **xmax (The Mutator):** The ID of the transaction that updated/deleted this node. If xmax is 0, this node is the end of the list (it is "Live").
• **nxtNodPtr (The Address):** The physical pointer to the next node on disk.

2. Building snapshot

Before a transaction executes, Postgres builds a Snapshot object. Think of this as a “lens” that filters out version history, showing the transaction only the data it is legally allowed to see.

• xMin: The oldest currently running transaction.
• xMax: The next available transaction ID.
• xIds: An array of all transactions currently "in-flight" (uncommitted).

Pro Tip: Postgres is lazy! If you type BEGIN;, Postgres does not instantly build a snapshot. It waits to take that snapshot until you fire your first actual data-touching query.

Scenario: Happy Path

Let’s look at the “Happy Path.”

1. T1: INSERT INTO users VALUES ('abc', 100); and commits.
2. T2: UPDATE users SET posts=posts+50 WHERE user_id='abc'; and commits.

Notice how the state of linked list changes

Observation

notice how mutator of node 1 is t2, mutator of node 2 is 0 because no other txn is pending

Here t2 is able to see the changes of t1 (read committed isolation level) and updates the value to 150 . Before starting the work, t2 marks itself as mutator for t1's row and creates a new node with t2 as creator, 0 as mutator.

Scenario 2: Sequential Updates

1. T1: INSERT INTO users VALUES ('abc', 100); and commits.
2. T2: BEGIN UPDATE posts=posts+50 WHERE user_id = ‘abc’ COMMIT;
3. T3: BEGIN UPDATE posts=posts+50 WHERE user_id = ‘abc’ COMMIT;

t1, t2 commits sequentially without conflicts, t3 starts after them

ow, let’s add T3 (Transaction 102) doing another update: UPDATE users SET posts=posts+50 WHERE user_id='abc';.

Because T2 already committed, this is easy. Here is the algorithmic walkthrough of T3:

1. Build Snapshot: T3 takes a snapshot.

state of list before insertion

1. Iterate List: T3 finds Node 1. It sees xmax is T2. Since T2 committed before T3 started, Node 1 is dead history.
2. Follow the Pointer: T3 jumps to Node 2. It sees xmax is 0. This is the live truth!
3. Update: T3 calculates 150 + 50 = 200. It sets Node 2's xmax to T3, and inserts Node 3.

Post insertion of node 3

Notice how we are iterating the versions nodes. But what happens if there is conflict or contention

// Step 1: Build Snapshot
let snapshot = buildSnapshot();

// Step 2: Find the initial visible node (Node 1 with value 100)
let node = findInitialVisibleNode('abc', snapshot);

// Loop
while (node != null) {

    // CASE A: The node is completely free
    if (node.xMax === 0) {
        lockAndPerformUpdate(node);
        return "Success";
    }

    // CASE B: Someone else has touched this node
    let lockOwnerStatus = getTransactionStatus(node.xMax); // node.xMax is T2

    if (lockOwnerStatus === "IN_PROGRESS") {
        ...
    } 

    else if (lockOwnerStatus === "COMMITTED") {
        // we jump nodes as long as we have clean commit history
        node = node.next;
    } 

}

Scenario 2: The Concurrent Collision (The Wait)

• T1 inserts ('abc', 100) and commits.
• T2 runs UPDATE posts=posts+50. But it does NOT commit yet.
• T3 runs UPDATE posts=posts+50 concurrently.

Let’s assume we are working with read committed isolation level, meaning a transaction will always see the latest value committed by another transaction even within it’s transaction.

when t3 has started out, t2 already acquired the exclusive row lock and performing a long operation.

Node creation is actually independent of transaction status.

ProTip ! T2 has not yet committed, but it doesn’t mean we will not have version for this, Node creation is the first step that happens and subsequently that transaction either succeeds or fails

Since T2 already has exclusive row lock, T3 goes to sleep and will be added to the waiting group. When T2 finishes T3 will be signalled by database to continue.

Below snippet constitutes the whole behavior.

// Step 1: Build Snapshot (T2 is currently uncommitted!)
let snapshot = buildSnapshot();

// Step 2: Find the initial visible node (Node 1 with value 100)
let node = findInitialVisibleNode('abc', snapshot);

// Step 3: The Engine Loop
while (node != null) {

    // CASE A: The node is completely free
    if (node.xMax === 0) {
        lockAndPerformUpdate(node);
        return "Success";
    }

    // CASE B: Someone else has touched this node
    let lockOwnerStatus = getTransactionStatus(node.xMax); // node.xMax is T2

    if (lockOwnerStatus === "IN_PROGRESS") {
        // T2 is actively modifying this node! 
        // We cannot touch it. We must go to SLEEP.
        console.log(`Node locked by ${node.xMax}. Sleeping...`);
        addToWaitQueueAndWaitFor(node.xMax); 

        // --- T3 WAKES UP ---
        // T2 just committed. T3 wakes up. 
        // We just loop back to the top to re-evaluate the exact same node!
        continue; 
    } 

    else if (lockOwnerStatus === "COMMITTED") {
        // We get here after waking up! T2 is now officially done.

        // THE JUMP: Because we are an UPDATE in Read Committed mode, 
        // we are allowed to ignore our snapshot and jump to T2's new node!
        node = node.next; 

        // THE RE-EVALUATION: Does this new node still match our WHERE clause?
        if (node.user_id === 'abc') {
            // Yes! Calculate using T2's NEW value (150 + 50 = 200)
            continue; // Loop runs again, hits CASE A on the new node, and updates!
        } else {
            return "Update Skipped";
        }
    } 

    else if (lockOwnerStatus === "ROLLED_BACK") {
        // T2 crashed. We pretend they never existed and overwrite their xMax.
        lockAndPerformUpdate(node);
        return "Success";
    }
}

Repeatable Read / Serializable Isolation level

Everything we just discussed was for the Read Committed isolation level, which is perfectly happy to let your transaction “discover” new data mid-flight.

But what if you explicitly set your isolation level to Repeatable Read?

In Repeatable Read, the database promises to freeze time. You are strictly forbidden from seeing any changes that happened after your snapshot was built.

If we run Scenario 3 in Repeatable Read, T3 still goes to sleep. But when T3 wakes up and sees that T2 committed, the logic changes:

else if (lockOwnerStatus === "COMMITTED") {

        if (isolationLevel === "REPEATABLE_READ") {
            // T2 committed after our snapshot was taken. 
            // We cannot legally see T2's new node, but we also can't overwrite it!
            throw new Error("ERROR: could not serialize access due to concurrent update");
        }

        // ... (Read Committed logic skips this and jumps to node.next) ...
    }

Instead of gracefully following the linked list, Postgres violently aborts T3. Because the database refuses to break its promise of “frozen time” and refuses to cause a lost update, its only logical choice is to blow up the transaction and force your application code to retry.

Conclusion

PostgreSQL’s MVCC might seem like black magic, but at its core, it’s just a remarkably robust implementation of a Linked List.

• Snapshots filter what you can see.
• The while loop handles the wait queue so writers don't step on each other.
• The CTID Pointers (node.next) allow transactions to safely hand off updates to one another without losing data.

Next time you type UPDATE, remember the journey your query takes—scanning the list, checking the locks, and appending its truth to the end of the chain.