SQL traps · Chapter 11 of 16
A lost update
Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Time
- 3 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL traps are free. This one comes with the all labs plan.
What you build: 3 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
repro.sql: two likes, one counted, on purpose
Write repro.sql. It runs in a fresh, empty database where likes.sql has just made the tables users, posts and likes and the function like_post(user_id, post_id), and it can play two sessions at once: a line -- session a or -- session b starts a step on that connection. Add three members and one post with no likes. In session a, begin a transaction and like the post with like_post, and do not commit yet. In session b, have another member like the same post: it waits for session a's lock. Then commit session a, and end in session b with a SELECT that gives one value: the post's likes_count.
The check runs your file twice, each time in a fresh database: beside the original likes.sql it must give 1 (two likes, one counted), and beside a fixed likes.sql it must give 2. So the two likes really have to overlap: a file that only says SELECT 1 fails the second run. Run it with node run.mjs in the terminal: it prints each step, says which one waited for a lock, then prints your last SELECT.
- Task 2
likes.sql: every like counts
Fix like_post in likes.sql so likes_count always matches the likes. One like at a time adds 1 each time. Two likes on the same post at the same moment (the second waiting for the first one's lock) add 2, and three add 3. Every like still inserts its row in likes, no like may fail or be skipped, and like_post returns the post's new count (2 for the like that waited, when two overlap).
Keep the three tables as they are, and keep like_post(user_id, post_id) and what it returns. The check runs its own sessions, and an UPDATE that only writes when the count is still the one it read is as wrong as the bug: it skips a like without an error.
- Task 3
likes.test.sql: a test that would have caught it
Write a SQL test file, likes.test.sql, that would have caught this bug before the busy hours. Use the lab's ok(condition, name) and is(got, want, name): each call is one test, and an error in any step is a failed test. The check runs your file four times, each in a fresh database where a version of likes.sql has just made the tables: beside the original likes.sql at least one test must fail; beside the fixed likes.sql every test must pass and nothing may raise an error; beside a fix that skips the +1 when the count changed, and beside a fix that gives up when the post is busy, at least one test must fail.
So make two likes overlap with session lines, as in your repro, then test both: the post has two rows in likes, and its likes_count is 2. Run it with node run.mjs likes.test.sql.
What you will learn
- repro.sql: two likes, one counted, on purpose
- likes.sql: every like counts
- likes.test.sql: a test that would have caught it
More in Time
Build "A lost update" yourself
A year after Chai aur SQL, MingleSphere sends a Sunday digest, pays its creators and shows investors a dashboard, and things break the way they break in real teams. Anya, the lead backend engineer, hands you each ticket. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL traps are free. This one comes with the all labs plan.