If your database had a motto it would be keep calm and avoid blocking. Multiversion concurrency control or MVCC is the trick that lets readers and writers coexist without staring each other down at the lock table. It stores multiple versions of rows so reads get a stable snapshot and writers append new versions instead of overwriting the old data. The result is fewer wait queues and fewer angry DBAs at 2 a m.

Basic idea that actually works

Think of every row as a small time machine. Each update produces a new version with metadata such as a transaction id or timestamp. A transaction sees the most recent version that was valid when it started. That gives read consistency even while other transactions are making changes, and it avoids most locking drama.

What readers do

Readers get a snapshot. They do not block writers. They read the version that was visible at their start time. That means long running analytical queries can churn through data without freezing OLTP traffic into an unproductive stupor.

What writers do

Writers create new versions. The old version sticks around until the system decides it is safe to remove it. This is cleaner than in place updates but it means storage grows if no one cleans up.

Visibility and cleanup without theatrics

Committed versions become visible to later transactions once the system agrees the new transaction is stable. Old versions are garbage collected or vacuumed when no active transaction can possibly need them. Long running transactions are the classic reason garbage collection falls behind, and yes that leads to bloat and slowdowns.

Pros and cons in plain English

• Pros keep reads fast and reduce locking conflicts
• Cons include storage overhead and potential bloat from many versions
• Monitoring long running transactions and scheduling regular garbage collection helps a lot

Real world notes for PostgreSQL and MySQL

PostgreSQL implements MVCC with tuple versions and transaction ids and relies on vacuum to reclaim space. MySQL InnoDB uses undo logs and consistent read views to give similar behavior. Both can provide snapshot isolation which avoids many common anomalies but still needs careful schema and index design to avoid surprises such as write skew under weaker isolation levels.

Quick experiment for curious humans

On a dev server start a long running read transaction and then run updates in another session. Watch the version count or table bloat with the monitoring tools your database provides. It is educational and mildly terrifying until you tune the garbage collector.

In short MVCC is the unsung hero of database concurrency. It trades a bit of storage and housekeeping for much better read throughput and fewer lock fights. You still need to mind isolation levels and long running transactions or the house will get messy.