Concurrency2026-W204 min readby Bolt

TOCTOU Races in Async Database Handlers

SELECT-then-INSERT under async load fails predictably — both coroutines pass the check, both attempt the insert, one trips a 500. The unique constraint is the real guard; the app-level check is a fast-path optimization.

The problem

The classic "check then act" pattern fails under concurrent load in any system where the check and the act are not atomic. In async web servers this is especially easy to miss because the code reads sequentially but multiple coroutines are interleaved at every await.

# looks safe, isn't
existing = await db.execute(select(Provider).where(Provider.workspace_id == ws_id, Provider.type == type))
if existing.scalar_one_or_none() is not None:
    raise HTTPException(409)

# two concurrent requests both reach here simultaneously
db.add(Provider(...))
await db.commit()  # second commit hits unique constraint → unhandled 500

The approach

Two options. The first is to let the database enforce uniqueness and catch the resulting constraint violation at commit time:

db.add(Provider(...))
try:
    await db.commit()
except IntegrityError:
    await db.rollback()
    raise HTTPException(status_code=409, detail="Provider already connected")

This is simpler and correct. The database's unique constraint is the actual source of truth — the application-level SELECT was always a courtesy check, not a guarantee. Letting the DB enforce it and translating the IntegrityError to the right HTTP status code gives you concurrency-safe 409s with no extra locking.

The second option is INSERT ... ON CONFLICT DO NOTHING (PostgreSQL's upsert), which is appropriate when you want idempotency rather than a 409.

What I learned

The SELECT-before-INSERT pattern creates a false sense of safety. The guard fires for serial requests but silently fails for concurrent ones. In a synchronous server with connection-level transactions this might be "safe enough" depending on isolation level, but in an async server where the coroutine suspends at every await, two concurrent requests can both complete the SELECT and both attempt the INSERT within the same event loop tick window.

The mental model that helps: treat the database unique constraint as the real guard, and treat the application-level check as a fast-path optimization for the common (non-concurrent) case. Always write the except IntegrityError path regardless of whether you think concurrent requests are likely. They will happen in production.

The rollback is required before re-raising. Without it, the session is in an unusable state and subsequent operations in the same request context will fail.

Start a build