A user-registration helper. Checks if an account exists, creates it if not. Subtle race: two concurrent registrations with the same email both find nothing, both try to insert. One wins, the other gets a unique-constraint violation surfaced to the user as a 500.
The vulnerable diff
// auth/register.ts
async function registerUser(email: string, name: string) {
// BUG: SELECT-then-INSERT race — both calls can see "not found"
const existing = await db.users.findUnique({ where: { email } });
if (existing) return existing;
return db.users.create({ data: { email, name } });
}What is wrong
Read-then-write decision-making against the same row class produces a race window between the read and the write. Two concurrent calls both `SELECT` and both see no row; both `INSERT`; the unique index lets one win and rejects the other. The application sees a 500. The fix is to push the conditional into the database with `ON CONFLICT DO NOTHING` or an `upsert` operation — the database evaluates the conflict against the index atomically.
The attack
Reproducer: two parallel sign-ups on the same email — happens in real traffic from double-click submits.
seq 1 2 | xargs -P 2 -I{} curl -X POST https://api.example.com/register \
-d '{"email":"alice@example.com","name":"Alice"}'
# Sometimes both succeed (lucky scheduling).
# Sometimes one returns 500: "duplicate key value violates unique constraint"
# Sometimes one is created, the other gets a 200 with stale data.Symptom is intermittent: looks like a flake. Real fix is structural.
Mesrai's review comment
mesraipilot · Bot · reviewed 1 min ago
[mesrai] [code-review] [Logic] [Upsert-Race] [medium]
SELECT-then-INSERT does not implement upsert. Concurrent calls
both pass the existence check, second insert violates the unique
constraint.
Use atomic upsert via ON CONFLICT (or the ORM's `upsert`):
await db.users.upsert({
where: { email },
create: { email, name },
update: {}, // empty = DO NOTHING semantics
});
Or with raw SQL:
INSERT INTO users (email, name)
VALUES ($1, $2)
ON CONFLICT (email) DO NOTHING
RETURNING *;
The conflict is evaluated inside the index lock — no race.The fix
// auth/register.ts — fixed
async function registerUser(email: string, name: string) {
return db.users.upsert({
where: { email },
create: { email, name },
update: {},
});
}The upsert is one statement. The unique-constraint check is part of the INSERT — concurrent inserts serialize on the index lock. First in wins, others fall through to the update branch (or return nothing for DO NOTHING). No race, no application-layer retry, no spurious 500s.
Why human review missed it
Application-layer upsert via read-then-write looks like every other read-modify-write pattern. The race is only triggered under exact concurrency on the same key. Mesrai catches the pattern by flagging every SELECT-then-INSERT against a table where the SELECT predicate matches a unique constraint.
Related rules + further reading
Mesrai rule pack: logic/upsert-via-on-conflict — flags SELECT-then-INSERT against tables with a unique constraint matching the WHERE.
Postgres docs: INSERT ON CONFLICT.
Same fix shape works in MySQL (INSERT ... ON DUPLICATE KEY UPDATE) and SQLite.
Takeaway
Use ON CONFLICT or the ORM's upsert. The database evaluates the conflict atomically. Application-layer upsert is racy by construction.