Optimistic or pessimistic locking: choose by conflict rate and think time Intermediate

Problem

Support agent An opens order 101 in the admin screen to change the address. A minute later, Binh opens the same order to cancel it. Both edit for a few minutes and click Save: whoever saves second overwrites the first without knowing it.

This is a lost update; the detailed schedule is in Transaction anomalies.

In the same system, the inventory-decrement API receives hundreds of requests per second on a few hot SKUs. Should these two overwrite-prevention problems share one locking approach?

What to choose (summary first)

Situation	Choose	Condition
Stock decrement: the condition fits in one SQL statement	Atomic UPDATE	Check rowCount; 0 means out of stock, do not retry
Read, run a few ms of app logic, then write; high conflict	SELECT ... FOR UPDATE	Short transaction, no external I/O, lock_timeout set
User edits a form, think time of seconds to minutes	Version column + If-Match	Never hold a transaction across user interaction
Background job reads, computes, writes; low conflict	Version column + retry	At most 3–5 attempts, re-read each time

Think time is the gap between read and write during which the application waits on something outside the database, usually a user.

Core concepts

Optimistic detects the conflict at write time (UPDATE 0). Pessimistic blocks from read time: the later request waits. Atomic folds the check into the write statement itself.

The conflict rate determines what optimistic costs: every conflict is one redo. In Transaction patterns, 50 requests competing for one hot row cost 189 retries.

Loading diagram…

How it works

Optimistic: version column

The orders table has a version integer NOT NULL DEFAULT 1 column. The API returns version via ETag when the form opens; on save, one statement both checks and writes:

UPDATE orders SET shipping_address = $3, version = version + 1
WHERE id = $1 AND version = $2
RETURNING version;

No lock is held between read and write, so An and Binh can think as long as they like without blocking anyone.

Binh saves with a stale version, gets UPDATE 0 and 412; the address An just changed is not overwritten

◀ Prev▶ PlayNext ▶↺ RestartStep 1/6

An opens order 101: the API returns the data with version = 1 (ETag "1"). No transaction is open.

1. An and Binh both hold ETag "1"; the database holds nothing.
2. An saves: row 101 moves to version = 2.
3. Binh saves with version = 1: UPDATE 0, the row is unchanged.
4. The API re-reads; the row exists with version = 2: return 412 with ETag "2".
5. Binh reviews the new version and saves with If-Match: "2": version = 3.

Two overlapping UPDATEs still behave correctly. Under Read Committed, the later statement waits for the first transaction to commit, then re-evaluates WHERE against the new version. Optimistic still takes a row lock, but only for one statement.

UPDATE 0: missing or conflict?

Re-read once to choose the status code:

Re-read	Meaning	HTTP
No row	Does not exist	404
Different version	Conflict	412 when using If-Match; 409 if the version came in the body

RFC 9110 §13.1.1: when If-Match fails, the server must not perform the method and may return 412. Matching uses strong comparison, so the ETag is "2", not W/"2". 409 (§15.5.10) is for conflicts the user can resolve.

Saving an order from the admin screen

✕ Common

const { rows } = await pool.query('SELECT version FROM orders WHERE id = $1', [id]);
if (rows[0].version !== expected) return res.status(409).end();
await pool.query('UPDATE orders SET shipping_address = $2 WHERE id = $1', [id, address]);

✓ Prefer

const ifMatch = req.get('If-Match');
if (!ifMatch) return res.status(428).end(); // RFC 6585
const tag = /^"(\d+)"$/.exec(ifMatch); // W/"2" does not pass strong comparison
if (!tag) return res.status(412).end();
const upd = await pool.query(
  `UPDATE orders SET shipping_address = $3, version = version + 1
   WHERE id = $1 AND version = $2 RETURNING version`,
  [id, Number(tag[1]), address],
);
if (upd.rowCount === 1) return res.set('ETag', `"${upd.rows[0].version}"`).end();
const cur = await pool.query('SELECT version FROM orders WHERE id = $1', [id]);
if (cur.rowCount === 0) return res.status(404).end();
return res.set('ETag', `"${cur.rows[0].version}"`).status(412).end();

Why

The bad version checks and writes in two statements (check-then-act) and never bumps the version. The good version folds the check into WHERE and re-reads only on failure.

The 412 body should follow Problem Details and include the current version.

Pessimistic: SELECT ... FOR UPDATE

FOR UPDATE locks the row until the end of the transaction: UPDATE, DELETE, and other FOR UPDATE must wait, while plain SELECT can still read (see Row-level locks). It fits when the logic between read and write is a few milliseconds inside the app.

Holding FOR UPDATE while the user edits a form

Open a transaction when An opens the order and commit when An clicks Save: the row stays locked for minutes, Binh waits, and the connection sits in idle in transaction. The PostgreSQL docs advise against keeping a transaction open while waiting for user input.

Atomic UPDATE: usually the best

Stock decrement has no think time:

UPDATE inventory SET stock = stock - 1
WHERE sku = $1 AND stock >= 1 RETURNING stock;

No version, no retry: UPDATE 0 means out of stock. Concurrent requests queue only for the duration of one statement.

