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,
OUTPUTreturns the new version. - If somebody changed it → the
WHEREmatches 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
ETagheader onGET, requireIf-MatchonPUT, 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!.