Chapter 19: Locks, MVCC, and Concurrency Control

How do databases let multiple transactions run simultaneously without corrupting data? Two main approaches: locking and MVCC (Multi-Version Concurrency Control).

Pessimistic Locking

Lock the data before accessing it. Others wait until you release.

-- Exclusive lock: SELECT ... FOR UPDATE
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- locks this row
-- Other transactions trying to UPDATE/SELECT FOR UPDATE this row will WAIT
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- lock released

Lock Types

Lock	Allows	Blocks	Acquired By
Shared (S)	Other reads	Writes	SELECT ... FOR SHARE
Exclusive (X)	Nothing	All other access	UPDATE, DELETE, SELECT FOR UPDATE
Row-level	Other rows	Same row	Most DML
Table-level	Depends	Conflicting table ops	ALTER TABLE, LOCK TABLE

MVCC (Multi-Version Concurrency Control)

PostgreSQL's approach: instead of locking, keep multiple versions of each row. Readers never block writers, writers never block readers.

Row "account 1" versions: Version 1: balance=1000, created by txn 100, deleted by txn 105 Version 2: balance=900, created by txn 105, deleted by txn 110 Version 3: balance=850, created by txn 110, still alive Transaction 107 (started before txn 110) sees Version 2 (balance=900) Transaction 112 (started after txn 110) sees Version 3 (balance=850) Each transaction sees the version that was "alive" at its snapshot time. No locks needed for reads!

How MVCC Works in PostgreSQL

Each row has xmin (transaction that created it) and xmax (transaction that deleted/updated it)
UPDATE = mark old version dead (set xmax) + insert new version (new xmin)
A transaction only sees rows where: xmin < my_snapshot AND (xmax == 0 OR xmax > my_snapshot)
Old versions are cleaned up by VACUUM

MVCC vs Locking Trade-offs

Aspect	MVCC (PostgreSQL)	Locking (traditional)
Readers block writers?	No	Yes (shared locks)
Writers block readers?	No	Yes (exclusive locks)
Storage overhead	Multiple row versions (needs VACUUM)	Minimal
Conflict handling	Abort on write-write conflict	Wait (or deadlock)

💡 Why MVCC Matters

In a high-concurrency system (like a telecom NF handling thousands of UE contexts), MVCC means read operations (status queries, monitoring) never block write operations (context updates). This is why PostgreSQL handles concurrent workloads so well.

Optimistic vs Pessimistic

-- PESSIMISTIC: lock first, then modify (use when conflicts are common)
SELECT * FROM inventory WHERE id=1 FOR UPDATE;
UPDATE inventory SET stock = stock - 1 WHERE id=1;

-- OPTIMISTIC: try to modify, detect conflict via version column
UPDATE inventory SET stock = stock - 1, version = version + 1
WHERE id=1 AND version = 5;  -- fails if someone else changed it
-- If 0 rows affected → conflict! Retry.

Key Takeaways

MVCC: multiple row versions, readers never block writers (PostgreSQL)
Locking: explicit locks prevent concurrent access (SELECT FOR UPDATE)
Pessimistic locking: lock first, safe but can cause waits
Optimistic locking: try and retry on conflict, better for low-contention
VACUUM cleans up old row versions in MVCC systems

← Previous: Isolation Levels Next: Deadlocks →