Introduction to RCSI and Improved Entity Framework Locking Hint Interceptor

TLDR

• RCSI (Read Committed Snapshot Isolation) is currently the best practice for resolving SQL Server read/write locking conflicts and is recommended over WITH (NOLOCK).
• RCSI is a database-level setting; once enabled, it prevents reads from being blocked by writes without reading dirty data.
• For fine-grained control over locking hints (such as NOLOCK) in EF Core, it is recommended to use TagWith combined with a DbCommandInterceptor for explicit declaration, rather than global forced replacement.
• For Parameter Sniffing issues, it is recommended to prioritize SQL Server's Query Store mechanism.
• Global handling at the framework level should focus on "reducing team cognitive load" and be weighed against the project's risk tolerance.

---

What is RCSI?

RCSI (Read Committed Snapshot Isolation) resolves locking issues through "versioning." When data is being written, read operations are not blocked, nor do they read uncommitted dirty data; instead, they read the last snapshot version before the write occurred.

Operational Characteristics of RCSI

• Database-level setting: Once enabled (ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON), it applies globally, and all queries without a specified isolation level automatically enter snapshot mode.
• Cannot be enabled partially: If fine-grained control is required for specific queries, use Snapshot Isolation and specify it explicitly within a Transaction.
• Does not replace optimistic locking: RCSI only resolves query blocking issues; to handle concurrency conflicts during data updates, a RowVersion mechanism must still be used.

RCSI vs. NOLOCK vs. READPAST

• WITH (NOLOCK): Allows reading dirty data; carries the highest risk.
• WITH (READPAST): Skips locked rows; suitable for Queue processing, not suitable for reports.
• RCSI: Reads consistent snapshot data; achieves non-blocking reads without adding hints.

TIP

In modern environments (where SSDs are ubiquitous and cloud defaults have it enabled), the TempDB burden of RCSI is no longer a critical issue and is a cleaner solution than adding NOLOCK everywhere.

---

TagWith + Interceptor Improved Implementation

In scenarios where RCSI cannot be fully enabled or specific requirements exist, TagWith can be used for explicit declaration to inject locking hints only into specific queries.

1. Define Extension Methods

Use Fluent API syntax to make code intent clearer:

public static class EfHintExtensions {
    public const string TagNoLock = "SQL_HINT: NOLOCK";
    public const string TagReadPast = "SQL_HINT: READPAST";
    public const string TagOptionUnknown = "SQL_OPTION: OPTIMIZE FOR UNKNOWN";
    public const string TagRecompile = "SQL_OPTION: RECOMPILE";

    public static IQueryable<T> WithNoLock<T>(this IQueryable<T> query) => query.TagWith(TagNoLock);
    public static IQueryable<T> WithReadPast<T>(this IQueryable<T> query) => query.TagWith(TagReadPast);
    public static IQueryable<T> WithOptionUnknown<T>(this IQueryable<T> query) => query.TagWith(TagOptionUnknown);
    public static IQueryable<T> WithRecompile<T>(this IQueryable<T> query) => query.TagWith(TagRecompile);
}

2. Implement the Interceptor

This interceptor supports Schema formats (e.g., [dbo].[Table]) and automatically merges SQL Options to avoid syntax errors.

public class SqlTaggingInterceptor : DbCommandInterceptor {
    private static readonly RegexOptions regexOptions = RegexOptions.Multiline | RegexOptions.IgnoreCase;

    private static readonly Regex tableAliasRegex = new(
        @"(?<tableAlias>(?:FROM|JOIN)\s+(?:\[[^\]]+\]\.)?\[[^\]]+\]\s+AS\s+\[[^\]]+\])(?!\s+WITH\s*\()",
        regexOptions
    );

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result
    ) {
        FixCommand(command);
        return base.ReaderExecuting(command, eventData, result);
    }

    private static void FixCommand(DbCommand command) {
        if (string.IsNullOrWhiteSpace(command.CommandText)) return;
        string text = command.CommandText;
        bool isChanged = false;

        string hintToApply = null;
        if (text.Contains(EfHintExtensions.TagNoLock)) hintToApply = "WITH (NOLOCK)";
        else if (text.Contains(EfHintExtensions.TagReadPast)) hintToApply = "WITH (READPAST)";

        if (hintToApply != null) {
            text = tableAliasRegex.Replace(text, $"${{tableAlias}} {hintToApply}");
            isChanged = true;
        }

        List<string> options = new ();
        if (text.Contains(EfHintExtensions.TagOptionUnknown)) options.Add("OPTIMIZE FOR UNKNOWN");
        if (text.Contains(EfHintExtensions.TagRecompile)) options.Add("RECOMPILE");

        if (options.Count > 0) {
            text = text.TrimEnd().TrimEnd(';');
            text += $" OPTION ({string.Join(", ", options)});";
            isChanged = true;
        }

        if (isChanged) command.CommandText = text;
    }
}

Usage Example

// Report: Allow dirty reads + resolve parameter sniffing
List<Order> orders = context.Orders
    .WithNoLock()
    .WithOptionUnknown()
    .ToList();

// Queue: Skip locked rows
Job job = context.Jobs
    .WithReadPast()
    .FirstOrDefault();

---

Reflections on Global Interception and Invisible Mechanisms

When evaluating whether to adopt global handling (such as automatic AsNoTracking injection or interceptors), it is recommended to weigh the following three dimensions:

• Context Frequency: If 90% of queries belong to the same type (e.g., a reporting system), global default handling can significantly simplify development.
• Ease of Identification: If "high-risk operations" are easier to identify than "low-risk operations," enabling global handling by default can actually reduce the risk of misjudgment.
• Risk Tolerance: Evaluate the consequences of misjudgment (e.g., data inconsistency vs. slower queries) and choose the architecture that best fits the project's needs.

Conclusion: The choice of technical means should prioritize "reducing team cognitive load" rather than blindly following dogmatic Anti-Pattern definitions.

---

Change Log

• 2026-02-05 Initial document creation.