Optimistic Locking — Core Concepts

Why optimistic locking exists

In multi-user applications, two requests can read the same database row, make changes independently, and try to save. Without protection, the second save silently overwrites the first — a problem called a lost update.

Example: Two support agents open the same ticket. Agent A updates the status to “in progress.” Agent B, still looking at the old version, adds a note and saves. Agent B’s save overwrites Agent A’s status change because it sends the entire old record back to the database.

Pessimistic locking (SELECT FOR UPDATE) prevents this by blocking concurrent access, but it reduces throughput and risks deadlocks. Optimistic locking takes a different approach: allow concurrent access, detect conflicts at save time, and reject stale updates.

How it works

Version column approach

The most common implementation adds a version column to the table:

1. Read the row, including its current version (e.g., version = 3)
2. Make your changes locally
3. When saving, include the version in the WHERE clause: UPDATE ... SET data = 'new', version = 4 WHERE id = 1 AND version = 3
4. If the row was modified by someone else (version is now 4), the WHERE clause matches zero rows
5. Zero affected rows = conflict detected → retry or report error

Timestamp approach

Instead of an integer version, use the updated_at timestamp:

UPDATE ... WHERE id = 1 AND updated_at = '2026-03-28 10:30:00'

This works but has a subtle flaw — if two updates happen within the same timestamp resolution (milliseconds on most systems), the conflict goes undetected. Integer versions are more reliable.

Key concepts

The read-modify-write cycle

Optimistic locking protects a specific pattern:

1. Read the current state
2. Modify the state in application code
3. Write the new state back

The vulnerability window is between read and write. The longer that gap, the higher the chance of a conflict. For web forms where users might take minutes to edit, conflicts are more common than for automated batch operations that process rows in milliseconds.

Retry strategies

When a conflict is detected, the application has options:

• Automatic retry: Re-read the latest version, re-apply the change, try again. Works when changes are commutative (e.g., incrementing a counter).
• User notification: Tell the user someone else modified the data. Show both versions and let them choose. Necessary when changes aren’t automatically mergeable.
• Merge: Combine both sets of changes programmatically. Complex but used in collaborative editing systems.

Optimistic vs. pessimistic: when to choose

Factor	Optimistic	Pessimistic
Conflict frequency	Low (most reads, few writes)	High (frequent concurrent writes)
Response time	Fast — no lock waiting	Potentially slow — blocks on locks
Failure mode	Retry on conflict	Deadlock on circular waits
Best for	Web APIs, user interfaces	Inventory systems, financial transfers

Common misconception

“Optimistic locking eliminates the need for database transactions.”

Not at all. Optimistic locking and transactions solve different problems. Transactions ensure atomicity (all changes or none). Optimistic locking detects concurrent modifications. You typically need both — the version check UPDATE happens inside a transaction to ensure atomicity of the check-and-update operation.

One thing to remember: Optimistic locking works by verifying that nobody changed the data between your read and your write. It’s ideal when conflicts are rare — which they are in most applications — because it avoids the performance overhead of holding locks.