.Net 的 SQL Optimistic Lock

最近又碰到並行的問題，資料改完以後結果跟預期的不一樣，而且看得出來有兩支以上的應用程式同時在動這筆資料，但要抓出到底是哪一支的行為有衝突真的很難，這種狀況用 optimistic lock 就可以解決。

什麼是 Optimistic Lock，以及怎麼在 SQL Database 中實作

Optimistic lock 是資料庫裡用來維持資料一致性很常見的一種手法，前提是資料通常不會有人同時改，所以存取之前不先鎖起來，等到要更新的時候才確認一致性。
最基本的作法就是幫每一筆資料掛上一個版本識別碼，要更新的時候先確認版本跟自己預期的一樣，像這樣：

DO $$
DECLARE TARGET_Id integer;
DECLARE X_MIN xid;
BEGIN
    SELECT id,xmin into TARGET_Id,X_MIN from table;

    update table
    set value = 'change_value'
    where id = TARGET_Id and xmin = X_MIN;
END $$;

xmin 是 PostgreSQL 裡一個特殊的值，代表 row 的版本，上面這段範例只要其他應用程式先動過目標 row，就不會更新到任何資料。

用 EFCore 在 .Net 中實作

開始之前先再介紹一次 xmin。PostgreSQL 裡每一筆 row 都有自己的 metadata，而 xmin 這個值代表 row 的 transaction ID，只要 row 一更新就會跟著變動，拿來做 optimistic lock 特別合適。

用 EFCore 實作 optimistic lock 很單純，先把 DbContext 的 model 設定好：

public class LockedExampleRow
{
    public Guid Id { get; set; }
    public string Name { get; set; } = string.Empty;
    //// 用 uint 對應 xmin
    public uint RowVersion { get; set; }
}

public class PostgresTestDbContext(string connectionString) : DbContext
{
    public DbSet<LockedExampleRow> LockedExampleRow { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseNpgsql(connectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<LockedExampleRow>(e =>
        {
            e.ToTable("locked_example_row");
            e.Property(p => p.Id)
                .HasColumnName("id")
                .HasDefaultValueSql("gen_random_uuid()");
            
            e.Property(p => p.Name)
                .HasColumnName("name");

            //// 設定 RowVersion 屬性對應到 xmin
            e.Property(p => p.RowVersion)
                .IsRowVersion();
        });
    }
}

用 EFCore 查詢資料的時候，PostgresTestDbContext 就會把 xmin 的值快取起來，之後呼叫 SaveChangesAsync() 更新資料，EFCore 會自動幫忙加上 row version 的檢查。

用 Dapper 在 .Net 中實作

換成 Dapper 就有點麻煩了，直接看範例：

await using var connection = new NpgsqlConnection(fixture.GetConnectionString());

const string findByIdSql = "SELECT id, name, xmin AS RowVersion FROM example_row WHERE id = @Id";
var row = (await connection.QueryFirstOrDefaultAsync<LockedExampleRow>(findByIdSql, new { Id = id }))!;

const string updateWithLockSql = "UPDATE example_row SET name = @Name WHERE id = @Id AND xmin = @RowVersion";
affectRowCount = await connection.ExecuteAsync(updateWithLockSql, new { Name = "expect concurrency", Id = id, RowVersion = (int)row.RowVersion });

這段就是用 Dapper 做 optimistic lock 的樣子，不過 Dapper 沒有直接支援 uint 型別，所以 RowVersion 這個屬性得改用 int，另外 xmin 的值也要自己管理，更新的時候再帶進去當條件。

End

Optimistic lock 處理並行問題滿有效的，大部分的情境這樣就夠用了，PostgreSQL 跟 MSSQL 兩種案例的測試範例我都放在 repo 裡了。下次來分享改用 Rust 實作的時候遇到了哪些麻煩。