Skip to content

C# Interview Guide: SQL Server Transaction Isolation Levels — RCSI vs SNAPSHOT

Q16 — Transaction Isolation Levels — READ COMMITTED SNAPSHOT vs SNAPSHOT Level: Senior | Topic: SQL Server Why Isolation Levels Matter Isolation levels control how transactions see data changed by concurrent transactions...

Ekanatha Reddy Urivakili12 min2,326 words

Q16 — Transaction Isolation Levels — READ COMMITTED SNAPSHOT vs SNAPSHOT

Level: Senior | Topic: SQL Server

Why Isolation Levels Matter

Transactional consistency is fundamental in any multi-user database system, especially for OLTP workloads that change data frequently and concurrently. Isolation levels govern how transactions see data changes made by other concurrent transactions, balancing between consistency guarantees and system throughput.

Stronger isolation means fewer anomalies but often increases locking, blocking, and deadlocks. SQL Server provides both locking-based isolation and versioning-based isolation — the latter reduces contention substantially while preserving correctness.

Two versioning-based isolation levels dominate modern OLTP applications:

  • READ COMMITTED SNAPSHOT ISOLATION (RCSI)
  • SNAPSHOT ISOLATION

Understanding the nuanced differences between these two is crucial to architect scalable database applications without sacrificing correctness.


Key Transaction Isolation Levels in SQL Server

Isolation LevelDescriptionReader BehaviorWriter BehaviorUse CasesBlocking & Deadlocks
READ UNCOMMITTEDDirty reads allowedReads uncommitted (dirty) dataNo locks on readsRarely recommended — only for non-criticalMinimal but inconsistent
READ COMMITTED (default)Prevents dirty readsReads only committed data; takes shared locksExclusive locks held until commitGeneral purpose but can block reads/writesReaders block writes, writers block readers, deadlocks may occur
READ COMMITTED SNAPSHOT (RCSI)Version store based read committed isolationReads latest committed row version (no shared locks)Writes behave as in read committedOLTP default—eliminates read/write deadlocksVery low blocking, no reader/writer deadlocks
SNAPSHOTTransaction-level consistent snapshotConsistent snapshot at transaction start (row versions)Writes detect conflicts, rollback on conflictComplex workflows requiring repeatable reads and concurrencyLow blocking, write conflicts cause rollback
SERIALIZABLEHighest isolation, prevents phantomsRange locks, prevents inserts/updates during txHigh risk of blockingFinancial transfers, critical consistencyHigh blocking and deadlocks

Detailed Comparison: RCSI vs SNAPSHOT

FeatureRCSISNAPSHOT
ActivationALTER DATABASE SET READ_COMMITTED_SNAPSHOT ONALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON
Transaction ScopePer-statement (consistent snapshot per statement)Per-transaction (consistent snapshot fixed at start)
Read BehaviorReads latest committed row version at statement startReads version at start of transaction regardless of commits after start
LockingNo shared locks on reads; writers still use locksNo shared locks on reads; writers use locks
Write ConflictsNo write conflict detection — writers block each other if neededWrite conflicts detected, causes rollback of winner
Application Changes RequiredNone (default isolation preserved)Explicit usage of SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Use CasesTypical OLTP — eliminate reader-writer deadlocksLong-running transactions, reporting queries, complex business logic
OverheadVersion store usage in tempdbVersion store use + potential increased rollbacks

Architecture & Flow Explained

A picture helps make this clearer. Here is a high-level diagram showing interactions of reader and writer transactions under RCSI and SNAPSHOT isolation.

flowchart LR
    A[Transaction Start] --> B{Isolation Level?}
    B -->|RCSI| C[Statement Starts]
    B -->|SNAPSHOT| D[Transaction Starts]
    C --> E[Read Row Version from Version Store]
    D --> F[Create Consistent Snapshot for Whole Transaction]
    E --> G[Read Data]
    F --> G
    G --> H[Write Happens]
    H --> I{Conflict?}
    I -->|No| J[Commit]
    I -->|Yes (Snapshot only)| K[Rollback]
    J --> L[Transaction Ends]
    K --> L

Diagram illustrating the fundamental difference between RCSI (statement-level snapshot reads) and SNAPSHOT (transaction-level snapshot reads with conflict detection).


Practical Code Examples in C# Using System.Data.SqlClient

