How do a lock-based engine and a multiversion engine each stop a transaction from seeing a different value when it re-reads a row, and what does each approach cost in production?

## The shared guarantee Both families promise the same observable thing: within one transaction, re-reading a row you already read returns the same values. How they get there is completely different, and the difference determines what breaks in production. ## Mechanism 1 — two-phase locking with long read locks In a lock-based engine, every read takes a **shared (S) lock** and every write takes an **exclusive (X) lock**; S and X conflict. The isolation level is essentially a policy on **how long S locks are held**: - Release at the end of the statement → READ COMMITTED → a later re-read can see a change. - Hold until commit → REPEATABLE READ → nobody can take the X lock to modify the row while you might still read it, so the value cannot move. What this costs: - **Readers block writers.** A long-running read transaction stalls every writer of the rows it touched. In an OLTP system a reporting query at this level can freeze the write path. - **Deadlocks increase.** More locks held for longer means more chances to form a wait cycle, especially where transactions read then upgrade to write. - **Connection pool pressure.** Blocked transactions hold connections. One contended table can exhaust the pool and take down endpoints that never touch it. - **Lock memory and escalation.** Tracking millions of row locks costs memory; some engines escalate to page or table locks under pressure, which turns a narrow conflict into a broad one. What it buys: the data you read is always the **latest committed** value. There is no staleness, and a decision made on a read is valid at least until you commit, because nobody can change the row. That is genuinely valuable for check-then-act logic. ## Mechanism 2 — multiversion concurrency control with a transaction-wide snapshot An update stores a **new version** of the row and leaves the old one in place; each version records which transaction created it and which removed it. A reader carries a **snapshot** — effectively the set of transactions it considers committed — and sees the version that was current as of that snapshot. Holding one snapshot for the whole transaction makes every re-read return the same version. What this costs: - **Staleness.** Your view is fixed at your first statement. A transaction that runs for thirty seconds is making decisions on thirty-second-old data. For a consistent report that is exactly right; for a check-then-act decision it can be wrong in a way that locking would have prevented. - **Version retention.** The engine cannot reclaim any version your snapshot might still need. A long-lived transaction therefore blocks cleanup **across the whole database**: tables and indexes bloat, scans read more pages, and some engines eventually error out when the retained history exceeds its limit. Long-running transactions are the number-one operational hazard here, and monitoring the oldest active transaction is standard practice. - **Write conflicts become errors.** If you update a row that changed after your snapshot, the engine cannot show you the newer version without breaking your consistent view, so it aborts you with a **serialization failure**. Every write path at this level needs an idempotent retry of the whole transaction. - **Cleanup work.** Reclaiming dead versions is background work that competes for I/O, and it falls behind under sustained write load. What it buys: **readers never block writers and writers never block readers**. Read throughput is largely independent of write load, which is why this design dominates modern engines. ## Choosing and operating - If your workload is read-heavy with long analytical reads mixed into an OLTP system, the multiversion approach is what makes that survivable — but push those reads to a replica or keep them short so they do not pin old versions. - If a transaction must act on what it reads (authorize, allocate, transition a state machine), the snapshot's staleness is a hazard. Take an explicit exclusive lock on the specific rows you will act on; that gives you lock-style exclusion exactly where you need it, inside an otherwise non-blocking engine. - Whatever the engine, prefer to shrink the problem: fewer statements per transaction, one statement where possible, and short transactions. Both cost models degrade with transaction duration — one through held locks, the other through retained versions. ## What to monitor Lock-based: lock wait time, deadlock rate, blocked-session count, escalation events. Multiversion: age of the oldest running transaction, dead-version accumulation and cleanup lag, table/index bloat, serialization-failure and retry rates. These two lists are the fingerprint of each mechanism, and being able to recite the right one is what distinguishes a candidate who has operated the system from one who has only read the isolation table.