EF死锁处理之：给 EF Core 查询增加 With NoLock

EF版本6.x.x

在项目里添加一个拦截器Interceptor

public class WithNoLockInterceptor: DbCommandInterceptor
    {
        private static readonly Regex TableAliasRegex =
            new Regex(@"(?<tableAlias>(FROM|JOIN) \[[a-zA-Z]\w*\] AS \[[a-zA-Z]\w*\](?! WITH \(NOLOCK\)))",
                RegexOptions.Multiline | RegexOptions.Compiled | RegexOptions.IgnoreCase);
        [ThreadStatic]
        public static bool WithNolock = false;
        public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
        {
            if (WithNolock)
            {
                command.CommandText = TableAliasRegex.Replace(
                command.CommandText,
                "${tableAlias} WITH (NOLOCK)"
                );
            }
            return base.ScalarExecuting(command, eventData, result);
        }

        public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result,
            CancellationToken cancellationToken = new CancellationToken())
        {
            if (WithNolock)
            {
                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)
        {
            if (WithNolock)
            {
                command.CommandText = TableAliasRegex.Replace(
                command.CommandText,
                "${tableAlias} WITH (NOLOCK)"
                );
            }
            return result;
        }

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

 

添加一个NoLock的静态类

public static class WithLockUtility 
    {
        /// <summary>
        /// 部分查询不使用锁查询的，可以调用此扩展（默认全局查询不使用with(nolock)）
        /// 参考：https://github.com/aspnetboilerplate/aspnetboilerplate/issues/1637/
        /// 示例：
        /// 1、query.OrderByCustom(filters.orderFields).Select({...}).NoLocking(querable => querable.PagingAsync(filters.page, filters.rows));
        /// 2、repository.EntitiesAsNoTracking.Select(...).NoLocking(item=>item.FirstOrDefaultAsync());
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="query"></param>
        /// <param name="queryAction"></param>
        /// <returns></returns>
        public static TResult NoLocking<T, TResult>(this IQueryable<T> query, Func<IQueryable<T>, TResult> queryAction)
        {
            WithNoLockInterceptor.WithNolock = true;

            TResult queryableResult = default(TResult);
            try
            {
                queryableResult = queryAction(query);
            }
            finally
            {
                WithNoLockInterceptor.WithNolock = false;
            }
            return queryableResult;
        }
    }

在dbcontext引用的地方引用拦截器

services.AddDbContext<Context>(options =>  
            options.UseSqlServer(connectionString)
            .AddInterceptors(new WithNoLockInterceptor())
            );

 

使用NoLocking示例

_dbContext.IntegrationLogs
                  .Where(log => log.LogId == request.LogId)
                  .Select(log => new IntegrationLogDto
                  {
                      Id = log.LogId,
                      Level = log.Level,
                      Params = log.Params,
                      Message = log.Message,
                      CreatedOn = log.CreatedOn
                  })
                  .Nolocking(querable => querable.ToList());

 

 

参考地址：

http://www.manongjc.com/detail/24-bxtwwksfpeezfjg.html

https://github.com/aspnetboilerplate/aspnetboilerplate/issues/1637/