I'm running SQL Azure and I have a following case.
I have two transactions
W (writer) and
R (reader). Both are running with RCSI (.net application with TransactionScopes using LTM). The
R transaction starts at the same time as
W and continuously executes SELECT statements to read the table that is written by the transaction
Normally, the transaction
R sees all the the changes immediately after
W commits its changes. However, occasionally the transaction
R sees the state without the most recent commit from the transaction
The question is:
Does the RCSI guarantee that
R always see the most recent state of the table?
Read committed should always get the latest revision of the row that was available at the time of executing the
SELECT query. But is it guaranteed, when having two independent sessions?