Backend Concurrency Control: Optimistic Locking, Versions, and ETags

Two requests can read the same record, make different edits, and both report success while the data is still wrong. When the later write silently replaces the earlier one, the system has suffered a lost update. One user changes an address, another changes a credit limit from an older copy, and one valid change disappears without an error. The race is uncommon in manual testing but natural when browser tabs, mobile applications, background workers, and webhooks write the same resource.

This guide develops a practical concurrency-control design for backends: identify the invariant, use a version column and conditional update, expose the validator through HTTP ETag and If-Match, return actionable conflicts, distinguish optimistic locking from row locking, and test races deterministically. The goal is not to lock everything. It is to ensure that a legitimate update is never lost silently.

1. How does a lost update happen?

Assume a customer profile has an email, a phone, and version = 8. Two clients read that state. Client A changes the email and saves. Client B still holds the older snapshot, changes the phone, and submits the whole object. If the backend only runs UPDATE customers SET email = ?, phone = ? WHERE id = ?, request B can restore the old email and erase A's successful change.

1. A and B both read version 8.
2. A updates from version 8; the database stores version 9.
3. B writes a snapshot based on version 8 without declaring that precondition.
4. The database accepts B's write, and part of A's change is lost.

A transaction alone does not automatically prevent this outcome. Each request may run inside a valid transaction, yet B's decision was formed from stale data before its transaction wrote. The application must protect the relationship “apply this update only if the state I read is still current.”

Sound concurrency control turns a write based on stale data into a visible conflict instead of a false success that destroys data.

2. Start with the invariant, not the lock type

Before choosing optimistic or pessimistic locking, state the invariant. A profile might permit independent fields to change concurrently. Inventory must never become negative. An order state may follow only valid transitions. A balance must reflect every ledger entry. Each invariant implies a different concurrency boundary.

Do not read data into the application, check a condition, and later update without a database predicate. Another request can always enter between the check and the write. Important invariants must be expressed through an atomic statement, constraint, unique index, or suitable transaction at the system of record.

3. The version-column compare-and-set pattern

The most common pattern adds a monotonically increasing integer to the record. A client reads both the data and its version. On write, the backend places the old version in the WHERE clause and increments it in the same statement. The affected-row count becomes the result of the compare-and-set operation.

UPDATE customer_profiles
SET display_name = :display_name,
    phone = :phone,
    version = version + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = :id
  AND version = :expected_version;

If one row is affected, the expected state was still current and the update succeeded. If zero rows are affected, the record may be missing, deleted, or at a newer version. The backend can perform a minimal follow-up read to classify the result, but it must not repeat the update against the new version without understanding the user's intent.

begin transaction
  affected = update ... where id = input.id and version = input.version
  if affected == 0:
    current = select id, version from customer_profiles where id = input.id
    if current is null: return NOT_FOUND
    return CONFLICT(current.version)
commit

The server should own the version value. A client supplies only the expected version, never the next one. A timestamp token may introduce resolution, normalization, or same-tick update problems. A monotonically increasing integer is usually easier to compare, log, and test.

4. Carry the version over HTTP with ETag and If-Match

A REST API can represent the resource version as an ETag. A read response returns an opaque validator such as ETag: "customer-42-v8". The client retains it and sends If-Match: "customer-42-v8" with a later PUT, PATCH, or DELETE. The server performs the operation only while that validator matches.

GET /api/customers/42

HTTP/1.1 200 OK
ETag: "customer-42-v8"
Content-Type: application/json

{"id":42,"display_name":"An","phone":"090..."}

PATCH /api/customers/42
If-Match: "customer-42-v8"
Content-Type: application/json

{"phone":"091..."}

If the resource is already at version 9, the precondition is false. 412 Precondition Failed closely describes a failed HTTP precondition. An API that carries a version field in JSON may instead use 409 Conflict. Consistency and a documented recovery contract matter more than forcing every design into one status. If an endpoint requires protection against blind writes and the request omits If-Match, 428 Precondition Required clearly communicates the requirement.

