READ COMMITTED, RCSI, SNAPSHOT, REPEATABLE READ, SERIALIZABLE, and Anomalies

Learning outcomes

ServiceHub wants less reader/writer blocking, but “turn on snapshot” hides two different SQL Server models. READ_COMMITTED_SNAPSHOT (RCSI) changes READ COMMITTED reads to statement-level row versions. SNAPSHOT provides a transaction-level versioned view after ALLOW_SNAPSHOT_ISOLATION is enabled. Neither removes writer/writer locking or all schema blocking.

1. Name the guarantee before choosing the level

Locking READ COMMITTED prevents dirty reads but does not guarantee repeatable values across statements. REPEATABLE READ keeps locks on rows already read until transaction end but still allows qualifying new rows. SERIALIZABLE also protects key ranges. RCSI serves a committed version appropriate to each statement. SNAPSHOT exposes the transaction to a consistent versioned view and uses optimistic update-conflict detection.

2. Record the current database state

SELECT name,is_read_committed_snapshot_on,       snapshot_isolation_state_desc,is_accelerated_database_recovery_onFROM sys.databases WHERE name=N'ServiceHubLab';GO

Keep this output as the rollback manifest. Compatibility level and engine version are separate from these database options.

-- Ensure other Chapter 07 transactions are closed first.ALTER DATABASE ServiceHubLab SET ALLOW_SNAPSHOT_ISOLATION ON;GOSELECT snapshot_isolation_state_desc FROM sys.databases WHERE name=N'ServiceHubLab';GO

3. SNAPSHOT is transaction-level and can reject a stale writer

USE ServiceHubLab;GOSET TRANSACTION ISOLATION LEVEL SNAPSHOT;BEGIN TRANSACTION;SELECT part_id,on_hand FROM lab07.Inventory WHERE part_id=10;-- Let Session B commit an update now.SELECT part_id,on_hand FROM lab07.Inventory WHERE part_id=10;-- Optional: now UPDATE the same row to observe snapshot update-conflict behavior.ROLLBACK;SET TRANSACTION ISOLATION LEVEL READ COMMITTED;GO

UPDATE lab07.Inventory SET on_hand=on_hand+1 WHERE part_id=10;SELECT * FROM lab07.Inventory WHERE part_id=10;GO

Both Session A reads should represent its transaction snapshot. If Session A then attempts to update a row changed after its snapshot began, optimistic conflict detection can abort that write. “Snapshot” therefore is not a last-writer-wins mode.

4. RCSI is statement-level READ COMMITTED versioning

-- Close other ServiceHubLab sessions first and retain the original setting.ALTER DATABASE ServiceHubLab SET READ_COMMITTED_SNAPSHOT ON;GOSELECT is_read_committed_snapshot_on FROM sys.databases WHERE name=N'ServiceHubLab';GO

Under RCSI, eligible READ COMMITTED SELECT statements can read versions instead of waiting on many writer locks. A later statement in the same transaction can see a newer committed value because its statement snapshot is newer. This is intentionally different from SNAPSHOT’s transaction-wide view.

5. Versioning still has write and metadata conflicts

UPDATE and DELETE still take locks; uniqueness/foreign-key checks can wait; DDL still uses schema locks; and SNAPSHOT updates can conflict. Row versioning is a concurrency model for consistent reads, not a switch that removes all contention. Measure writer latency and version-store pressure before adopting it broadly.

6. Restore the recorded baseline

-- With all lab transactions closed:ALTER DATABASE ServiceHubLab SET READ_COMMITTED_SNAPSHOT OFF;ALTER DATABASE ServiceHubLab SET ALLOW_SNAPSHOT_ISOLATION OFF;GOSELECT is_read_committed_snapshot_on,snapshot_isolation_state_descFROM sys.databases WHERE name=N'ServiceHubLab';GO

If your starting values were already ON, restore those actual starting values instead of blindly using this snippet. Production change plans should record current setting, application assumptions, capacity evidence, and rollback.

Authoritative references

• SET TRANSACTION ISOLATION LEVEL — locking semantics
• Snapshot isolation — RCSI/SNAPSHOT behavior
• Locking and row versioning guide — concurrency model
• ADR — version-store location nuance
• SQL Server 2025 builds — CU baseline