Category: Databases & Data Tags: optimistic-locking concurrency-control database-transactions

What Is Optimistic Locking?

By Oleksandr Andrushchenko — Published on Oct 04

0 Likes

0 Dislikes

Optimistic locking is a concurrency-control technique that allows multiple clients to read the same data without holding a lock, then detects whether the data changed before an update is accepted.

The approach assumes conflicts are possible but relatively uncommon. Instead of preventing concurrent access, the application performs the update only when the record still has the version that was originally read. If another writer changed it first, the update is rejected as a concurrency conflict.

The Problem Optimistic Locking Solves
How Optimistic Locking Works
Optimistic Locking with Timestamps
Optimistic vs Pessimistic Locking
Optimistic Locking and Transactions
Optimistic Locking and MVCC
Handling Optimistic Locking Conflicts
Optimistic Locking in APIs
When Optimistic Locking Works Well
When Optimistic Locking Works Poorly
Production Design Example
Common Optimistic Locking Mistakes
Production Checklist
Frequently Asked Questions
Conclusion
- More Articles to Read

The Problem Optimistic Locking Solves

Consider a product record:

product_id = 42
price      = 100
version    = 7

Two administrators open the product at approximately the same time.

Alice                          Bob

READ product                   READ product

price = 100                    price = 100
version = 7                    version = 7

Alice changes the price to $110 and saves it.

price:   100 → 110
version:   7 → 8

Bob still has the old state in his browser. He changes the price to $120 and submits the form.

A naive update might execute:

UPDATE products
SET price = 120
WHERE id = 42;

The database accepts the update and silently overwrites Alice's newer value.

Alice saves $110
       ↓
database = $110
       ↓
Bob saves stale form
       ↓
database = $120

Alice's update is lost

This is a lost update. The problem is not that either update is individually invalid. The problem is that Bob made his change using stale state.

Holding a database lock from the moment Bob opens the page until he eventually clicks Save would be impractical. That period could last seconds, minutes, hours, or forever.

Optimistic locking solves the problem without keeping such a long-lived lock.

How Optimistic Locking Works

The basic idea is simple: read both the data and a value representing its current version.

The database does not reserve the record for the original reader. Other transactions remain free to update it.

The version check happens when the write is attempted.

Version-Based Optimistic Locking

A common implementation adds an integer version column:

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(12, 2) NOT NULL,
    version BIGINT NOT NULL DEFAULT 1
);

The application reads the record:

SELECT id, name, price, version
FROM products
WHERE id = 42;

Suppose it receives:

id      = 42
name    = "Keyboard"
price   = 100
version = 7

The application keeps version = 7 together with the data being edited.

When the update is submitted, that version becomes part of the write condition.

Conditional Update

The update can be expressed as one atomic SQL statement:

UPDATE products
SET price = 110,
    version = version + 1
WHERE id = 42
  AND version = 7;

The important part is:

AND version = 7

The update means:

Change this product only if it still has the version that was originally read.

If nobody modified the record:

Current version = 7
Expected version = 7

UPDATE succeeds

New version = 8

If another writer already changed it:

Current version = 8
Expected version = 7

UPDATE matches zero rows

Conflict detected

The stale writer cannot silently overwrite the newer state.

Detecting Conflicts

The application must check whether the conditional update actually modified a row.

PostgreSQL can make this convenient with RETURNING:

UPDATE products
SET price = :price,
    version = version + 1
WHERE id = :product_id
  AND version = :expected_version
RETURNING id, price, version;

A returned row means the update succeeded.

1 returned row
→ update accepted

No returned row means the condition did not match:

0 returned rows
→ record missing or version changed

If distinguishing those cases matters, the application can reload the record after the failed conditional update.

A zero-row update should not simply be ignored. It is part of the concurrency protocol.

Optimistic Locking with Timestamps

A timestamp such as updated_at can also be used as a concurrency token:

UPDATE products
SET price = :price,
    updated_at = CURRENT_TIMESTAMP
WHERE id = :product_id
  AND updated_at = :expected_updated_at;

The principle is the same: the update succeeds only if the concurrency token has not changed.

Version numbers are often easier to reason about because they represent logical modifications directly:

version 41
   ↓
update
   ↓
version 42
   ↓
update
   ↓
version 43

Timestamps require care around precision, serialization, database behavior, and whether every relevant modification reliably changes the timestamp.

Other systems may use revision IDs, hashes, sequence numbers, or storage-specific compare-and-swap tokens. The important requirement is that stale writers can reliably detect that the state they originally read is no longer current.

Optimistic vs Pessimistic Locking

