DB Optimistic Locking

Optimistic Locking

Optimistic locking assumes conflicts are rare, allows concurrent reads without locks, and detects conflicts at write time using version columns or conditional updates.

When to Use

• Web applications with read-heavy workloads and infrequent write conflicts
• Edit-and-save workflows (CMS, admin panels, document editing)
• Any scenario where holding database locks during user think-time is unacceptable
• APIs where multiple clients may update the same resource concurrently
• Distributed systems where database-level locks are impractical

Instructions

Key Concepts

1. Version Column Pattern

Add an integer version column. Read the row including version. Update with a WHERE clause that checks the version, incrementing it on success. If no rows are affected, someone else modified the row first.

ALTER TABLE products ADD COLUMN version INTEGER NOT NULL DEFAULT 0;

-- Read
SELECT id, name, stock, version FROM products WHERE id = 42;
-- returns: id=42, name='Widget', stock=10, version=3

-- Update (conditional on version)
UPDATE products
  SET stock = 9, version = version + 1
  WHERE id = 42 AND version = 3;

-- Check result: if 0 rows affected, conflict detected

2. Timestamp-Based

Use updated_at instead of an integer version. Less reliable -- two updates in the same millisecond create a false "no conflict" result. Acceptable only for low-contention workloads where millisecond-level collisions are extremely unlikely.

3. Conditional Update (Compare-and-Swap)

No extra column needed. Compare the relevant field values directly:

UPDATE accounts
  SET balance = 350.00
  WHERE id = 1 AND balance = 500.00;

Only works when you can compare the fields that matter. Not suitable when multiple fields may change independently.

4. Conflict Detection and Retry

When rows_affected = 0, the row was modified by another transaction. The correct response is to re-read the current state, re-apply business logic, and retry. Limit retries (3-5) with exponential backoff. Return HTTP 409 Conflict to the client if retries are exhausted.

Worked Example

Product inventory update in an e-commerce system:

-- Step 1: Read current state
SELECT id, name, stock, version
  FROM products WHERE id = 42;
-- returns: stock=10, version=3

-- Step 2: Application logic
-- Customer orders 1 unit, new stock = 9

-- Step 3: Conditional update
UPDATE products
  SET stock = 9, version = version + 1
  WHERE id = 42 AND version = 3
  RETURNING version;
-- If returns a row: success, new version = 4
-- If returns nothing: conflict, another transaction modified this row

-- Step 4: Retry on conflict (pseudocode)
max_retries = 3
for attempt in range(max_retries):
    row = SELECT id, stock, version FROM products WHERE id = 42
    new_stock = row.stock - 1
    if new_stock < 0:
        raise OutOfStock()
    result = UPDATE products SET stock = new_stock, version = version + 1
               WHERE id = 42 AND version = row.version
    if result.rows_affected == 1:
        break  -- success
    -- else: conflict, retry with fresh data
else:
    raise ConflictError("Too many concurrent modifications")

Anti-Patterns

1. Forgetting to check rows_affected. The UPDATE silently does nothing on conflict. Without checking the result, the application assumes success and proceeds with stale data.

2. Using optimistic locking for high-contention resources. If conflicts exceed 5-10%, pessimistic locking (SELECT FOR UPDATE) is more efficient. Fewer wasted retries means better throughput. See db-pessimistic-locking.

3. Version column not included in every UPDATE. If any code path updates the row without incrementing version, the version becomes meaningless and conflicts go undetected.

4. Retrying without re-reading. The retry must re-read the current state and re-apply business logic. Re-executing the same UPDATE with the same stale version will fail again indefinitely.

PostgreSQL Specifics

• Use RETURNING to get the new version in one round trip: UPDATE ... SET version = version + 1 ... RETURNING version;
• The xmin system column changes on every update and can serve as a free version indicator. However, xmin is a 32-bit transaction ID that wraps around and is not safe for long-lived comparisons or external storage (e.g., in an API ETag).
• pg_advisory_lock can be combined with optimistic locking as a hybrid approach for specific high-contention scenarios.

Details

Advanced Topics

Combining with Read Committed: The WHERE version = $old check in the UPDATE provides conflict detection that Read Committed does not natively offer. This gives you most of the benefit of stricter isolation levels without the overhead of Serializable or the complexity of retry logic for serialization failures.

Optimistic locking in distributed systems: The same pattern appears outside databases. HTTP ETags implement optimistic concurrency for REST APIs. Redis CAS (WATCH/MULTI/EXEC) provides optimistic locking for cache updates. The principle is universal: read, compute, write-if-unchanged.

Advisory locks as hybrid: For resources that are usually low-contention but occasionally spike, use pg_try_advisory_lock to attempt an immediate lock. If it fails (contention detected), fall back to optimistic retry. This avoids both the overhead of always locking and the wasted work of retrying under contention.

Engine Differences

MySQL InnoDB supports the same version column pattern. MySQL's ROW_COUNT() function serves the same role as PostgreSQL's returned row count for checking rows_affected. Both engines handle optimistic locking at the application level -- it is not a database feature but a design pattern implemented through conditional updates.

The pattern is identical across engines because it does not depend on engine-specific features. The only difference is syntax for the result check (RETURNING in PostgreSQL vs ROW_COUNT() in MySQL).

Real-World Case Studies

A content management system had editors frequently working on the same articles. Pessimistic locking (SELECT FOR UPDATE) caused editors to wait for each other, sometimes for minutes during long editing sessions. Switching to optimistic locking with version columns and a merge UI for conflicts improved throughput significantly. Actual conflicts occurred in less than 2% of edits, and when they did occur, editors could review and merge changes rather than losing work.

Source

• Fowler, M. "Patterns of Enterprise Application Architecture" (2002) -- Optimistic Offline Lock
• PostgreSQL Application-Level Consistency

Process

1. Add a version integer column (default 0) to mutable entities that may be concurrently modified.
2. Include AND version = $expected_version in every UPDATE, with version = version + 1 in the SET clause.
3. Check rows_affected after every conditional update and implement retry logic with exponential backoff for conflicts.

Harness Integration

• Type: knowledge -- this skill is a reference document, not a procedural workflow.
• No tools or state -- consumed as context by other skills and agents.
• related_skills: db-pessimistic-locking, db-mvcc, db-isolation-levels, db-deadlock-prevention, db-acid-properties, prisma-transactions, drizzle-transactions, api-conditional-requests

Success Criteria

• Version column present on mutable entities subject to concurrent modification
• Every UPDATE includes version check in WHERE clause and increments version
• rows_affected checked after every conditional update
• Retry logic handles conflicts with exponential backoff and a maximum retry count