Minimal Example: Reading with RCSI Enabled

In this example, the database has RCSI enabled. The transaction defaults to READ COMMITTED, which is internally upgraded to snapshot reads.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        var connString = "Server=.;Database=MyDatabase;Trusted_Connection=True;";
        using var connection = new SqlConnection(connString);
        connection.Open();

        // Default isolation level READ COMMITTED with RCSI enabled
        var command = new SqlCommand("SELECT Stock FROM Inventory WHERE ProductId = 42;", connection);
        var stock = (int)command.ExecuteScalar();

        Console.WriteLine($"Stock for ProductId 42: {stock}");
    }
}

Notice how the application code requires no change despite RCSI affecting isolation behavior behind the scenes.

Realistic Example: Using SNAPSHOT Isolation in a Transfer Transaction

This example demonstrates using explicit SNAPSHOT isolation. It transfers stock between warehouses atomically with write conflict detection.

using System;
using System.Data;
using System.Data.SqlClient;

class InventoryTransfer
{
    static void Main()
    {
        var connString = "Server=.;Database=MyDatabase;Trusted_Connection=True;";
        using var connection = new SqlConnection(connString);
        connection.Open();

        using var transaction = connection.BeginTransaction(IsolationLevel.Snapshot);

        try
        {
            var decreaseCmd = new SqlCommand(
                "UPDATE Inventory SET Stock = Stock - @qty WHERE WarehouseId = @from AND ProductId = @prod AND Stock >= @qty;",
                connection, transaction);
            decreaseCmd.Parameters.AddWithValue("@qty", 10);
            decreaseCmd.Parameters.AddWithValue("@from", 1);
            decreaseCmd.Parameters.AddWithValue("@prod", 42);

            var rowsAffected = decreaseCmd.ExecuteNonQuery();
            if (rowsAffected == 0)
                throw new InvalidOperationException("Insufficient stock to transfer.");

            var increaseCmd = new SqlCommand(
                "UPDATE Inventory SET Stock = Stock + @qty WHERE WarehouseId = @to AND ProductId = @prod;",
                connection, transaction);
            increaseCmd.Parameters.AddWithValue("@qty", 10);
            increaseCmd.Parameters.AddWithValue("@to", 2);
            increaseCmd.Parameters.AddWithValue("@prod", 42);

            increaseCmd.ExecuteNonQuery();

            transaction.Commit();
            Console.WriteLine("Transfer completed successfully.");
        }
        catch (SqlException ex) when (ex.Number == 3960) // Snapshot write conflict error number
        {
            Console.WriteLine("Write conflict detected. Transaction rolled back.");
            transaction.Rollback();
        }
        catch (Exception)
        {
            transaction.Rollback();
            throw;
        }
    }
}

In this example, if two concurrent transfers try to update the same inventory row, one will rollback due to write conflict — which must be handled gracefully by the application.


Common Pitfalls and Recommendations

  • Long-running transactions with versioning: Both RCSI and SNAPSHOT store versions in the tempdb version store. Long-running transactions prevent cleanup of old versions, leading to version store bloat and tempdb growth.
  • Monitoring version store usage is essential after enabling version-based isolation.
  • Always test under load — enabling RCSI or SNAPSHOT can change blocking and deadlock behavior significantly.
  • Make sure your application handles write conflicts gracefully when using SNAPSHOT.
  • Do not enable both RCSI and SNAPSHOT simultaneously unless you have a clear strategy; they serve different use cases.
  • SERIALIZABLE is still needed for some critical workflows to prevent phantom reads but should be avoided on high throughput paths.

Monitoring and Observability

With version-based isolation, observability shifts towards monitoring the health of the version store, transaction concurrency conflicts, and lock contention.

What to Log

  • Transaction start and end timestamps
  • Isolation level used in transactions
  • Commit and rollback outcomes, especially write conflicts (error 3960)
  • Version store size and growth
  • Deadlock and blocking events with involved sessions

Example structured log entry (using JSON):

{
  "timestamp": "2024-06-24T15:23:45Z",
  "event": "TransactionCommit",
  "transaction_id": "TX12345",
  "isolation_level": "SNAPSHOT",
  "duration_ms": 520,
  "outcome": "Rollback",
  "error_code": 3960,
  "message": "Write conflict detected; transaction rolled back"
}

Key Metrics (Use RED/USE principles)

