MVCC mental model in PostgreSQL - Step-by-Step Execution

Concept Flow - MVCC mental model in PostgreSQL

Transaction Start

↓

Read Snapshot of Data

↓

Perform Reads/Writes

↓

Create New Versions for Writes

↓

Commit or Rollback

↓

Old Versions Visible to Other Transactions Until Cleanup

This flow shows how a transaction in PostgreSQL reads a snapshot of data, creates new versions for writes, and commits or rolls back, while old versions remain visible to other transactions until cleaned up.

Execution Sample

PostgreSQL

BEGIN;
SELECT * FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
COMMIT;

A transaction reads an account balance, updates it by adding 100, and commits the change.

Execution Table

Step	Action	Data Version Read	New Version Created	Visibility to Other Transactions
1	BEGIN transaction	Snapshot of current committed data	No	No changes yet
2	SELECT balance WHERE id=1	Version visible at transaction start	No	No changes yet
3	UPDATE balance + 100 WHERE id=1	Reads old version	Creates new version with updated balance	New version visible only to this transaction
4	COMMIT transaction	N/A	New version becomes committed	New version visible to all new transactions
5	Other transactions still see old version until cleanup	Old version remains until vacuum	No	Old version visible to other active transactions

💡 Transaction commits, new version becomes visible; old versions remain until cleanup.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
balance_version	v1 (old)	v1 (read)	v2 (new created)	v2 (committed)	v2 (final visible)
transaction_state	idle	active	active	committed	committed

Key Moments - 3 Insights

Why does the transaction read the old version of data even after an update?

Because PostgreSQL uses MVCC, the update creates a new version invisible to other transactions until commit. The current transaction sees its own new version, but others still see the old version until cleanup. See execution_table step 3.

When do other transactions see the updated data?

Other transactions see the new version only after the committing transaction finishes and commits. Before that, they see the old version. See execution_table step 4.

What happens to old versions after commit?

Old versions remain in the database until a cleanup process (vacuum) removes them. This allows other transactions that started earlier to still see consistent data. See execution_table step 5.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the new data version created?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Check the 'New Version Created' column in execution_table rows.

According to variable_tracker, what is the transaction_state after the COMMIT?

A idle

B committed

C active

D rolled back

💡 Hint

Look at the 'transaction_state' row after Step 4 in variable_tracker.

When do other transactions stop seeing the old data version?

A Immediately after UPDATE

B After BEGIN

C After COMMIT and cleanup

D Before COMMIT

💡 Hint

Refer to execution_table steps 4 and 5 about visibility and cleanup.

Concept Snapshot

MVCC in PostgreSQL:
- Each transaction reads a snapshot of data.
- Writes create new versions, not overwrite.
- New versions visible only after commit.
- Old versions kept for other transactions until vacuum.
- Ensures consistent reads without locking.

Full Transcript

In PostgreSQL, MVCC means each transaction works with a snapshot of the database. When a transaction starts, it sees the data as it was at that moment. If it updates data, PostgreSQL creates a new version of that data row instead of changing the old one. This new version is only visible to the transaction that made the change until it commits. Other transactions continue to see the old version until the first transaction commits and the old versions are cleaned up later. This system allows many transactions to work at the same time without blocking each other, keeping data consistent and safe.

Practice

1. What does MVCC in PostgreSQL primarily allow multiple users to do?

easy

A. Delete data instantly without backups

B. Run only one transaction at a time

C. Work with data simultaneously without waiting for locks

D. Automatically create database indexes

Solution

1. Step 1: Understand MVCC purpose

   MVCC stands for Multi-Version Concurrency Control, which allows multiple users to access data concurrently.

2. Step 2: Identify MVCC effect in PostgreSQL

   It lets users work without waiting for locks by providing each transaction a snapshot of data.

3. Final Answer:

   Work with data simultaneously without waiting for locks -> Option C

4. Quick Check:

   MVCC = concurrent access without waiting [OK]

