Similar to Read Committed Isolation level but you can still read the old version of the data.
Before a row is locked in preparation for changes, that means an update statement that might be run to a given row, that row is then placed in a version store (TempDB).
The big advantage that our SCI has is that it does not incur shared locks when reading data, since you’re not actually reading the data from the table, you’re reading from the version in temp DB. A simple way of saying this is readers do not block writers. Since these locks are not taken, concurrency on the database can greatly increase. This also means the opportunity for transactions that deadlock is greatly reduced.
Readers aren’t going to block writers.
Recommend for almost all production workloads. It might not be the best choice if your application depends on reading currently committed data rather than data that is current when the transaction started.
Example:
ALTER DATABASE xxxxx
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE xxxxx
SET ALLOW_COMMITTED_SNAPSHOT ON
Sources:
https://www.linkedin.com/learning/sql-server-performance-for-developers/rcsi-why-it-s-good
Comments