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 W
.
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 W
.
The question is:
Does the RCSI guarantee that R
always see the most recent state of the table?
According to:
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-2017
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?
ReadCommitted
isolation level with yourTransactionScope
? The default isolation level withTransactionScope
isSerializable
so readers could block writers.ReadCommitted
option passed to theTransactionScope