# Understanding non repeatable read anomaly in databases

This paper discusses in detail ANSI isolation levels discussed in ANSI SQL standard on page 84.

Paper introduces following shorthand for describing histories which contain reads, writes, commits and aborts:

w1[x] means a write by transaction 1 on data item x (which is how a data item is “modified’), and r2[x] represents a read of x by transaction 2. Transaction 1 reading and writing a set of records satisfying predicate P is denoted by r1[P] and w1[P] respectively. Transaction 1’s commit and abort (ROLLBACK) are written c1 and a1, respectively.

Standard defines "non repeatable or fuzzy read phenomena" as follows:

Non repeatable read: Transaction T1 reads a data item. Another transaction T2 then modifies or deletes that data item and commits. If T1 then attempts to reread the data item, it receives a modified value or discovers that the data item has been deleted.

Paper says above definition is ambiguous and can have two interpretations:

Strict interpretation (A2) : states anomaly "actually" occur

r1[x]...w2[x]...c2...r1[x]...c1


Broad interpretation (P2) : states anomaly "might" occur

r1[x]...w2[x]...((c1 or a1) and (c2 or a2) in any order)


(Both standard and paper mainly discuss two phenomena, out of which this one is second, thats why 2 in A2 and P2)

Then the paper says we should assume P2 to be correct interpretation and for that it gives example of following history on page 5 and 6 (of transferring balance from one account to another):

 H2: r1[x=50]r2[x=50]w2[x=10]r2[y=50]w2[y=90]c2r1[y=90]c1


H2 is non-serializable — it is another inconsistent analysis, where T1 sees a total balance of 140. The problem with H2 is that by the time T1 reads y, the value for x is out of date. If T2 were to read x again, it would have been changed; but since T2 doesn't do that, A2 doesn't apply.

I am confused with the above last sentence in italics:

If T2 were to read x again, it would have been changed; but since T2 doesn't do that, A2 doesn't apply.

Precise doubt: It says "If T2 were to read x again", but in the definition of A2, T2 performs read only once. Its T1, who performs two reads.

I believe, this is a typing / silly mistake and authors really meant to say T1 instead of T2, that is T1 should read x again to find that value of x is changed in two reads, which is what non repeatable read is all about. But since it does not do that, definition A2 fails to capture the phenomena and hence we should use definition P2.

Am I right with this? Or am I still tangled with words?