MetricDescriptionCollection Method
Request Rate (R)Number of transactions started per secondSQL Server DMVs, Application metrics
Error Rate (E)Number of failed transactions (e.g., rollbacks)SQL Server DMVs, Application logs
Duration (D)Transaction duration percentilesSQL Server DMVs, Application metrics
Tempdb Version Store UsageSize of version store in KBsys.dm_tran_active_snapshot_database_transactions query
Lock Wait TimeTime spent waiting for locksExtended Events, DMVs

Distributed Tracing with OpenTelemetry in .NET

Instrument transactions with OpenTelemetry tracing for end-to-end visibility.

using OpenTelemetry.Trace;
using System.Data.SqlClient;

var tracerProvider = Sdk.CreateTracerProviderBuilder()
    .AddSource("MyApp.SqlClient")
    .AddConsoleExporter()
    .Build();

var tracer = tracerProvider.GetTracer("MyApp.SqlClient");

using var span = tracer.StartActiveSpan("SQL Transaction");
try
{
    using var connection = new SqlConnection(connString);
    connection.Open();
    using var transaction = connection.BeginTransaction(IsolationLevel.Snapshot);

    // Execute commands...

    transaction.Commit();
}
catch (Exception ex)
{
    span.SetStatus(OpenTelemetry.Trace.Status.Error.WithDescription(ex.Message));
    throw;
}
finally
{
    span.End();
}

SLIs, SLOs, and Alerts for Transaction Isolation

SLISLOAlert CriterionAction on Alert
Successful transaction commit rate≥ 99.9%> 0.1% transaction rollbacks with error 3960Investigate write conflicts
Average transaction latency (95th percentile)< 200ms> 500ms sustained for 5 minutesInvestigate blocking or version store bottlenecks
Version store size in tempdb< 1 GB (assumption)Growth > 800MB in 10 minutesAlert DBAs for long-running tx
Deadlocks per minute< 1> 5 per minute sustainedInvestigate locking patterns

How SQL Server Implements Row Versioning Internally

Understanding the internal mechanics of version-based isolation in SQL Server clarifies why RCSI and SNAPSHOT behave differently, and what trade-offs each involves.

Version Store in tempdb

Both RCSI and SNAPSHOT rely on row versioning via a persistent version store implemented in tempdb. The version store contains historical versions of modified rows to serve readers without blocking writers.

  • Creation of Row Versions: When a transaction updates or deletes a row under versioning isolation, SQL Server stores the before-image of the row in the version store along with metadata:
    • Transaction sequence number (XSN)
    • Row identifier, timing information
  • Reading Versions: A reader transaction uses the XSN it represents as its snapshot "timestamp" to read the correct version from the version store. Older versions can be cleaned up once all active transactions that might need them complete.

Transaction Sequence Numbers (XSN)

SQL Server assigns a monotonically increasing transaction sequence number (XSN) to each committed transaction. Readers and writers compare their snapshot XSN against row versions' XSNs to determine visibility.

  • In RCSI, each statement uses the current XSN at the statement start, so it sees the latest committed snapshot at that moment.
  • In SNAPSHOT, the transaction gets a fixed XSN at the start, preserving a consistent view throughout.

Cleanup and Version Store Bloat

  • SQL Server regularly cleans up version store entries, but versions cannot be removed as long as any active transaction requires them.
  • Long-running transactions block cleanup, causing version store growth that can significantly impact tempdb space and I/O.
  • Monitoring the oldest active XSN and the version store size is critical in production environments to prevent version store exhaustion or tempdb contention.

Locking Interplay with Versioning

  • Writers still take exclusive (X) locks on rows they modify, even under RCSI or SNAPSHOT, to ensure serialization of writes.
  • Readers, however, never take shared (S) locks on reads under versioning, thus becoming non-blocking readers.
  • Write Conflicts happen only in SNAPSHOT isolation, where two concurrent transactions modifying the same row lead to one rolling back to maintain serializability.

##Worked Production Scenario: Scaling OLTP Reads with RCSI & Detecting SNAPSHOT Conflicts##

Consider an e-commerce order processing system running on SQL Server with high concurrent updates to inventory and orders.

Pre-RCSI: Issues Faced

  • The database used classic READ COMMITTED (lock-based).
  • Heavy SELECT queries on inventory caused reader-writer blocking.
  • During sales surges, deadlocks increased, blocking users and slowing order throughput.
  • Investigations showed many queries waiting on shared locks held by write transactions.

