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^[1]. At high contention (> 10%), optimistic collapses into O(N^2) wasted work from retry storms^[2]. Default to optimistic (MVCC + version check) and escalate to SELECT FOR UPDATE on measured hot rows. For distributed locks, always attach a fencing token^[3].

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)^[2:1].

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"^[4]. DynamoDB's conditional writes make OCC a natural fit because most NoSQL access patterns hit distinct keys^[2:2].

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.

Pessimistic pays upfront coordination cost; optimistic defers it to commit and pays only on conflict.

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^[4:2].

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^[3:2].

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^[2:4].

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 = ?^[1:2].

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^[6].

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^[7].

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^[3:3].

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^[3:4][8].

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^[9]. Spanner lets you set ReadLockMode per transaction: pessimistic for hot paths, optimistic for long-running multi-region reads^[4:3].

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^[10]), 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.

Decision flowchart: conflict rate and side-effect profile determine the strategy. Most OLTP paths land on optimistic with escalation for measured hot rows.

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^[5:3]. Conflict resolution uses wound-wait: older transactions abort younger ones, guaranteeing forward progress without deadlocks^[5:4]. Four lock modes (ReaderShared, WriterShared, Exclusive, WriterSharedTimestamp) provide fine-grained concurrency^[5:5].

PostgreSQL SSI is the first production implementation of Serializable Snapshot Isolation (Ports and Grittner, VLDB 2012)^[7:1]. Reads acquire non-blocking SIRead predicate locks; the engine watches for dangerous read-write dependency cycles and aborts one transaction with SQLSTATE 40001^[9:1]. First-committer-wins: the transaction that reaches commit first succeeds^[11]. 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^[6:1]. 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.

OCC version-check pattern: the second writer detects the conflict at commit time and retries with the current version.

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^[10:1].)
• 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^[3:6].
• Retry storms are the silent killer of optimistic systems. Exponential backoff with full jitter is non-negotiable^[2:7].

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#