The vanity alias race

Why it matters

Words you need

Race condition

A bug that only appears when two requests overlap in an unlucky order. Each request is correct on its own.

Unique index

A database rule that rejects a second row with the same value. A safety net: it turns a silent duplicate into an error.

Upsert

INSERT … ON CONFLICT DO UPDATE: insert the row, or overwrite it if one already exists. Handy for retries, dangerous when the conflict was the thing you needed to know about.

Idempotent

Safe to repeat. A retried request ends in the same state as the first attempt, instead of an error or a second effect.

Atomic

Done as one step that nothing can interrupt. Another request sees it either not started or fully finished.

Picture it

Alice and Bob both want /launch. Nobody has it yet.

Break it yourself

The fixes people try

• Read back after saving

  Fails
  save; SELECT owner; compare

  The last write wins and each request reads back its own write, so both see themselves as the owner and both report success.

• A mutex in the API

  Fails
  await mutex.acquire()

  A mutex lives inside one process. Two servers have two mutexes, and their requests never wait for each other.

• SELECT … FOR UPDATE

  Fails
  SELECT 1 FROM links … FOR UPDATE

  You cannot lock a row that does not exist yet. There is nothing to lock until someone has saved.

• Advisory lock on the alias

  Works
  pg_advisory_xact_lock(hashtext(alias))

  The second request waits, then sees the first one's save. Correct, but it is extra machinery for something the index already does.

• INSERT … ON CONFLICT DO NOTHING

  Works
  … DO NOTHING RETURNING code

  Claiming and checking are one statement. Exactly one insert gets a row back; every other one gets zero.

• A plain INSERT, catch the duplicate

  Works
  catch (e) { e.code === '23505' }

  Same guarantee when your ORM has no ON CONFLICT: an exception instead of zero rows.

The fix and its cost

Check yourself

1. 1The alias column is a primary key. Why didn't that stop the duplicate?Show answerHide

   The upsert turns the conflict into an update. The one thing that would have caught the race was converted into a silent overwrite.

2. 2Why doesn't SELECT … FOR UPDATE work here?Show answerHide

   It locks existing rows. Before anyone claims the alias there is no row, so there is nothing to lock.

3. 3A user clicks Claim twice because of a slow network. How should that behave?Show answerHide

   The second request gets zero rows back, reads that the owner is the same user, and returns CLAIMED. A retry from the owner succeeds; a claim from anyone else is TAKEN.

The code

import type { Pool } from "pg";

export async function claimAlias(db: Pool, alias: string, dest: string, owner: string) {
  // Check…
  const taken = await db.query("SELECT 1 FROM links WHERE code = $1", [alias]);
  if (taken.rowCount! > 0) return "TAKEN";

  // …then act. Everyone who checked before the first save gets here.
  await db.query(
    `INSERT INTO links (code, dest, owner) VALUES ($1, $2, $3)
     ON CONFLICT (code) DO UPDATE
       SET dest = EXCLUDED.dest, owner = EXCLUDED.owner`,
    [alias, dest, owner],
  );
  return "CLAIMED"; // the upsert never complains
}