Two phones at the same table pressed Join in the same moment, and the loser was told the quiz did not exist

Nobody who plays a quiz on PubTrivia has an account. They scan the code on their table, type a name, and they are in. That is deliberate, because the alternative is asking six people in a pub to install something, and the honest conversion rate on that is zero.

Which leaves the question every anonymous multiplayer thing has to answer: what is a player, if not a user?

Here, a player is a row in table_participants, and its primary key is the only identity that exists. Everything interesting follows from that, including a race that for a while told the second person at a table that the quiz was not running.

Where the id lives

On the phone, in localStorage, keyed by table:

type SavedPlayer = { sessionId: string; participantId: string; playerName: string }

const storageKey = (tableId: string) => `pub-trivia:player:${tableId}`

It used to live only in React state, and the failure mode was specific and bad. A phone locks, the browser evicts the page, the player taps back into it and lands on the join form again. In team mode, where one row represents the whole table, their own table was now "already joined" by them, and they were locked out for the rest of the night.

Every read and write of that key is wrapped, because localStorage throws in some private browsing modes rather than returning null:

function savePlayer(tableId: string, player: SavedPlayer): void {
    try {
        window.localStorage.setItem(storageKey(tableId), JSON.stringify(player))
    } catch {
        // Storage unavailable: the game still works, it just cannot resume.
    }
}

The saved value is a claim, not a credential. On resume the server joins the participant row to the session and checks both the id and the session it says it belongs to, and that the session is still in a state you can be in. A participant id from last Tuesday's quiz resolves to nothing.

Three checks that are all read-then-write

Joining has to enforce three rules, and every one of them is a read followed by a write:

• names are unique within a session, case insensitively, so the leaderboard never shows two "Dave"s and nobody can pass as somebody else
• in team mode one row per table, because the table is the contestant
• if late joining is off and the quiz has started, no new players

Read-then-write is where concurrency bugs live, and this is a product whose entire traffic shape is "sixty phones do the same thing in the same second". Two people at table 4 both hit Join. Both read: no row for table 4 yet. Both insert.

The first insert succeeded. The second one got past the participant insert and then hit the partial unique index on the leaderboard, which allows exactly one row per table in team mode. The transaction aborted, and the catch at the bottom of the function turns any thrown error into one result:

} catch (err) {
    console.error('[joinSession] Transaction failed:', err)
    return { success: false, error: GameError.SESSION_NOT_FOUND }
}

So the second person at the table was told the session did not exist. They are sitting in the pub looking at the big screen, where the session plainly does exist.

One line of SQL

The fix is the first statement inside the transaction:

outcome = await db.transaction(async (tx): Promise<Joined | Rejected> => {
    await tx.execute(sql`SELECT pg_advisory_xact_lock(hashtextextended(${`join:${sessionId}`}, 0))`)
    // ... reads, decision, inserts
})

Two details worth unpacking.

hashtextextended is there because Postgres advisory locks are keyed by bigint, and the thing I actually want to serialise on is the string join:<uuid>. That function hashes text to a 64 bit integer with a seed, so the natural key becomes a usable lock id without a side table. The namespacing in the string matters: another feature locking on the same session for a different reason must not pick the same number by accident, so every advisory lock in the codebase hashes a prefixed string rather than a bare id.

_xact_ is the part I would get wrong if I wrote it from memory. The transaction-scoped variant releases at commit or rollback, with no unlock call. The session-scoped pg_advisory_lock would need pg_advisory_unlock, in a finally, on a pooled connection, and a connection handed back to the pool still holding a lock is an outage you get to debug at 9pm on a Thursday. If you are reaching for advisory locks inside a transaction, reach for the xact one.

The scope is one session. Joins at two different quizzes in two different pubs never touch each other. Joins at the same quiz are serialised, which is fine, because the work inside the lock is four small indexed queries and the whole room is joining over a couple of minutes, not in one instant.

Why a unique index was not enough on its own

The indexes are still there, and they are still the thing that makes the invariant true. But a constraint can only fail. The product needs the same collision to produce three different outcomes:

if (config.team_mode) {
    const team = clashes.find((p) => p.table_id === tableId)
    if (team) {
        if (sameName(team.player_name)) {
            return { participantId: team.id, playerName: team.player_name, reclaimed: true }
        }
        return GameError.TABLE_ALREADY_JOINED
    }
}

if (clashes.some((p) => sameName(p.player_name))) return GameError.NAME_TAKEN

Same table, same name: you are the team whose phone died, have your row back. Same table, different name: that table is taken. Same name, different table: pick another name. "23505 unique violation" cannot tell those apart, and the player needs to know which one happened, because two of the three have an action attached.

The reclaim case is my favourite thing in this file, because it is a database decision that exists for a purely physical reason. A phone dies, or its owner goes home, and the team wants back in. The code on their table is the proof they are at that table. Typing the team name again is the proof they are that team. That is enough, and the message they see says so:

This table has already joined. To rejoin on a new phone, enter your team name exactly as before.

A reclaim also skips the broadcast to the WebSocket server, because nobody new arrived. The live scoreboard should not animate a join that did not happen.

One query for both kinds of collision

The clash lookup is a single query with an or, rather than one query per rule:

const clashes = await tx
    .select({ /* id, table_id, player_name */ })
    .from(tableParticipantsTable)
    .where(
        and(
            eq(tableParticipantsTable.session_id, sessionId),
            or(
                eq(tableParticipantsTable.table_id, tableId),
                sql`lower(${tableParticipantsTable.player_name}) = lower(${name})`,
            ),
        ),
    )

It returns everyone this join could possibly collide with, and the decisions above are then made in TypeScript against that one array. Inside a lock, round trips are the thing to count. The index that makes it cheap is (session_id, table_id), which exists for this call site specifically.

The lower(...) = lower(...) half does not use an index, and I know it. A session has tens of participants, not thousands, and the session_id predicate has already narrowed it. A functional index on lower(player_name) is the thing to add on the day that stops being true.

The quiet beneficiary

This insert is inside the same transaction, and it is only correct because of the lock:

rank: sql`(SELECT count(*) + 1 FROM leaderboard_entries WHERE session_id = ${sessionId})`,

Count the rows, add one, that is your starting rank. Two concurrent joins without serialisation both count the same rows and both claim the same rank. With the lock it cannot happen, and the thing to notice is that this line did not change when the bug was fixed. It was wrong in a way nobody would have reported, and the fix for the loud bug fixed it too.

What I would still change

The catch that maps every thrown error to session_not_found is wrong, and it is wrong in the direction that costs me debugging time rather than the player's evening. A deadlock, a connection drop and a genuinely missing session all read the same from the pub floor. The failure is logged, so the information exists, but the player's message should distinguish "it is us" from "it is gone", and right now it cannot.