Operational details

• Every write path must bump the version: API, batch jobs, manual fix scripts; a BEFORE UPDATE trigger can enforce it.
• Retry only for machines: a background job that hits UPDATE 0 re-reads and recomputes, up to 3–5 times with backoff and jitter. A user’s change is not retried automatically: that is exactly the overwrite.
• Repeatable Read: an overlapping UPDATE gets 40001 instead of UPDATE 0; treat both as conflicts.
• lock_timeout and idle_in_transaction_session_timeout both default to 0 (disabled). Set both when using FOR UPDATE; see Lock timeouts.
• xmin as the version? Acceptable when you cannot add a column. But xmin is the ID of the transaction that created the row version: it changes on every UPDATE, even one that changes no data (false conflicts), and it is a 32-bit number that wraps around. Prefer a version column.

Real-world example

Admin order edits and the stock-decrement API in one system

Orders: few conflicts, long think time, so use a version column and 412. Inventory: many requests competing for one SKU, no think time, so use an atomic UPDATE.

Demo on PostgreSQL 17.11 (schema demo_optimistic_locking), one psql process per session; order 101 starts at version = 1.

1. Optimistic. An and Binh both read version = 1; An saves first:

-- An: ... WHERE id = 101 AND version = 1 RETURNING version;
 version
---------
       2
(1 row)

UPDATE 1
-- Binh: ... WHERE id = 101 AND version = 1 RETURNING version;
 version
---------
(0 rows)

UPDATE 0

Re-reading id 101 returns version = 2 (412); the same statement with id 999 also gives UPDATE 0, but the re-read returns 0 rows (404). While An has not committed, Binh’s UPDATE waits about 1.97 s and still gets UPDATE 0.

2. Pessimistic. An runs BEGIN; SELECT ... FOR UPDATE; then leaves the client idle for 4 s; Binh runs the same statement. A third session sees:

 application_name |        state        | wait_event_type |  wait_event   | blocked_by
------------------+---------------------+-----------------+---------------+------------
 session_1_an     | idle in transaction | Client          | ClientRead    | {}
 session_2_binh   | active              | Lock            | transactionid | {16803}

Binh finishes after 2984.470 ms, exactly when An commits; a plain SELECT returns immediately. The session holding the lock is doing nothing: that is the price of think time inside a transaction.

3. Atomic and xmin. SKU-7 has 1 left: the first stock-decrement UPDATE gives UPDATE 1, the second UPDATE 0. A SET note = note statement on order 101 changes xmin from 3126 to 3139 while version stays 2 (the xmin values differ on every run).

Trade-offs

What a version column gains

• Holds no lock or connection during think time.

• Maps directly to HTTP: ETag, If-Match, 412.

• Cheap when conflicts are rare; forms do not wait on each other.

< slot="cons">

• Hot row: retries explode, whereas FOR UPDATE simply queues.

• Forgetting to bump the version on one write path brings lost updates back.

• The user has to review the new version and redo the edit.

What a version column costs

Production checklist

1. Move every read-modify-write path that fits in one SQL statement to an atomic UPDATE.
2. Tables edited through forms have version; every UPDATE sets version = version + 1, batch jobs included.
3. The API returns ETag, requires If-Match for PATCH/PUT (428 if missing), and returns 412 with the current version on mismatch.
4. On UPDATE 0, re-read to choose 404 or 412; only background jobs retry (3–5 times, with jitter).
5. Transactions with FOR UPDATE: no I/O outside the database; set lock_timeout and idle_in_transaction_session_timeout.
6. Track the 412 rate and retry count; if they climb, consider atomic or pessimistic.

Common mistakes

Misconception	Consequence	What to do
Optimistic means no locks at all	Surprise when UPDATEs wait on each other	Locks still exist, just for one statement
UPDATE 0 always means conflict	Returning 412 for a deleted order	Re-read, distinguish 404
On 412 the server retries automatically	Overwrites exactly what it was meant to block	Let the user decide

Self-check

1. An has not committed when Binh sends UPDATE ... WHERE version = 1. Why does Binh wait, and why is the result UPDATE 0?
2. Why does the stock-decrement API not need a version column?
3. Why does xmin cause false conflicts while a version column does not?

Summary

Key takeaways

• Choose along two axes: conflict rate, and whether there is think time outside the database.
• Long think time: version column; on UPDATE 0, re-read and return 404 or 412/409.
• Never hold FOR UPDATE across user interaction; use it for short app logic when conflicts are dense.
• When the condition fits in SQL, an atomic UPDATE is usually best.

References

• PostgreSQL 17 — Row-Level Locks: whom FOR UPDATE blocks.
• PostgreSQL 17 — Deadlocks: do not wait on users inside a transaction.
• PostgreSQL 17 — Read Committed: re-evaluating WHERE.
• PostgreSQL 17 — System Columns: xmin.
• PostgreSQL 17 — Client Connection Defaults: the timeouts.
• RFC 9110: If-Match (§13.1.1), 409 (§15.5.10), 412 (§15.5.13).
• RFC 6585 §3: 428 Precondition Required.