Introduction to RCSI and Improved Entity Framework Locking Hint Interceptor

TLDR

• RCSI (Read Committed Snapshot Isolation) is the modern standard practice for resolving high-concurrency read/write conflicts, using versioning to prevent reads from being blocked by writes.
• RCSI is a database-level setting; once enabled, it can replace most use cases for WITH (NOLOCK).
• If fine-grained control over SQL Hints (such as NOLOCK or OPTIMIZE FOR UNKNOWN) is required, it is recommended to use EF Core's TagWith combined with a DbCommandInterceptor for explicit declaration, rather than global forced injection.
• Framework-level automation should focus on "reducing team cognitive load" rather than blindly pursuing technical purity.

---

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 dirty data; instead, they read the "last version before the write (Snapshot)."

Characteristics and Limitations of RCSI

• Globally Effective: RCSI is a database-level setting; once enabled, it affects all queries using the Read Committed isolation level.
• Not a Per-Table Switch: It cannot be enabled for a single table. If selective usage is required, you must use Snapshot Isolation and specify it explicitly within a transaction.
• Cannot Replace Optimistic Locking: RCSI only resolves read blocking issues; concurrency conflicts during data updates still require a RowVersion mechanism.

Comparison of Common Locking Mechanisms

• WITH (NOLOCK): Reads may include uncommitted "dirty data."
• WITH (READPAST): Skips locked rows immediately; suitable for queue processing, but not for reports.
• RCSI: Reads the committed historical snapshot; achieves non-blocking reads without adding hints.

TIP

In modern hardware (SSD) and cloud environments (such as Azure SQL Database, where it is enabled by default), the TempDB burden of RCSI is no longer a critical issue and is a cleaner solution than adding NOLOCK everywhere.

TagWith + Interceptor Improved Implementation

When a project is not suitable for globally enabling RCSI, or when performance tuning is needed for specific queries, you can utilize EF Core's TagWith feature for explicit declaration and use an interceptor to dynamically inject SQL Hints.

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 TagOptimizeForUnknown = "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> WithOptimizeForUnknown<T>(this IQueryable<T> query) => query.TagWith(TagOptimizeForUnknown);
    public static IQueryable<T> WithRecompile<T>(this IQueryable<T> query) => query.TagWith(TagRecompile);
}

2. Implement the Interceptor

This interceptor supports Schema formatting and can automatically merge multiple Query Options.

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.TagOptimizeForUnknown)) 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;
    }
}

Practical Usage Example

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

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

TIP

For the complete executable example of this article, please refer to: CloudyWing/EfCoreSqlHintInterceptorSample.

Conclusion and Recommendations

• Prioritize RCSI: In systems with high read and low write volume, RCSI is the cleanest default solution that fundamentally resolves read/write conflicts.
• Explicit Declaration is Better than Implicit Injection: Using TagWith for explicit declaration allows developers to clearly understand which queries use special SQL Hints, reducing maintenance risks.
• Architectural Design Considerations: The value of global processing (such as AsNoTracking or interceptors) lies in reducing the team's cognitive load. Evaluate project scenarios to find the balance between "default behavior" and "explicit intervention," rather than blindly rejecting framework-level automation.

Changelog

1. 2026-02-05

   • Initial document created.
2. 2026-05-29

   • Renamed extension method WithOptionUnknown to WithOptimizeForUnknown to match the sample project.
   • Added link to the corresponding GitHub sample project.