MVCC mental model in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does MVCC handle concurrent reads and writes?

In PostgreSQL's MVCC system, what happens when one transaction reads a row that another transaction is currently updating but not yet committed?

A The reading transaction sees the old committed version of the row, ignoring the uncommitted changes.

B The reading transaction sees the uncommitted changes immediately.

C The reading transaction gets an error because the row is locked.

D The reading transaction waits until the writing transaction commits or rolls back before reading the row.

💡 Hint

Think about how MVCC provides a consistent snapshot for readers.

MVCC allows readers to see the last committed version of data, so they do not block or get blocked by writers. Uncommitted changes are invisible to other transactions.

❓ query_result

intermediate

Result of concurrent transactions with MVCC

Consider two transactions in PostgreSQL:

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Transaction 2
BEGIN;
SELECT balance FROM accounts WHERE id = 1;

What balance does Transaction 2 see if Transaction 1 has not committed yet?

A The balance after subtracting 100 (uncommitted update).

B An error due to concurrent update.

C NULL because the row is locked.

D The balance before subtracting 100 (last committed value).

💡 Hint

Remember MVCC shows a consistent snapshot to readers.

Transaction 2 sees the last committed balance because Transaction 1's update is not committed yet.

📝 Syntax

advanced

Identify the MVCC-related error in this transaction

Which option shows a transaction that will cause a serialization failure error due to MVCC in PostgreSQL?

-- Transaction 1
BEGIN;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
COMMIT;

A Two concurrent transactions both read and update the same row without locking, causing serialization failure.

B A transaction that updates different rows, so no serialization failure occurs.

C A transaction that only reads data without updates, so no serialization failure occurs.

D A transaction that uses explicit locks to prevent serialization failure.

💡 Hint

Think about what causes serialization failures in MVCC.

When two transactions read and update the same row concurrently without proper locking, PostgreSQL may raise a serialization failure to maintain consistency.

❓ optimization

advanced

Optimizing MVCC vacuuming in PostgreSQL

Which approach best helps reduce bloat caused by MVCC dead tuples in PostgreSQL?

A Disabling autovacuum and manually running VACUUM FULL weekly.

B Increasing autovacuum frequency and tuning vacuum cost parameters.

C Using only DELETE statements without UPDATEs to avoid dead tuples.

D Increasing transaction isolation level to SERIALIZABLE.

💡 Hint

Think about how PostgreSQL cleans up old row versions.

Autovacuum automatically removes dead tuples created by MVCC. Increasing its frequency and tuning cost parameters helps keep tables lean.

🔧 Debug

expert

Diagnosing phantom reads under MVCC

In PostgreSQL, a transaction running at REPEATABLE READ isolation sees phantom rows after another transaction inserts new rows and commits. Why does this happen?

A Phantom reads occur because the first transaction did not commit before the second started.

B MVCC blocks all phantom reads by locking all rows in the table.

C REPEATABLE READ in PostgreSQL does not prevent phantom reads; only SERIALIZABLE does.

D Phantom reads are impossible in PostgreSQL due to MVCC.

💡 Hint

Consider the difference between REPEATABLE READ and SERIALIZABLE isolation levels.

PostgreSQL's REPEATABLE READ prevents non-repeatable reads but allows phantom reads. Only SERIALIZABLE isolation prevents phantoms by stricter locking and serialization.