Join us Nov 24 to watch Insights Agent turn slow queries into PRs. RSVP
Navigation

Reference

Allocate Ticketing Seats Without Double-Booking on Neki

This guide allocates seats on Neki with a conditional UPDATE that moves a seat from available to held in one statement, so two buyers can never hold the same seat. It also assigns any free seat with FOR UPDATE SKIP LOCKED, confirms a hold into a booking, and lets an expired hold be claimed again.

What you will build

seats (event_id, seat_id, status, held_by, held_until)   -> shard group by_event, xxhash(event_id)

claim a seat:  UPDATE seats SET status='held' ... WHERE event_id = 2 AND seat_id = 5
                 AND (status = 'available' OR (status = 'held' AND held_until < now()))
claim any:     the same UPDATE, with seat_id = (SELECT ... LIMIT 1 FOR UPDATE SKIP LOCKED)
confirm:       UPDATE seats SET status='booked' ... WHERE held_by = buyer AND held_until > now()

AI Prompt

Using the PlanetScale CLI (pscale 0.344.0), psql and python3 with psycopg, allocate ticket seats on Neki without double-booking.

Requirements:
- Create the Neki database with --engine neki, --cluster-size PS-10-AWS-ARM-NEKI and --replicas 2, add two shards, and wait until every shard answers a direct SELECT 1 with SET __neki.shard. Use one database role for every step.
- Shard seats by event_id with {"type":"xxhash","columns":["event_id"]}, with PRIMARY KEY (event_id, seat_id), so all seats of an event are on one shard.
- Claim a seat with one UPDATE that changes status only when the seat is available or its hold has expired, and use RETURNING to learn whether the claim won. Do not read the seat first.
- To assign any free seat, put SELECT seat_id ... ORDER BY seat_id LIMIT 1 FOR UPDATE SKIP LOCKED in a subquery of the same UPDATE. The router sends the whole statement to one shard.
- Confirm a hold only for the buyer who holds it and only before held_until.
- Run the verification step below before finishing.

Prerequisites

  • A PlanetScale organization with access to the Neki Platform Preview. Create an account if you do not have one, and see Neki availability and access for how an organization administrator joins the preview.

  • pscale 0.344.0 with a service token that can create databases, psql 18.6, jq, and python3 with psycopg 3.3.6:

export PLANETSCALE_SERVICE_TOKEN_ID="<token id>"
export PLANETSCALE_SERVICE_TOKEN="<token>"
export PLANETSCALE_ORG="<organization name>"
pscale version
pip install "psycopg[binary]==3.3.6"
  • A Neki database with three shards. Create it with these commands:
pscale database create ticketing --engine neki --org "$PLANETSCALE_ORG" \
  --region us-east --cluster-size PS-10-AWS-ARM-NEKI --replicas 2 --wait

pscale role create ticketing main admin --inherited-roles postgres --org "$PLANETSCALE_ORG" --format json > role.json
export PGHOST=$(jq -r .access_host_url role.json) PGUSER=$(jq -r .username role.json) \
       PGPASSWORD=$(jq -r .password role.json) PGPORT=5432 PGDATABASE=postgres PGSSLMODE=require
rm role.json
until psql -At -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done

pscale branch shard create ticketing main --config-profile default --count 2 --org "$PLANETSCALE_ORG"
until [ "$(psql -At -c 'SELECT count(*) FROM __neki.list_shards()')" = "3" ]; do sleep 10; done
for u in $(psql -At -c "SELECT uid FROM __neki.list_shards()"); do
  until psql -At -c "SET __neki.shard='$u';" -c "SELECT 1" >/dev/null 2>&1; do sleep 5; done
done

SHARD_1=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh1'")
SHARD_2=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh2'")
SHARD_3=$(psql -At -c "SELECT uid FROM __neki.list_shards() WHERE name='sh3'")

Note: Output samples are trimmed to the relevant lines. Each shard bills as a three-node cluster. PS-10-AWS-ARM-NEKI lists at $30 per month per shard, prorated to the millisecond. Current rates are on the Neki pricing page. Delete the database when you finish with pscale database delete ticketing --force --org "$PLANETSCALE_ORG".

1. Create the table and load seats

psql -q -v ON_ERROR_STOP=1 \
  -c "CREATE TABLE seats (event_id bigint NOT NULL, seat_id int NOT NULL, status text NOT NULL DEFAULT 'available' CHECK (status IN ('available', 'held', 'booked')), held_by bigint, held_until timestamptz, PRIMARY KEY (event_id, seat_id))" \
  2>&1 | grep -v NOTICE

