repeatable read and second lost updates issue

Question

With repeatable read isolation level, it is still possible to lose updates (second lost updates problem). E.g. in the scenario with isolation level set to RR:

1) transaction t1 reads data from row r1,

2) transaction t2 reads same data from row r1,

3) t1 modifies the data read in #1 and commits data to r1

4) t2 modifies the data read in #2 and commits data to r1. t1's update is lost

I tried this with Hibernate (isolation level set to RR) and saw the behavior as mentioned above.

Why then is it said that with RR isolation we do not get second lost updates problem?

Answer 1

I had tried the above experiment with MySQL, and looks like MySQL implements a different notion of RR: MySQL repeatable read and lost update/phantom reads

Answer 2

You can deduce that in the version of MySQL that you used in this test, the implementation doesn't really conform to Repeatable Read, like you say in your other question, because if you have done

transaction t2 reads same data from row r1

again in the step 4 instead of

t2 modifies the data read in #2 and commits data to r1.

then the t2 would have read it the value saved by t1 in the step 3. So you don't have repeteable read at first, so it is not a case of repeteable read with lost update.

ANSI SQL-92 defines Isolation Levels in terms of phenomena: Dirty Reads, Non-Repeatable Reads, and Phantoms.

and not in terms of locks like you thought at first when you said

Now, as I understand it, RR uses shared read locks and exclusive write locks

This is because

ANSI SQL Isolation designers sought a definition that would admit many different implementations, not just locking.

In fact one example of this is the READ_COMMITED implementation from SQL SERVER.

If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. [...]

If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

The lost updates is not one of this phenomena, but in A Critique of ANSI SQL Isolation Levels pointed out by Argeman in the other question is explained that repeteable read guarantees no lost updates:

P1 = Non-repeteable reads P4 = Lost updates The loose interpretation of P2 (specifies a phenomenon that might lead to an anomaly) is

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

The strict interpretation of P2 (specifies an actual anomaly), called A1 is

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

While the interpretation of lost updates is

P4: r1[x]...w2[x]...w1[x]...c1

The case that you present is in the form of:

A4: r1[x]...r2[x]...w1[x]...c1...w2[x]...c2

At first it seems that is a case that doesn't have non-repeteable reads, in fact the t1 will always read the same value of x along the whole transaction.

But if we focus on the t2 and invert the numbers we can see that is clearly a case of non-repeteable read.

A4: r1[x]...r2[x]...w1[x]...c1...w2[x]...c2

A4: r1[x]...w2[x]...c2...w1[x]...c1 (with the numbers inverted for better readability)

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