給 EF Core 查詢?cè)黾?With NoLock

Intro

EF Core 在 3.x 版本中增加了 Interceptor，使得我們可以在發(fā)生低級(jí)別數(shù)據(jù)庫(kù)操作時(shí)作為 EF Core 正常運(yùn)行的一部分自動(dòng)調(diào)用它們。 例如，打開連接、提交事務(wù)或執(zhí)行命令時(shí)。

所以我們可以自定義一個(gè) Interceptor 來記錄執(zhí)行的 sql 語(yǔ)句，也可以通過 Interceptor 來實(shí)現(xiàn) sql 語(yǔ)句的執(zhí)行。

這里我們可以借助 Interceptor 實(shí)現(xiàn)對(duì)于查詢語(yǔ)句的修改，自動(dòng)給查詢語(yǔ)句加 (WITH NOLOCK)，WITH NOLOCK 等效于 READ UNCOMMITED（讀未提交）的事務(wù)級(jí)別，這樣會(huì)造成一定的臟讀，但是從效率上而言，是比較高效的，不會(huì)因?yàn)閯e的事務(wù)長(zhǎng)時(shí)間未提交導(dǎo)致查詢阻塞，所以對(duì)于大數(shù)據(jù)場(chǎng)景下，查詢 SQL 加 NOLOCK 還是比較有意義的

NoLockInterceptor

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

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 的使用

在注冊(cè) DbContext 服務(wù)的時(shí)候，可以配置 Interceptor，配置如下：

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

使用效果

通過 loggerFactory 記錄的日志查看查詢執(zhí)行的 sql 語(yǔ)句

image

可以看到查詢語(yǔ)句自動(dòng)加上了 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