Optimistic vs pessimistic locking

The decision

Two requests can read the same row and then both write. The second write replaces the first, and the first change is lost. Pessimistic locking assumes a clash will happen, so it takes a lock first. A lock makes other writers wait. Optimistic locking assumes a clash is rare, so it takes no lock. It keeps a version number on the row and writes only if the version is still the one it read. The loser gets an error and tries again.

Choose optimistic locking when

• Clashes are rare, below about 5 percent of writes (Assumption: our own limit).
• The user thinks between the read and the write, for example a form that stays open for 3 minutes. A lock cannot be held that long.
• Redoing the work is cheap and has no side effects, such as sending a message or charging a card.
• The read and the write happen in two different web requests, so no database transaction spans both.
• You use a store that has no row locks but has conditional writes. DynamoDB, for example, has them.

Choose pessimistic locking when

• Clashes are common, above about 30 percent of writes (Assumption: our own limit). Retries would waste more work than waiting.
• The work after the read is costly to redo, so a failed attempt would waste it. It must still be short enough to finish while the row is locked, and side effects wait until after the commit.
• The read, the decision and the write happen in one short database transaction, under about 10 ms (Assumption).
• One decision needs several rows, and you can lock them in the same order every time.
• You cannot add a version column to every table or every writer.

If one statement can do the job, use neither. UPDATE stock SET n = n - 1 WHERE id = 7 AND n > 0 checks and writes in one step.

The numbers that change the answer

Clash chance. Assumption: other writers arrive at random, r per second on the same row. Your read and write are w seconds apart. The chance of a clash is 1 minus e to the power of (minus r x w). That is about r x w when it is small.

Cost of retries. If each attempt clashes with chance p, the expected number of attempts is 1 / (1 - p). At p = 0.05 that is 1.05. At p = 0.5 it is 2. At p = 0.9 it is 10.

Retry pile-up. Assumption: 100 buyers read version 7 at the same moment and retry at once. One wins in round 1, and 99 retry. One wins in round 2, and 98 retry. The total attempts are 100 + 99 + ... + 1 = 100 x 101 / 2 = 5,050 for 100 sales. That is 50.5 attempts for each sale. Random waiting before a retry helps, but it does not remove the work.

Cost of waiting. Assumption: a transaction holds the row for 5 ms. One row can finish 1,000 ms / 5 ms = 200 transactions per second. If 2,000 arrive per second, the load is 10 times too high. Assumption: the app has a pool of 50 database connections. Each waiting request holds one. The queue grows by 2,000 - 200 = 1,800 per second. The pool is full after 50 / 1,800 = 0.028 seconds, which is 28 ms. Then every other request, even for other rows, waits for a free connection.

The one-row limit. Even an atomic update that holds the row for 1 ms (Assumption) finishes only 1,000 per second on one row. For 2,000 buyers per second, split the stock into 4 rows. Then 4 x 1,000 = 4,000 per second is possible.

What breaks with each choice

Optimistic.

• A hot row makes most attempts fail and retry, as the pile-up shows. Add random waiting, a limit on retries, or switch to an atomic update.
• A user who typed for 3 minutes loses the work. Show both versions and let the user merge.
• One code path forgets to check the version, and it overwrites everyone. Put the check in one shared place, such as the data layer.
• A retry that repeats a side effect, such as a payment, is a bug. Do the side effect only after the write wins.

Pessimistic.

• Deadlock. Transaction 1 locks row A and wants B. Transaction 2 locks B and wants A. Postgres detects this and aborts one of them. Lock rows in one fixed order to prevent it.
• A slow holder. A lock lasts until the transaction ends. A request that waits for a payment inside the transaction blocks everyone behind it.
• A full connection pool, as in the 28 ms example. Set a lock timeout so waiters fail fast.

What it costs

All numbers here are assumptions.

• Optimistic: one version column, one check in the data layer, and a retry rule. About a day of work for one table. Adding the column to a large live table in steps (add it, fill it, then enforce it) takes a few days.
• Pessimistic: no schema change, but you must test for deadlocks and set timeouts.
• Changing your mind: moving a hot path to pessimistic locking or an atomic update is a code change of a few days. No data moves.

How each level answers

The question is: "Two admins edit the same restaurant menu. How do you stop a lost update?"

SDE-1: Optimistic locking checks a version when it writes. Pessimistic locking locks the row before it reads.

SDE-2: I use a version column, because clashes on a menu are rare and the form stays open for minutes. A lock cannot be held that long. The loser sees "someone else changed this".

Senior: The menu clash chance is 180 / 3,600 = 5 percent, so a retry is rare and cheap. A flash-sale row clashes almost every time, and 100 contenders cause 5,050 attempts, so I use atomic updates on split rows. The fallback for a multi-row decision is FOR UPDATE in a fixed order, with a lock timeout.

Staff: Lost updates cost support tickets and refunds, so I want one shared rule, not a choice for each team. The data library adds a version to every writable table, and hot rows move to atomic updates. I measure version failures, lock wait and deadlocks, and change a table's method above 30 percent failures.

Say it in the interview

For the menu I would use a version column, since clashes are about 5 percent and nobody can hold a lock while typing. For a flash-sale stock row I would use a conditional update on split rows, because retries would pile up. The fallback is a row lock in a fixed order, with a lock timeout.

Common mistakes

• "Optimistic is faster, so always use it." It is faster only when clashes are rare. On a hot row, 100 contenders cause 5,050 attempts.
• Holding a lock while the user thinks or while a payment runs. Everyone behind waits, and the connection pool fills in milliseconds.
• Reading, checking in code, then writing. That has a gap in both styles. The check and the write must be one step.
• Retrying forever. Without a limit and random waiting, retries become the load.

Sources

• DynamoDB, conditional writes (a write succeeds only if a condition is true; the loser gets an error; used when many users change one item): https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/WorkingWithItems.html (opens in a new tab) (checked 26 Oct 2026).
• Postgres, explicit locking (a row lock is held until the end of the transaction; deadlocks are detected and one transaction is aborted): https://www.postgresql.org/docs/current/explicit-locking.html (opens in a new tab) (checked 26 Oct 2026).
• The clash model, the times, the pool size and the limits of 5 and 30 percent are assumptions made for teaching. The other numbers are calculations shown above.