A Counter That Doesn't Lose Clicks

An interview classic: "Your app counts clicks on a link. On a busy day the count comes out too low. Why, and how do you fix it?" The usual culprit is read, add 1, write: the code reads the count, adds 1 in Python and writes the result back. When two requests both read the old value before either one writes, both write the same new value and one click is gone. This is called a lost update.

The idea: let the database read and add in one statement. SET n = n + 1 uses the value in the row at the moment of the write, so no other request can slip in between. Here are both ways, on another table:

# read, add, write: two buyers can both read 5 and both write 4
stock = db.execute("SELECT stock FROM products WHERE id = 2").fetchone()[0]
db.execute("UPDATE products SET stock = ? WHERE id = 2", (stock - 1,))

# one statement: the database reads and writes together, 5 → 4 → 3
db.execute("UPDATE products SET stock = stock - 1 WHERE id = 2")

The tests can't fire two requests at the very same moment, so they bring a hidden rival: a request with the header X-Test-Rival makes another database connection add one click just before your UPDATE runs. With one UPDATE, both clicks count.

GET  /clicks/banner               →  200  {"link": "banner", "clicks": 0}
POST /clicks/banner               →  200  {"link": "banner", "clicks": 1}
POST /clicks/banner      + rival  →  200  {"link": "banner", "clicks": 3}
GET  /clicks/banner               →  200  {"link": "banner", "clicks": 3}
POST /clicks/newsletter  + rival  →  200  {"link": "newsletter", "clicks": 2}
POST /clicks/newsletter           →  200  {"link": "newsletter", "clicks": 3}
POST /clicks/contest              →  404  {"error": "Link not found"}

Your task: the clicks table and GET /clicks/<link> are given. Add POST /clicks/<link>: add one click with a single UPDATE … SET n = n + 1, commit, and answer with get_clicks(link), which also answers 404 for a link that doesn't exist.

Assignment
Follow the Coding Tutorial to add the POST /clicks/<link> route, with one UPDATE that can't lose a click.

Hint
Don't SELECT the count and write it back: the read and the add must happen in the same statement. Call db.commit() before you return get_clicks(link).