Enabling RCSI: Minimal Application Changes, Big Impact

  • DBAs enable RCSI:
ALTER DATABASE EcommerceDB SET READ_COMMITTED_SNAPSHOT ON;
  • No application code changes are needed because the default READ COMMITTED now reads versions instead of locking rows.
  • Result:
    • Significantly fewer blocking waits on SELECT queries.
    • Deadlock frequency dropped substantially.
    • Throughput improved during peak traffic without code rewrites.

Edge Case: Long-Running Reporting Query and Version Store Growth

  • A nightly reporting job begins a long-running transaction at READ COMMITTED (uses RCSI snapshot reads).
  • This transaction keeps snapshot XSN open for hours.
  • Meanwhile, many OLTP transactions keep modifying rows, creating new versions.
  • Because the old snapshot XSN remains active:
    • The version store cannot purge old row versions.
    • tempdb grows dramatically.
    • Other online queries slow due to tempdb contention.

Mitigation:

  • Split reporting into smaller transactions.
  • Schedule reporting during low activity windows.
  • Monitor tempdb and use sys.dm_tran_active_snapshot_database_transactions DMV to track oldest active snapshot transaction.

SNAPSHOT Isolation for Complex Business Logic

  • A new inventory allocation service requires read consistency across multiple queries per user request.
  • It explicitly uses SNAPSHOT isolation to keep a transaction-level consistent snapshot.
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

-- multiple reads/updates without changing view

COMMIT TRANSACTION;
  • With concurrent SNAPSHOT transactions attempting to update the same inventory rows:

    • Write conflicts generate error 3960.
    • Application retries intelligently to avoid failure cascade.
  • Business logic includes retry backoff and deduplication, handling write conflict errors robustly, preventing user-impacting failures.


Performance Considerations and Trade-offs

When choosing between RCSI and SNAPSHOT, engineers must understand the performance implications on:

AspectRCSISNAPSHOT
Read LatencyVery low overhead; statement scoped snapshot.Slightly higher latency from full transaction snapshot creation.
Write OverheadStandard locking for writers, no conflict detection.Writers check for conflicts on commit, possible rollback.
Conflict/Rollback RateNo write conflicts; writers simply serialize using locks.Write conflicts rollback transactions, increasing retry overhead.
Version Store UsageModerate—versions held per statement.Higher—versions held for entire transactions, especially long ones.
DeadlocksGreatly reduced reader-writer deadlocks.Reduced deadlocks; write conflicts replace some deadlocks.
Application ChangesNone required for existing READ COMMITTED code.Requires setting isolation explicitly, handling conflicts in app.

When to Prefer RCSI

  • General OLTP workloads with many reads/writes mixed.
  • Applications that cannot easily handle retries.
  • Desire for simple deployment—just enable the DB setting.
  • Minimize blocking and deadlocking while preserving committed-read consistency.

When to Use SNAPSHOT

  • Multi-statement read consistency is mandatory.
  • Complex business workflows requiring repeatable reads and predictability.
  • Application has built-in retry logic for write conflicts.
  • Reporting or auditing requiring stable views over multiple queries.
  • Less frequent write conflicts or less contention expected on writes.

Pitfalls to Avoid

  • Enabling RCSI without monitoring version store can cause tempdb issues.
  • Under high write concurrency, SNAPSHOT’s rollback overhead can degrade throughput.
  • Using SNAPSHOT without retry/backoff mechanisms leads to user-visible failures.
  • Long-running SNAPSHOT or RCSI transactions keep versions, inhibiting garbage collection.
  • Not considering interaction with other database features like Change Data Capture or Temporal Tables.

Observability Deep Dive: Logging, Metrics and Distributed Tracing

Strong observability is crucial for safe operation of RCSI and SNAPSHOT systems.

What to Log

  • Transaction lifecycle logs:
    • Start time, transaction ID, isolation level.
    • Commit or rollback with outcome and duration.
  • Version Store events:
    • Size of version store, oldest active snapshot transaction start time.
  • Write conflicts and deadlocks:
    • Error codes (e.g., 3960 for write conflicts).
    • Blocking and deadlock graphs with sessions and resource details.
  • Resource pressure:
    • tempdb utilization and IO waits.
    • Wait stats for locking and tempdb contention.

