What is the difference between optimistic and pessimistic locking?

Q: What is the difference between optimistic and pessimistic locking?

**Target Audience:** Beginners to databases and concurrency — you know what a row, a transaction and an `UPDATE` are; locking is built up from there. **Difficulty:** Medium **Verified with:** Node 22.22 and Python 3.12 (both print identical output). PostgreSQL defaults quoted below were read with `SHOW` on a local PostgreSQL 16.15 server; the SQL patterns are shown for illustration and were not executed in this run. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Latencies, retry counts and lost updates come from the example program; the **Verified** section quotes its full output. ## 1. Why This Even Matters — A Story First A shop has one shared spreadsheet with the stock count for its best-selling mug: 100. Two assistants, Ana and Ben, each sell a mug at the same moment. Both open the sheet and read 100. Both subtract one and type 99. Both save. The sheet sa

The problem

Question presented to candidate: "Two requests try to update the same row at the same time. What is the difference between optimistic and pessimistic locking for handling that, how would you implement each, and when would you pick one over the other?"

What a strong answer should cover:

The problem both solve: the lost update (read, modify, write by two transactions; one write silently overwrites the other)
Pessimistic: take a lock before reading (SELECT ... FOR UPDATE); others wait until commit; risks are waiting, deadlocks and locks held by slow clients
Optimistic: read a version (or ETag), write with WHERE version = :v; if zero rows changed, someone else won, so re-read and retry; risks are wasted work and retry storms under contention
The deciding factor is contention: rare conflicts favour optimistic (no waiting, no lock held during think time); hot rows favour pessimistic (no wasted retries)
Neighbours worth naming: a single atomic UPDATE ... SET n = n - 1 WHERE n > 0 that needs neither, NOWAIT / SKIP LOCKED, HTTP If-Match returning 412, serializable isolation that aborts and must be retried

Clarifying questions expected:

How often do two writers touch the same row, and how long is the gap between reading and writing (milliseconds of code, or minutes of a human editing a form)?
Is the lock inside one database, or across services (which needs a distributed lock with fencing)?

Code / implementation expected: Optional — the SQL for both patterns, or a simulation comparing them under low and high contention; the example does the simulation.

locking

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners to databases and concurrency — you know what a row, a transaction and an UPDATE are; locking is built up from there. Difficulty: Medium Verified with: Node 22.22 and Python 3.12 (both print identical output). PostgreSQL defaults quoted below were read with SHOW on a local PostgreSQL 16.15 server; the SQL patterns are shown for illustration and were not executed in this run.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Latencies, retry counts and lost updates come from the example program; the Verified section quotes its full output.

1. Why This Even Matters — A Story First

A shop has one shared spreadsheet with the stock count for its best-selling mug: 100. Two assistants, Ana and Ben, each sell a mug at the s

The solution is hidden so you can try first

16 min read · 13 sections

Read the code

Lost updates, SELECT FOR UPDATE and version-checked writes under low and high contention

Open in playground

// Many buyers decrement one stock row (stock = 100). Each transaction reads the row (1 ms),
// thinks for T ms, then writes stock - 1 (1 ms). Fake clock in ms, deterministic.
//  none        : read, then blind UPDATE stock = <value read> - 1          (lost updates)
//  pessimistic : SELECT ... FOR UPDATE first; others queue until COMMIT    (waiting)
//  optimistic  : UPDATE ... SET stock = s - 1, version = v + 1 WHERE version = v; 0 rows -> re-read and retry
function pessimistic(arr, think) {
  const order = arr.map((a, i) => i).sort((x, y) => arr[x] - arr[y] || x - y);
  let free = 0, stock = 100;
  const done = [];
  for (const i of order) {
    const granted = Math.max(arr[i], free); // lock granted when the previous holder commits
    free = granted + 1 + think[i] + 1;
    done[i] = free; stock--;
  }
  return { stock, retries: 0, done };
}
function optimistic(arr, think, check) {
  let stock = 100, version = 0, retries = 0;
  const ev = arr.map((a, i) => ({ t: a + 1, kind: 1, i })); // kind 0 = write attempt, 1 = read finishes
  const seen = [], done = [];
  while (ev.length) {
    let b = 0;
    for (let j = 1; j < ev.length; j++) {
      const x = ev[j], y = ev[b];
      if (x.t < y.t || (x.t === y.t && (x.kind < y.kind || (x.kind === y.kind && x.i < y.i)))) b = j;
    }
    const e = ev.splice(b, 1)[0];
    if (e.kind === 1) { seen[e.i] = [stock, version]; ev.push({ t: e.t + think[e.i] + 1, kind: 0, i: e.i }); continue; }
    if (!check || seen[e.i][1] === version) { stock = seen[e.i][0] - 1; version++; done[e.i] = e.t; }
    else { retries++; ev.push({ t: e.t + 1, kind: 1, i: e.i }); } // version moved: re-read, think again, retry
  }
  return { stock, retries, done };
}
function report(name, arr, think, stall) {
  console.log("== " + name + " ==");
  const runs = [["none", optimistic(arr, think, false)], ["pessimistic", pessimistic(arr, think)], ["optimistic", optimistic(arr, think, true)]];
  for (const [label, r] of runs) {
    const lat = r.done.map((d, i) => d - arr[i]);
    const lost = r.stock - (100 - arr.length);
    let tail;
    if (stall) tail = "buyer 1 done after " + lat[0] + " ms, slowest other buyer " + Math.max(...lat.slice(1)) + " ms";
    else {
      const sum = lat.reduce((x, y) => x + y, 0), avg10 = Math.floor((sum * 10 + Math.floor(lat.length / 2)) / lat.length);
      tail = "avg latency " + Math.floor(avg10 / 10) + "." + (avg10 % 10) + " ms, slowest " + Math.max(...lat) + " ms";
    }
    console.log("  " + label.padEnd(12) + "stock " + String(r.stock).padStart(3) + ", lost updates " + String(lost).padStart(2) +
      ", failed attempts " + String(r.retries).padStart(4) + ", " + tail);
  }
}
const range = (n, f) => Array.from({ length: n }, (_, i) => f(i));
report("1. Low contention: 10 buyers, one every 20 ms, think 5 ms", range(10, (i) => i * 20), range(10, () => 5));
report("2. Hot row: 10 buyers at t=0, think 5 ms", range(10, () => 0), range(10, () => 5));
report("3. Hotter row: 50 buyers at t=0, think 5 ms", range(50, () => 0), range(50, () => 5));
report("4. Buyer 1 stalls 30 s holding its read; 9 others every 20 ms from t=10", range(10, (i) => (i === 0 ? 0 : 10 + (i - 1) * 20)), range(10, (i) => (i === 0 ? 30000 : 5)), true);

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.