Written by Marvin Hsu
on May 6, 2024

SQL Optimistic Lock in .Net

@Georg Bommeli

Once again, I've encountered a concurrency issue recently. The data I attempted to modify didn't yield the desired results. There's evidence showing that more than two applications accessed this data simultaneously. Identifying the application exhibiting conflicting behavior is really difficult. Optimistic locking is a solution for this issue.

What is Optimistic Lock and How to Implement in SQL Database

Optimistic locking is a common mechanism used to maintain data consistency in databases. It assumes that data is typically not modified simultaneously. Therefore, it only ensures consistency when updating data, rather than locking it before accessing.
One fundamental approach to implementing optimistic locking involves associating each piece of data with a version identifier. Make sure the version as you expected when you want to update the data. For example:

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 is a special value in PostgreSQL which represents row version. The sample code will not update any data if the target row has been changed by other applications.".

Implement in .Net with EFCore

Before starting this section, let's reintroduce Xmin. In PostgreSQL, every row of data has its own metadata. Xmin is a value that represents the transaction ID for the row and changes when the row is updated. Xmin is particularly suitable for optimistic locking.

Implementing optimistic locking with EFCore is straightforward. First, we need to set up the DbContext model:

public class LockedExampleRow
{
    public Guid Id { get; set; }
    public string Name { get; set; } = string.Empty;
    //// use uint to map 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");

            //// set RowVersion property mapping to xmin
            e.Property(p => p.RowVersion)
                .IsRowVersion();
        });
    }
}

When we retrieve data using EFCore, PostgresTestDbContext will cache the xmin value. After updating the data using the SaveChangesAsync() method, EFCore will automatically apply the row version check.

Implement in .Net with Dapper

Using Dapper for implementing this can be somewhat cumbersome. Here's an example program:

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 });

This code snippet demonstrates the usage of Dapper to implement optimistic locking. However, Dapper does not support the uint type directly, so you would need to use int for the RowVersion property. Additionally, you need to manage the xmin value yourself and use it as a condition when updating the row.

End

Optimistic locking proves highly effective in addressing concurrency problems and often suffices for various scenarios. I've included sample tests for both PostgreSQL and MSSQL cases in my repository here. Next time, I will share the challenges I encounter when trying to implement this with Rust.

Top