Concurrency Control: Locking, 2PL, and MVCC

Isolation levels describe what guarantees a database offers; concurrency control is how it delivers them while many transactions run at once. There are two great families of technique: locking (make writers and readers wait) and multiversion concurrency control (let readers see a snapshot so they never wait for writers). Understanding both — and the deadlocks that locking creates — is what separates a memorized answer from a real one.

Lock-based concurrency control

The simplest approach is to guard data with locks. The two fundamental modes are:

• Shared lock (S): for reading. Multiple transactions can hold a shared lock on the same item simultaneously.
• Exclusive lock (X): for writing. Only one transaction can hold it, and it is incompatible with any other lock.

| Requested \ Held | None | Shared (S) | Exclusive (X) | |------------------|------|-----------|---------------| | Shared (S) | Grant | Grant | Wait | | Exclusive (X) | Grant | Wait | Wait |

Locks come at different granularities — row, page, or table. Fine-grained row locks maximize concurrency but cost memory and bookkeeping; coarse table locks are cheap but serialize access. Databases use lock escalation to promote many row locks into a table lock under pressure.

Two-Phase Locking (2PL): the correctness rule

Simply taking locks is not enough to guarantee serializability. Two-Phase Locking adds a discipline: every transaction has a growing phase (it may acquire locks but not release any) followed by a shrinking phase (it may release locks but not acquire any). Once a transaction releases its first lock, it can never take another. 2PL guarantees conflict serializability — the schedule is equivalent to some serial order.

The plain form has a subtle flaw: if a transaction releases a lock and then aborts, another transaction may have already read its uncommitted change, causing a cascading rollback. The fix is Strict 2PL, which holds all exclusive locks until commit or abort, and Rigorous 2PL, which holds all locks (shared and exclusive) until the end. Real databases use strict/rigorous variants precisely to prevent cascading aborts and dirty reads.

Deadlocks: the price of locking

When transaction A holds a lock B wants, and B holds a lock A wants, neither can proceed — a deadlock. The four Coffman conditions must all hold for a deadlock: mutual exclusion, hold-and-wait, no preemption, and circular wait. Databases handle deadlocks in two ways:

• Detection: maintain a wait-for graph; a cycle means a deadlock. The database picks a victim (usually the transaction with the least work done) and aborts it, and the application retries.
• Prevention: schemes like wait-die and wound-wait use transaction timestamps to decide whether an older or younger transaction waits or aborts, so a cycle can never form.

The practical advice interviewers want to hear: acquire locks in a consistent global order across your application so a circular wait cannot arise, keep transactions short, and be prepared to retry on deadlock.

-- Deadlock-prone if two sessions lock rows in opposite order.
-- Fix: always lock the lower id first.
BEGIN;
  SELECT * FROM accounts WHERE id = LEAST(1, 2) FOR UPDATE;
  SELECT * FROM accounts WHERE id = GREATEST(1, 2) FOR UPDATE;
  -- ... transfer ...
COMMIT;

MVCC: readers don't block writers

Locking makes readers and writers contend. Multiversion Concurrency Control eliminates most of that contention by keeping multiple versions of each row. When a transaction updates a row, the database does not overwrite in place; it creates a new version stamped with the writing transaction's id, while old versions remain visible to transactions that started earlier. Every transaction reads from a consistent snapshot as of its start (or its statement, depending on the level).

The killer property: readers never block writers, and writers never block readers. A long analytical read sees a stable snapshot while writes stream in around it. This is why PostgreSQL, Oracle, and MySQL/InnoDB all use MVCC as their backbone.

• In PostgreSQL, each row version (tuple) carries xmin/xmax transaction ids; visibility rules decide which version a snapshot sees. Dead versions are reclaimed by VACUUM.
• In InnoDB, old versions live in the undo log, and readers reconstruct the correct version by walking undo records.

Writers still conflict with other writers on the same row — MVCC does not make two concurrent updates to the same row free. That last write still needs an exclusive lock or a first-committer-wins abort.

Snapshot isolation and its famous gap

Most MVCC systems implement snapshot isolation: every transaction reads a frozen snapshot and writes are checked for conflict at commit. Snapshot isolation prevents dirty reads, non-repeatable reads, and phantoms, but it permits a subtle anomaly called write skew: two transactions read an overlapping set, each makes a decision valid alone, and together they violate a constraint (for example, two doctors each going off-call because they each see the other still on-call). True SERIALIZABLE requires extra machinery — PostgreSQL's Serializable Snapshot Isolation (SSI) detects dangerous read-write dependency cycles and aborts a transaction to close the gap.

Optimistic vs pessimistic control

• Pessimistic (locking): assume conflicts are likely; lock first, work second. Best under high contention.
• Optimistic (validation/versioning): assume conflicts are rare; do the work, then validate at commit and retry on conflict. Best under low contention, and the natural fit for MVCC snapshot systems.

What interviewers actually probe

1. Explain 2PL and why strict 2PL exists — the growing/shrinking phases give serializability; holding X-locks to commit prevents cascading aborts.
2. Describe deadlock detection vs prevention, and give the practical fix (consistent lock ordering, short transactions, retry).
3. State MVCC's core benefit in one line: readers and writers don't block each other because each reads a snapshot.
4. Know write skew as the anomaly snapshot isolation still allows, and that true serializability closes it.
5. Contrast optimistic and pessimistic control and when each wins.

The synthesis: locking trades concurrency for simplicity and creates deadlocks; MVCC trades storage (old versions) and cleanup (vacuum) for the enormous win of non-blocking reads. Modern engines combine them — MVCC for reads, locks for write-write conflicts.