CodePark/ questions / Locking, concurrency & deadlocks
START PRACTICINGPRACTICE
Backend question · Databases & SQL › Locking, concurrency & deadlocks

Two requests run this code at the same time for the same product. What goes wrong, and what are three ways to fix it?

const { rows } = await db.query(
  'SELECT stock FROM products WHERE id = $1', [id]);
const stock = rows[0].stock - 1;
await db.query(
  'UPDATE products SET stock = $1 WHERE id = $2', [stock, id]);

This is a lost update: one sale disappears. Both requests read stock 10. Both compute 9. Both write 9. The stock should be 8, but it is 9.

Wrapping the code in a Read Committed transaction does not help. The plain SELECT takes no lock, so both requests can still read 10.

Fix 1, atomic update: let the database do the arithmetic in one statement:
UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock > 0 RETURNING stock
The second update waits for the row lock. Then it works on the committed value, so it gets 9 and writes 8.

Fix 2, pessimistic locking: use one transaction on one connection. SELECT ... FOR UPDATE locks the row when it reads it. The other request waits until the first one commits.

Fix 3, optimistic locking: add a version column. Update with WHERE id = $1 AND version = $2. If zero rows change, someone else changed the row first, so reload it and retry. This suits data that few requests change at the same time (low contention), and long edits by a user.

  • A read-modify-write race makes one update get lost.
  • An atomic UPDATE with an expression like stock - 1 fixes it.
  • SELECT ... FOR UPDATE inside a transaction locks the row so others wait.
  • An optimistic version column detects the conflict, and you retry.