DbCommandInterceptor

若拿關鍵字 “EfCore With Lock” 去 Google，應該大多都是這個做法，透過正則表達式替換產生出來的SQL語法，在後面加上 With(NOLOCK)

1. 新增繼承DbCommandInterceptor的攔截器

public class WithNoLockDbCommandInterceptor : 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<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        command.CommandText = ReplaceCommandText(command);
        return result;
    }

    public override Task<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        command.CommandText = ReplaceCommandText(command);
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    private static string ReplaceCommandText(IDbCommand command)
    {
        return TableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
    }
}

從Startup為DB添加攔截器

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<TestDb>(builder =>
    {
        builder.UseSqlServer(Configuration.GetConnectionString("TestDb"))
            .AddInterceptors(new WithNoLockDbCommandInterceptor());
    });
}

簡單的 SQL 查詢

public List<Member> Get()
{
    return _db.Member.ToList();
}

public class Member
{
    public int Id { get; set; }

    public string Name { get; set; }

    public IEnumerable<MemberFile> Files { get; set; }
}

未加上攔截器的執行結果

執行後可以在Console看到EF產生的SQL(需要調整appsetting.json的LogLevel部分)

SELECT [m].[Id], [m].[Name]
FROM [dbo].[Member] AS [m]

加上攔截器的執行結果

結果看起來沒有問題，也可以正常的查詢DB

SELECT [m].[Id], [m].[Name]
FROM [dbo].[Member] AS [m] WITH (NOLOCK)

稍微複雜的查詢

加上了一個一對多的Table，並且join查詢

public List<MemberFile> Query()
{
    var member = _db.Member.Include(r => r.Files).First(r => r.Id == 1);
    return member.Files.ToList();
}

public class MemberFile
{
    public int Id { get; set; }

    public string Name { get; set; }

    public int MemberId { get; set; }

    [JsonIgnore]
    public Member Member { get; set; }
}

未加上攔截器的結果

SELECT [t].[Id], [t].[Name], [m0].[Id], [m0].[MemberId], [m0].[Name]
    FROM (
        SELECT TOP(1) [m].[Id], [m].[Name]
        FROM [dbo].[Member] AS [m]
        WHERE [m].[Id] = 1
    ) AS [t]
    LEFT JOIN [dbo].[MemberFile] AS [m0] ON [t].[Id] = [m0].[MemberId]
    ORDER BY [t].[Id], [m0].[Id]

加上攔截器的執行結果

這次產生出來的結果就造成了Exception，無法正常的查詢

SELECT [t].[Id], [t].[Name], [m0].[Id], [m0].[MemberId], [m0].[Name]
    FROM (
        SELECT TOP(1) [m].[Id], [m].[Name]
        FROM [dbo].[Member] AS [m] WITH (NOLOCK)
        WHERE [m].[Id] = 1
    ) AS [t] WITH (NOLOCK)
    LEFT JOIN [dbo].[MemberFile] AS [m0] WITH (NOLOCK) ON [t].[Id] = [m0].[MemberId]
    ORDER BY [t].[Id], [m0].[Id]

結論

簡單的查詢用這個方式是可以正常執行的，但若要執行複雜的查詢，就很有可能需要再調整攔截器裡替換的方式，考慮到EF可能會產生的各種神語法，我決定跳過這一題，尋覓其他可行方案

2. IsolationLevel（交易隔離等級）

SQL Server 可以透過設定隔離等級來讓查詢可以在資料被Lock的情況下中途讀取內容，相當於 With(NOLOCK)。

隔離等級有幾種，各個差異如下（預設為ReadCommitted）：

隔離等級	中途讀取
ReadUncommitted	O
ReadCommitted	X
RepeatableRead	X
Serializable	X
Snapshot	X

未指定交易隔離等級

先試著維持原本的程式碼，未加上攔截器，並且故意製造一個未完成的交易

begin transaction
update Member set Name = 'test' where id = 1;

接著透過啟用 DBCC TRACEON(3604) 查詢DB資訊，確認到有一筆update的交易進行中

接著執行對Member的查詢，再確認一次 DBCC TRACEON(3604)，會看到有正在 Wait的資料

而查詢方也是理所當然的timeout了

指定交易隔離等級

開啟交易，並且指定隔離等級為 ReadUncommitted

public List<MemberFile> Query()
{
    using (_db.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
    {
        return _db.Member.ToList();
    }
}

重複上面做的事情

執行查詢，可以看到查詢到的是未完成的交易更改的值

接著把剛才的交易 rollback

rollback  transaction

再重新查詢一次，就拿到了rollback後的值

結論

透過指定交易隔離等級的方式可以更容易的避免Lock導致查詢失敗等問題，更換資料庫時也不用為語法煩惱

---

好在不久前上了SkillTree的SQL Server相關課程，剛好彌補了對DB Lock知識的不足，在處理這部分問題時順暢了許多