Phenomenon P2, also known as Non-Repeatable Read or Fuzzy Read, occurs when one transaction modifies data that another, in-progress transaction has read. Specifically, transaction Ti reads some version xi, and a different transaction Tj writes xi before Ti completes. Both Ti and Tj may commit or abort.
P2 is defined in terms of a total order of events, and makes implicit assumptions about time and state that may not hold true. For instance, if Ti committed before Tj began, it would be odd if Tj read xi-1, then read xi. Tj’s read is clearly not repeatable, but because it is not concurrent with Ti, it does not meet this definition of P2. For this reason, Jepsen prefers G1a, G1b, and G1c.
Literature
Non-Repeatable Read comes from the ANSI SQL standard, which says:
P2 (“Non-repeatable read”): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.
This description is famously ambiguous: is the final sentence meant to be an example, or required? In 1995 Berenson et al summarized the problems with ANSI SQL’s definitions and offered two possible interpretations of Non-Repeatable Read. The strict interpretation, A2, requires two reads, and that both transactions commit. The broad interpretation, P1, requires only one read, and covers any combination of commits and aborts.
- P2: r1[x] … w2[x] … ((c1 or a1) and (c2 or a2) in any order)
- A2: r1[x] … w2[x] … c2 … r1[x] … c1
Both A2 and P2 prevent a specific pattern that leads to Tj reading two different versions it did not write. However, A2 allows phenomena like A5A (Read Skew) and A5B (Write Skew) which P2 prevents.
As of 2024 the ANSI standard’s definition of isolation levels is unchanged. Whether or not ANSI Read Committed prevents G1b and G1c is still unclear. Berenson et al argue P2 is the correct interpretation, and Jepsen concurs.
See Also
Adya’s G2-item captures “what we think the standard meant to say” in more general and precise terms. The Adya formalism relies on dataflow, rather than assuming a single database state with real-time order. P2 is also arguably broader than necessary: it still occurs even if both writer and reader abort. G2-item, by contrast, applies only to committed transactions.