Backend Study Lab

System Design

Optimistic Locking Explained

Learn how optimistic locking prevents lost updates in backend systems using version columns, compare-and-swap updates, and 409 conflicts.

By CloveCode Studio Jun 28, 2026 Updated Jul 5, 2026 4 min read

Quick answer

Optimistic locking is a concurrency control pattern that detects conflicting updates instead of preventing them with a long-held lock.

A common implementation stores a version column on a row. When updating, the backend includes the version it previously read. If another request changed the row first, the update affects zero rows and the API returns a conflict.

The lost update problem

Consider two users editing the same account settings.

10:00 User A reads version 7
10:01 User B reads version 7
10:02 User A saves changes and creates version 8
10:03 User B saves changes based on old version 7

Without conflict detection, User B can overwrite User A’s change accidentally. That is a lost update.

Optimistic locking turns this into an explicit conflict so the client can reload, merge, or ask the user what to do.

Version column pattern

A simple table might include:

CREATE TABLE account_settings (
  account_id BIGINT PRIMARY KEY,
  display_name TEXT NOT NULL,
  timezone TEXT NOT NULL,
  version INTEGER NOT NULL DEFAULT 1,
  updated_at TIMESTAMP NOT NULL
);

The client reads the current data:

{
  "accountId": 42,
  "displayName": "Backend Team",
  "timezone": "UTC",
  "version": 7
}

When saving, the client sends the version it edited:

{
  "displayName": "Platform Team",
  "timezone": "UTC",
  "version": 7
}

Compare-and-swap update

The backend update includes the expected version:

UPDATE account_settings
SET display_name = ?,
    timezone = ?,
    version = version + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE account_id = ?
  AND version = ?;

If the update count is 1, the save succeeded. If the update count is 0, either the row does not exist or the version is stale.

The important part is atomicity. The version check and update happen in one database statement.

API response shape

For REST APIs, a stale version is often a 409 Conflict:

HTTP/1.1 409 Conflict
Content-Type: application/json
{
  "error": "version_conflict",
  "message": "The resource was changed by another request. Reload and try again.",
  "currentVersion": 8
}

Read REST API Status Codes Explained for broader guidance on 409, 400, and validation responses.

ETag and If-Match

HTTP has a related pattern using ETag and If-Match.

The server returns an entity tag:

ETag: "settings-v7"

The client sends it back during update:

If-Match: "settings-v7"

If the resource changed, the server can reject the request because the tag no longer matches. This works well for HTTP-native APIs, though many JSON APIs use explicit version fields because they are easier for clients to understand.

Optimistic vs pessimistic locking

Optimistic locking assumes conflicts are uncommon and checks at write time. Pessimistic locking prevents conflicts by locking data before the update.

ApproachHow it worksBest when
Optimistic lockingDetect stale writes with version checks.Conflicts are occasional and requests should not hold locks.
Pessimistic lockingLock the row or resource before editing.Conflicts are frequent or the operation must be serialized.

Optimistic locking is common in web APIs because users, clients, and services may hold data for an unpredictable amount of time.

Where optimistic locking helps

Use optimistic locking for:

  • Profile and account settings.
  • Admin edit screens.
  • Inventory adjustments.
  • Document metadata.
  • Configuration records.
  • Workflow state transitions.
  • Any update where overwriting another writer would be harmful.

It is less useful for append-only events, immutable records, or operations that already use a stronger transaction boundary.

Relation to idempotency

Optimistic locking and idempotency solve different problems.

Idempotency protects a single logical operation from being applied twice during retries. Optimistic locking protects a resource from being overwritten by stale data.

For example, an order creation endpoint may need Idempotency in APIs Explained. An order update endpoint may need optimistic locking to prevent two agents from overwriting each other’s edits.

API response pattern

Optimistic locking works best when the API tells clients exactly how to recover from a conflict. A common pattern is to return 409 Conflict with the current version, a stable error code, and a message that asks the client to reload before retrying.

{
  "error": "version_conflict",
  "message": "The record changed since you loaded it.",
  "currentVersion": 8
}

This is better than silently overwriting data or returning a generic 500. For human-facing workflows, the UI can show a merge prompt. For API clients, the client can fetch the latest representation, apply its intended change again, and submit with the new version.

Common mistakes

The first mistake is checking the version in application code and then issuing a separate update. That creates a race. Put the version condition in the update statement.

The second mistake is silently overwriting on conflict. If the client sent stale data, make that visible with a conflict response.

The third mistake is using timestamps as versions without understanding precision. Two updates can happen within the same timestamp granularity, depending on the database and clock behavior.

The fourth mistake is hiding version fields from API clients that need to perform safe updates.

Practical backend checklist

Before adding optimistic locking:

  • Add a numeric version column or equivalent concurrency token.
  • Return the current version in read responses.
  • Require the expected version in update requests.
  • Update with WHERE id = ? AND version = ?.
  • Return 409 Conflict on stale writes.
  • Make conflict messages clear for humans and stable for machines.
  • Test two concurrent updates against the same row.

Read Idempotency in APIs Explained to separate retry safety from update conflict detection. Read REST API Status Codes Explained for conflict response design. For schema-change safety around versioned records, read Database Migration Rollback Explained. You can also browse the System Design Learning Path or Topics for related backend architecture guides.

Keep reading