Malekbenz

Hi, I'm MalekBenz. I author this blog, I'm FullStack Developer, create courses & love learning, writing, teaching technology. ( Dotnet , Javascript, ASP.NET , NodeJS, SQL Server )

Sql server Optimistic Concurrency with rowversion - From Dapper to React

Two support agents open the same order. The first one changes the status to Shipped and saves. The second one, still looking at the old screen, adds a note and saves too — and the status silently goes back to Paid. No error, no warning, nobody knows. This is the lost update problem. Let’s fix it end to end: SQL Server, Dapper, an ASP.NET Core API and a React screen with RTK Query.

The lost update problem

Agent A                               Agent B
───────                               ───────
GET order 42  (Status = Paid)
                                      GET order 42  (Status = Paid)
PUT order 42  (Status = Shipped)  ✅
                                      PUT order 42  (Status = Paid, Notes = "Called customer")  ✅
                                      
Result: Status = Paid  ❌  Agent A's change is lost

Transactions don’t help here. Each UPDATE is its own short transaction, and both succeed. The problem is that Agent B’s update was based on data that was already stale.

There are two ways to deal with it:

  Pessimistic Optimistic
Idea Lock the row while the user is editing Let everybody edit, check for changes on save
How UPDLOCK / HOLDLOCK inside a long transaction A version column checked in the WHERE
Cost Locks held for minutes, blocking, timeouts One extra column, conflicts handled in the UI
Fits Very short server-side operations Web apps (users take time, HTTP is stateless)

In a web app, a user can keep a screen open for 20 minutes. You can’t hold a lock that long. Optimistic concurrency is the right tool.


rowversion explained

SQL Server has a special column type for this:

ALTER TABLE Orders ADD RowVersion ROWVERSION NOT NULL;
  • It’s an 8-byte binary value, generated by SQL Server.
  • It changes automatically on every update of the row — you never set it yourself.
  • It’s unique in the database and always increasing (a database-wide counter).
  • It has nothing to do with time, despite its old name timestamp (deprecated, don’t use it).
SELECT Id, Status, RowVersion FROM Orders WHERE Id = 42;
-- 42 | Paid | 0x00000000000007D1

UPDATE Orders SET Notes = 'test' WHERE Id = 42;

SELECT Id, Status, RowVersion FROM Orders WHERE Id = 42;
-- 42 | Paid | 0x00000000000007D5   ← changed

The rule: when you read a row, you read its version. When you update it, you say “only if the version is still the one I read”.


Update with version check

UPDATE Orders
SET    Status = @Status,
       Notes  = @Notes
OUTPUT inserted.RowVersion
WHERE  Id = @Id
  AND  RowVersion = @RowVersion;
  • If nobody changed the row → 1 row updated, OUTPUT returns the new version.
  • If somebody changed it → the WHERE matches nothing → 0 rows, nothing is overwritten.

Replay the scenario:

Agent A: UPDATE ... WHERE Id = 42 AND RowVersion = 0x...7D1   → 1 row  ✅ new version 0x...7D9
Agent B: UPDATE ... WHERE Id = 42 AND RowVersion = 0x...7D1   → 0 rows ❌ conflict detected

0 rows can mean two things: the row was changed, or the row was deleted. We’ll make the difference in the repository.


The Dapper repository

public record OrderDto(int Id, int CustomerId, string Status, string? Notes, decimal Total, byte[] RowVersion);

public record UpdateOrderRequest(string Status, string? Notes, byte[] RowVersion);

public enum UpdateResult { Updated, Conflict, NotFound }

public class OrderRepository
{
    private readonly IDbConnectionFactory _factory;

    public OrderRepository(IDbConnectionFactory factory) => _factory = factory;

