The last-admin race, and the lock that fixed it

The admin dashboard has one invariant it must never break: there is always at least one enabled admin. Otherwise the only recovery is editing the database by hand.

The obvious implementation is: count the admins, and if demoting this one would leave zero, refuse. It is also wrong.

The race

Two admins, Alice and Bob, each decide to demote the other at the same moment.

1. Alice's transaction counts admins: 2.
2. Bob's transaction counts admins: 2.
3. Alice demotes Bob. Her transaction sees 1 admin — herself. Fine.
4. Bob demotes Alice. His transaction sees 1 admin — himself. Fine.
5. Both commit. Zero admins.

Each transaction only sees its own uncommitted write. Under Postgres's default isolation level (READ COMMITTED) this is a legitimate interleaving.

The first fix, which was correct and wrong

SERIALIZABLE isolation makes Postgres guarantee that the outcome equals some serial ordering. In the race above it would abort one of the two transactions. Textbook.

The browser test suite disagreed within the hour. Roughly one run in three, an admin action failed with a serialization error — and there were no other admins acting. The conflicts were coming from sign-ups.

Counting admins reads across the whole User table, and under SERIALIZABLE that read takes a predicate lock. Any unrelated insert into User that commits mid-transaction invalidates it. In a test suite creating accounts in parallel, that is constant. Retrying with backoff did not help: the inserts were a steady stream, not a moment.

The second fix

The invariant only needs admin-count changes serialized with each other. Not with sign-ups, not with project edits, not with anything else. That is exactly what an advisory lock is for:

await tx.$executeRaw`SELECT pg_advisory_xact_lock(CAST(${ADMIN_COUNT_LOCK} AS bigint))`;

Every action that can change the admin count takes the same lock key inside its transaction. They queue behind each other; everything else is untouched. The lock releases at commit or rollback, so it cannot leak.

The suite went from 51 seconds to 16 once the spurious conflicts stopped. That number was the real evidence of how much the predicate locks had been costing.

The lesson

Serialize what needs serializing. SERIALIZABLE is a blunt instrument that protects every read your transaction happens to make; a lock names the one thing you actually care about.