cat > data-topology.json <<JSON
{
  "shard_indexes": { "event": { "type": "xxhash", "columns": ["event_id"] } },
  "shard_groups": [
    { "uid": "$SHARD_1", "key_ranges": [ { "shard_uid": "$SHARD_1" } ] },
    { "uid": "by_event", "default_shard_index": "event",
      "key_ranges": [ { "shard_uid": "$SHARD_2", "end": "80" }, { "shard_uid": "$SHARD_3", "start": "80" } ] }
  ],
  "databases": { "postgres": { "schemas": { "public": { "tables": {
    "seats": { "shard_group": "by_event" }
  } } } } },
  "default_shard_group": "$SHARD_1",
  "authoritative_shard_group": "$SHARD_1"
}
JSON
pscale branch data-topology update ticketing main --org "$PLANETSCALE_ORG" --format json < data-topology.json | jq -r '.status // "applied"'
sleep 10

psql -q -c "INSERT INTO seats (event_id, seat_id) SELECT e, s FROM generate_series(1, 3) e, generate_series(1, 40) s"
psql -At -c "SELECT 'event ' || event_id || ': ' || count(*) || ' seats' FROM seats GROUP BY event_id ORDER BY event_id"

Real output:

applied
event 1: 40 seats
event 2: 40 seats
event 3: 40 seats

2. Let 100 buyers claim the same seat

cat > seats.py <<'EOF'
import psycopg, sys, threading, collections
from concurrent.futures import ThreadPoolExecutor

mode, event, buyers = sys.argv[1], int(sys.argv[2]), int(sys.argv[3])
outcome, errors, seats, lock = collections.Counter(), collections.Counter(), [], threading.Lock()

CLAIM_ONE = """UPDATE seats SET status = 'held', held_by = %s, held_until = now() + interval '5 minutes'
  WHERE event_id = %s AND seat_id = 5 AND (status = 'available' OR (status = 'held' AND held_until < now()))
  RETURNING seat_id"""
CLAIM_ANY = """UPDATE seats SET status = 'held', held_by = %s, held_until = now() + interval '5 minutes'
  WHERE event_id = %s AND seat_id = (
    SELECT seat_id FROM seats WHERE event_id = %s AND (status = 'available' OR (status = 'held' AND held_until < now()))
    ORDER BY seat_id LIMIT 1 FOR UPDATE SKIP LOCKED)
  RETURNING seat_id"""

def claim(buyer):
    try:
        with psycopg.connect(autocommit=True) as c:
            if mode == "one":
                row = c.execute(CLAIM_ONE, (buyer, event)).fetchone()
            else:
                row = c.execute(CLAIM_ANY, (buyer, event, event)).fetchone()
        with lock:
            outcome["held" if row else "none"] += 1
            if row: seats.append(row[0])
    except Exception as e:
        with lock: errors[str(e).splitlines()[0][:60]] += 1

with ThreadPoolExecutor(16) as pool:
    list(pool.map(claim, range(1, buyers + 1)))
print(" ".join(f"{k}={v}" for k, v in sorted(outcome.items())), "| distinct seats:", len(set(seats)), "| errors:", dict(errors) or "none")
EOF

python3 seats.py one 2 100
psql -At -c "SELECT status || ' seat ' || seat_id FROM seats WHERE event_id = 2 AND status <> 'available'"

Real output:

held=1 none=99 | distinct seats: 1 | errors: none
held seat 5

One hundred buyers raced for seat 5 of event 2. One claim returned a row and the other 99 returned nothing, so one buyer holds the seat. Which buyer wins varies from run to run.

3. Let 100 buyers each take any free seat

psql -At -c "EXPLAIN (NEKI_PLAN, COSTS OFF) UPDATE seats SET status = 'held', held_by = 1, held_until = now() + interval '5 minutes' WHERE event_id = 3 AND seat_id = (SELECT seat_id FROM seats WHERE event_id = 3 AND status = 'available' ORDER BY seat_id LIMIT 1 FOR UPDATE SKIP LOCKED) RETURNING seat_id" | grep -oE 'Route \[[A-Za-z]+\]'

python3 seats.py any 3 100
psql -At -c "SELECT status || ': ' || count(*) FROM seats WHERE event_id = 3 GROUP BY status"

