ADR 0019: Atomic updateMany for SOS Accept (Race Condition Fix)

Status: Accepted
Date: 2026-04-14
Deciders: Ideony team

Context

Ideony’s SOS flow broadcasts a dispatch request to multiple nearby professionals simultaneously. When one professional taps “Accept,” the backend must assign that professional to the SOS request and prevent any other professional from also accepting the same request. A naive read-then-update sequence (findFirst status=PENDING → update status=ASSIGNED) creates a race condition: two professionals accepting within milliseconds both read PENDING and both succeed.

Decision

Use a single atomic prisma.sOSRequest.updateMany({ where: { id, status: 'PENDING' }, data: { status: 'ASSIGNED', professionalId } }) call. The count result is checked: if count === 0, the request was already accepted (return 409). If count === 1, this professional won the race; proceed with assignment notifications. No application-level lock or SELECT FOR UPDATE required.

Consequences

Single SQL statement (UPDATE ... WHERE status = 'PENDING') is atomically serialised by PostgreSQL’s row-level locking; only one concurrent UPDATE wins.
No explicit transaction, advisory lock, or Redis distributed lock needed — the predicate WHERE status = 'PENDING' acts as the guard.
updateMany returns { count: N } — zero-count check is a clean, readable race-loss indicator.
Pattern generalises to any “first-writer-wins” concurrency scenario (e.g., booking slot capture).

updateMany does not return the updated record — a follow-up findUnique is needed to fetch the updated SOS request for the response payload; adds one extra round-trip.
Only works correctly if status transitions are one-way (PENDING → ASSIGNED → COMPLETED); concurrent status updates from other directions could still cause issues if not similarly guarded.

Alternatives considered

Read-then-update (findFirst + update) — rejected: classic TOCTOU race; two concurrent accepts both succeed.
SELECT FOR UPDATE in raw SQL — considered: works, but requires prisma.$transaction with raw SQL, loses type safety, more complex code.
Redis distributed lock (SETNX) — considered: overkill for a single-row predicate update; adds network hop to Redis for every SOS accept; Postgres row-lock is sufficient.
Pessimistic locking via Prisma interactive transactions — considered: valid but verbose; atomic updateMany with predicate achieves the same result in fewer lines.