In previous posts, I wrote about my pet project - a URL shortener written in three programming languages. The prototype works well, but there is one issue: if many clients try to add new URLs concurrently, they can get the same short URL, and that's not correct.

Why could this happen? Available short URLs are stored in the MySQL database with a flag showing if the URL code is used or not. URL codes are unique due to unique index usage.

url_code
id | code | is_used

When a client submits a new URL, the application first fetches one unused URL code record, then updates that record making it used, and returns the URL code. Problems happen when another client fetches the same URL code before the first client flags it as used. Can our DB provide a way to select and update a record in one atomic operation?

Unfortunately, MySQL can't do it. The UPDATE query returns a number of affected records but not the records themselves. I'm sure there are workarounds to solve this issue (adding some unique code when updating the record and then fetching the updated record by that code).

Postgres has this feature out of the box:

UPDATE url_code
SET is_used=1
WHERE id IN (select id from url_code where is_used=0 limit 1)
RETURNING code

My project is in the prototype stage without any real user data. I could simply migrate from one DB to another without complex data migration and massive code updates. Of course, in real production projects, it could be much harder.

Links:

Url shortener on Github