Six Months of Fighting Race Conditions — Here’s What I Learned
Our e-commerce platform had a subtle bug that only showed up during flash sales. Two users would simultaneously grab the last item in stock, both transactions would succeed, and we’d end up with a negative inventory count. Classic concurrent write problem.
After six months of iterating on our locking strategy — switching approaches, measuring the impact, getting burned by edge cases — I want to share what actually works in production. Not just what looks clean on paper.
Two Fundamentally Different Philosophies
Both strategies tackle the same root problem: data corruption when multiple transactions modify the same row at once. Where they diverge is timing — when to enforce exclusivity, and at what cost.
Pessimistic Locking
Pessimistic locking assumes conflict will happen. So it prevents it upfront by acquiring a lock before reading the data. No other transaction can modify the locked row until you release it.
-- PostgreSQL: Lock the row immediately on read
BEGIN;
SELECT quantity FROM inventory
WHERE product_id = 42
FOR UPDATE; -- Row is now locked
-- Do your business logic here
UPDATE inventory SET quantity = quantity - 1
WHERE product_id = 42;
COMMIT;The FOR UPDATE clause tells PostgreSQL to hold an exclusive lock on that row for the entire transaction. Any other transaction trying to read with FOR UPDATE will block and wait its turn.
Optimistic Locking
Optimistic locking takes the opposite bet: conflicts are rare, so don’t lock anything upfront. Instead, detect conflicts at write time using a version number or timestamp.
-- Table needs a version column
ALTER TABLE inventory ADD COLUMN version INTEGER DEFAULT 0;
-- Read without locking
SELECT quantity, version FROM inventory WHERE product_id = 42;
-- Returns: quantity=10, version=5
-- Update only if version hasn't changed
UPDATE inventory
SET quantity = quantity - 1, version = version + 1
WHERE product_id = 42 AND version = 5;
-- If another transaction already updated it, version is now 6
-- This UPDATE affects 0 rows → conflict detectedYour application checks the affected row count. Zero rows updated means someone else modified the data between your read and write — you retry the operation.
Trade-offs: The Unfiltered Take
Pessimistic Locking
What works well:
- Conflicts never reach the application layer — error handling stays simple
- Ideal when write conflicts are frequent (think: flash sales, seat booking)
- Easier to reason about correctness — the lock enforces the invariant for you
What hurts in production:
- Lock contention kills throughput under high concurrency — transactions queue up fast
- Deadlock risk when transactions acquire locks in different orders
- Long-running transactions hold locks longer, blocking everything else downstream
- Doesn’t play well with read replicas — locks only apply to the primary
Optimistic Locking
What works well:
- Zero lock overhead on reads — scales much better for read-heavy workloads
- Deadlocks are structurally impossible (nothing is locked)
- Works across distributed systems and read replicas without special handling
- Higher throughput when conflicts are genuinely rare (under ~5% of requests)
What hurts in production:
- Retry logic adds real complexity — you need to handle
StaleDataErroreverywhere it can surface - Under high contention, retries pile up and throughput collapses — often worse than pessimistic
- Requires schema changes (version column) on existing tables, which means a migration
- Users see failed operations instead of brief waits — needs thoughtful UX handling
Matching Strategy to Contention Level
After running both in production across a few services, here’s the rule of thumb I’ve settled on:
- High-contention writes (inventory, seat booking, financial accounts) → pessimistic locking
- Low-contention writes (user profile updates, document editing, config changes) → optimistic locking
- Mixed workloads → optimistic by default, pessimistic for specific hot resources
For our flash sale inventory problem, pessimistic locking was the right call. The contention window was tiny — maybe 50–200ms during checkout. Blocking was acceptable. Overselling was not.
PostgreSQL-Specific Tip: SKIP LOCKED
For queue-like patterns, FOR UPDATE SKIP LOCKED is a game-changer. Instead of blocking, transactions skip already-locked rows and process the next available ones:
-- Process pending orders without blocking
SELECT id, customer_id, total
FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;This pattern shines for job queues and batch processors. You just need any available row — not a specific one — so skipping locked rows keeps all workers moving in parallel.
Implementation: Python + SQLAlchemy
Here’s the actual code I use in a FastAPI service backed by PostgreSQL — copy-paste ready.
Optimistic Locking with SQLAlchemy
from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.exc import StaleDataError
class Base(DeclarativeBase):
pass
class Inventory(Base):
__tablename__ = "inventory"
id = Column(Integer, primary_key=True)
product_id = Column(Integer, nullable=False)
quantity = Column(Integer, nullable=False)
# SQLAlchemy uses this column automatically for optimistic locking
version = Column(Integer, nullable=False, default=0)
__mapper_args__ = {
"version_id_col": version # Enables optimistic locking
}
def deduct_inventory(session, product_id: int, qty: int) -> bool:
"""Returns True on success, False on conflict (caller should retry)."""
try:
item = session.query(Inventory).filter_by(
product_id=product_id
).first()
if item.quantity < qty:
return False # Not enough stock
item.quantity -= qty
session.commit() # Raises StaleDataError if version mismatch
return True
except StaleDataError:
session.rollback()
return False # Caller retriesPessimistic Locking with SQLAlchemy
from sqlalchemy import select
from sqlalchemy.orm import Session
def deduct_inventory_pessimistic(
session: Session,
product_id: int,
qty: int
) -> bool:
"""Uses SELECT FOR UPDATE — blocks until lock is acquired."""
# with_for_update() adds FOR UPDATE to the SQL
item = session.execute(
select(Inventory)
.filter_by(product_id=product_id)
.with_for_update()
).scalar_one_or_none()
if item is None or item.quantity < qty:
return False
item.quantity -= qty
session.commit() # Lock released here
return True
# For skip-locked variant (job queue pattern):
def claim_pending_orders(session: Session, batch_size: int = 10):
return session.execute(
select(Order)
.filter_by(status="pending")
.order_by(Order.created_at)
.limit(batch_size)
.with_for_update(skip_locked=True)
).scalars().all()Retry Decorator for Optimistic Locking
Centralise your retry logic. Scattering it across every service method is a maintenance nightmare:
import functools
import time
from sqlalchemy.exc import StaleDataError
def retry_on_conflict(max_retries: int = 3, delay: float = 0.1):
"""Decorator that retries on optimistic locking conflicts."""
def decorator(func):
@functools.wraps(func)
def wrapper(*args, **kwargs):
for attempt in range(max_retries):
result = func(*args, **kwargs)
if result is not False: # Adapt to your return convention
return result
if attempt < max_retries - 1:
time.sleep(delay * (attempt + 1)) # Exponential backoff
raise RuntimeError(
f"Operation failed after {max_retries} retries (conflict)"
)
return wrapper
return decorator
@retry_on_conflict(max_retries=3)
def update_user_profile(session, user_id, data):
# optimistic locking logic here
passHandling Deadlocks (Pessimistic Locking)
Always handle deadlocks explicitly — PostgreSQL will detect and surface them as errors:
from psycopg2 import errors as pg_errors
from sqlalchemy.exc import OperationalError
def safe_checkout(session, order_id):
try:
# Your pessimistic locking logic
process_order(session, order_id)
except OperationalError as e:
if "deadlock detected" in str(e).lower():
session.rollback()
# Log and retry or surface to user
raise RetryableError("Please try again")
raiseOne More Practical Tip
Debugging a gnarly locking issue once, I needed to cross-reference a database CSV export with some JSON config files. I used toolcraft.app/en/tools/data/csv-to-json to do the conversion directly in the browser — nothing to install, nothing uploaded to a server. Saved me 20 minutes of ad-hoc scripting just to verify version column values were consistent across environments.
The Bottom Line
Neither strategy is universally better. Pessimistic locking gives you strong guarantees at the cost of concurrency. Optimistic locking gives you scalability at the cost of retry complexity.
The real skill is identifying which parts of your system are actual contention hotspots. Instrument your slow queries and lock waits first — don’t guess. Start with optimistic locking for most updates, then add pessimistic locking surgically for the handful of high-contention resources that genuinely need it. Monitor lock waits in production (pg_stat_activity and pg_locks are your friends), and revisit your strategy as traffic patterns shift.
