Concurrency Without rowversion: Application Tokens, Locking Behavior, WAL Awareness, and Retries

Learning outcomes

1. Two different concurrency systems are operating at once

ServiceHub already protects WorkOrder with an application-managed Guid Revision. That is EF optimistic concurrency: the original token becomes part of the write predicate and zero affected rows becomes DbUpdateConcurrencyException. SQLite locking is different. It controls concurrent access to the database file and can produce busy/locked waits/errors before EF’s token predicate even gets a chance to execute.

2. SQLite has no database-generated rowversion; keep Revision application-managed

The SQLite provider explicitly lists database-generated concurrency tokens as unsupported. The course mapping therefore remains:

builder.Property(x => x.Revision)    .HasColumnName("revision")    .IsConcurrencyToken();

// Existing Chapter 06 domain behavior.workOrder.ReviseSummary("Escalated after field inspection");await db.SaveChangesAsync(ct);

UPDATE "work_orders"SET "summary" = @p0,    "revision" = @p1WHERE "work_order_id" = @id  AND "revision" = @original_revisionRETURNING 1;

Do not map Revision as a SQL Server-style rowversion. SQLite has no engine feature that automatically changes such a token on every row update.

3. Reproduce a true optimistic conflict with two short-lived contexts

A deterministic lost-update test must let both contexts read the same original revision before the first write commits.

