Optimistic vs pessimistic locking

Transactions, isolation and locking Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Pick the fix

Two support agents edit the same ticket in a web form for several minutes. Which approach fits?

Options
Hint 1 of 1

Can you hold a database lock while a human types?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A Optimistic: a version column, sent as an ETag, checked with If-Match

Correct. No lock is held during think time; the second save gets 412 and can merge.

Optimistic locking checks for conflicts only at save time. The version read with the form travels as an ETag, and a save with an old version is refused, so no lock is held while people type.

Why not the others?
  • B SELECT ... FOR UPDATE when the form opens. A transaction can't stay open across HTTP requests for minutes.
  • C SERIALIZABLE isolation. Each save is its own short transaction; isolation can't see the other agent's open form.
  • D Last write wins. Silently throws away the first agent's work.

30-second answer

Pessimistic locking takes the lock first (SELECT ... FOR UPDATE) and makes others wait, which is good when conflicts are frequent and work is short. Optimistic concurrency doesn't lock; it checks at write time that nobody changed the row (a version or timestamp in the WHERE clause) and retries on conflict, which is good when conflicts are rare or the edit spans user think time, like a form open for minutes.

Practise saying it · 35 s. No recording, just a timer

Compare optimistic and pessimistic locking.

Say your answer out loud, then check which of these points you covered:

  • Pessimistic: lock first, others wait
  • Optimistic: check version at write
  • Choose by conflict rate and duration
  • ETag and If-Match at the API

Deep dive

Optimistic is the only option across HTTP requests: you can't hold a database lock while a user edits. Send the version as an ETag and require If-Match.

Pessimistic locks held across network calls cause lock queues and deadlocks.

Under high contention optimistic retries waste work and can starve unlucky writers.

Both are about one row or aggregate; cross-row invariants need SERIALIZABLE or constraints.

Common mistakes

  • Using a timestamp with low precision as the version, so two fast writes look identical.
  • Retrying optimistic conflicts forever with no limit or backoff.
  • Locking pessimistically for a multi-minute user edit.

Senior take & follow-ups

Choose by conflict rate and how long the edit lasts, and connect optimistic locking to ETags and 412 at the API edge.

Follow-up questions

Think of your answer first, then open the hint.

  1. How does HTTP express optimistic concurrency?

    Show hint

    ETag on GET, If-Match on PUT, 412 Precondition Failed on conflict.

  2. What does the user see on a conflict?

    Show hint

    Ideally a merge or 'someone else changed this' with their edits kept.

Related questions

Suggest a fix if something here is wrong or out of date.