    public async Task<OrderDto?> GetAsync(int id)
    {
        using var connection = _factory.Create();

        return await connection.QuerySingleOrDefaultAsync<OrderDto>(@"
            SELECT Id, CustomerId, Status, Notes, Total, RowVersion
            FROM Orders WHERE Id = @Id", new { Id = id });
    }

    public async Task<(UpdateResult Result, byte[]? NewVersion)> UpdateAsync(int id, UpdateOrderRequest request)
    {
        using var connection = _factory.Create();

        var newVersion = await connection.QuerySingleOrDefaultAsync<byte[]>(@"
            UPDATE Orders
            SET    Status = @Status, Notes = @Notes
            OUTPUT inserted.RowVersion
            WHERE  Id = @Id AND RowVersion = @RowVersion;",
            new
            {
                Id = id,
                Status = new DbString { Value = request.Status, IsAnsi = true, Length = 20 },
                request.Notes,
                request.RowVersion
            });

        if (newVersion is not null)
            return (UpdateResult.Updated, newVersion);

        // 0 rows: was it changed or deleted?
        var exists = await connection.ExecuteScalarAsync<bool>(
            "SELECT CAST(COUNT(1) AS BIT) FROM Orders WHERE Id = @Id", new { Id = id });

        return (exists ? UpdateResult.Conflict : UpdateResult.NotFound, null);
    }
}

Dapper maps ROWVERSION to byte[] with no extra configuration. And because System.Text.Json serializes byte[] as a base64 string, the version travels to the browser and back without any custom code:

{ "id": 42, "status": "Paid", "notes": null, "total": 99.90, "rowVersion": "AAAAAAAAB9E=" }

The ASP.NET Core API

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddSingleton<IDbConnectionFactory, SqlConnectionFactory>();
builder.Services.AddScoped<OrderRepository>();

var app = builder.Build();

app.MapGet("/api/orders/{id:int}", async (int id, OrderRepository repo) =>
    await repo.GetAsync(id) is { } order ? Results.Ok(order) : Results.NotFound());

app.MapPut("/api/orders/{id:int}", async (int id, UpdateOrderRequest request, OrderRepository repo) =>
{
    var (result, newVersion) = await repo.UpdateAsync(id, request);

    return result switch
    {
        UpdateResult.Updated  => Results.Ok(new { rowVersion = newVersion }),
        UpdateResult.NotFound => Results.NotFound(),
        _ => Results.Problem(
                title: "The order was changed by someone else.",
                statusCode: StatusCodes.Status409Conflict,
                extensions: new Dictionary<string, object?> { ["current"] = await repo.GetAsync(id) })
    };
});

app.Run();

On a conflict we return 409 Conflict with a standard ProblemDetails body, and we include the current version of the order so the UI can show what changed.

Prefer the HTTP standard? You can send the version as an ETag header on GET, require If-Match on PUT, and return 412 Precondition Failed on mismatch. Same idea, different transport. If you call the API from another domain, don’t forget to expose the header in CORS: .WithExposedHeaders("ETag").


The React side with RTK Query

The API slice

// features/orders/ordersApi.js
import { createApi, fetchBaseQuery } from '@reduxjs/toolkit/query/react';

export const ordersApi = createApi({
  reducerPath: 'ordersApi',
  baseQuery: fetchBaseQuery({ baseUrl: '/api' }),
  tagTypes: ['Order'],
  endpoints: (builder) => ({
    getOrder: builder.query({
      query: (id) => `orders/${id}`,
      providesTags: (result, error, id) => [{ type: 'Order', id }],
    }),
    updateOrder: builder.mutation({
      query: ({ id, ...body }) => ({ url: `orders/${id}`, method: 'PUT', body }),
      invalidatesTags: (result, error, { id }) => [{ type: 'Order', id }],
    }),
  }),
});

export const { useGetOrderQuery, useUpdateOrderMutation } = ordersApi;

invalidatesTags means that after every update attempt — success or conflict — RTK Query refetches the order, so the screen gets the latest data and the latest rowVersion.

The edit screen

// features/orders/EditOrder.jsx
import { useEffect, useState } from 'react';
import { useGetOrderQuery, useUpdateOrderMutation } from './ordersApi';

export function EditOrder({ id }) {
  const { data: order, isLoading } = useGetOrderQuery(id);
  const [updateOrder, { isLoading: isSaving }] = useUpdateOrderMutation();
  const [form, setForm] = useState({ status: '', notes: '' });
  const [conflict, setConflict] = useState(null);

  // Fill the form when the order is loaded
  useEffect(() => {
    if (order) setForm({ status: order.status, notes: order.notes ?? '' });
  }, [order]);

  const save = async () => {
    try {
      await updateOrder({ id, ...form, rowVersion: order.rowVersion }).unwrap();
      setConflict(null);
    } catch (err) {
      if (err.status === 409) setConflict(err.data.current);
      else throw err;
    }
  };

  if (isLoading) return <p>Loading…</p>;

  return (
    <div>
      {conflict && (
        <div className="alert alert-warning">
          This order was changed by someone else (status is now <b>{conflict.status}</b>).
          The form now shows the latest version — check it and save again.
        </div>
      )}

      <select value={form.status} onChange={(e) => setForm({ ...form, status: e.target.value })}>
        <option>New</option>
        <option>Paid</option>
        <option>Shipped</option>
      </select>

      <textarea value={form.notes} onChange={(e) => setForm({ ...form, notes: e.target.value })} />

      <button onClick={save} disabled={isSaving}>Save</button>
    </div>
  );
}

What happens for Agent B now:

1. Clicks Save with the old rowVersion
2. API returns 409 + the current order
3. The warning appears: "status is now Shipped"
4. RTK Query refetches the order → the form shows the new data and the new rowVersion
5. Agent B adds the note again and saves → ✅ nothing is lost

Careful: in this simple version, step 4 replaces what Agent B typed. In a real app, keep the user’s text (for example, only refill the fields they didn’t touch) — losing someone’s typing is a lost update too!


The same thing with EF Core

If you use EF Core for writes, it supports this out of the box:

public class Order
{
    public int Id { get; set; }
    public string Status { get; set; } = "";
    public string? Notes { get; set; }

    [Timestamp]                       // or .IsRowVersion() in OnModelCreating
    public byte[] RowVersion { get; set; } = [];
}

EF adds AND [RowVersion] = @p to every UPDATE and DELETE, and throws when 0 rows are affected:

try
{
    db.Entry(order).Property(o => o.RowVersion).OriginalValue = request.RowVersion;
    order.Status = request.Status;
    order.Notes = request.Notes;
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    var current = await ex.Entries.Single().GetDatabaseValuesAsync();
    // current == null → deleted, otherwise → changed: return 409
}

The important line is setting the OriginalValue: the version must be the one the client read, not the one EF just loaded.


Key rules to remember

Rule Why
Add a ROWVERSION column to tables edited by users SQL Server maintains it for free
Always WHERE Id = @Id AND RowVersion = @RowVersion 0 rows = conflict, nothing is overwritten
Use OUTPUT inserted.RowVersion The client gets the new version without another query
Tell “changed” from “deleted” 409 vs 404 — different messages for the user
Send the version to the client and back (base64 / ETag) HTTP is stateless, the version is the only memory
Return 409 with the current data The UI can explain what changed
In RTK Query, invalidate the tag on every update The screen always gets the latest version
Never lose what the user typed A lost form is a lost update too

That’s it & see you soon!.

Comments