Optimistic vs Pessimistic Locking in Databases

Two requests read the same row, both compute a new value from what they read, and both write it back — the second write silently overwrites the first as if it never happened. This is a lost update, and it's the reason concurrent database access needs some form of locking. The two standard strategies, optimistic and pessimistic, make opposite bets about how often conflicts actually happen.

The lost update, concretely

Say you're decrementing stock on checkout.

-- request A reads stock = 5
SELECT stock FROM products WHERE id = 42;

-- request B reads stock = 5, at nearly the same time
SELECT stock FROM products WHERE id = 42;

-- both compute 5 - 1 = 4 and write it back
UPDATE products SET stock = 4 WHERE id = 42;
UPDATE products SET stock = 4 WHERE id = 42;

Two units sold, but stock only dropped by one. Neither query failed, neither transaction rolled back — the data is just wrong. Locking exists to make one of these two requests wait for, or fail against, the other.

Pessimistic locking: assume a conflict will happen

Pessimistic locking grabs a lock on the row before you touch it, and holds it until your transaction ends, blocking any other transaction that wants the same lock.

BEGIN;
SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- stock is now locked until this transaction commits or rolls back
UPDATE products SET stock = stock - 1 WHERE id = 42;
COMMIT;

FOR UPDATE (Postgres, MySQL/InnoDB, and most other relational engines support this or an equivalent) tells the database: any other transaction trying to read this row with FOR UPDATE, or trying to update it, has to wait until this one finishes. Request B above simply queues behind request A instead of racing it.

This is straightforward to reason about — there's no possibility of a lost update, full stop — but it comes at a cost: every locked row is a point of contention. If your checkout flow holds that lock across a slow payment API call, everyone else trying to buy the same product waits on it too.

Optimistic locking: assume a conflict is rare

Optimistic locking doesn't lock anything up front. Instead, it tags each row with a version marker, and checks that the version hasn't changed when it goes to write.

-- add a version column
ALTER TABLE products ADD COLUMN version INT NOT NULL DEFAULT 0;

-- read the row along with its current version
SELECT stock, version FROM products WHERE id = 42;
-- suppose stock = 5, version = 7

-- write back conditioned on the version being unchanged
UPDATE products
SET stock = 4, version = 8
WHERE id = 42 AND version = 7;

Check UPDATE's affected-row count. If it's 1, your write succeeded and nobody else touched the row in between. If it's 0, someone else updated the row (and bumped its version) between your read and your write — your data is stale, and the application needs to decide what to do: re-read and retry, or surface a conflict to the user.

async function decrementStock(productId) {
  const { stock, version } = await db.one(
    'SELECT stock, version FROM products WHERE id = $1',
    [productId],
  );

  const result = await db.result(
    'UPDATE products SET stock = $1, version = $2 WHERE id = $3 AND version = $4',
    [stock - 1, version + 1, productId, version],
  );

  if (result.rowCount === 0) {
    throw new ConflictError('Product was modified concurrently, retry');
  }
}

No lock is ever held across application logic, network calls, or user think-time. Contention only becomes visible at the moment of the write, and only when a conflict actually happened.

Choosing between them

• Pessimistic when conflicts are frequent and cheap to serialize — a small number of hot rows (a single popular product's stock, a shared counter, a seat in a booking system) where you'd rather make requests wait than make them retry.
• Optimistic when conflicts are rare and the work between read and write is expensive or long-running — editing a document, updating a user profile, anything involving a human filling out a form between the read and the write. Holding a database lock for the duration of "user is thinking" isn't viable.

A useful heuristic: if you're locking around a network call, a UI interaction, or anything that isn't a tight database transaction, you almost certainly want optimistic locking instead.

Handling the conflict well

A 0-row update from optimistic locking isn't an error condition to swallow — it's information. Three common ways to handle it:

• Retry: re-read the row, reapply the change, try the write again. Works well for simple numeric updates like the stock decrement above, since re-reading gets you a fresh, correct starting point.
• Merge: if the update is more complex (editing a document with multiple fields), re-read and merge the non-conflicting parts, then retry.
• Surface it: for user-facing edits, tell the user "this was changed by someone else since you loaded it" and let them decide, rather than silently overwriting or silently discarding their work.

What you should never do is catch the zero-row-count case and just move on — that's the lost update again, just with extra steps.

Takeaway

Pessimistic locking prevents conflicts by making concurrent access wait its turn; optimistic locking detects conflicts after the fact and leaves it to the application to respond. Neither is universally better — it comes down to how often you expect to collide, and how expensive it is to make a request wait versus retry.