Optimistic concurrency with a SQLite token

Concurrency evidence must be explicitly configured and advanced

An original concurrency token is evidence about the row version a context read. EF includes that original value in the predicate for a protected update or delete; a changed stored token can make the update affect no row. SQLite does not generate SQL Server rowversion values, so this lesson uses a deliberately configured application-managed integer. Every relevant writer must advance it. Calling a property Version without configuring it, or configuring it but never changing it, would not establish the intended stale-write protection. The example makes both pieces visible before triggering a real conflict.

Load the same original version in two separate units

The fixture seeds Initial at version one, then two distinct contexts load that row sequentially. The winner changes Name, increments Version and saves version two. The stale context still owns original version one, changes its proposed values and attempts to save. The resulting DbUpdateConcurrencyException is checked for the expected entity entry. A fresh no-tracking query verifies Winner:v2. No simultaneous operation shares a context or the connection. That sequence supplies genuine stale-original evidence without needing a timing race, a fabricated exception or a claim that two SQL transactions executed concurrently in this classroom fixture.

using System;
using System.Linq;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;

await using var connection = new SqliteConnection("Data Source=:memory:;Foreign Keys=True");
await connection.OpenAsync();
var options = new DbContextOptionsBuilder<LabDb>().UseSqlite(connection).Options;
await using (var seed = new LabDb(options)) { await seed.Database.EnsureCreatedAsync(); seed.Items.Add(new Item { Name = "Initial", Version = 1 }); await seed.SaveChangesAsync(); }
await using var first = new LabDb(options); await using var second = new LabDb(options);
var winner = await first.Items.SingleAsync(); var stale = await second.Items.SingleAsync();
winner.Name = "Winner"; winner.Version++; await first.SaveChangesAsync();
stale.Name = "Stale"; stale.Version++; bool conflict = false;
try { await second.SaveChangesAsync(); } catch (DbUpdateConcurrencyException ex) { conflict = ex.Entries.Count == 1; }
await using var verify = new LabDb(options); var stored = await verify.Items.AsNoTracking().SingleAsync();
Check(conflict && stored.Name == "Winner" && stored.Version == 2, "original token prevents stale write"); Console.WriteLine("conflict:detected"); Console.WriteLine(stored.Name + ":v" + stored.Version);

static void Check(bool condition, string name) { if (!condition) throw new InvalidOperationException("CHECK: " + name); }

Compare supplied versions without pretending to perform compare-and-swap

The .NET9 helper answers whether an expected integer equals a current integer. One and one produces True; one and two produces False. The comparison is a useful part of a proposed conflict policy, but it does not atomically read and write storage. Another writer could intervene between two ordinary application operations. The external provider test is what demonstrates EF’s original-token predicate and conflict exception. Keep the pure decision separated from database enforcement, and do not turn a local Version property into a pretend ORM that claims to reproduce affected-row concurrency semantics.

A conflict needs reconciliation, not blind repetition

After a conflict, the application must decide whether to reload, reject, merge or intentionally overwrite under a defined policy. Retrying the same stale values without refreshing the original token can repeat the conflict; replacing the token blindly can discard someone else’s edit. Database-generated tokens, application-generated GUIDs and integer counters also have different management responsibilities. This lesson’s integer increment is explicit and protected by the original-token check, but production counters need an overflow policy and every relevant writer must cooperate. Raw SQL or an older application version can bypass a convention that exists only in one service’s code.

Practice: test a deliberate conflict resolution on fresh data

Keep the original conflict assertion, then build a separate operation that reloads the current row and asks whether the stale user intention can still be applied. Use an independently specified merge rule rather than automatically choosing whichever value was read last. Add a case where a row is deleted between reading and saving, and classify that result separately from a duplicate insert. Verify final storage using another context after each scenario. A user-facing conflict message should identify an unresolved decision, not report success simply because an exception was caught and suppressed.

• Hint: original values and proposed values serve different roles during reconciliation.
• Hint: constraint violations on insert are not this token conflict mechanism.

Practice: add a result that preserves the user’s intention

Extend the portable version comparison to return a result containing Expected, Current and the proposed edit when a conflict is found. Test equal versions, a newer current version and an invalid counter policy independently. Preserve the proposed edit for a caller that might reconcile it later, but do not mutate a shared current value in the comparison function. That remains a pure application contract and cannot decide whether a real database update committed. The engine boundary should adopt the tested policy only after actual provider observations establish which token and affected-row facts it received.

• Hint: a conflict result can retain useful information without applying the rejected change.
• Hint: define how a counter can advance and what happens at its numeric boundary.

State what the stale-write test actually proves

The external run verifies a configured token, two original snapshots, a winning update, a rejected stale update and Winner:v2 in fresh SQLite storage. It does not establish SQL Server rowversion behavior, distributed locking, provider-independent retry safety or a universal merge algorithm. The portable practice checks equality on supplied integers only. A useful review names token generation, protected columns, writer cooperation and conflict resolution. Preserve those details in migrations and production adapters so a well-named property cannot silently become a concurrency guarantee that neither the model nor the database workflow actually implements.