Implementing a high-concurrency system with Postgres

Holding is trickier than it looks: we want to grab the seat if it's free, or take it over if the current hold has expired, but never if someone is actively holding or has reserved it. The tempting version is read-then-write — SELECT to check, then INSERT or UPDATE — and that has a gap between the check and the write where another request slips in.

Instead we do it in one statement. The primary key turns the insert into an upsert: if a row already exists, ON CONFLICT DO UPDATE takes over — but only when its WHERE says the existing hold has expired. The unique constraint and the lock on the conflicting reservation protect this seat. This standalone statement doesn't enforce the user's hold limit; the transaction above adds that protection.

EXCLUDED is a pseudo-table Postgres exposes inside DO UPDATE: it holds the row you just tried to insert (the one that hit the conflict), so EXCLUDED.user_id below means "the user_id from the VALUES clause" — whoever is making this hold attempt.

Reserving is where a naive implementation overbooks. Near the expiry deadline, your hold can lapse and someone else can take the seat while your confirmation is mid-flight. If confirm is a plain UPDATE ... SET status = 'R', you'd hand the seat to a user whose hold is already gone.

So we put every precondition in the WHERE: it's still an H row, still yours, and still within the window at the statement's start. If another writer changes the row while we wait, Postgres checks the updated row against these conditions. A hold that was already expired when this statement began matches nothing:

A seat is available if it has no reservation row at all, or if its only row is a hold that has already expired. The same 30-second rule that governs holds decides visibility here:

The upsert and guarded updates use row locks automatically to protect a single seat. The per-user hold limit spans several seats: two requests can write different reservation rows and never conflict. Putting a count and an insert in one statement alone doesn't prevent that race.

Run it naively and two concurrent requests from the same user both read the same count, both see room under the limit, and both insert. The limit is broken.

Both requests first call pg_advisory_xact_lockwith the same key for this user/event pair. The second request waits until the first commits or rolls back, releasing its lock. At READ COMMITTED, its subsequent statement gets a fresh snapshot that includes the first request's committed hold. Acquiring the lock in a CTE inside the count statement would retain a snapshot from before the wait.

The check and seat insert must finish in this same transaction, on the same connection. Committing after the count and inserting afterward releases the lock too early. A zero-row insert means either the user is at the limit or the seat is unavailable; report success only after commit.

This is a coordination rule: an advisory lock does not automatically lock reservations. Every path that creates a hold or extends its expiry must acquire the same lock first. That's why the refresh operation above also uses it. Confirming a reservation only reduces the number of holds, so its guarded update can stand alone.

Locking the user row would also queue that user's requests for unrelated events, and FOR UPDATEconflicts with foreign-key checks referencing that user. The advisory lock coordinates the user/event pair without a persistent lock row. Use the transaction-scoped function shown here: a session-scoped advisory lock would survive commit and rollback until explicitly released or the connection closes.

Our IDs are UUIDs, while this advisory-lock function accepts a 64-bit integer. hashtextextended hashes a namespaced string containing the event UUID followed by the user UUID, with seed zero. Casting through uuid::text gives a canonical spelling. Every caller must use this same key recipe. A hash collision makes unrelated pairs wait for each other; it does not allow two requests for the same pair to bypass the lock.

Keep the explicit READ COMMITTED setting. An advisory lock does not refresh a transaction's snapshot under REPEATABLE READ, so changing the isolation level would invalidate this count-and-insert strategy.

The index on (event_id, user_id, holding_date), restricted to status = 'H', supports the live-hold lookup. The example's maximum is two, so LIMIT 2inside the counted subquery stops after two matches. For a maximum of 100, use LIMIT 100 and compare the count with 100. The expiry cutoff belongs in the query: a partial index cannot automatically remove entries as time passes.

The lock makes concurrent holds and refreshes for the same user/event pair take turns. Pairs with different lock keys can proceed independently, with the reservation's unique key still resolving competition for the same seat. Atomicity, advisory locks, row locks, and statement snapshots each do a different part of the work.

This is the shape every remaining hard case takes — postponing an expiry, confirming right at the deadline, any "check several rows then decide." It's also exactly what Redis struggles with: its GET/SET pairs aren't atomic, and its handful of compare-and-set operators can't express "read these rows under a lock, then write." In Postgres it's one clause.

To implement this in Redis, you need a Lua script or Redis Function to make the multi-key check and write atomic.