MVCC mental model in PostgreSQL - Mini Project: Build & Apply

Understanding MVCC Mental Model in PostgreSQL

📖 Scenario: You are managing a small library database where multiple users can borrow and return books at the same time. To keep track of book availability without conflicts, PostgreSQL uses a system called MVCC (Multi-Version Concurrency Control).This project will help you understand how MVCC works by creating a simple table and simulating transactions that show how PostgreSQL handles concurrent data access.

🎯 Goal: Build a simple PostgreSQL table for books and simulate transactions to see how MVCC manages data versions and visibility.

📋 What You'll Learn

Create a table named books with columns id (integer primary key) and title (text).

Insert three specific book records into the books table.

Set a transaction isolation level to READ COMMITTED to observe MVCC behavior.

Write a query to select all books visible in the current transaction.

💡 Why This Matters

🌍 Real World

MVCC is used in real-world databases to allow many users to read and write data at the same time without conflicts or locking delays.

💼 Career

Understanding MVCC helps database administrators and developers optimize performance and avoid data anomalies in multi-user applications.

1

Create the books table

Write a SQL statement to create a table called books with two columns: id as an integer primary key and title as text.

PostgreSQL

-- Your code here to create the books table

Hint

Use CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT); to create the table.

2

Insert initial book records

Insert three books into the books table with these exact entries: (1, 'The Hobbit'), (2, '1984'), and (3, 'Pride and Prejudice').

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);
-- Insert the three book records here

Hint

Use a single INSERT INTO books (id, title) VALUES statement with all three rows.

3

Set transaction isolation level

Write a SQL command to set the transaction isolation level to READ COMMITTED to observe MVCC behavior in PostgreSQL.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Hobbit'),
(2, '1984'),
(3, 'Pride and Prejudice');

-- Set transaction isolation level here

Hint

Use SET TRANSACTION ISOLATION LEVEL READ COMMITTED; to set the isolation level.

4

Select visible books in current transaction

Write a SQL query to select all columns from the books table to see which rows are visible in the current transaction.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO books (id, title) VALUES
(1, 'The Hobbit'),
(2, '1984'),
(3, 'Pride and Prejudice');

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Select all books visible in this transaction

Hint

Use SELECT * FROM books; to see all visible rows.

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