Optimistic vs Pessimistic Locking: Preventing Lost Updates

Two people open the same product in an admin panel. Alice changes the price; Bob fixes a typo in the description. Alice saves. Bob saves. Bob's form still had the old price, so his save quietly puts it back. Alice's change is gone, and nobody got an error.

That's a lost update, one of the most common concurrency bugs in web apps. There are two classic defences — pessimistic and optimistic locking — and a third option that's often better than both.

The pattern that causes it

const product = await db.product.find(id)        // 1. read
product.stock = product.stock - quantity          // 2. modify in app code
await db.product.update(id, product)              // 3. write back everything

Between steps 1 and 3, someone else can write. Whatever happened in between is overwritten. This happens in forms (minutes between read and write) and in fast request handlers (milliseconds — but under load, it happens).

Option 0: Atomic updates (no lock needed)

If the change can be expressed as one SQL statement, let the database do it atomically:

UPDATE products
SET stock = stock - 2
WHERE id = 42 AND stock >= 2
RETURNING stock;

No read-modify-write in your code, so there's nothing to race. The AND stock >= 2 guard prevents overselling; if no row is returned, there wasn't enough stock.

Counters, balances, stock levels, "increment views" — use this first. Many lost-update bugs disappear here.

Pessimistic locking: lock it while you work

"Assume conflict will happen, so block others." Lock the row when you read it; others wanting to lock it wait until you commit.

BEGIN;
SELECT * FROM accounts WHERE id = 42 FOR UPDATE;   -- row locked
-- ... app logic, checks, calculations ...
UPDATE accounts SET balance = 90 WHERE id = 42;
COMMIT;                                              -- lock released

Variants:

• FOR UPDATE NOWAIT — fail immediately instead of waiting.
• FOR UPDATE SKIP LOCKED — skip rows others have locked (great for job queues). (Postgres SKIP LOCKED queue)
• FOR NO KEY UPDATE — a lighter lock when you're not changing the key, which doesn't block inserts referencing this row.

Good for: short, high-contention operations inside one request — money transfers, reserving the last seat, anything where retrying is expensive.

Costs: waiting under load, and deadlocks if two transactions lock rows in different orders. Always lock in a consistent order and keep transactions short. (Postgres deadlocks, transactions explained)

Never hold a database lock while waiting for a human (across form loads) or a slow external API call.

Optimistic locking: detect conflicts at save time

"Assume conflict is rare; check when saving." Add a version column:

ALTER TABLE products ADD COLUMN version integer NOT NULL DEFAULT 1;

Read the version along with the data, send it to the form (hidden field), and include it in the update:

UPDATE products
SET price = 15, description = 'Fixed typo', version = version + 1
WHERE id = 42 AND version = 7;

If someone saved in between, the version is now 8, the WHERE matches nothing, and zero rows are updated. Your code checks that:

const result = await db.query(
  `UPDATE products SET price = $1, description = $2, version = version + 1
   WHERE id = $3 AND version = $4`,
  [price, description, id, version],
)
if (result.rowCount === 0) {
  throw new ConflictError('This product was changed by someone else. Reload and try again.')
}

Return 409 Conflict from the API and show the user what changed. (HTTP status codes)

An updated_at timestamp can work as the version, but integers avoid precision and clock problems. Many ORMs support optimistic locking with a version field built in. (What is an ORM?)

Good for: forms and editors (long gaps between read and write), low-contention data, APIs (where ETag / If-Match headers carry the version). (HTTP caching headers)

Costs: users occasionally see a conflict and must retry or merge; under high contention, many retries.

Comparison

	Atomic update	Pessimistic	Optimistic
Mechanism	Single SQL statement	Row lock (FOR UPDATE)	Version check on write
Conflicts	Avoided	Wait	Detected, then retry
Works across human think-time	Yes	No	Yes
High contention	Great	OK (watch deadlocks)	Many retries
Complexity	Lowest	Medium	Medium

How to choose

1. Can it be a single atomic UPDATE? → Do that.
2. A short critical section in one request, high contention, retries costly? → Pessimistic.
3. A user edits something over seconds or minutes? → Optimistic, with a friendly conflict message.

Not just your code: AI agents too

Concurrent edits aren't only human. Background jobs, webhooks and AI agents updating the same records hit the same races. Coding agents generating CRUD endpoints almost always produce the naive read-modify-write; ask for atomic updates or version checks explicitly. (What is CRUD?)

The summary

• Read-modify-write without protection causes silent lost updates.
• Prefer atomic single-statement updates when possible.
• Pessimistic: SELECT … FOR UPDATE inside short transactions.
• Optimistic: a version column checked in the WHERE; zero rows updated = conflict → 409.
• Never hold database locks across user think-time.

---

EasySpawn runs your app beside a real PostgreSQL database on the same server, so Claude Code can reproduce concurrency bugs with real transactions and verify the fix. See how it works or join the waitlist.

Related: Database Transactions Explained · Postgres Deadlocks · Idempotency Keys · Postgres Advisory Locks