Module Review: Concurrency

[!NOTE] Concurrency in databases is fundamentally difficult because it requires balancing high throughput (allowing many users to read and write simultaneously) with strict safety guarantees (ACID properties). In this module, we reviewed the core mechanisms Postgres uses to achieve this balance. We explored how Multi-Version Concurrency Control (MVCC) enables lock-free reads, why different Isolation Levels allow or prevent specific transaction anomalies, and how explicit locking mechanisms provide granular control when automatic resolution isn’t enough.

1. Key Takeaways

  • Readers Don’t Block Writers: Thanks to MVCC, reading a table never blocks someone else from updating it.
  • Update = Delete + Insert: Updating a row in Postgres creates a new version of the tuple. The old version (xmax set) stays until VACUUM runs.
  • Isolation Matters:
  • Read Committed (Default) is fine for most things but allows anomalies like Non-Repeatable Reads.
  • Serializable is the safest but requires retry logic for serialization failures.
  • Locking Hierarchy: Row locks are cheap and automatic. Table locks are rare but heavy. Deadlocks happen when two transactions wait for each other.

2. Interactive Flashcards

Test your knowledge of the core concepts.

Question

Loading...

(Click to Flip)

Answer

1 / 5

3. Cheat Sheet

Isolation Levels

Level Dirty Read Non-Repeatable Phantom Write Skew
Read Committed ❌ ✅ ✅ ✅
Repeatable Read ❌ ❌ ❌ ✅
Serializable ❌ ❌ ❌ ❌

Lock Compatibility

  • ACCESS SHARE (SELECT) conflicts with ACCESS EXCLUSIVE (DROP/TRUNCATE).
  • ROW EXCLUSIVE (UPDATE/DELETE) conflicts with SHARE (CREATE INDEX).
  • SHARE UPDATE EXCLUSIVE (VACUUM) conflicts with itself.

Key Commands

-- Check active locks
SELECT * FROM pg_locks;

-- Kill a stuck query (dangerous!)
SELECT pg_terminate_backend(pid);

-- Manual Lock
LOCK TABLE accounts IN ACCESS EXCLUSIVE MODE;

-- Row Lock
SELECT * FROM jobs FOR UPDATE SKIP LOCKED;

4. Next Steps

You’ve mastered concurrency! Now let’s move on to scaling Postgres across multiple servers.