Optimistic vs Pessimistic Concurrency Control

TL;DR. Pessimistic locking acquires a lock before touching data and holds it until commit; optimistic concurrency does the work lock-free and validates at commit. The deciding variable is conflict rate. At low contention (< 1% aborts), optimistic wins because validation is cheaper than locking and readers never block writers¹. At high contention (> 10%), optimistic collapses into O(N^2) wasted work from retry storms². Default to optimistic (MVCC + version check) and escalate to SELECT FOR UPDATE on measured hot rows. For distributed locks, always attach a fencing token³.

Learning Objectives

• Compare optimistic and pessimistic concurrency across throughput, tail latency, and failure modes at varying conflict rates.
• Identify the three variables (conflict rate, retry cost, side effects) that determine the correct choice.
• Justify a hybrid MVCC-plus-escalation approach as the production default.
• Evaluate Spanner’s wound-wait, PostgreSQL’s SSI, and Stripe’s idempotency keys as real implementations of each strategy.

The Core Trade-off

Two transactions want the same row at the same time. Pessimistic says: make one wait. Optimistic says: let both proceed, then abort the loser at commit. The fundamental tension is wasted work versus wasted waiting.

Pessimistic locking serializes access to hot data. Every waiter pays the lock-holder’s full execution time as latency, but no work is thrown away. Optimistic concurrency lets all transactions run in parallel, but losers discard their work and retry. At low contention, almost nobody loses, so optimistic throughput dominates. At high contention, the abort-retry cycle compounds: with N contending clients, total work grows as O(N^2)².

The crossover point is workload-specific. Google Spanner defaults to pessimistic concurrency control for serializable transactions; the docs note that “in highly contentious workloads, it helps transactions make progress”⁴. DynamoDB’s conditional writes make OCC a natural fit because most NoSQL access patterns hit distinct keys².

A second variable flips the choice regardless of conflict rate: the cost of retrying. A 5 ms row update retries for free. A 30-second analytical job or a non-idempotent payment API call cannot be retried safely. Side-effect-bearing operations push toward pessimistic locking even at low contention.

flowchart LR
    subgraph Pessimistic
        P1[Acquire lock] --> P2[Do work] --> P3[Commit + release]
    end
    subgraph Optimistic
        O1[Read version] --> O2[Do work] --> O3{Version unchanged?}
        O3 -->|Yes| O4[Commit]
        O3 -->|No| O5[Abort + retry]
    end

Side-by-Side Comparison

The table misleads on “deadlock risk.” Optimistic avoids deadlocks but introduces livelock: under sustained contention, transactions can starve indefinitely. Spanner documents this as “transaction starvation” and recommends switching to pessimistic mode⁴.

The “tail latency” row hides the real danger of pessimistic locking: any GC pause, slow query, or network blip during the critical section blocks every waiter for the full duration of the stall³.

When to Pick Optimistic

Low-contention wide keyspaces. User profile edits, configuration updates, most CRUD on sharded data. DynamoDB conditional writes handle millions of keys with near-zero conflict because each user touches their own partition².

Short, idempotent transactions. The retry cost is negligible when the transaction is a single-row update under 5 ms. The canonical pattern: UPDATE ... SET version = version + 1 WHERE id = ? AND version = ?¹.

HTTP APIs with ETags. RFC 7232 If-Match headers make writes conditional on the resource version. The client retries on 412 Precondition Failed. Stripe’s idempotency keys extend this to payment APIs with a 24-hour minimum TTL⁶.

Read-heavy workloads on MVCC databases. PostgreSQL, InnoDB, and Spanner all serve snapshot reads without blocking writers. SSI (PostgreSQL 9.1+) gives full serializability without 2PL blocking⁷.

When to Pick Pessimistic

Hot rows with measured high contention. Inventory counts, seat reservations, auction bid counters, account balances updated by many concurrent writers. Serializing with SELECT FOR UPDATE avoids the retry storm entirely.

Long-running transactions where retry is expensive. Batch imports, multi-step workflows, or transactions that take seconds. Aborting and redoing 10 seconds of work is worse than waiting 50 ms for a lock.

Operations with non-idempotent external side effects. Calling a payment provider, sending an SMS, or triggering a webhook inside the transaction. You cannot safely retry these, so you must hold the lock across the critical section³.

Distributed coordination requiring correctness. Leader election, distributed mutex, exactly-once resource allocation. Use a consensus-backed lock (ZooKeeper zxid, etcd lease) with a fencing token, never a bare TTL-based lock³⁸.

The Hybrid Path

Most production databases already run hybrid: MVCC for the happy path, explicit locks for hot paths. PostgreSQL uses snapshot isolation for reads and exposes SELECT FOR UPDATE for application-level escalation⁹. Spanner lets you set ReadLockMode per transaction: pessimistic for hot paths, optimistic for long-running multi-region reads⁴.

