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://docs.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?
Best Answer
Yes, you are correct. Since it's RCSI, it's at a statement level and not a transaction level which would be regular snapshot isolation.
Yes, it should be.
Yes, from when the statements starts it'll be the most recent and based off the version store if need be. This means if the read query started and the write was in the middle of the write, the older value would be seen by read statement.
What you're describing is normal and in line with small, quick, concurrent updates on one thread and reads on another. Most of the time RCSI alleviates the locking/blocking at the cost of potentially slightly stale reads. You could get to the bottom of the issue by checking on an individual statement level when each statement started, committed, etc., but if they are milliseconds or microseconds apart there really isn't anything you could do to change it outside of writing in thread synchronization for the application accessing the data - thus forcing the reads to not be stale.
Note that this assumes the writes are small, fast, and complete successfully. For example, a write where the application is hung and never commits would show the previous value when read via RCSI instead of staying blocked such as in read committed.