Optimistic vs Pessimistic Locking: Which One Your Database Actually Needs

Two requests hit the same row at nearly the same instant. One is decrementing inventory for the last unit of something in stock, the other is doing the exact same thing a few milliseconds later. Whether your database handles that correctly, and how much it costs you when it’s not actually contended, comes down to a choice you usually make once, early, and then live with across the whole schema: optimistic locking or pessimistic locking.

Pessimistic locking: stop the collision before it happens

Pessimistic locking assumes conflicts are common enough that preventing them upfront is cheaper than detecting and cleaning them up after. The mechanism is a row lock, acquired the moment a transaction touches the row, held until that transaction commits or rolls back.

BEGIN;
SELECT quantity FROM inventory WHERE sku = 'WIDGET-1' FOR UPDATE;
-- any other transaction running the same SELECT ... FOR UPDATE
-- on this row now blocks until this transaction ends
UPDATE inventory SET quantity = quantity - 1 WHERE sku = 'WIDGET-1';
COMMIT;

FOR UPDATE tells the database: lock this row for writing, and make every other transaction that wants the same lock wait in line. Nothing weird can happen to quantity between the read and the write, because nothing else can touch the row at all during that window.

The cost is exactly what you’d expect from making transactions wait: throughput on that row drops to however fast transactions can process one at a time, and a transaction that holds the lock too long, waiting on a slow network call before committing, for instance, blocks everyone behind it. Pessimistic locking is a good trade when the row really is hot and the alternative, retries, would be expensive or messy. It’s a bad trade applied blanket-wide to rows that see one write a month.

Optimistic locking: let it run, check at the end

Optimistic locking assumes most transactions won’t actually collide, so it skips the upfront blocking and instead checks for a conflict right before the write commits. The standard mechanism is a version column:

ALTER TABLE accounts ADD COLUMN version INT NOT NULL DEFAULT 0;

Read the row, including its current version, do whatever work the application needs, then write back with the version as a condition:

-- read
SELECT balance, version FROM accounts WHERE id = 42;
-- application computes new_balance using the balance it read

-- write, conditioned on the version not having changed
UPDATE accounts
SET balance = :new_balance, version = version + 1
WHERE id = 42 AND version = :version_read;

If nothing else touched the row between the read and the write, the version still matches, the update succeeds, and the row count is 1. If another transaction got there first and incremented the version, this update’s WHERE clause matches zero rows. Nothing is silently overwritten. The application checks the affected row count, sees zero, and knows to retry (re-read the current state and try again) or surface a conflict to the caller.

def update_balance(account_id, delta, max_retries=3):
    for attempt in range(max_retries):
        row = db.execute(
            "SELECT balance, version FROM accounts WHERE id = %s", (account_id,)
        ).fetchone()
        new_balance = row["balance"] + delta
        result = db.execute(
            """UPDATE accounts SET balance = %s, version = version + 1
               WHERE id = %s AND version = %s""",
            (new_balance, account_id, row["version"]),
        )
        if result.rowcount == 1:
            db.commit()
            return new_balance
        db.rollback()  # someone else won, retry with fresh data
    raise ConflictError(f"Too much contention on account {account_id}")

No row was ever locked. Two transactions on the same account can both proceed at once; only the loser has to redo its read-modify-write cycle. This is where the tradeoff actually lives: optimistic locking costs nothing when there’s no conflict, and costs a full retry, wasted work plus latency, when there is one.

Picking between them isn’t about preference

The deciding variable is contention rate weighed against retry cost, not which pattern feels more modern. A shared row that many requests genuinely race for, a limited-quantity item at checkout, a single counter incremented by thousands of concurrent workers, has a high enough collision rate that pessimistic locking’s upfront blocking costs less than optimistic locking’s constant stream of failed, retried writes. A typical user profile update, an order status change one specific order goes through once, has such low collision odds that pessimistic locking’s blocking overhead is paid on every single write for a conflict that almost never happens.

	Pessimistic (FOR UPDATE)	Optimistic (version column)
Best for	High contention, cheap to block	Low contention, retries are cheap
Cost when uncontended	Blocking overhead paid anyway	Near zero
Cost when contended	Transactions queue and wait	Losers redo the whole operation
Failure mode if done wrong	Lock held too long, blocks pile up	Version check skipped, updates silently lost
Typical use	Inventory decrements, seat reservations, counters	Most ordinary row updates

Most production schemas end up using both, not one exclusively. A checkout flow might use FOR UPDATE on the specific inventory row being decremented, because that row really does see bursts of concurrent demand, while every other table in the same schema uses optimistic version checks, because a customer’s shipping address or a support ticket’s status almost never has two writers racing for it at once. Treating locking strategy as a per-table decision instead of a single project-wide default is usually the right level of granularity: it matches the actual shape of where your contention lives, similar to how idempotency keys get applied to the specific endpoints that need retry safety rather than blanket across an entire API.

What actually breaks in practice

The most common pessimistic-locking bug is holding the lock across something slow, a third-party API call, a synchronous email send, inside the same transaction as the row lock. Every other request wanting that row queues behind that slow call, and a minor latency spike upstream turns into a full outage on that table. The fix is almost always to shrink the transaction: do the slow work before acquiring the lock or after releasing it, never while holding it.

The most common optimistic-locking bug is quieter and more dangerous: an UPDATE that omits the version check entirely, or code that doesn’t inspect the affected row count and treats every write as successful. Both produce lost updates that pass every test written against a single-user scenario and only show up under real concurrent load, usually in production, usually against data someone cares about. If you adopt optimistic locking, the row-count check on every write isn’t optional error handling, it’s the entire mechanism.