An ETag must validate the representation or state protected by the operation; it is not a decorative string. Do not expose sensitive data inside it. A server may sign or encode internal structure, but clients should treat the token as opaque and return it unchanged.

5. PATCH does not solve concurrency by itself

Sending only changed fields through PATCH reduces the overwrite surface but does not prove an edit is still valid. Two users may change the same status, or one may change the customer type while another assigns a limit based on the old type. A small payload can still violate an invariant.

There are three common granularity choices. Whole-resource versioning is simple and safe but can report conflicts between genuinely independent fields. Aggregate versioning groups fields that share an invariant and is often the best balance. Per-field versioning reduces false conflicts but makes contracts, auditing, and merging more complex. Measure real conflict frequency before optimizing for finer granularity.

An endpoint accepting a complete object should avoid mass-assigning an old snapshot. Map only fields allowed by the command, validate transitions against current state, and still check the version. An explicit DTO prevents accidental overwrite and narrows the security surface.

6. Treat a conflict as a business result

An optimistic-lock conflict is expected behavior in a concurrent system, not an infrastructure fault deserving a 500 response. Return a stable machine-readable code, the affected resource, the version used by the client, the current version, and a recovery action. Do not include the full record if it contains fields the caller cannot access.

{
  "error": "resource_version_conflict",
  "resource": "customer_profile",
  "id": "42",
  "expected_version": 8,
  "current_version": 9,
  "action": "reload_and_review"
}

A user interface should preserve unsaved edits, load the new state, and help the user compare. Automatic merging is appropriate only when a domain rule defines it, such as adding two independent members to a set. Last-write-wins can be a valid policy for disposable data such as a temporary cursor position, but it should be an explicit choice rather than an accidental database behavior.

Do not blindly retry an optimistic conflict. Replaying the same payload with a new version can turn a decision made from old state into an invalid decision on new state. Automatic retry is safe only when the operation is commutative or the backend can recompute the entire decision from current data without changing its intent.

7. When is an atomic update better than a version check?

If an operation can be expressed entirely in one statement, an atomic conditional update is often simpler and stronger than a read-modify-write loop. Inventory can remain nonnegative with a direct predicate:

UPDATE inventory
SET available = available - :quantity,
    version = version + 1
WHERE sku = :sku
  AND available >= :quantity;

Zero affected rows may mean the SKU is absent or stock is insufficient; a follow-up read can classify the result. The application never reads a quantity, checks it, and writes it back. Similarly, a counter can use SET value = value + 1, while a transition can include WHERE status = 'pending'. The predicate directly expresses the business precondition.

A version remains useful for auditing and client synchronization, but it does not replace constraints. A unique index protects uniqueness, a foreign key protects references, and a check constraint protects a value domain. Put protection as close to the invariant as possible so correctness does not depend on every caller remembering the same rule.

8. How does optimistic locking differ from a row lock?

Optimistic locking holds no database lock while a user is thinking. It permits competition and detects a conflict at write time, which works well when conflicts are relatively rare or requests must remain independent. Pessimistic locking, such as selecting a row for update inside a transaction, blocks another transaction from changing that row until the lock is released.

Never keep a row lock while calling an external service, waiting for a user, or performing long computation. That lengthens transactions, exhausts connection pools, and propagates latency. If a workflow lasts seconds or minutes, use an expiring reservation, state machine, saga, or asynchronous job rather than a long-open transaction.

9. Multi-table aggregates and external side effects

An aggregate may span several tables, such as an order, its lines, and its total. Place the version on the root representing their shared invariant. A transaction changes child rows and increments the root version under the expected-version condition. If child changes do not advance the root, a client reading the aggregate cannot detect that its snapshot became stale.

For side effects such as email or event publication, do not call the external system before commit. Insert an outbox record in the same transaction as the conditional update and publish only after commit. A command idempotency key and an outbox message ID solve a different problem from a resource version: idempotency prevents duplicate execution of the same request, while versioning rejects a legitimate request formed from obsolete state.