await using var a = factory.CreateDbContext();await using var b = factory.CreateDbContext();var fromA = await a.WorkOrders.SingleAsync(w => w.Id == id, ct);var fromB = await b.WorkOrders.SingleAsync(w => w.Id == id, ct);var commonRevision = fromA.Revision;Debug.Assert(fromB.Revision == commonRevision);fromA.ReviseSummary("Writer A");await a.SaveChangesAsync(ct);fromB.ReviseSummary("Writer B");try{    await b.SaveChangesAsync(ct);    throw new InvalidOperationException("Expected an optimistic concurrency conflict.");}catch (DbUpdateConcurrencyException){    // Expected: writer A changed the stored revision first.}

This is not a lock timeout. Writer B successfully reaches an UPDATE whose original revision no longer matches the stored row. Chapter 13’s merge/retry policy applies.

4. SQLite serializes pending writes even when the application has many contexts

SQLite allows only one transaction to have changes pending at a time. A second writer may wait until the first transaction releases its lock and then continue, or time out. More DbContext instances do not create server-style parallel writers because they still coordinate through the same database file.

var cs = "Data Source=servicehub-sqlite-deepdive.db;Default Timeout=1";using var first = new SqliteConnection(cs);using var second = new SqliteConnection(cs);first.Open();second.Open();using var tx = first.BeginTransaction();using (var hold = first.CreateCommand()){    hold.Transaction = tx;    hold.CommandText = "UPDATE work_orders SET customer_name = customer_name WHERE work_order_id = $id";    hold.Parameters.AddWithValue("$id", id);    hold.ExecuteNonQuery();}// A write on 'second' can wait and eventually surface SqliteException// if the first transaction holds the write lock beyond Default Timeout.

Capture SqliteException.SqliteErrorCode and SqliteExtendedErrorCode. Do not catch every database exception and label it an optimistic conflict.

5. WAL improves concurrency shape, not writer multiplicity

Write-ahead logging (WAL) lets readers continue against a stable snapshot while a writer appends to the WAL file in many common workloads. It often reduces reader/writer interference, but SQLite still serializes writes. Microsoft.Data.Sqlite recommends WAL for concurrent applications and notes that EF-created databases enable WAL by default; verify the actual journal mode rather than assuming.

await using var connection = new SqliteConnection(    "Data Source=servicehub-sqlite-deepdive.db");await connection.OpenAsync(ct);await using var command = connection.CreateCommand();command.CommandText = "PRAGMA journal_mode = WAL;";var mode = (string?)await command.ExecuteScalarAsync(ct);Console.WriteLine($"journal_mode={mode}");

6. WAL adds operational files and checkpoint behavior

With WAL, SQLite can create -wal and -shm side files next to the main database. A “single file database” therefore does not mean every live deployment moment consists of exactly one file. Backups, container volume handling, disk monitoring and recovery procedures must account for SQLite’s documented backup/checkpoint behavior rather than copying the main file during arbitrary writes.

PRAGMA journal_mode;PRAGMA wal_checkpoint(PASSIVE);

Do not force aggressive checkpoints from request code without understanding latency and writer impact. For a normal small application, SQLite defaults are often reasonable; changes require measurement.

7. Microsoft.Data.Sqlite already waits/retries busy/locked operations until timeout

When Microsoft.Data.Sqlite encounters busy/locked conditions it automatically retries until the command timeout is reached. The default command timeout is 30 seconds; a value of 0 disables the timeout. Default Timeout also governs implicit commands such as transaction begin. An application-level retry loop layered blindly on top can multiply latency and repeat non-idempotent work.

var builder = new SqliteConnectionStringBuilder{    DataSource = "servicehub-sqlite-deepdive.db",    ForeignKeys = true,    DefaultTimeout = 5};services.AddDbContext<ServiceHubContext>(options =>    options.UseSqlite(builder.ToString()));

Five seconds is a lab value, not a universal recommendation. In production, choose timeouts from user/request budgets, transaction length and measured contention.

8. Retry policy differs for a lock wait and a business conflict

A busy error may be transient infrastructure contention. A DbUpdateConcurrencyException says the database state changed since this unit of work read it. Retrying the second case without merge/reload policy can overwrite a newer decision.

9. Failure case: calling every writer collision DbUpdateConcurrencyException

A test starts two long transactions and assumes the loser will throw DbUpdateConcurrencyException. Instead SQLite reports busy/locked because the second writer cannot acquire the file/database lock. The test has measured engine contention, not EF token conflict.

Repair: use ordered commits for the optimistic-token test so both actors read first and writer B executes only after writer A commits. Use a separate lock-contention test with an intentionally held transaction and short timeout. Assert different exception types and log evidence.

10. Mandatory lab: prove both concurrency mechanisms independently

1. Record SQLite version, journal mode and timeout settings.
2. Run the deterministic two-context Revision race and assert exactly one DbUpdateConcurrencyException.
3. Log original/current/database revisions from the conflict entry before choosing store/client/merge policy.
4. Run a separate raw-connection lock test: hold one write transaction, attempt a second write, and record wait duration plus SQLite error codes.
5. Enable/verify WAL on the disposable database and repeat one reader-during-writer observation; do not claim it enables concurrent writers.
6. Shorten the first transaction and re-measure contention before adding any application retry loop.
7. Reset/delete the disposable database and any -wal/-shm files after all connections close.

11. Production judgment and bridge

Use optimistic tokens for business lost-update protection and SQLite timeout/WAL/transaction design for file-level concurrency. They solve different problems. Keep transactions short, capture busy/locked metrics, avoid shared DbContext concurrency, and test the actual topology. WAL can improve an embedded workload dramatically, but it does not turn SQLite into a multi-writer database server. Lesson 4 now examines another set of abstraction leaks: JSON, date/time, decimal, collation, foreign-key state and translated functions.

Authoritative references

Concurrency requires EF token semantics plus actual SQLite transaction/journal behavior.

• Handling concurrency conflicts - EF Core — optimistic token predicates and conflict resolution
• SQLite provider limitations - EF Core — database-generated concurrency-token limitation
• Transactions - Microsoft.Data.Sqlite — single pending writer and SQLite isolation behavior
• Database errors - Microsoft.Data.Sqlite — busy/locked automatic retry and timeout behavior
• Connection strings - Microsoft.Data.Sqlite — Default Timeout, Foreign Keys, pooling and shared-cache guidance
• Write-ahead logging - SQLite — engine WAL behavior, checkpoints and concurrency model