Can Readers See Inconsistent Data While Writers Update? A Deep Dive into MVCC and Locks

01

When one transaction reads an order list while another transaction updates the status of an order, the database must avoid blocking reads with writes and vice‑versa, otherwise concurrency performance would be terrible. The solution is to let readers view a historical version of the data, which is the core idea of MVCC.

02

MVCC (Multi‑Version Concurrency Control) stores not only the current value of a row but also its past versions. For example, an order amount may have been 120, then changed to 100, and later to 80. The database can retrieve any of these versions, allowing a transaction to read a stable snapshot taken at the start of the transaction instead of the latest uncommitted value.

03

The first key term is Undo Log . It records the old version of a row (e.g., the amount changing from 100 to 80) so that the system can roll back a transaction and also locate historical versions for snapshot reads.

04

The second term is the Version Chain . Each modification creates a new version, forming a timeline such as version 3 = 80, version 2 = 100, version 1 = 120. Transactions decide which version they are allowed to see based on their own rules.

05

The third term is ReadView . When a transaction starts reading, it captures a “photo” of which other transactions’ changes are visible. Uncommitted changes are normally invisible, and ReadView is used to make that determination.

06

Two kinds of reads are distinguished:

Snapshot read : ordinary SELECT statements read the version visible to the current transaction, which may not be the latest value but is stable and does not acquire locks.

Current read : UPDATE, DELETE, and SELECT … FOR UPDATE read the latest value and acquire locks because they may modify the data.

07

Locks address write‑write conflicts. For example, if two users simultaneously try to deduct a single item from inventory, without locking the stock could become negative. Therefore, write operations must protect the current latest data with locks.

08

Pessimistic lock assumes conflicts will happen, so it locks the row before modification, e.g., SELECT * FROM product WHERE id = 1 FOR UPDATE. It suits high‑conflict, high‑concurrency, and strong‑consistency scenarios, but can degrade performance because other transactions must wait.

09

Optimistic lock does not lock upfront; instead it checks a version field at commit time. If the version has changed (e.g., from 5 to 6), the update affects zero rows, indicating a conflict. The application can then retry or report failure.

10

Lock granularity:

Row lock : locks a single row, offering fine granularity and high concurrency.

Table lock : locks the whole table, simple but reduces concurrency.

11

Gap locks and Next‑Key Lock prevent phantom reads. A phantom read occurs when a range query returns a different number of rows because another transaction inserts a new row in the range. Gap locks lock the space between existing keys (e.g., between id 10 and id 20) to stop inserts, while Next‑Key Lock combines a record lock with a gap lock to protect both the row and its surrounding range.

12

Recap:

Transaction isolation levels define how much concurrent transactions may affect each other.

MVCC lets ordinary reads avoid blocking writes by reading a stable snapshot.

Undo Log provides the old version for rollback and snapshot reads.

ReadView decides which versions are visible to the current transaction.

Locks resolve write‑write conflicts and enforce strong consistency.

Next‑Key Lock prevents phantom reads in range queries.

The key takeaway is: let reads use snapshots whenever possible, and protect writes with appropriate locks.