begin transaction
  update orders
    set status = 'approved', version = version + 1
    where id = :id and status = 'pending' and version = :version
  require affected_rows == 1
  insert into outbox(event_id, aggregate_id, event_type, payload)
commit

If a command changes several independent aggregates, a single shared version can create unnecessary contention. Revisit aggregate boundaries and data ownership. Concurrency does not imply that every table belongs under a global lock.

10. Migrate a version column safely

Introduce optimistic locking to a running system through compatible stages. First add the column with an appropriate default and backfill if the database requires it. Deploy code that reads and returns the version without yet requiring clients to submit it. Upgrade clients and observe adoption. Then require a precondition on write endpoints, and finally remove the compatibility path.

• Do not leave a null value as a permanent bypass for concurrency checks.
• Ensure every write path, including admin tools, cron jobs, and consumers, advances the version.
• Include before-and-after versions in the audit record.
• Verify that the ORM places the version in the WHERE clause and checks affected rows.
• Do not reset versions during a partial restore while older clients may still be active.

When several services write the same database, agree on the version contract first. One writer that forgets to increment the value makes the validator dishonest. Over time, a single owner for each aggregate or an explicit command API reduces uncontrolled write paths.

11. Observe conflicts to distinguish protection from a design problem

Record successful updates, conflict count, endpoint, resource type, and caller class. Avoid user IDs or sensitive values in high-cardinality metric labels. Structured logs can contain a request ID, normalized resource ID, expected and current versions, and the outcome without storing the entire payload.

A small conflict rate can demonstrate that the mechanism is correctly protecting data. A sudden increase after deployment may reveal a client that stopped preserving ETags, a cache serving stale snapshots, or a worker writing too frequently. Conflicts concentrated on one hot aggregate may call for an atomic command, partitioning, serialization by key, or a different user experience.

• Alert on a material change from the conflict-rate baseline, not merely a value above zero.
• Measure time from read to write to identify workflows holding snapshots too long.
• Track cases where a user reloads and encounters another conflict.
• Separate version conflicts from unique violations, deadlocks, and lock timeouts.

12. Test race conditions deterministically

Sequential tests are insufficient. A useful integration test creates two connections or requests, lets both read the same version, and coordinates their writes. Exactly one update must succeed; the other must return a conflict, and the final record must contain the winning change. Use a barrier or latch instead of relying on random sleeps.

snapshotA = readCustomer(42) // version 8
snapshotB = readCustomer(42) // version 8

resultA = updateCustomer(42, snapshotA.version, changeA)
resultB = updateCustomer(42, snapshotB.version, changeB)

assert exactlyOne(resultA, resultB).isSuccess()
assert exactlyOne(resultA, resultB).isConflict()
assert readCustomer(42).version == 9

Also test delete versus update, invalid state transitions, missing preconditions, idempotent retry after a timeout, and multi-table aggregates. Run tests on the same database engine used in production because isolation and affected-row behavior can differ from an in-memory substitute. API tests should prove that an update returns a new ETag and rejects the old one.

Production checklist

• The invariant and aggregate boundary are explicit before choosing a locking mechanism.
• Each write contains an expected version or business precondition in its WHERE clause.
• The backend checks affected rows and never treats zero rows as success.
• ETag/If-Match or a version field has a consistent client contract.
• Conflicts return a stable code and are not converted into HTTP 500 errors.
• Automatic retry is disabled unless safe recomputation has been demonstrated.
• Every writer advances the version, while database constraints still protect core invariants.
• Metrics, logs, and concurrent tests verify the mechanism in production.

Optimistic locking does not remove competition; it makes competition visible and manageable. A reliable design starts with the invariant, places the precondition in the database write, carries a validator through the API, and treats conflicts as an ordinary branch. When versions, atomic updates, constraints, idempotency, and observability are each used for their proper role, a backend can support many writers without sacrificing data integrity.

References

• RFC 9110: If-Match
• PostgreSQL Documentation: Explicit Locking