At 8pm, Tiffin's kitchen tablets update order statuses over a thousand times a second. At the same time, a restaurant owner opens a dashboard that scans the day's orders, and a finance job reads a month of them. If every reader had to wait for every writer, and every writer had to wait for every reader, the tablets would freeze every time someone opened a report.
Older databases worked roughly like that, and some still do by default. Readers took shared locks, writers took exclusive locks, and they queued behind each other. Modern databases mostly use a different trick: multi-version concurrency control, or MVCC. This lesson explains how it works, using Postgres and MySQL as the two main examples, and the operational problems it creates.
It helps to have read the previous lesson on isolation levels. Quick recap: snapshot isolation means a transaction reads from a frozen picture of the database. MVCC is how that picture is made.
Keep the old version around
MVCC keeps several versions of each row, so a reader can see the version that was current when its snapshot was taken while a writer creates a new one.
Instead of overwriting order 9001's status in place, the database writes a new version of the row and keeps the old one for a while. A reader that started earlier keeps seeing the old version. A reader that starts later sees the new one. Nobody waits.
The rule that falls out of this:
| Who | Blocks whom? |
|---|---|
| Reader and reader | Never |
| Reader and writer | Never |
| Writer and writer on the same row | Yes, the second waits for the first to commit or abort |
That last row matters. MVCC removes read/write blocking, not write/write blocking. Two kitchen tablets updating the same order still queue. More on that in the next lesson.