Isolation, RCSI and snapshot

Define the consistency you need.

UnderstandStudy an examplePractise

Understand the concept

READ UNCOMMITTED permits dirty reads. Lock-based READ COMMITTED avoids uncommitted changes but does not freeze data between statements. REPEATABLE READ protects read rows; SERIALIZABLE also protects ranges against phantoms. Stronger isolation can increase waiting.

With READ_COMMITTED_SNAPSHOT enabled, READ COMMITTED uses statement-level row versions. SNAPSHOT provides a transaction-level view when allowed in advance and can encounter update conflicts. Versioning does not eliminate all locks or costs. Version storage and long readers require monitoring. This lesson does not change database options; those need operational assessment. Restore the session isolation level after experiments.

WHEN TO USE IT

Choose consistency for a transaction scenario.

COMMON PITFALL

Assuming RCSI and SNAPSHOT have the same scope.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

DBCC USEROPTIONS;

Displays session settings.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Read the current database’s RCSI state without changing it.

Show the solution and explanation

Worked solution

SELECT name, is_read_committed_snapshot_on FROM sys.databases WHERE name = DB_NAME();

1 means enabled and 0 disabled; do not enable it casually.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.