Serialize per-user check-then-write flows with pg_advisory_xact_lock(hashtext(id))

When the same user can race against themselves on a check-then-write flow (multi-tab uploads, double-clicked submits), wrap the whole flow in a transaction holding a per-user advisory lock. Other users keep going without contention.

The pattern

async function withUserLock<T>(
  tx: Prisma.TransactionClient,
  userId: string,
  body: () => Promise<T>,
): Promise<T> {
  await tx.$executeRaw`SELECT pg_advisory_xact_lock(hashtext(${userId}))`;
  return body();
}

await db.$transaction(async (tx) => {
  return withUserLock(tx, userId, async () => {
    const used = await sumUsedBytes(tx, userId);
    if (used + incomingBytes > cap) {
      throw new QuotaExceededError(used, cap);
    }
    return tx.userImage.create({ data: { userId, byteSize: incomingBytes, ... } });
  });
});

Why this and not the alternatives

• pg_advisory_xact_lock (the right one): transaction-scoped. Auto-releases on COMMIT or ROLLBACK. Nothing leaks if the request crashes mid-transaction.
• pg_advisory_lock (avoid): session-scoped. Requires a paired pg_advisory_unlock. One missed unlock path and the connection holds the lock forever.
• SELECT ... FOR UPDATE on a user row: works, but couples the lock to a specific row’s existence and write pattern. Advisory locks are application-named: any flow can use the same key without joining tables.
• Application-level mutex (e.g., Map<userId, Promise> in Node): breaks the moment you run two app instances. Postgres is already shared infrastructure; let it own the lock.

Why hashtext(userId) and not the raw string

pg_advisory_xact_lock takes a bigint (or two int4s). User IDs are typically cuid or uuid strings. hashtext(text) → int4 is the standard collapse: deterministic, cheap, no extension required.

-- Both forms work; the bigint variant is fine and gives a wider key space.
SELECT pg_advisory_xact_lock(hashtext('user-abc-123'));
SELECT pg_advisory_xact_lock(hashtext('user-abc-123')::bigint);

Gotchas

• Acquire the lock INSIDE the transaction, not before. Acquiring on a separate connection releases on that connection’s next idle moment, not on the transaction’s commit. The wait is correct; the release is wrong.
• hashtext collides at scale. Birthday-paradox math puts collision probability around 1% for 10K simultaneously-active users hashing into 2^32. A collision means two unrelated users briefly serialize. No correctness impact, only a small fairness blip. For a multi-tenant high-concurrency tier, pre-namespace the key (e.g., hashtext('image-upload:' || userId)) so different flows can’t collide with each other either.
• Don’t await unrelated I/O while holding the lock. Network calls, AI requests, anything slow belongs outside the $transaction. The lock body should be: read counter, compare, insert row, commit. Keep it short.
• Tests must exercise real concurrency. Mocked Prisma calls won’t reproduce the race the lock fixes. Use a real test database and parallel Promise.all writers to assert serialization.

When you don’t need this

• The read-then-write is genuinely atomic at the SQL layer (UPDATE ... SET col = col + N is — see Prisma increment is atomic).
• The “check” half is a uniqueness constraint the DB can enforce directly (use a UNIQUE constraint, not application code).
• The flow is single-user-single-tab and you can prove it (rare).

Otherwise: any flow where the new row’s validity depends on the existing aggregate of the user’s rows, and where the same user can fire two requests at once, needs serialization.