Optimistic Locking Explained | Backend Study Lab

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.

Approach	How it works	Best when
Optimistic locking	Detect stale writes with version checks.	Conflicts are occasional and requests should not hold locks.
Pessimistic locking	Lock 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 Database Isolation Levels Explained to understand anomalies beyond a single versioned row, and Idempotency in APIs Explained to separate retry safety from conflict detection. REST API Status Codes Explained covers conflict responses, while Database Migration Rollback Explained covers schema-change safety.

The database still maintains row versions and physical cleanup beneath a version column, as MVCC, VACUUM, and Table Bloat Explained describes; when conflicts become long waits instead of immediate compare-and-swap misses, Database Lock Contention and Long Transactions provides the blocker evidence needed for a safe response.

This production update follows the Spring order service across both supported database engines.

Learning objectives

Reject stale order updates with an atomic version predicate.
Preserve user intent with bounded conflict handling rather than blind retry.

Prerequisites

Know update predicates, HTTP conflicts, ORM version fields, and transactions.

Shared relational contract

Optimistic locking protects one compared state transition; it is not global serialization. Use the course and topic.

PostgreSQL 18 boundary

PostgreSQL MVCC and row-count evidence determine whether the versioned update matched; ORM behavior remains separate.

MySQL 8.4 InnoDB boundary

InnoDB executes the same application predicate under its own MVCC, locking, and affected-row semantics.

Production failure scenario

A stale shipment command that updates zero rows must reload and revalidate business intent before retry.

Common misconceptions

An entity version does not protect several rows, another service, or a remote payment.

Decision checklist

Choose the version owner, make the update atomic, cap retries, and return an explicit conflict.

Official sources

PostgreSQL concurrency control and InnoDB locking, accessed August 19, 2026. See lock contention and production Spring systems.