How does MVCC improve concurrency compared with strict lock-based execution?
Interview preparation resource from Gate Smashers.
MVCC (Multi-Version Concurrency Control) improves concurrency by keeping multiple versions of rows so readers can often read a consistent snapshot without waiting for writers. Updates create new versions while older committed versions remain visible to transactions using a snapshot, which greatly reduces read-write blocking—especially in read-heavy workloads—though concurrent writers can still conflict and old versions require cleanup.
Short answer
MVCC (Multi-Version Concurrency Control) improves concurrency by preserving multiple versions of rows so readers can read a consistent snapshot without blocking writers. This reduces read-write blocking common under strict lock-based execution and is especially effective for read-heavy workloads.
How MVCC works
When a row is updated under MVCC, the system creates a new row version while preserving the older committed version. A transaction reading from a snapshot continues to see the appropriate older committed version even while another transaction creates a new version. Visibility rules and snapshots determine which version each transaction sees, allowing readers to proceed without acquiring locks that block writers.
- Readers: Read from a snapshot and often do not block or get blocked by writers.
- Updates: Create new row versions instead of modifying the existing version in place.
- Visibility rules: Snapshots and visibility rules decide which version a transaction can see.
- Cleanup: Old versions are eventually removed when no active transaction can still need them.
Benefits and costs
MVCC greatly reduces read-write blocking compared with strict lock-based execution and typically yields higher concurrency for read-heavy workloads. The trade-offs are additional storage for extra versions and the need for version cleanup; strict locking trades those costs for simpler state but can cause more waiting and lower concurrency under contention.
- Benefit: Readers continue without waiting for writers, reducing contention.
- Cost: Extra row versions and background cleanup (garbage collection) are required.
Limits and conflicts
MVCC does not eliminate all conflicts. Concurrent writers can still conflict and the DBMS must detect or serialize those cases. Old versions must be retained until no active transaction can need them, and are removed only afterward.