MVCC and Optimistic/Pessimistic Locking

1. Introduction

Multi-Version Concurrency Control (MVCC) is PostgreSQL's core strategy for handling concurrent access without blocking readers. Instead of locking rows for reads, PostgreSQL keeps multiple versions of each row (tuples) and uses transaction snapshots to determine which version each transaction can see. This eliminates the classic "readers block writers" problem found in lock-based databases. Optimistic locking assumes conflicts are rare and checks at commit time; pessimistic locking acquires locks upfront to prevent conflicts.

2. Why It Matters

MVCC is why PostgreSQL can serve thousands of concurrent read queries without any locking overhead. Understanding it helps you diagnose table bloat (dead tuples), tune autovacuum, choose between optimistic and pessimistic locking in your application code, and avoid surprises like "tuple concurrently updated" errors.

3. Real-World Analogy

MVCC is like a library that keeps photocopies of every edition of a book. When you check out a book (start a transaction), you get the latest edition at that moment. Others can add new editions (commits), but you keep reading your copy. Old editions aren't destroyed until all current readers have returned their copies (VACUUM cleans up dead tuples).

4. How It Works

Every row in PostgreSQL has hidden system columns:

• xmin: The XID of the transaction that created this tuple version.
• xmax: The XID of the transaction that deleted or updated this tuple (0 if still current).
• cmin/cmax: Command IDs within the transaction for statement-level ordering.

When a transaction reads a row, PostgreSQL applies visibility rules: a tuple is visible if its xmin committed before the snapshot was taken AND its xmax is 0 or the deleting transaction hasn't committed yet. On UPDATE, PostgreSQL creates a new tuple version (new xmin) and sets xmax on the old version. Both versions coexist until VACUUM removes the old one.

5. Internal Architecture

Optimistic locking in application code: add a version column, include it in the WHERE clause of UPDATE, and check if any rows were affected. If not, retry. Pessimistic locking: use SELECT ... FOR UPDATE to lock rows before modifying. MVCC internally uses HOT (Heap-Only Tuple) updates when the updated columns aren't indexed — the new tuple is stored on the same page, avoiding index updates.

6. Visual Explanation

The diagram shows a single row with multiple tuple versions (v1, v2, v3), each with xmin/xmax values, and how two concurrent transactions with different snapshots see different versions of the same row.

7. Practical Example

8. Common Mistakes

• Not tuning autovacuum for high-update tables: Dead tuples accumulate, bloating tables and slowing sequential scans. Set autovacuum_vacuum_scale_factor = 0.01 for large, active tables.
• Long-running read transactions: A transaction that stays open for hours prevents VACUUM from cleaning up old tuple versions, causing bloat.
• Using pessimistic locking everywhere: Reduces concurrency. Use optimistic locking for low-contention scenarios and pessimistic only when conflicts are likely.
• Forgetting HOT update requirements: Updating indexed columns prevents HOT, causing unnecessary index bloat. Consider which columns to index carefully.

9. Quick Quiz

10. Scenario-Based Challenge

An e-commerce product page gets 10,000 reads/second but only 10 updates/second (stock changes). Design the concurrency strategy: should reads use FOR UPDATE? What happens to table size if VACUUM doesn't run for 24 hours with 864,000 updates? Calculate the dead tuple count and propose an autovacuum configuration.

11. Debugging Exercise

12. Interview Questions

• Q: What is the trade-off between optimistic and pessimistic locking?
  A: Optimistic locking (version column) is better for low-conflict scenarios — no lock overhead, but requires retry logic. Pessimistic locking (FOR UPDATE) is better for high-conflict scenarios — prevents wasted work but reduces concurrency.
• Q: Why does MVCC cause table bloat?
  A: Every UPDATE creates a new tuple version while keeping the old one for concurrent readers. VACUUM removes dead tuples, but long-running transactions prevent cleanup. High-update tables need aggressive autovacuum tuning.

13. Production Considerations

• old_snapshot_threshold: In PG 9.6+, this setting forces a snapshot timeout, allowing VACUUM to clean up even with long-running transactions (at the cost of "snapshot too old" errors).
• fillfactor: Set fillfactor = 80 on high-update tables to leave 20% free space per page for HOT updates.
• VACUUM FULL vs pg_repack: VACUUM FULL rewrites the table but takes ACCESS EXCLUSIVE. Use pg_repack for online table reorganization without blocking.
• pg_stat_user_tables: Monitor n_dead_tup / n_live_tup ratio. Alert when it exceeds 20%.