The practical pattern: default to optimistic everywhere, instrument your conflict rate (PostgreSQL pg_stat_database.xact_rollback and deadlocks, plus application-level logging of SQLSTATE 40001 for SSI serialization failures; Spanner Lock Statistics¹⁰), and escalate to pessimistic on paths where the abort rate exceeds 5-10%. This gives most operations lock-free throughput while giving known-hot paths deterministic progress.

flowchart TD
    Start["New write path"]
    Q1{"External side effect<br/>under the lock?"}
    Q1 -->|Yes| Saga["Saga or idempotency key<br/>do not hold a lock across it"]
    Q1 -->|No| Q2{"Measured conflict rate?"}
    Q2 -->|"< 1%"| OCC["Optimistic: version check + retry"]
    Q2 -->|"1-10%"| Hybrid["Optimistic + backoff + jitter<br/>monitor for escalation"]
    Q2 -->|"> 10%"| PCC["Pessimistic: SELECT FOR UPDATE<br/>or distributed lock + fencing"]
    Start --> Q1

Real-World Examples

Google Spanner uses pessimistic locking by default for serializable read-write transactions. Locks are acquired at cell granularity (row x column), so two transactions can modify different columns of the same row simultaneously⁵. Conflict resolution uses wound-wait: older transactions abort younger ones, guaranteeing forward progress without deadlocks⁵. Four lock modes (ReaderShared, WriterShared, Exclusive, WriterSharedTimestamp) provide fine-grained concurrency⁵.

PostgreSQL SSI is the first production implementation of Serializable Snapshot Isolation (Ports and Grittner, VLDB 2012)⁷. Reads acquire non-blocking SIRead predicate locks; the engine watches for dangerous read-write dependency cycles and aborts one transaction with SQLSTATE 40001⁹. First-committer-wins: the transaction that reaches commit first succeeds¹¹. Applications must retry on 40001, making this optimistic at the application layer.

Stripe’s idempotency keys implement application-layer OCC for payment APIs. Clients generate a UUID per logical operation; Stripe deduplicates server-side with a 24-hour minimum TTL⁶. Retries reuse the same key. If a second request modifies the payload, Stripe rejects it. This is OCC where the “version” is the idempotency key claim.

sequenceDiagram
    participant C1 as Client 1
    participant C2 as Client 2
    participant DB
    C1->>DB: SELECT balance, version FROM acct WHERE id=42
    DB-->>C1: balance=100, version=7
    C2->>DB: SELECT balance, version FROM acct WHERE id=42
    DB-->>C2: balance=100, version=7
    C1->>DB: UPDATE SET balance=80, version=8 WHERE id=42 AND version=7
    DB-->>C1: 1 row affected
    C2->>DB: UPDATE SET balance=90, version=8 WHERE id=42 AND version=7
    DB-->>C2: 0 rows affected (conflict)
    Note over C2: Reread, retry with version=8

Common Mistakes

Decision Checklist

• What is the measured conflict rate on this path? (Check pg_stat_database.xact_rollback and deadlocks, plus application logs for SQLSTATE 40001, or Spanner Lock Statistics¹⁰.)
• How expensive is redoing the work on retry? (Seconds of compute? External API call?)
• Does the transaction contain a non-idempotent side effect?
• Is your database MVCC-based? (If yes, optimistic is the default happy path.)
• For distributed locks: does your lock service produce a fencing token?
• Have you implemented exponential backoff with jitter on retry paths?

Key Takeaways

• The deciding variable is conflict rate. Below 1%, optimistic wins. Above 10%, pessimistic wins. Between 1-10%, optimistic with backoff and monitoring.
• Default to MVCC + version check for most operations. Escalate to SELECT FOR UPDATE on measured hot rows.
• Never hold a database lock across a network call. Use sagas or idempotency keys for operations with external side effects.
• Distributed locks without fencing tokens are unsafe for correctness. Use ZooKeeper zxid, etcd lease ID, or a database sequence as the token³.
• Retry storms are the silent killer of optimistic systems. Exponential backoff with full jitter is non-negotiable².

Further Reading

• How to do distributed locking: Kleppmann’s canonical critique of Redlock and the fencing-token argument; start here for correctness reasoning about distributed locks.
• Exponential Backoff And Jitter: Marc Brooker’s simulation showing why naive OCC retries collapse at scale and how jitter fixes it.
• Serializable Snapshot Isolation in PostgreSQL: Ports and Grittner, VLDB 2012. The paper behind PostgreSQL’s SSI; shows how optimistic validation achieves serializability without 2PL.
• Transaction locking in Cloud Spanner: wound-wait, cell-level locks, and the four lock modes explained with production examples.
• Designing robust and predictable APIs with idempotency: Stripe’s engineering blog on application-layer OCC for payment APIs; the definitive idempotency-key pattern.
• Is Redlock safe?: Sanfilippo’s rebuttal to Kleppmann. Read both sides before choosing a distributed lock implementation.

Flashcards

References