Sleep – repeatable reading and second loss update problem

Use can be repeated The read isolation level of, it is still possible to lose updates (the second lost update problem). For example, in the scenario where the isolation level is set to RR:

1) Transaction t1 reads data from row r1 ,

2) Transaction t2 reads the same data from row r1,

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

< p>4) t2 modify the data read in #2 and submit the data to r1. The update of t1 is lost

I try to use Hibernate (isolation level is set to RR) and see the above mentioned Behavior.

Then why do we say that we have not lost the update problem for the second time in RR isolation?

You can infer that in the version of MySQL you are using in this test, the implementation does not really comply with repeatable reads, just like you did in other questions As stated in the, because if you have already done

transaction t2 reads same data from row r1

Again in step 4 instead of

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

Then t2 will read the value saved in t1 in step 3.
Therefore, you initially have no repeatable reads, so there will be no repeatable reads that are lost repeatedly.

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

Rather than when You said the locks that you thought of at the beginning of the year

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, an example of this is 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 missing update is not one of this phenomenon, but in A Critique of ANSI SQL Isolation Levels by Argeman in the other question Point out that repeatable reads guarantee that no updates will be lost:

P1 = non-repeatable reads
P4 = lost updates
Loose interpretation of P2 (pointing out possible phenomena)
An exception) Yes

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

The strict interpretation of P2 (specify an actual exception), called A1

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

Although the explanation for the missing update is

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

Your case is:

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

At first, it seems to be a situation where there is no non-repeatable read. In fact, t1 will always read the same value of x throughout the transaction.

However, if we focus on t2 and reverse the number , We can see that this is obviously a non-repeatable read situation.

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)