Every lock in our app is one line of SQL, and a 32-bit hash is why two accounts can share one

Nakodo runs people's outreach on a schedule, so a lot of
its code is "check what is left of today's allowance, then spend one". Two
requests doing that at the same moment must not both get the last one. The
whole mechanism is 31 lines, and this is the part that matters:

// src/db/index.ts
export function withLock<T>(key: string, fn: (tx: Tx) => Promise<T>): Promise<T> {
  return db.transaction(async (tx) => {
    await tx.execute(sql`select pg_advisory_xact_lock(hashtext(${key}))`);
    return fn(tx);
  });
}

// Serialises limit checks per user, so two requests can't both spend the last one.
export const withUserLock = <T>(userId: string, fn: (tx: Tx) => Promise<T>) => withLock(userId, fn);

No lock table, no Redis, no leases to expire. pg_advisory_xact_lock is held
until the transaction ends, so there is no unlock call to forget and nothing
to clean up when a serverless function is killed halfway through.
The plan limits this protects are counted in
rows rather than counters, and these locks are the only reason two concurrent
sends cannot both book the last one.

The census

16 call sites in 10 files, and seven distinct key shapes:

Three of those keys are fixed strings, which means every account in the
service queues behind one lock when that sweep runs. That is on purpose: the
sweeps are cron work, they run once, and the point of the lock is that they
run once even if two cron invocations overlap.

The squeeze

hashtext returns an integer:

postgres=# select pg_typeof(hashtext('daily_digest')), hashtext('daily_digest');
 pg_typeof | hashtext
-----------+-----------
 integer   | 447889227

So every key in the table above, uuids included, is folded into 2^32 values
before Postgres ever sees it. You can watch the folding in pg_locks, where a
single-argument advisory lock is stored as a pair:

postgres=# begin; select pg_advisory_xact_lock(hashtext('daily_digest'));
postgres=# select classid, objid from pg_locks where locktype = 'advisory';
 classid |   objid
---------+-----------
       0 | 447889227

A negative hash shows up as classid 4294967295, because the 32-bit value is
sign-extended into the 64-bit lock id. Both halves are visible, and neither of
them is 64 bits of key.

Finding a collision

I started a throwaway Postgres and asked for 200,000 random uuids, which is
roughly the number of accounts a service this shape might have one day:

create unlogged table k as
  select gen_random_uuid()::text as key from generate_series(1, 200000);

select h, array_agg(key) from (select key, hashtext(key) as h from k) t
group by h having count(*) > 1;

Five colliding pairs, against 4.66 expected from the birthday approximation.
One of them:

         h   | keys
 -1111455545 | {17963da0-ab4a-4574-8eca-202bc22ceb7d,
                b1d30740-dd19-43ff-b650-978f27bfc618}

Two different accounts, one lock id. In one session:

begin;
select pg_advisory_xact_lock(hashtext('17963da0-ab4a-4574-8eca-202bc22ceb7d'));

In another:

set lock_timeout = '2s';
select pg_advisory_xact_lock(hashtext('b1d30740-dd19-43ff-b650-978f27bfc618'));
ERROR:  canceling statement due to lock timeout

The second account waits on the first. The odds of at least one such pair
existing, by number of distinct keys in flight:

Why this is not a bug, and where it would become one

A collision over-serialises. Two unrelated things take turns instead of
running at once; nothing is corrupted, nothing is double-spent, no limit is
exceeded. For locks that are held for a few milliseconds around a read and a
write, taking turns is invisible.

It stops being invisible in two places. The first is the fixed keys: if an
account's uuid happened to hash to the same value as daily_digest, that one
account's spending would queue behind the nightly sweep. That is three chances
in 2^32 per account, and I am content to leave it.

The second is the one actually worth fixing. A waiter is not spinning in
JavaScript, it is sitting in a Postgres transaction, holding one of the
connections in a pool configured like this:

postgres(env("DATABASE_URL")!, { prepare: false, max: 5 })

Five slots. Five waiters is the whole pool, and nothing in the app sets
lock_timeout, so a waiter waits as long as it takes. The honest fix is not a
bigger hash, it is a timeout and a retry, and we have not written one.

The thing the type system had to solve

A function that spends an allowance is sometimes called on its own and
sometimes called from inside a lock somebody else already took. Calling
withLock inside withLock would open a second transaction on a second
connection while holding the first, which on a five-slot pool is how you build
a deadlock out of two correct functions.

So the executor is a parameter:

export type Tx = Parameters<Parameters<typeof db.transaction>[0]>[0];
export type Executor = typeof db | Tx;

9 function signatures take exec: Executor = db, which means "use the
transaction if you are in one, otherwise use the pool", and two more take an
Executor with no default at all. Six functions take tx: Tx, which will not
compile if you hand them the pool. And one pair exists twice on purpose:

export async function consumeResearch(userId: string, opts = {}) {
  return withUserLock(userId, (tx) => consumeResearchIn(tx, userId, opts));
}

// The same inside a transaction that already holds the user's lock.
export async function consumeResearchIn(tx: Tx, userId: string, opts = {}) { ... }

The naming convention is the whole safety mechanism: In means "you bring the
transaction".

The finding I did not expect

One of the 16 does hold its transaction across the network. The Stripe sync
reads the subscription from Stripe's API inside the lock:

const change = await withLock(`stripe:${customerId}`, async (tx) => {
  const before = await tx.query.subscriptions.findFirst({ ... });
  if (!before) return null;
  const { data } = await stripe().subscriptions.list({ customer: customerId, status: "all", limit: 10 });
  ...
});

That is deliberate: the webhook fires for every subscription and invoice event,
events arrive late and out of order, and the only way a stale read cannot
overwrite a fresh one is for the read, the decision and the write to be inside
the same serialised block. Nobody ever sees
a plan that disagrees with Stripe, and
the app does plan changes itself rather than
sending people to a portal, so the mirror has to be right.

The cost is a database connection, out of five, held open for one or two
round trips to a third party, in a burst of webhooks. Everything else in those
16 blocks is a read, an arithmetic decision and a write. This one is not, and
writing the census down is how I noticed.

Two keys, one person

The last thing the sweep turned up: limit_email:<user uuid> and the bare
<user uuid> are different locks for the same human. The first guards "has
this person already been emailed about their limit this period", the second
guards their spending. They do not touch the same rows today, so the split is
harmless, but nothing in the code says why there are two, and the next person
to add a per-user lock has no rule to follow except grep.

If you want to see what all of this is protecting, the plan limits are on
the pricing page and
how the sending day works is public. The
numbers in them are the ones these 16 blocks are not allowed to let anybody
exceed.