Example structured log entry monitoring a SNAPSHOT write conflict:

{
  "timestamp": "2024-06-24T16:45:10Z",
  "level": "Warning",
  "event": "TransactionRollback",
  "txId": "abcd1234",
  "isolationLevel": "SNAPSHOT",
  "durationMs": 350,
  "errorCode": 3960,
  "errorMessage": "Snapshot write conflict detected, transaction rolled back",
  "sessionId": 52,
  "blockingSessionId": 47,
  "resource": "Inventory.Stock.ProductId:42"
}

Critical Metrics to Monitor

MetricUnitWhy ImportantCollect via
Transaction start ratetx/secIndicates load and concurrencySQL Server DMVs, App telemetry
Transaction rollback rate%High write conflict rollbacks signal contentionApplication logs, SQL error counters
Version store sizeMBPrevents tempdb space exhaustionDMV sys.dm_tran_version_store_counts
Oldest active snapshot agesecondsLong active snapshots block cleanupsys.dm_tran_active_snapshot_database_transactions
Lock waits and deadlockscount/minDetect contentionExtended Events, SQL DMVs
Tempdb io latencymsMeasures tempdb pressure from version store accessPerfmon / SQL diagnostics

Implementing Distributed Tracing to Correlate DB Operations

Using OpenTelemetry in .NET apps, tracing each transaction step captures time spent on SQL commands, detects latency spikes and errors, and correlates with backend services.

Example of adding spans for SQL operations:

using OpenTelemetry.Trace;
using System.Data.SqlClient;

var tracerProvider = Sdk.CreateTracerProviderBuilder()
    .AddSource("MyEcommerceApp.Sql")
    .AddConsoleExporter()
    .Build();

var tracer = tracerProvider.GetTracer("MyEcommerceApp.Sql");

using var span = tracer.StartActiveSpan("InventoryTransferTransaction");

try
{
    using var connection = new SqlConnection(connString);
    connection.Open();

    using var transaction = connection.BeginTransaction(IsolationLevel.Snapshot);

    var cmd = new SqlCommand("UPDATE Inventory SET Stock = Stock - 1 WHERE ProductId=42", connection, transaction);
    cmd.ExecuteNonQuery();

    transaction.Commit();
}
catch (SqlException ex) when (ex.Number == 3960)
{
    span.SetStatus(OpenTelemetry.Trace.Status.Error.WithDescription("Write conflict rollback"));
    // Handle conflict retry logic
}
finally
{
    span.End();
}

This trace can be analyzed end-to-end from user request to final commit/rollback, helping troubleshoot anomalies related to versioning.


Decision Checklist: Choosing RCSI or SNAPSHOT for Your Application

QuestionChoose RCSIChoose SNAPSHOT
Do you need multi-statement consistent reads?NoYes
Can your app handle retries on write conflicts?NoYes
Which isolation level is currently in use?READ COMMITTED defaultExplicitly set SNAPSHOT
Is reducing reader-writer blocking a priority?YesYes
Are long-running transactions common?Minimize themMinimize or control strictly
How is tempdb capacity and monitoring?Adequate and monitoredCritical to monitor closely
Do you want minimal app code change?YesNo; app changes needed
How frequent are write conflicts in previews or tests?RareCould be frequent; needs mitigation

⚠️ Caution: Enabling both simultaneously (RCSI ON and ALLOW_SNAPSHOT_ISOLATION ON) is supported but confusing—only one applies per transaction based on the isolation level setting. Avoid enabling both unless handling explicitly and educating developers.


These deeper technical insights and operational guidance help engineering teams adopt and run SQL Server’s version-based isolation confidently in demanding production systems, balancing correctness, performance and scalability.

Summary

  • RCSI and SNAPSHOT leverage row versioning for concurrency control in SQL Server.
  • RCSI gives statement-level snapshot reads — ideal as a drop-in replacement for traditional READ COMMITTED.
  • SNAPSHOT provides transaction-level consistent snapshot views with optimistic concurrency, detecting write conflicts.
  • Choose RCSI for most OLTP workloads; use SNAPSHOT when you need repeatable reads and can handle conflicts.
  • Monitor version store usage, long transactions, and write conflicts carefully.
  • Instrument with logging, metrics, and distributed tracing for reliable operations.

References