Optimistic and pessimistic locking solve similar concurrency problems using opposite assumptions.

Optimistic locking allows concurrent work and checks for a conflict when writing.

Pessimistic locking coordinates access earlier by acquiring a database lock before the protected operation proceeds.

Characteristic	Optimistic Locking	Pessimistic Locking
Strategy	Detect conflicts	Prevent conflicting access
Typical mechanism	Version check	Row or resource lock
Waiting	Usually little waiting during business processing	Conflicting transactions may wait
Conflict result	Reject, retry, or merge	Wait, timeout, or deadlock
Best fit	Conflicts relatively uncommon	Conflicts expected or serialization required

A pessimistic database operation might use:

BEGIN;

SELECT id, price
FROM products
WHERE id = 42
FOR UPDATE;

UPDATE products
SET price = 110
WHERE id = 42;

COMMIT;

The row remains protected while the transaction holds the lock.

With optimistic locking:

read version 7

        ↓

no long-lived application lock

        ↓

UPDATE ... WHERE version = 7

        ↓

success or conflict

This makes optimistic locking particularly useful when there can be a long delay between reading and updating a record.

Transaction and Concurrency in Databases covers optimistic and pessimistic locking as part of the broader database concurrency model.

Optimistic Locking and Transactions

Optimistic locking does not replace database transactions. They solve different problems.

A transaction defines which database operations must succeed or fail together. Optimistic locking verifies that a write is still based on the expected state.

Consider updating an order and creating an audit record:

BEGIN;

UPDATE orders
SET status = 'shipped',
    version = version + 1
WHERE id = 9001
  AND version = 12;

-- verify one row was updated

INSERT INTO order_history (
    order_id,
    status
)
VALUES (
    9001,
    'shipped'
);

COMMIT;

If the optimistic update fails, the transaction should not continue and insert a history record for a transition that never occurred.

The transaction protects atomicity:

order transition
       +
history record

must commit together

The version condition protects against stale concurrent updates.

These are complementary guarantees. Database Locks and Transactions explains the distinction between transaction boundaries and concurrency control in more detail.

Optimistic Locking and MVCC

Optimistic locking is also different from Multi-Version Concurrency Control.

MVCC is an internal database technique that maintains row versions so transactions can observe appropriate snapshots while reducing unnecessary reader-writer blocking.

Application-level optimistic locking commonly adds an explicit business-visible version:

Application version:

order.version = 17

Database MVCC versions:

internal row version A
internal row version B
internal row version C

The two mechanisms can operate together.

MVCC determines which database row versions a transaction can see. Optimistic locking uses an expected version condition to decide whether an application update should still be accepted.

What Is MVCC? explains database snapshots, row versions, and visibility rules separately.

Handling Optimistic Locking Conflicts

Detecting a conflict is only half of the design. The application must decide what happens afterward.

A conflict can be handled by rejecting the stale operation, retrying it using current data, merging changes, or abandoning the operation.

The correct response depends on the business operation.

Reject and Reload

For interactive editing, rejection is often the safest behavior.

User reads version 7
       ↓
another user creates version 8
       ↓
user submits version 7
       ↓
CONFLICT
       ↓
reload version 8
       ↓
show current state

This prevents an old form from silently overwriting a newer edit.

The application can return a conflict message such as:

This record was modified by another request.
Reload the latest version before saving again.

Automatic Retry

Some operations can safely reload the latest state, recalculate the desired result, and try again.

For example, a retry loop can be appropriate for a purely local operation:

class ConcurrentUpdateError(Exception):
    pass


def update_record(store, record_id, change, max_attempts=3):
    for attempt in range(max_attempts):
        record = store.get(record_id)

        updated = store.update_if_version(
            record_id=record_id,
            expected_version=record.version,
            change=change,
        )

        if updated:
            return

    raise ConcurrentUpdateError(
        f"Could not update {record_id} due to concurrent changes"
    )

Retries should be bounded. Under high contention, unlimited retries can turn conflicts into a retry storm.

Automatic retry is also dangerous when the workflow includes external side effects.

charge payment
      ↓
optimistic update conflicts
      ↓
retry entire operation
      ↓
charge payment again?

A database concurrency retry must not accidentally duplicate payments, emails, messages, or other non-idempotent operations.

Merge Changes

Some applications can merge concurrent edits instead of rejecting one of them.

Suppose the original record is:

name  = "Alice"
phone = "111"
version = 5

One user changes the name while another changes the phone number.

Update A:
name = "Alicia"

Update B:
phone = "222"

The changes do not necessarily conflict semantically even though both started from Version 5.