Real output:

Route [EqualUnique]
held=40 none=60 | distinct seats: 40 | errors: none
held: 40

The subquery and the update are one EqualUnique route to the event's shard. Event 3 has 40 seats and 100 buyers: 40 got a seat, 60 got nothing, and the 40 seats were all different. SKIP LOCKED lets a buyer move to the next free seat instead of waiting on a seat another buyer has just claimed.

4. Confirm a hold, and claim an expired one

CONFIRM="UPDATE seats SET status = 'booked', held_until = NULL WHERE event_id = 2 AND seat_id = 5 AND held_by = %s AND status = 'held' AND held_until > now()"
CLAIM="UPDATE seats SET status = 'held', held_by = %s, held_until = now() + interval '5 minutes' WHERE event_id = 2 AND seat_id = 5 AND (status = 'available' OR (status = 'held' AND held_until < now()))"
HOLDER=$(psql -At -c "SELECT held_by FROM seats WHERE event_id = 2 AND seat_id = 5")

echo "confirm by a buyer who does not hold it:  $(psql -At -c "$(printf "$CONFIRM" 999999)")"
psql -q -c "UPDATE seats SET held_until = now() - interval '1 minute' WHERE event_id = 2 AND seat_id = 5"
echo "confirm by the holder after the hold expired: $(psql -At -c "$(printf "$CONFIRM" $HOLDER)")"
echo "another buyer claims the expired seat:    $(psql -At -c "$(printf "$CLAIM" 555)")"
echo "that buyer confirms:                       $(psql -At -c "$(printf "$CONFIRM" 555)")"
echo "a third buyer claims the booked seat:      $(psql -At -c "$(printf "$CLAIM" 777)")"
psql -At -c "SELECT status || ' by ' || held_by FROM seats WHERE event_id = 2 AND seat_id = 5"

Real output:

confirm by a buyer who does not hold it:  UPDATE 0
confirm by the holder after the hold expired: UPDATE 0
another buyer claims the expired seat:    UPDATE 1
that buyer confirms:                       UPDATE 1
a third buyer claims the booked seat:      UPDATE 0
booked by 555

A confirmation succeeds only for the buyer who holds the seat and only before held_until. After the hold expired, the original holder could no longer confirm, and a second buyer claimed the seat with the same statement that claims an available seat. A booked seat cannot be claimed.

Verify the result

psql -At -c "SELECT event_id || '|' || status || '|' || count(*) FROM seats GROUP BY event_id, status ORDER BY event_id, status"

Expected: 1|available|40, 2|available|39, 2|booked|1, 3|held|40. No seat is held or booked by more than one buyer, because every seat is a single row.

How it works

Every seat is one row, and UPDATE ... WHERE status = 'available' ... RETURNING checks and changes it under the row lock. A second claim on the same row waits for the first to commit, then re-evaluates the WHERE clause against the new status and matches nothing. event_id is the shard key, so all seats of an event are on one shard and the claim, the SKIP LOCKED subquery and the confirmation are single-shard statements. The OR clause for expired holds lets a claim take over a stale hold without a cleanup job. The Neki data topology guide describes how a shard key is hashed and placed.

Common issues

An expired hold still says held

The row keeps status = 'held' until another claim changes it. Treat a seat as free when status = 'available' or held_until < now(), as the claim statement does, and use the same condition in any seat map query.

One event is one shard

All seats of an event share a shard, so a very popular on-sale sends all of its claims to that shard. Choose the event as the shard key when seat maps and claims for one event dominate, and read the Neki guides on resharding and on isolating a large tenant if one event needs its own shard.

Reading the seat first

A SELECT followed by an UPDATE is not safe, because two buyers can both read available. Put the availability condition in the UPDATE and check whether it returned a row.

Next steps

  • Delete the database with pscale database delete ticketing --force --org "$PLANETSCALE_ORG".
  • Read the guide on reserving flash-sale inventory without overselling hot rows for counting stock instead of claiming named rows.
  • Read the Neki data topology guide for placing related tables in one shard group.

verification:
  status: verified
  tested_at: "2026-10-29"
  product_version: "pscale 0.344.0, Neki PostgreSQL 18.6 (Platform Preview)"
  command: "seat counts by event and status after the contention, any-seat and confirmation steps"
  expected_result: "1|available|40, 2|available|39, 2|booked|1, 3|held|40"