Optimistic vs Pessimistic Locking: Choosing Your Concurrency Strategy

Two Users, One Row, Zero Good Outcomes

You have a product with 5 units in stock. User A adds it to their cart and hits "purchase." At the same moment, User B does the same thing. Both transactions read stock = 5, both see enough inventory, and both decrement to 4. You've now sold 6 units of a product you had 5 of.

This is a lost update -- one of the most common concurrency bugs in database-backed applications. The fix requires some form of locking. But how you lock determines your application's behavior under contention: whether users wait, whether requests fail, and how your system scales.

There are two fundamental approaches: pessimistic locking (lock first, then work) and optimistic locking (work first, check for conflicts later). They solve the same problem with completely different trade-offs.

Pessimistic Locking: Lock First, Ask Questions Never

Pessimistic locking assumes the worst: conflicts will happen, so prevent them upfront. When you read a row you intend to update, you acquire a lock on it immediately. Other transactions trying to touch the same row have to wait.

In SQL, this looks like:

BEGIN;
-- This locks the row until COMMIT/ROLLBACK
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- No other transaction can read-for-update or modify this row
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;

The key mechanism is SELECT ... FOR UPDATE. It tells the database: "I'm reading this row and I plan to change it -- don't let anyone else touch it until I'm done."

When Pessimistic Locking Works Well

• High contention: Many transactions frequently competing for the same rows
• Critical correctness: Financial operations, inventory management, booking systems
• Short transactions: The lock duration should be brief (milliseconds, not seconds)

The Costs

• Blocking: Other transactions wait, which increases latency under contention
• Deadlocks: If Transaction A locks row 1 then tries to lock row 2, while Transaction B locks row 2 then tries to lock row 1, both are stuck forever. The database detects this and kills one.
• Reduced throughput: Locks serialize access to hot rows, creating bottlenecks

Optimistic Locking: Hope for the Best, Detect the Worst

Optimistic locking takes the opposite bet: conflicts are rare, so don't pay the locking cost upfront. Instead, let everyone read and write freely, but check at write time whether anyone else modified the data since you read it.

The implementation uses a version column (or timestamp):

-- Step 1: Read with version
SELECT stock, version FROM products WHERE id = 42;
-- Returns: stock = 5, version = 3
 
-- Step 2: Application checks stock >= 1, computes new value
 
-- Step 3: Conditional update
UPDATE products
SET stock = 4, version = 4
WHERE id = 42 AND version = 3;
 
-- Step 4: Check rows affected
-- If 0 rows: someone else changed it, retry or fail
-- If 1 row: success, your update landed

The WHERE version = 3 clause is the entire mechanism. If someone else updated the row between your read and write, the version won't match, zero rows get updated, and you know you had a conflict.

When Optimistic Locking Works Well

• Low contention: Conflicts are rare (most rows are read far more than written)
• Long-lived operations: User edits a form for 5 minutes before saving -- you don't want to hold a database lock that entire time
• Distributed systems: When the "lock" needs to span multiple services or databases

The Costs

• Retries: When a conflict is detected, the application must re-read the data and try again
• Wasted work: If your application spent 2 seconds computing before the write failed, that work is lost
• Starvation: Under high contention, one unlucky transaction might fail repeatedly as others keep winning the version race

The Conflict: Side by Side

Here's what happens when two users simultaneously try to debit from the same account:

Both approaches prevent the lost update. The difference is when the conflict is detected and who waits.

Try It Yourself

Use the simulator below to see both strategies handle concurrent access to the same row. Try the conflict scenario: have both users read, then both try to write.

Try these scenarios:

1. Optimistic conflict: Both users SELECT (both see v1). User A writes -200 (succeeds, bumps to v2). User B writes -200 (fails -- version mismatch). User B must re-read and retry.
2. Pessimistic blocking: User A does SELECT FOR UPDATE (gets lock). User B tries to SELECT FOR UPDATE (blocked). User A writes and commits. User B is automatically unblocked and reads the updated value.
3. Clean non-conflicting: User A reads, writes, commits. Then User B reads (sees updated data), writes, commits. No conflict in either strategy.

The Decision Framework

The choice between optimistic and pessimistic isn't about which is "better" -- it's about your contention profile.

Real-World Patterns

Pattern 1: Inventory Check (Pessimistic)

For high-demand products (flash sales, ticket drops), many users hit the same row simultaneously. Pessimistic locking prevents overselling:

BEGIN;
SELECT quantity FROM inventory WHERE sku = 'ITEM-001' FOR UPDATE;
-- Check: quantity > 0
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'ITEM-001';
COMMIT;

Pattern 2: User Profile Edit (Optimistic)

Users rarely edit their profile at the same time from two devices. Optimistic locking avoids unnecessary locks:

-- Read
SELECT name, bio, version FROM profiles WHERE user_id = 42;
-- User edits in UI for 3 minutes...
-- Write
UPDATE profiles SET name = 'New Name', version = version + 1
WHERE user_id = 42 AND version = 7;
-- If 0 rows: show "Someone else edited this, please refresh"

Pattern 3: The Hybrid

Many systems use both. Pessimistic for hot paths with high contention (checkout, transfers). Optimistic for everything else (profile updates, content editing, settings changes). The two strategies aren't mutually exclusive.

Common Mistakes

Mistake 1: Pessimistic locks on long operations

BEGIN;
SELECT * FROM orders WHERE id = 123 FOR UPDATE;
-- Call external payment API (takes 3 seconds)
-- During these 3 seconds, EVERY other transaction
-- touching order 123 is blocked
UPDATE orders SET status = 'paid' WHERE id = 123;
COMMIT;

Never hold a database lock while doing I/O. Read the data, release the lock, do the external call, then re-acquire and update (with an optimistic check).

Mistake 2: Forgetting to handle optimistic conflicts

# WRONG: ignoring the return value
db.execute(
    "UPDATE products SET stock = %s, version = %s WHERE id = %s AND version = %s",
    [new_stock, new_version, product_id, old_version]
)
# What if 0 rows were affected? You just silently lost the update.

Always check affected rows. Zero rows means your update was lost.

Mistake 3: Version column without consistent use

If some code paths update the row without incrementing the version, optimistic locking breaks silently. Every UPDATE to that table must increment the version -- no exceptions.

Performance Comparison

At low contention, optimistic wins because it avoids lock overhead. At high contention, pessimistic wins because retries are more expensive than waiting. The crossover point depends on your specific workload, but ~20-30% conflict rate is roughly where pessimistic starts winning.

Key Takeaways

The decisions that matter:

• Low contention + long operations → Optimistic. Don't hold locks during user think-time or external I/O.
• High contention + short operations → Pessimistic. Let the database queue access instead of burning CPU on retries.
• Always handle conflicts: Optimistic failures must trigger retries. Pessimistic deadlocks must be caught and retried.
• Keep locks short: Whether it's a database lock or a version check, minimize the window between read and write.
• Both can coexist: Use pessimistic for your hottest paths and optimistic for everything else.

The wrong choice won't crash your system on day one. It'll show up as mysterious failures under load, degraded throughput during traffic spikes, or subtle data inconsistencies that only appear when two users happen to click at the same moment.

References

• PostgreSQL: Explicit Locking -- Official docs on row-level locks and FOR UPDATE
• MySQL: InnoDB Locking -- How InnoDB handles lock types
• Martin Kleppmann, Designing Data-Intensive Applications -- Chapter 7 covers write skew and strategies for preventing it
• Hibernate: Optimistic Locking -- How the most popular ORM implements version-based locking