An application can compare the original state, current state, and requested changes to determine whether an automatic merge is safe.

That logic is domain-specific. Blindly applying the stale object over the current row is not a merge; it recreates the lost-update problem.

Optimistic Locking in APIs

The same concurrency model can be exposed through an HTTP API.

One approach includes the version in the resource representation:

{
  "id": 42,
  "name": "Keyboard",
  "price": 100,
  "version": 7
}

The client sends the expected version with an update:

{
  "price": 110,
  "version": 7
}

The service converts it into a conditional database update:

UPDATE products
SET price = 110,
    version = version + 1
WHERE id = 42
  AND version = 7;

If Version 7 is stale, the service can return a conflict instead of overwriting the newer resource.

HTTP also provides conditional request mechanisms using validators such as ETags:

GET /products/42

ETag: "product-42-v7"

A later update can require the same representation version:

PUT /products/42
If-Match: "product-42-v7"

If the resource changed, the condition fails and the stale update is rejected.

The important design principle is the same whether the concurrency token is called a database version, revision, generation, or ETag: the write must prove that it is based on the expected state.

When Optimistic Locking Works Well

Optimistic locking works best when conflicts exist but are uncommon enough that detecting them is cheaper than preventing them.

Typical examples include:

user profile editing;
administrative CRUD interfaces;
document metadata updates;
order state transitions;
configuration records;
long-lived forms;
background workers updating independently processed entities;
APIs where clients may submit stale representations.

Consider a customer profile viewed by thousands of requests per minute but edited only a few times per day.

Holding locks for every reader would provide little value. A version check on the relatively rare update is much cheaper.

When Optimistic Locking Works Poorly

Optimistic locking becomes less attractive as contention increases.

Consider thousands of workers updating the same row:

Worker 1 ─┐
Worker 2 ─┤
Worker 3 ─┤
Worker 4 ─┼──→ same record
Worker 5 ─┤
Worker 6 ─┘

If only one version can win, many workers repeatedly:

read
 ↓
calculate
 ↓
attempt update
 ↓
conflict
 ↓
reload
 ↓
recalculate
 ↓
retry

The system can spend substantial CPU and database capacity performing work that is later discarded.

High-contention resources may benefit from a different design:

pessimistic locking;
atomic conditional statements;
database constraints;
single-writer processing;
partitioned queues;
append-only events;
redesigning hot shared state.

Optimistic locking should therefore not be selected simply because it avoids waiting. Conflict frequency and retry cost matter.

Production Design Example

Consider an order service where multiple processes can update an order:

customer API;
payment worker;
warehouse worker;
support dashboard.

The order table contains:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    status TEXT NOT NULL,
    shipping_address TEXT NOT NULL,
    version BIGINT NOT NULL DEFAULT 1,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Order 9001 currently contains:

id      = 9001
status  = confirmed
version = 18

The warehouse worker reads Version 18 and begins preparing a transition to processing.

At almost the same time, the support system cancels the order:

UPDATE orders
SET status = 'cancelled',
    version = version + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 9001
  AND version = 18
RETURNING id, status, version;

The update succeeds:

status  = cancelled
version = 19

The warehouse worker still holds Version 18 and attempts:

UPDATE orders
SET status = 'processing',
    version = version + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 9001
  AND version = 18
RETURNING id, status, version;

No row is returned.

Expected version: 18
Current version:  19

Result: conflict

Without optimistic locking, the warehouse worker could overwrite cancelled with processing based on stale information.

The service reloads the order:

status = cancelled
version = 19

The transition is no longer valid, so it is abandoned rather than retried.

This illustrates an important point: not every optimistic conflict should result in another write attempt. Reloading current state can change the business decision itself.

A FastAPI service using SQLAlchemy could expose the behavior explicitly:

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncConnection


class ConcurrentUpdateError(RuntimeError):
    pass


async def update_order_status(
    connection: AsyncConnection,
    order_id: int,
    expected_version: int,
    next_status: str,
) -> dict:
    result = await connection.execute(
        text("""
            UPDATE orders
            SET status = :next_status,
                version = version + 1,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = :order_id
              AND version = :expected_version
            RETURNING id, status, version
        """),
        {
            "order_id": order_id,
            "expected_version": expected_version,
            "next_status": next_status,
        },
    )

    row = result.mappings().one_or_none()

    if row is None:
        raise ConcurrentUpdateError(
            f"Order {order_id} was modified concurrently"
        )

    return dict(row)

Production monitoring should track optimistic conflicts separately from infrastructure and SQL errors.

Useful metrics include:

optimistic conflicts per operation;
conflict rate as a percentage of writes;
retry attempts;
retry success rate;
records generating the most conflicts;
transaction latency;
database CPU and write load.

Suppose an order service reports:

Order writes:              8,000/min
Optimistic conflicts:         24/min
Conflict rate:              0.30%
Successful retries:           18/min
Rejected stale operations:     6/min

A 0.30% conflict rate may be perfectly reasonable.

If a deployment changes the behavior to:

Order writes:              8,000/min
Optimistic conflicts:      2,600/min
Conflict rate:             32.50%

the concurrency strategy deserves investigation. A hot resource, unnecessary shared state, excessively long read-modify-write cycle, or inappropriate use of optimistic locking may be causing large amounts of wasted work.

Common Optimistic Locking Mistakes

Reading the version but not checking it during the update. The version provides no protection unless it is part of the write condition.
Ignoring the affected-row count. Zero updated rows can represent a real concurrency conflict.
Incrementing the version outside the atomic update. The data change and version change should be one operation.
Automatically retrying every conflict. Current state may make the original business operation invalid.
Retrying external side effects. Payments, messages, and emails can be duplicated.
Using optimistic locking for highly contested hot rows. Excessive retries can waste substantial capacity.
Assuming optimistic locking replaces transactions. Multi-statement atomic operations still require transaction boundaries.
Confusing optimistic locking with MVCC. They operate at different layers and solve different parts of concurrency control.
Using timestamps without understanding precision. Two changes can become difficult to distinguish if the concurrency token is not reliable.
Updating only some code paths with version checks. A writer that ignores the concurrency protocol can undermine the protection.
Returning a generic database error for expected conflicts. Concurrency conflicts are often normal application outcomes and should be handled explicitly.
Allowing unlimited retries. Heavy contention can create retry storms.

Production Checklist

Identify the business state that must be protected from stale writes.
Store a reliable version or revision token.
Return the version whenever clients need to update the resource later.
Include the expected version in the same atomic update as the data modification.
Increment the version only when the protected state changes.
Check the affected-row count or returned row.
Treat a version mismatch as an explicit concurrency outcome.
Define whether each conflict should reject, reload, retry, or merge.
Keep automatic retries bounded.
Re-evaluate business rules after reloading current state.
Do not blindly retry non-idempotent external side effects.
Use transactions when multiple database changes must commit together.
Use pessimistic locking or another strategy when contention is consistently high.
Ensure every writer follows the same versioning protocol.
Monitor conflict and retry rates.
Identify hot records producing disproportionate conflicts.
Load-test concurrent updates to the same entities.

Frequently Asked Questions

Optimistic locking is straightforward once the distinction between preventing conflicts and detecting conflicts is clear.

Does Optimistic Locking Use Database Locks?

Optimistic locking does not normally hold an application-visible exclusive lock from the original read until the later write.

The database still uses its normal internal concurrency mechanisms while executing the conditional UPDATE. The term optimistic refers to allowing work to proceed without reserving the record for the entire read-modify-write period.

Is a Version Column Required?

No. A version integer is a common and convenient implementation, but timestamps, revision identifiers, ETags, hashes, and storage-specific generation numbers can also serve as concurrency tokens.

The token must reliably indicate whether relevant state changed since it was read.

Does Optimistic Locking Prevent Lost Updates?

It can prevent stale writers from silently overwriting newer state when every relevant write participates in the version-check protocol.

If some update paths ignore the version condition, those writers can still bypass the protection.

Should Optimistic Locking Conflicts Always Be Retried?

No. The latest state should often be re-evaluated first.

A retry makes sense only if the operation is still valid and safe to repeat. A stale request to move an order to processing, for example, should not be retried if the order has already been cancelled.

Should Optimistic Locking Be Used for High-Contention Data?

Usually with caution. Optimistic locking is most attractive when conflicts are relatively rare.

If many writers constantly modify the same resource, repeated failures and retries can become more expensive than coordinating access earlier through pessimistic locking, atomic operations, queue-based serialization, or a different data model.

Conclusion

Optimistic locking protects data from stale concurrent writes without holding a long-lived lock while the application performs its work. A record is read with a version, and a later update succeeds only if that version is still current.

This makes the technique particularly effective for applications where reads are common, writes are less frequent, and concurrent modifications to the exact same record are relatively rare.

The version check only detects the conflict. Production systems must still decide whether to reject, reload, retry, or merge the operation, and retries must account for external side effects and changing business state.

Key takeaway: optimistic locking does not prevent another writer from changing a record. It prevents a stale writer from silently overwriting that newer state.

What Is Optimistic Locking?

Table of Contents