Lost Updates under Read Committed Snapshot Isolation (RCSI)
I shared an image on social media this week that describes how I feel about isolation levels in SQL Server (and its various flavors): the more concurrent sessions you have in a database reading and writing data at the same time, the more attractive it is to use version-based optimistic locking for scalability reasons.
There are two isolation levels in SQL Server that use optimistic locking for disk-based tables:
- Read Committed Snapshot Isolation (RCSI), which changes the implementation of the default Read Committed Isolation level and enables statement-based consistency.
- Snapshot Isolation, which provides high consistency for transactions (which often contain multiple statements). Snapshot Isolation also provides support for identifying update conflicts.
Many folks get pretty nervous about RCSI when they learn that certain timing effects can happen with data modifications that don’t happen under Read Committed. The irony is that RCSI does solve many OTHER timing risks in Read Committed, and overall is more consistent, so sticking with the pessimistic implementation of Read Committed is not a great solution, either.
