Deadlocks, Locking and MVCC

Isolation levels are the promise; locking and MVCC are the mechanisms that deliver it. Interviewers ask about these to see if you understand what happens under the hood when transactions collide.

There are two broad strategies:

• Pessimistic (locking): block conflicting access until a lock is released.
• Optimistic / MVCC: let everyone read a consistent snapshot and detect conflicts at commit.

This lesson covers locks, deadlocks, and MVCC, plus the trade-offs between them.

Classic locking uses two main modes:

• Shared (S) lock for reads. Many transactions can hold a shared lock on the same row at once.
• Exclusive (X) lock for writes. Only one transaction can hold it, and it blocks all other locks on that row.

The rule: S is compatible with S, but X is compatible with nothing. A writer must wait for all readers, and readers must wait for a writer.

You can request a write lock on rows you only read, to prevent others from changing them before you act. This is the standard way to avoid lost updates in a read-modify-write cycle.

SELECT ... FOR UPDATE takes exclusive row locks; the rows stay locked until you COMMIT or ROLLBACK.

A deadlock occurs when two or more transactions each hold a lock the other needs, forming a cycle where none can proceed.

The textbook case: T1 locks row A then wants row B; T2 locks row B then wants row A. Each waits forever for the other.

Databases detect this with a wait-for graph. When a cycle is found, the engine picks a victim and aborts it, returning a deadlock error so the others can continue.

Watch the lock order cross. T1 takes row 1 then asks for row 2; T2 takes row 2 then asks for row 1. Neither releases, so the engine aborts one.

The aborted transaction sees an error like deadlock detected and must retry. The survivor commits normally.

You cannot eliminate deadlocks entirely, but you can make them rare. Standard interview answers:

• Consistent lock ordering: always acquire rows in the same order (for example, ascending id). This breaks the cycle.
• Keep transactions short: hold locks for as little time as possible.
• Lower isolation when safe: fewer locks, fewer conflicts.
• Add retry logic: deadlock victims should automatically retry.

Consistent ordering is the single most effective fix and the one interviewers want to hear first.

Locks can be taken at different scopes, a trade-off between concurrency and overhead:

• Row-level locks allow high concurrency but cost more to manage.
• Page or table locks are cheaper to track but block more transactions.

Some engines escalate from row to table locks when a transaction touches too many rows (lock escalation). Knowing this explains why a big bulk UPDATE can suddenly block everyone.

MVCC (Multi-Version Concurrency Control) is how Postgres, Oracle, and InnoDB avoid most read locks. Instead of locking, the database keeps multiple versions of each row.

The headline benefit, and a favorite interview soundbite: readers do not block writers, and writers do not block readers.

Each transaction sees a consistent snapshot as of a point in time, while writers create new row versions rather than overwriting in place.

When a row is updated, MVCC writes a new version and keeps the old one. Each version carries transaction-id metadata (in Postgres, xmin and xmax) marking when it became visible and when it was superseded.

A transaction's snapshot decides which version it sees. Old versions that no transaction can still see become dead tuples, reclaimed later by a cleanup process. In Postgres that process is VACUUM; not running it causes table bloat, a common follow-up question.

Summarize the comparison crisply:

• Pure locking: simple correctness, but readers and writers block each other, hurting concurrency.
• MVCC: excellent read concurrency, no read locks, but pays for it with version storage and cleanup (VACUUM, bloat) and still needs locks for write-write conflicts.

Even MVCC engines lock on writes: two transactions updating the same row must serialize. MVCC removes the reader-writer contention, not the writer-writer one.

Beyond engine-level MVCC, applications often add optimistic locking for read-modify-write over long user sessions. You add a version column, read it, and on update require the version to match, incrementing it.

If another transaction updated the row first, the version no longer matches, zero rows are affected, and your code knows to reload and retry. No locks are held while the user thinks, so concurrency stays high. Interviewers like this for "how do you handle two users editing the same record?".

Test the core MVCC soundbite.

You can now explain the machinery behind isolation:

• Shared/exclusive locks coordinate access; SELECT FOR UPDATE takes explicit write locks.
• Deadlocks are lock cycles; the engine aborts a victim, and consistent lock ordering prevents most of them.
• MVCC keeps row versions so readers and writers do not block, at the cost of cleanup (VACUUM, bloat).

Pair these mechanisms with the isolation levels and anomalies from earlier lessons and you can carry a full concurrency interview end to end.