Optimistic Offline Lock

The pattern

A version stamp on every protected record. Reads don’t lock; the application carries the version through the modify step. Writes check WHERE id = ? AND version = ?. If no row matches, somebody else committed first — the write is rejected and the transaction re-reads and retries. The conflict is detected, not prevented.

The version is monotonic — a counter, a timestamp, or a UUID. Two writers reading the same version is fine; one of them loses. The read-modify-write is one logical step carried by the application, not a strict database transaction. The retry re-reads and re-applies; a retry that just re-runs the original write is a lost update.

Where it fits

Long-running business transactions where holding a database lock is impractical: a multi-page form carrying data through page reloads, a mobile app syncing after going offline, a batch job that accumulates overnight. Cross-service updates that overlap without a shared lock service. Read-heavy workloads where writes would serialise under Pessimistic.

It does not fit invariants that cannot tolerate a retry — uniqueness on first write, financial double-spend, first-writer-wins auctions. Those need Pessimistic or a different mechanism.

A worked example

PCA report shares are mutated by read-modify-write flows that enrich a snapshot JSON column. The conflict is detected at commit by Postgres xmin (mapped through EF’s row-version token) and resolved by an explicit retry.

From PcaShareSnapshotConcurrency.TryUpdateAsync in the Storeboost monorepo:

public static async Task<int> TryUpdateAsync(
    /* dbContext, share, serializer, enrich callback */,
    CancellationToken cancellationToken)
{
    for (var attempt = 1; attempt <= MaxAttempts; attempt++)
    {
        var graph = serializer.Deserialize(share.SnapshotJson);
        var (updated, enriched) = await enrich(graph, cancellationToken);
        if (enriched == 0) return 0;

        share.ReplaceSnapshotJson(serializer.Serialize(updated));
        try
        {
            await dbContext.SaveChangesAsync(cancellationToken);
            return enriched;
        }
        catch (DbUpdateConcurrencyException) when (attempt < MaxAttempts)
        {
            await dbContext.Entry(share).ReloadAsync(cancellationToken);
        }
    }
    return 0;
}

DbUpdateConcurrencyException is EF Core’s typed translation of a Postgres xmin mismatch on UPDATE. The helper catches, reloads the latest state from the database, and tries again — the conflict is detected at commit time, never blocked ahead of time.

Tradeoffs

• No blocking, no deadlocks, scales across machines and services.
• The user sees the conflict; retries can lose work if the merge is wrong.
• High-conflict workloads livelock (every commit retries).
• Every write touches the version field; small but non-zero overhead.

When to use, when not to

Use when conflicts are rare and retries are cheap. Don’t use when the conflict is common (Pessimistic is cheaper there) or when the invariant forbids retry. Pair with Pessimistic for hot paths within an otherwise Optimistic system.