Hint: MVCC means no waiting for others' data changes [OK]

Common Mistakes:

• Thinking MVCC locks data exclusively
• Believing MVCC deletes old data immediately
• Assuming only one transaction runs at a time

2. Which SQL statement correctly starts a transaction in PostgreSQL to use MVCC?

easy

A. BEGIN;

B. START;

C. BEGINNING TRANSACTION;

D. OPEN TRANSACTION;

Solution

1. Step 1: Recall PostgreSQL transaction syntax

   PostgreSQL uses BEGIN; to start a transaction.

2. Step 2: Compare options

   Only A is valid syntax. B and C use incorrect keywords, D is invalid.

3. Final Answer:

   BEGIN; -> Option A

4. Quick Check:

   PostgreSQL transaction start = BEGIN; [OK]

Hint: Use BEGIN; to start transactions in PostgreSQL [OK]

Common Mistakes:

• Using START; which is invalid syntax
• Typing OPEN TRANSACTION; which is invalid
• Confusing transaction start with commit or rollback

3. Consider this sequence in PostgreSQL:
BEGIN;
SELECT * FROM products WHERE id = 1;
UPDATE products SET price = 20 WHERE id = 1;
COMMIT;
What does the SELECT see if another transaction updated the same row before this transaction started?

medium

A. An error due to concurrent update

B. The new price updated by the other transaction

C. No rows returned because of the update

D. The old price before the other transaction's update

Solution

1. Step 1: Understand snapshot isolation in MVCC

   The SELECT sees data as it was at transaction start, ignoring later committed changes.

2. Step 2: Apply to given scenario

   Since another transaction updated before this one started, the snapshot at start excludes that committed update, so SELECT sees the old price.

3. Final Answer:

   The old price before the other transaction's update -> Option D

4. Quick Check:

   MVCC snapshot = data at tx start [OK]

Hint: SELECT sees snapshot at transaction start, not later changes [OK]

Common Mistakes:

• Expecting an error due to concurrent update
• Thinking SELECT sees the old price before the other transaction's update
• Thinking no rows returned because of the update

4. You run this code:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;
ROLLBACK;
Why might the SELECT show the updated balance even though the transaction is not committed?

medium

A. Because the transaction sees its own changes inside the transaction

B. Because ROLLBACK commits the changes automatically

C. Because SELECT ignores transaction boundaries

D. Because balance is cached outside the database

Solution

1. Step 1: Understand visibility of changes inside a transaction

   Within a transaction, you see your own uncommitted changes.

2. Step 2: Explain why SELECT shows updated balance

   Even before commit, SELECT sees the updated balance because it's in the same transaction.

3. Final Answer:

   Because the transaction sees its own changes inside the transaction -> Option A

4. Quick Check:

   Transaction sees own changes before commit [OK]

Hint: Inside transaction, you see your own updates [OK]

Common Mistakes:

• Thinking ROLLBACK commits changes
• Believing SELECT ignores transaction state
• Assuming external cache affects SELECT results

5. In PostgreSQL, if two transactions try to update the same row simultaneously, what happens to maintain MVCC consistency?

hard

A. Both transactions update the row and overwrite each other

B. One transaction waits or fails due to a lock conflict

C. PostgreSQL merges both updates automatically

D. The second transaction reads old data but commits anyway

Solution

1. Step 1: Understand MVCC row update behavior

   PostgreSQL uses row-level locks to prevent conflicting updates.

2. Step 2: Explain conflict resolution

   When two transactions update the same row, one waits or fails to keep data consistent.

3. Final Answer:

   One transaction waits or fails due to a lock conflict -> Option B

4. Quick Check:

   Concurrent updates cause lock wait or failure [OK]

Hint: Concurrent updates cause lock wait or error [OK]

Common Mistakes:

• Assuming updates merge automatically
• Believing both updates overwrite without conflict
• Thinking second transaction commits ignoring locks