MVCC
The conceptWhy a slow report doesn't freeze a shop's checkout
MVCC (multiversion concurrency control) means an update never overwrites a row: it writes a new version and keeps the old one. Each transaction reads from a snapshot, a frozen view of what was committed when it began.
Say a 10-minute sales report is running when a customer buys the last mug. Checkout writes a new version with stock 0; the report keeps reading the old one with stock 1. Neither waits for the other.
Each version is stamped with the transaction ID that created it (xmin) and the one that replaced it (xmax). IDs only go up, so comparing them with the reader's snapshot picks the right version.
What it’s forDecides whether a reader's snapshot can see a given row version (simplified: IDs below S had committed when the snapshot was taken).
visible ⇔ xmin < S ≤ xmax
- xmin
- ID of the transaction that created this version
- xmax
- ID of the transaction that replaced or deleted it; ∞ if it is still current
- S
- the reader's snapshot point: the next transaction ID when its snapshot was taken
What it givesExactly one version of each row for every reader: the one that was current at its snapshot.
- Old stock=1: xmin 100, xmax 105. New stock=0: xmin 105, xmax ∞.
- Report with S=103: 100 < 103 ≤ 105, so it sees stock=1; 105 < 103 fails, so it skips stock=0.
- New reader with S=110: 103 ≤ 105 fails for the old one; 105 < 110 ≤ ∞, so it sees stock=0.