intermediateLocking — Optimistic vs Pessimistic
An e-commerce site has a flash sale — 1000 users trying to buy the last item simultaneously. Which locking strategy do you use?
Pessimistic locking is appropriate here — high-conflict scenario (1000 users competing for 1 item). Implementation: BEGIN; SELECT * FROM inventory WHERE product_id = 42 FOR UPDATE NOWAIT; -- Check: if 0 stock, ROLLBACK and return 'sold out'; -- If stock > 0: UPDATE inventory SET stock = stock - 1 WHERE product_id = 42; INSERT INTO orders ...; COMMIT; Use NOWAIT to fail fast rather than queuing 999
Ready to master this question?
Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.
Sign in to generate a response