Optimistic vs Pessimistic Locking

Optimistic locking assumes conflicts are rare and checks for them at write time. Pessimistic locking assumes conflicts are likely and prevents them by acquiring exclusive locks before reading or writing.

What It Really Means

When two users try to modify the same data simultaneously, you have a concurrency conflict. There are two fundamental approaches to handling this:

Pessimistic locking says: "Conflicts are likely, so lock the data before anyone reads it. No one else can touch it until I release the lock." This is like reserving a meeting room — once you book it, no one else can use it until your meeting ends.

Optimistic locking says: "Conflicts are rare, so let everyone read and modify freely. At write time, check if anyone else modified the data since I read it. If yes, retry." This is like editing a Google Doc — everyone edits simultaneously, and conflicts are merged or flagged when they occur.

The choice depends on your conflict rate. If conflicts happen in 1 out of 1,000 transactions, optimistic locking avoids 999 unnecessary locks. If conflicts happen in 1 out of 2 transactions, pessimistic locking avoids 500 retries.

How It Works in Practice

Pessimistic Locking — SELECT FOR UPDATE

Variants:

• FOR UPDATE — exclusive lock, blocks other FOR UPDATE and writes
• FOR SHARE — shared lock, allows other reads but blocks writes
• FOR UPDATE NOWAIT — fail immediately instead of blocking
• FOR UPDATE SKIP LOCKED — skip locked rows (useful for job queues)

Optimistic Locking — Version Column

Real-World Example: E-commerce Checkout

Pessimistic (low-volume, high-value items):

Optimistic (high-volume, many items):

Implementation

Optimistic locking with ORM (SQLAlchemy):

Pessimistic locking for job queues (SKIP LOCKED):

Trade-offs

Choose optimistic when:

• Read-heavy workloads with rare writes to the same row
• Long-running operations between read and write (e.g., user editing a form)
• Distributed systems where locks are expensive or impractical

Choose pessimistic when:

• High contention on specific rows (limited inventory, popular items)
• Short-lived transactions where lock duration is minimal
• Correctness is critical and retries are not acceptable

Common Misconceptions

• "Optimistic locking uses no locks at all" — The UPDATE statement still acquires a brief row-level lock. "Optimistic" means no lock is held between the read and the write, not that locks are never used.
• "Pessimistic locking prevents deadlocks" — It can cause deadlocks if two transactions lock rows in different orders. Always lock in a consistent order or use lock timeouts.
• "Optimistic locking scales infinitely" — Under high contention, optimistic locking causes excessive retries. If 100 transactions compete for the same row, 99 fail and retry, then 98 fail again. This is called a retry storm.
• "You must choose one approach for your entire application" — Use optimistic locking for most operations and pessimistic locking for hot-spot rows (limited inventory, counters).

How This Appears in Interviews

1. "How do you prevent two users from buying the last item?" — Pessimistic locking (SELECT FOR UPDATE) for limited inventory. Optimistic locking with retry for general stock decrements.
2. "Design a concurrent ticket booking system" — Pessimistic locking for seat selection (high contention on popular seats). Discuss deadlock prevention.
3. "What happens when two users edit the same document?" — Optimistic locking with version column. On conflict, show a merge UI or last-write-wins.
4. "How do you implement a job queue in PostgreSQL?" — FOR UPDATE SKIP LOCKED for non-blocking worker coordination.

Related Concepts

• MVCC — the database mechanism that enables optimistic concurrency
• Database Transactions — locking operates within transaction boundaries
• ACID Properties — locking strategies implement the Isolation guarantee
• Connection Pooling — long-held locks interact with connection pool exhaustion
• Tail Latency — lock contention is a common cause of p99 spikes
• System Design Interview Guide
• Algoroq Pricing — access all concept deep-dives