Module lessons (2/4)
Race conditions
Understanding how a database works in isolation on a single terminal is simple, but in a real web scenario there will be hundreds of concurrent "Node/PHP/Go" processes sending requests. This is where the famous Race Conditions are born.
The "Lost Update" problem
Lost Update happens when two threads read the same old row, do some math on it in the JavaScript server and then send back the UPDATE to save the result.
Scenario:
- Product #5 has 10 units.
- User A buys one (Server A reads "10", computes "9").
- In the same millisecond user B buys another before A's purchase finishes the COMMIT phase.
- Server B also reads "10", computes "9".
- Server A sends
UPDATE: set quantity = 9. - Server B sends
UPDATE: set quantity = 9. - Both think they decremented the number successfully. But the final value should have been 8 units! A phantom purchase has been lost.
Solution without heavy transactions (Atomic Updates)
Instead of reading and doing the math outside, let the DB do it during the update itself, which is always synchronized.
-- Wrong
UPDATE products SET stock = 9 WHERE id = 5;
-- Correct and natively thread-safe
UPDATE products SET stock = stock - 1 WHERE id = 5;More extreme anomalies (Dirty Reads & Phantoms)
- Dirty Read: You read someone else's temporary data that hasn't yet been confirmed by a COMMIT (and could suddenly go to ROLLBACK invalidating you).
- Phantom Reads: You do a scan of all the rows, meanwhile a second thread inserts a new record, then you do another scan during the same transaction and you magically find the new record appeared out of nowhere, compromising calculated financial reports.
Atomically modify the customers record for a small string manipulation.\nLet's say we want to rename the 'city' of a customer to add an asterisk. \nDo it by leveraging the row's own value for the one with id=10, concatenating ' - Old' to the current value (Use postgres' || concatenation pipe).
Show hint
UPDATE customers SET city = city || ' - Old' WHERE id = 10;
Solution available after 3 attempts
A classic lost update happens in payments and prices.\nLet's say there's an emergency price hike: Increase the 'unit_price' of the 'order_items' table (for product 1 of order 1) by 20% atomically, i.e. multiplying it * 1.2 instead of inserting the new price manually precomputed in JS.
Show hint
UPDATE order_items SET unit_price = unit_price * 1.2 WHERE order_id = 1 AND product_id = 1;
Solution available after 3 attempts