Two users load the same customer record and both submit an edit. Compare optimistic concurrency control (a version column checked at write time) with pessimistic concurrency control (locking the row at read time via SELECT ... FOR UPDATE): how does each stop one edit from silently overwriting the other, and when would you choose each?
answer
- Lost update = read-modify-write interleaving
- Pessimistic = lock on read, others wait, hold until commit
- Optimistic = version in WHERE, rowcount 0 = conflict
- Locks can't span user think time
- Rare conflicts → optimistic; hot row → pessimistic
basics
~20 sPessimistic locks the row when you read it, so the second writer waits — safe, but it holds locks and blocks. Optimistic takes no lock: you remember a version, and the update applies only if the version is unchanged; otherwise you re-read and retry. Rare conflicts favor optimistic; hot contended rows favor pessimistic.
solid answer
~50 sBoth attack the **lost update** anomaly: two transactions read the same row, each computes a new value from its own stale copy, and the later write erases the earlier one. **Pessimistic** serializes writers up front. The reader takes an exclusive row lock and holds it until commit, so the second transaction blocks, then reads the fresh value. Simple and conflict-free, but it holds locks for the whole unit of work, adds blocking and deadlock risk, and is unusable across user think time — you cannot hold a database lock while a human fills in a form. **Optimistic** takes no lock. You read the row plus its version, and the write carries `WHERE id = ? AND version = ?` while bumping the version. Zero rows updated means someone else changed it: re-read and redo, or report a conflict to the user. Rule of thumb: rare conflicts, long or offline edits, read-heavy traffic → optimistic. Hot rows, short transactions, or work that is expensive/unsafe to redo → pessimistic.
code
sql · 10 lines-- read
SELECT id, name, credit_limit, version FROM customer WHERE id = 42;
-- write (version 7 was what we read)
UPDATE customer
SET credit_limit = 5000,
version = version + 1
WHERE id = 42 AND version = 7;
-- affected rows = 1 -> success
-- affected rows = 0 -> concurrent modificationgo deeper
Be able to define lost update, name the two strategies, and state the version-column mechanism and the rowcount-zero signal.
Add the mechanics: how long each holds locks, deadlock and blocking risk, why locks cannot span think time, and how the version travels to the client.
Discuss contention profiles, retry safety and side effects, starvation under heavy conflict, and mixing both strategies in one system.
Frame it as a workload-shaping decision: which rows are hot by design, whether the schema should be changed so contention disappears, and the throughput/latency curves each strategy produces.
## The anomaly both techniques target A *lost update* happens with a read-modify-write cycle. Transaction A reads `balance = 100`, transaction B reads `balance = 100`, A writes 90, B writes 80 — A's deduction vanished, because B computed its new value from a copy that was already stale. Nothing about this is a bug in either transaction individually; the damage comes from the interleaving. At the common default isolation level (read committed) the engine does not prevent it: both reads were legal, both writes were legal. There are exactly two families of fixes: prevent the interleaving (pessimistic) or detect it at write time (optimistic). ## Pessimistic concurrency control The reader declares intent to write by acquiring an exclusive lock on the row as part of the read — `SELECT ... FOR UPDATE`. The lock lives until the transaction commits or rolls back. A second transaction issuing the same locking read blocks until the first is finished, and then sees the committed new value, so its read-modify-write starts from fresh data. Properties worth naming in an interview: - **Cost is paid always**, even when no conflict would have occurred. Every reader pays lock acquisition and potential waiting. - **Lock hold time equals transaction length.** Anything slow inside the transaction (a remote call, a large batch) is lock time other sessions wait on. - **Deadlocks become possible** when two transactions lock the same rows in different orders; the engine kills one victim, and the application must be prepared to retry anyway. - **It cannot span user think time.** A lock held across an HTTP round trip means one abandoned browser tab freezes a row indefinitely, plus a connection pinned from the pool. - **It is exact.** If your business rule is "only one worker may process this row", pessimistic locking expresses that directly. ## Optimistic concurrency control Each row carries a version counter (an integer bumped on every write, or a row-version/timestamp column the engine maintains). The flow is: read row + version → do the work, possibly outside the transaction and across several user interactions → issue `UPDATE ... SET col = ?, version = version + 1 WHERE id = ? AND version = <the version I read>`. The update is a compare-and-set. The database still takes a row lock while executing that statement, but only for its duration, not across your think time. The affected-row count is the conflict signal: 1 means you won, 0 means someone else wrote the row first and your work is based on stale data. On 0 you either retry the whole unit of work against fresh data, or return a conflict to the user ("this record was changed by someone else"). Properties: - **No cost when there is no conflict** — the common case in most CRUD systems is fast. - **Wasted work when there is one.** Under heavy contention on the same row, many transactions do the work and throw it away; throughput can collapse and individual requests can starve. - **It requires that redo be safe.** If the unit of work already emailed a customer or charged a card, blind retry is wrong; side effects must be idempotent or deferred until after commit. - **It works offline.** The version travels with the data — into a form, an API response, an ETag — so it protects edits that span requests, which is where pessimistic locking simply cannot go. ## Choosing Ask three questions. *How likely is a conflict on the same row?* Rare (per-user or per-order rows) → optimistic; hot single rows (a global counter, a small inventory pool) → pessimistic, or restructure so the hot row stops existing. *How long is the window between read and write?* Anything spanning a user or a network call rules out pessimistic. *How expensive is redo?* Cheap and pure → optimistic retry; expensive or side-effecting → lock first. A hybrid is common and worth mentioning: optimistic for the ordinary edit path, pessimistic (`FOR UPDATE`) for the few short critical sections — decrementing stock, allocating a seat — where conflicts are the norm and the transaction is measured in milliseconds.
- Does using SERIALIZABLE or REPEATABLE READ isolation remove the need for either technique?It changes the failure mode rather than removing the problem. Under snapshot-based implementations a conflicting write typically causes one transaction to abort with a serialization error, so you still need a retry loop — the same code you would write for optimistic conflicts. Other engines implement repeatable read with locking and read-latest-on-write semantics, where a plain read-modify-write can still lose an update. Relying on a specific isolation level is also a global performance decision, whereas a version column or FOR UPDATE is targeted at the row that needs it.
- A row-version timestamp is used instead of an integer counter. What can go wrong?Clock resolution and clock movement. If two updates land inside the timestamp's granularity they can carry the same value, so a stale write passes the check and the update is lost again. Clocks can also move backwards after an NTP correction, or differ between application servers if the timestamp is generated client-side. A monotonically incremented integer, or an engine-maintained row-version column, has none of these hazards.
Pessimistic is checking out the only library copy of a book — nobody else can edit it while you have it. Optimistic is everyone photocopying the page and the librarian rejecting your edits if the original changed while you were writing.
saying these in an interview costs you the question
- Saying the database prevents lost updates automatically at the default isolation level
- Believing optimistic locking takes no locks at all — the UPDATE itself still locks the row briefly
- Holding SELECT ... FOR UPDATE across a user's form-filling session or an external API call
- Reading the version but not including it in the UPDATE's WHERE clause, then trusting the ORM to have handled it
- Ignoring the affected-row count, so a conflicting write silently does nothing