給 EF Core 查詢增加 With NoLock

Intro

EF Core 在 3.x 版本中增加了 Interceptor，使得我們可以在發生低級別資料庫操作時作為 EF Core 正常運行的一部分自動調用它們。 例如，打開連接、提交事務或執行命令時。

所以我們可以自定義一個 Interceptor 來記錄執行的 sql 語句，也可以通過 Interceptor 來實現 sql 語句的執行前的修改或者更準確的記錄 DbCommand 執行的耗時。

這裡我們可以藉助 Interceptor 實現對於查詢語句的修改，自動給查詢語句加 (WITH NOLOCK)，WITH NOLOCK 等效於 READ UNCOMMITED（讀未提交）的事務級別，這樣可能會造成一定的臟讀，但是從效率上而言，是比較高效的，不會因為別的事務長時間未提交導致查詢阻塞，所以對於大數據多事務的場景下，查詢 SQL 加 NOLOCK 還是比較有意義的

NoLockInterceptor

繼承 DbCommandInterceptor，重寫查詢 sql 執行之前的操作，在執行查詢 sql 之前增加 WITH(NOLOCK)，實現代碼如下：

public class QueryWithNoLockDbCommandInterceptor : DbCommandInterceptor
{
    private static readonly Regex TableAliasRegex =
        new Regex(@"(?<tableAlias>AS \[[a-zA-Z]\w*\](?! WITH \(NOLOCK\)))",
            RegexOptions.Multiline | RegexOptions.Compiled | RegexOptions.IgnoreCase);

    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        command.CommandText = TableAliasRegex.Replace(
            command.CommandText,
            "${tableAlias} WITH (NOLOCK)"
            );
        return base.ScalarExecuting(command, eventData, result);
    }

    public override Task<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        command.CommandText = TableAliasRegex.Replace(
            command.CommandText,
            "${tableAlias} WITH (NOLOCK)"
            );
        return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        command.CommandText = TableAliasRegex.Replace(
            command.CommandText,
            "${tableAlias} WITH (NOLOCK)"
            );
        return result;
    }

    public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        command.CommandText = TableAliasRegex.Replace(
            command.CommandText,
            "${tableAlias} WITH (NOLOCK)"
            );
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }
}

Interceptor 的使用

在註冊 DbContext 服務的時候，可以配置 Interceptor，配置如下：

var services = new ServiceCollection();
services.AddDbContext<TestDbContext>(options =>
{
    options
        .UseLoggerFactory(loggerFactory)
        .UseSqlServer(DbConnectionString)
        .AddInterceptors(new QueryWithNoLockDbCommandInterceptor())
        ;
});

使用效果

通過 loggerFactory 記錄的日誌查看查詢執行的 sql 語句

可以看到查詢語句自動加上了 WITH (NOLOCK)

Reference

• https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15
• https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/#interception-of-database-operations
• https://docs.microsoft.com/zh-cn/ef/core/what-is-new/ef-core-3.0/#interception-of-database-operations
• https://github.com/WeihanLi/WeihanLi.EntityFramework/blob/dev/src/WeihanLi.EntityFramework/Interceptors/QueryWithNoLockDbCommandInterceptor.cs