Concurrency Control: MVCC, Locking, OCC

The contract vs the machinery

Isolation levels are the contract; concurrency control is how the database actually delivers them. There are two big families, and modern databases lean on the first.

MVCC: readers don't block writers

Multi-Version Concurrency Control (MVCC) is the reason "readers don't block writers and writers don't block readers" in Postgres, MySQL InnoDB, Oracle, and most serious OLTP engines. The idea: a write does not overwrite a row in place; it creates a new version of the row, tagged with the transaction that created it. Every transaction runs against a consistent snapshot defined by which versions were committed as of its start. So a long analytical read sees a frozen, coherent view while writers keep creating new versions alongside it, and neither waits on the other. This is what makes snapshot isolation cheap and is why read-heavy systems love it.

The cost of MVCC is that old row versions pile up and must be reclaimed. In Postgres this is VACUUM (and autovacuum); in InnoDB it is the purge thread cleaning the undo log. The dangerous failure mode is a long-running transaction: it holds an old snapshot, so the database cannot reclaim any version newer than that snapshot's start, and dead tuples accumulate as bloat. A single forgotten transaction (an idle-in-transaction connection, a stuck analytics query) can bloat a table many times its live size and tank performance. This is the operational tax of MVCC, and interviewers love it.

Interview nuance: "What breaks if a transaction stays open for an hour?" The strong answer: it pins the vacuum horizon, so dead tuples cannot be reclaimed, the table and its indexes bloat, and sequential scans slow down. Mitigation: idle_in_transaction_session_timeout, keep transactions short, and monitor the oldest transaction age.

Locking and optimistic control

The second family is locking-based / pessimistic concurrency, classically two-phase locking (2PL): acquire shared (read) or exclusive (write) locks, hold them, and release only at commit. It is correct but writers block conflicting readers and each other, so throughput drops under contention, and it introduces deadlocks: transaction 1 holds lock A and wants B, transaction 2 holds B and wants A. Databases handle this by detecting the cycle and aborting one victim, so your app must catch the deadlock error and retry. You reduce deadlocks by acquiring locks in a consistent order (always lock the lower account id first) and keeping transactions short.

Optimistic concurrency control (OCC) assumes conflicts are rare: do not lock, just read a version, and at commit check WHERE version = :read_version; if it changed, abort and retry. OCC wins when contention is genuinely low, because it skips all lock overhead. It loses badly under high contention, because the abort-and-retry rate explodes and you burn CPU redoing work. So the rule is: optimistic under low contention, pessimistic under high contention.

The hot key

For a hot key specifically (a viral post's like counter taking thousands of increments per second on one row), the wrong move is heavy pessimistic locking, which serializes every writer behind one lock and caps throughput at one-at-a-time. The right moves: shard the counter into N sub-rows (like_count_shard_0..N), increment a random shard, and sum on read, which spreads contention N-fold; or aggregate increments in memory/Redis and flush periodically; or use an atomic in-database increment so each write is a single short operation rather than a read-modify-write holding a lock.

MVCC read:  txn snapshot --> sees v2 (committed), ignores v3 (in-flight) -- no wait
Hot counter: 1 row, all writers -- LOCK --> serialized (bad)
             N shards, random pick ------> ~N-way parallel, sum on read (good)

Recap: MVCC makes readers and writers not block each other by versioning rows, at the cost of vacuum and bloat from long transactions; choose optimistic control under low contention and pessimistic under high, and for a hot key shard the counter instead of serializing writers behind one lock.