Kendra Little | February 17, 2024
Most modern hardware supports RCSI and Snapshot isolation beautifully.
When you’ve got a bunch of sessions modifying data while others are reading it, these isolation levels help your workload scale while not compromising on the correctness of results.
Why do I have Snapshot as more “correct” than RCSI?
Snapshot Isolation provides consistency for multi-statement transactions. It’s common to have multiple queries run to slice and dice data in different ways, and the data just looks wrong (and to the user, is wrong) if those individual statements reflect different points in time.
Basically, Snapshot provides highly correct results on even more scenarios than RCSI, so I put it to the right. But RCSI is still highly consistent for individual statements.
Remember: modifications have their own rules
A transaction always gets an exclusive lock on data it modifies and keeps it until the transaction completes, regardless of isolation level.