Offline Concurrency Control

Offline Concurrency Control

Purpose

Handle the concurrency that database transactions cannot reach. A transaction protects a unit of work measured in milliseconds; the business problem is two people editing the same order over ten minutes. No isolation level addresses that, and reaching for one is the most common wrong turn in this area.

The second failure this prevents is treating a conflict as an infrastructure error: an OptimisticLockException surfacing as a 500 with a stack trace, or being silently retried so that the later write wins after all — which reintroduces exactly the lost update the version column was added to stop.

The problem, precisely

t0  User A reads order v7            t0  User B reads order v7
t1  ... thinks for 4 minutes         t1  edits quantity, saves → v8
t2  edits address, saves → writes over v8 with data derived from v7

Nothing here is a database anomaly: both writes are perfectly serialisable transactions. The loss happens between them, in application time. The four patterns below are the available answers.

The four patterns

Optimistic offline lock    detect the conflict at write time by comparing a
                           version. No lock across think time. Conflict is a business
                           outcome to present, not an error to swallow.

Pessimistic offline lock   prevent the conflict by recording ownership before
                           the edit begins. Needs an owner, an acquisition
                           time and abandonment recovery, because owners crash.

Coarse-grained lock        one version or lock for a whole aggregate, so
                           related changes share one concurrency boundary;
                           independent edits may conflict spuriously.

Implicit lock              the mechanism is applied by the framework or a
                           base class rather than by each developer, so it
                           is harder to omit — at the cost of being
                           invisible when it fires.

Workflow

1. Establish that the conflict spans transactions. If both writes are in one transaction, this is an isolation or row-locking question (enterprise-transactions), not an offline one.
2. Measure or estimate the conflict rate on the actual data. Two users editing the same order or jobs touching the same summary row have workload-dependent overlap. Combine observed conflict frequency with the cost of discarded work and waiting.
3. Choose the lock granularity from the invariant, not from the table layout: whatever must stay consistent together should be versioned together.
4. Design the conflict experience before the mechanism. What does the user see, and what can they do about it? A pattern that produces an unusable error is not implemented.
5. Make the mechanism implicit once chosen — a mapped superclass, a repository base, a framework feature — and audit bypass paths such as bulk/native writes. Make it observable so it can still be diagnosed.
6. Verify with a concurrent test, not by reasoning. Two threads, real transactions, synchronized after both load the same version, asserting exactly one commits.

Decision rules

Conflicts are rare; users can redo the work; edits are short
        → optimistic. Default choice; costs one column and one branch.

Conflicts are frequent, or the work lost on conflict is expensive
(a long form, a document, a manual reconciliation)
        → pessimistic. The user is told up front the record is busy,
          instead of after the effort is spent.

Conflicts are frequent AND the work is cheap to redo
        → consider optimistic merge or retry when intent remains valid on fresh state.

Several people must work on different parts of one consistent whole
        → coarse-grained lock on the aggregate. Accept that they will
          conflict or wait; that is the invariant's concurrency boundary.

An unattended process (batch, integration) competes with users
        → optimistic for the process too; retry only valid intent in fresh
          transactions. Any pessimistic checkout needs abandonment recovery.

The mechanism can be forgotten on a new write path
        → make it implicit, and add a test that fails when a versioned
          type is written by a path that bypasses it.

Rules

• Optimistic locking detects, it does not prevent. Its value is entirely in what happens next: a conflict must reach the user or the calling system as a meaningful outcome ("this order changed while you were editing; here is what changed"), never as a 500 and never as a silent overwrite.
• Do not blindly retry an optimistic conflict. A retry that re-reads and re-applies the user's intent may be correct after domain revalidation and effect deduplication. A retry that re-applies the user's stale data is a lost update with extra steps, and it is the most common misuse of @Retryable in this area.
• A version column must be checked in the WHERE clause of the update and the update's affected-row count must be tested. Normal versioned entity writes get this from the ORM; hand-written SQL and bulk updates need explicit participation. Incrementing the version invalidates old snapshots, but does not replace a predicate protecting the bulk operation's own expected state (orm-behavioral-patterns).
• Pessimistic offline locks need ownership and abandonment recovery. A lease uses acquisition time, expiry and safe renewal; a durable checkout may instead require explicit release plus an audited administrative recovery procedure. Expiry is valuable but unsafe if work can outlive it without fencing, because two owners may then act concurrently.
• Do not implement a pessimistic offline lock with a database transaction held open across requests. It holds a pooled connection for a human's thinking time, and it will exhaust the pool long before it will protect data.
• Lock granularity follows the invariant. Versioning rows independently reduces conflicts but can permit combinations that violate an aggregate-wide invariant. One root version protects the invariant but can create false conflicts between independent edits (domain-logic-organization).
• Coarse granularity trades throughput for correctness, and the trade is real: one version on a hot aggregate makes its writers compete. If that hurts, measure contention and reconsider boundaries only where the required invariant remains enforceable.
• Implicit locking is a safety property, not a convenience. Its cost is diagnosability: when a conflict fires, the reason is in a superclass or an interceptor and not in the code being read. Pay that cost back with logging that names the entity, the version expected and the version found when known; a later read observes a later state.
• Optimistic locking and idempotency solve different problems and are frequently confused. Versioning stops a stale write; an idempotency key stops a duplicate write. After the first successful update increments the version, a duplicate carrying the old version normally fails optimistic locking rather than returning the original result (idempotency).
• Test concurrency with concurrency. A unit test with a mocked repository cannot observe a lost update; two threads against a real database can.

Before proposing a change, inspect the Java toolchain, ORM/provider, database dialect and isolation level, client version contract and all affected write paths. Return the chosen concurrency boundary, conflict/recovery behavior and a test exposing stale-client or stale-owner writes. Treat missing mapping or database evidence as a reason to keep the implementation recommendation conditional, not to assume a generic SQL/JPA guarantee.

References

• Optimistic and pessimistic offline locks — partial Java/JPA examples, version-check SQL, conflict presentation and merge, conditional retry, a lease protocol with ownership validation, and reproducible integration-test recipes. Read when implementing or reviewing either mechanism.
• Granularity, implicit locks and their failure modes — choosing what to version together, root-version bumping for child changes, contention and deadlock arising from lock ordering across aggregates, making locking implicit without making it invisible, bulk-write bypasses and cache-related stale reads. Read when conflicts are frequent, spurious, or absent when they should not be.