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.