Pessimistic Offline Lock

The pattern

Lock the data up front. Before reading-and-modifying, the application acquires a lock that prevents other transactions from accessing the same data until the lock is released. The conflict is prevented, not detected.

Three implementations carry the pattern:

1. Database locks. SELECT … FOR UPDATE takes a row-level lock for the transaction; pg_advisory_xact_lock(int, int) takes a named lock keyed on integers. Released on commit or rollback.
2. Application-tier locks. A lock service (Redis, etcd, ZooKeeper) keyed on (aggregate_type, aggregate_id). Released explicitly or by TTL.
3. Framework-managed locks. The ORM or repository takes the lock on find(id). See Implicit Lock.

Where it fits

When conflicts are common and retries aren’t safe. First-writer-wins uniqueness. Financial transactions where a retry would double-charge. Invariants that span an aggregate and can’t tolerate a torn write.

It does not fit when the lock would outlive a database transaction — that’s where Pessimistic drifts into long-running business transaction territory, which is what Optimistic Offline Lock is for.

A worked example

The Footfall hourly-apply path in Storeboost needed to serialize concurrent same-hour writers across replicas. The fix is a transaction-scoped pg_advisory_xact_lock keyed off (namespace, hour) taken as the first statement inside the existing transaction.

From FootfallStoreLocationAggregateRepository.TryApplyCompletedHourAsync (branch fix/sb-api-new-1hf-footfall-race, commit 91dbfbbb0):

private const int FootfallProcessedHoursLockNamespace = unchecked((int)0x5B0FF00D);

public async Task<bool> TryApplyCompletedHourAsync(
    DateTime hourStartUtc,
    /* dictionaries of computed aggregates */,
    CancellationToken cancellationToken)
{
    await using var transaction = await _dbContext.Database.BeginTransactionAsync(cancellationToken);

    var hourKey = (int)(hourStartUtc.UtcDateTime
        - new DateTime(2026, 1, 1, 0, 0, 0, DateTimeKind.Utc)).TotalHours;
    await _dbContext.Database.ExecuteSqlRawAsync(
        "SELECT pg_advisory_xact_lock(@ns, @hour)",
        new object[]
        {
            new NpgsqlParameter("@ns", FootfallProcessedHoursLockNamespace),
            new NpgsqlParameter("@hour", hourKey),
        },
        cancellationToken);

    if (await _dbContext.FootfallStoreLocationProcessedHours
            .AnyAsync(row => row.HourStartUtc == hourStartUtc, cancellationToken))
        return false;

    await ReplaceHourCoreAsync(/* store/location/hour dictionaries */);
    _dbContext.FootfallStoreLocationProcessedHours.Add(
        FootfallStoreLocationProcessedHour.Create(hourStartUtc, appliedAtUtc));
    await _dbContext.SaveChangesAsync(cancellationToken);
    await transaction.CommitAsync(cancellationToken);
    return true;
}

The lock key is (namespace, hour) — unrelated hours don’t queue behind each other; _xact_lock (not _lock) releases on commit or rollback without an explicit unlock. The existing AnyAsync guard stays as a fast path for the “already applied” case, but the lock is what prevents the destructive race.

Tradeoffs

• The conflict is prevented — no retry, no user-visible surprise.
• Other transactions wait; hot paths serialise; deadlocks are possible if multiple locks are taken in different orders.
• Lock granularity matters: too coarse (whole table) blocks unrelated work; too fine (per row) reintroduces the same deadlocks.

When to use, when not to

Use when the invariant cannot tolerate a retry, or when the conflict is common enough that retries would dominate. Don’t use when the lock would outlive a database transaction — that’s Optimistic’s territory. Don’t use when the lock granularity is unclear — that’s Coarse-Grained’s problem.