Module Review: Concurrency

[!NOTE] This module explores the core principles of Module Review: Concurrency, deriving solutions from first principles and hardware constraints to build world-class, production-ready expertise.

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.