Building a ticket booking system in Nuxt that doesn't double-book under load
The classic interview question with real code. SELECT FOR UPDATE vs atomic UPDATE, reservation TTL with Nitro tasks, idempotency keys
Engineering interview might land on "design a ticket booking system". The expected answer - transactions, row locking, TTL, idempotency - is mostly correct. But there's a gap between knowing the answer and knowing why the naive version silently fails under load.
Here's the full implementation in Nuxt and Nitro, starting with the failure mode.
What actually breaks without this
Two users try to book seat 14B at the same time. Your naive implementation:
User A: SELECT * FROM seats WHERE seat_number = '14B' → status: 'available' ✓
User B: SELECT * FROM seats WHERE seat_number = '14B' → status: 'available' ✓
User A: UPDATE seats SET status = 'reserved' WHERE seat_number = '14B'
User B: UPDATE seats SET status = 'reserved' WHERE seat_number = '14B'
User A: Booking confirmed. 🎉
User B: Booking confirmed. 🎉
Both users have a confirmed booking for the same seat. Under normal traffic this is rare. Under a ticket sale for anything popular - a concert, a flight opening - it's a certainty. This is called TOCTOU (Time of Check to Time of Use): you check a condition, then act on it, but the condition can change between the check and the action.
The schema first
Before any code, the schema. The database is the source of truth and must enforce consistency even if application code is buggy:
CREATE TABLE seats (
id SERIAL PRIMARY KEY,
event_id UUID NOT NULL REFERENCES events(id),
seat_number VARCHAR(10) NOT NULL,
status TEXT NOT NULL DEFAULT 'available'
CHECK (status IN ('available', 'reserved', 'sold')),
reserved_by UUID,
reserved_until TIMESTAMPTZ,
-- This is your last line of defense
UNIQUE (event_id, seat_number)
);
CREATE TABLE bookings (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
event_id UUID NOT NULL REFERENCES events(id),
seat_id INTEGER NOT NULL REFERENCES seats(id),
user_id UUID NOT NULL,
idempotency_key TEXT UNIQUE, -- prevents double-charge
status TEXT NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'confirmed', 'cancelled')),
created_at TIMESTAMPTZ DEFAULT NOW()
);
Two things worth noting:
UNIQUE (event_id, seat_number) is the schema-level guarantee. If your application code has a bug and two transactions somehow both reach the INSERT stage for the same seat, the database rejects the second one with a unique constraint violation. Application logic prevents the problem. The constraint catches the impossible case.
CHECK (status IN (...)) means the database rejects invalid state transitions. You can't accidentally set status to 'booked' because a developer mistyped it.
With Drizzle ORM:
import { pgTable, serial, uuid, varchar, text, timestamp, unique, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
export const seats = pgTable('seats', {
id: serial('id').primaryKey(),
eventId: uuid('event_id').notNull().references(() => events.id),
seatNumber: varchar('seat_number', { length: 10 }).notNull(),
status: text('status').notNull().default('available'),
reservedBy: uuid('reserved_by'),
reservedUntil: timestamp('reserved_until', { withTimezone: true }),
}, (t) => ({
uniqueSeat: unique().on(t.eventId, t.seatNumber),
statusCheck: check('status_check', sql`${t.status} IN ('available', 'reserved', 'sold')`),
}))
export const bookings = pgTable('bookings', {
id: uuid('id').primaryKey().default(sql`gen_random_uuid()`),
eventId: uuid('event_id').notNull(),
seatId: serial('seat_id').notNull().references(() => seats.id),
userId: uuid('user_id').notNull(),
idempotencyKey: text('idempotency_key').unique(),
status: text('status').notNull().default('pending'),
createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
})
The core problem: checking then acting is always wrong
The 6-point interview answer says "use a transaction with SELECT FOR UPDATE." That's correct - but it's not the only solution, and it's not always the best one.
There are two approaches to this problem: pessimistic locking and optimistic locking.
Pessimistic locking: SELECT FOR UPDATE
Lock the row before reading it. Nobody else can touch it until your transaction commits.
export default defineEventHandler(async (event) => {
const { eventId, seatId, userId } = await readBody(event)
const seat = await db.transaction(async (tx) => {
// Lock the row - anyone else waiting on this seat blocks here
const [row] = await tx
.select()
.from(seats)
.where(and(eq(seats.id, seatId), eq(seats.eventId, eventId)))
.for('update') // SELECT ... FOR UPDATE
if (!row || row.status !== 'available') {
throw createError({ statusCode: 409, message: 'Seat no longer available' })
}
const [updated] = await tx
.update(seats)
.set({
status: 'reserved',
reservedBy: userId,
reservedUntil: new Date(Date.now() + 10 * 60 * 1000), // 10 minutes
})
.where(eq(seats.id, seatId))
.returning()
return updated
})
return { seat }
})
This works. Under concurrent requests, the second transaction that reaches the SELECT ... FOR UPDATE line will wait until the first transaction commits. It then reads the updated row (status: 'reserved') and throws the 409.
The tradeoff: rows are locked for the duration of the transaction. Under high concurrency, this creates a queue of waiting database connections.
Optimistic locking: atomic UPDATE
Don't lock at all. Instead, express the condition and the mutation as a single atomic statement:
export default defineEventHandler(async (event) => {
const { eventId, seatId, userId } = await readBody(event)
// Single atomic statement: update the seat ONLY IF it's still available
// If someone else got it first, this affects 0 rows
const [reserved] = await db
.update(seats)
.set({
status: 'reserved',
reservedBy: userId,
reservedUntil: new Date(Date.now() + 10 * 60 * 1000),
})
.where(and(
eq(seats.id, seatId),
eq(seats.eventId, eventId),
eq(seats.status, 'available'), // atomic check - if someone else took it, 0 rows updated
))
.returning()
if (!reserved) {
throw createError({ statusCode: 409, message: 'Seat no longer available' })
}
return { seat: reserved }
})
This is a single SQL statement. The database evaluates the WHERE clause and the UPDATE atomically - there's no gap between checking and acting. If status changed between your application reading it and this statement executing, the WHERE eq(seats.status, 'available') clause matches zero rows. You get an empty array back. No lock ever held.
SELECT FOR UPDATE (pessimistic) when you need to read the row's current state as part of complex transaction logic before deciding what to write.For the seat reservation case - where the only decision is "is it available?" - the atomic UPDATE is cleaner and faster.
What about SQLite?
SELECT ... FOR UPDATE is not supported in SQLite. If you're using better-sqlite3, pessimistic locking isn't an option. The atomic UPDATE approach works because SQLite serializes writes at the database level anyway (one writer at a time in WAL mode).
That said - for a real ticket booking system with production concurrency, use PostgreSQL. SQLite's write serialization becomes a bottleneck when you have thousands of concurrent booking attempts.
Reservation TTL: holding seats without losing them forever
The flow is: user reserves seat → goes to payment → payment takes 2 minutes → succeeds or fails. If it fails (card declined, user closes tab), the seat must be released. Without this, a seat can be "reserved" forever by a user who never completed payment.
The reserved_until column handles this. Seats with status = 'reserved' and reserved_until < NOW() are logically available - you just need to clean them up periodically.
Nitro tasks are the right tool:
export default defineTask({
meta: {
name: 'seats:release-expired',
description: 'Release seats where reservation has expired',
},
async run() {
const released = await db
.update(seats)
.set({
status: 'available',
reservedBy: null,
reservedUntil: null,
})
.where(and(
eq(seats.status, 'reserved'),
lt(seats.reservedUntil, new Date()),
))
.returning({ id: seats.id, seatNumber: seats.seatNumber })
return { released: released.length, seats: released }
},
})
Schedule it in Nitro config:
export default defineNuxtConfig({
nitro: {
experimental: { tasks: true },
scheduledTasks: {
// Run every minute
'* * * * *': ['seats:release-expired'],
},
},
})
Also run it inline during availability checks - so a user viewing the seat map doesn't see stale "reserved" seats that have already expired:
export default defineEventHandler(async (event) => {
// Release any expired reservations before returning availability
// This is cheap - the UPDATE WHERE clause is fast with an index on status + reserved_until
await db
.update(seats)
.set({ status: 'available', reservedBy: null, reservedUntil: null })
.where(and(eq(seats.status, 'reserved'), lt(seats.reservedUntil, new Date())))
return db.select({
id: seats.id,
seatNumber: seats.seatNumber,
status: seats.status,
}).from(seats).where(eq(seats.eventId, event.context.params!.id))
})
The TTL is also a weapon
The reserved_until window is exploitable - and on some airlines, it still works.
The trick: you buy a ticket with online check-in included but no assigned seat. A few hours before the flight, you open a second account and start selecting every seat you don't want - middle seats, last row, seats next to the toilet. You don't complete payment on any of them. Each selection attempt triggers a reservation, so those seats appear as status: 'reserved' in the database for a few minutes while the system waits for payment confirmation.
With the undesirable seats temporarily locked out, you open check-in on your real account. The only "available" seats happen to be the window and aisle spots you wanted. Free upgrade, courtesy of the TTL.
This has been documented in travel communities for years. Some airlines have patched it. Others haven't.
The fix from a systems perspective is straightforward: rate-limit reservation attempts per account per event. If one user initiates more than N reservations in a short window without confirming any of them, reject further attempts or flag the account.
export default defineEventHandler(async (event) => {
if (event.method !== 'POST' || !event.path.startsWith('/api/bookings/reserve')) return
const userId = event.context.user?.id
if (!userId) return
const storage = useStorage('cache')
const key = `reserve-attempts:${userId}:${Math.floor(Date.now() / 300_000)}` // 5-minute window
const attempts = ((await storage.getItem<number>(key)) ?? 0) + 1
await storage.setItem(key, attempts, { ttl: 300 })
if (attempts > 5) {
// More than 5 unconfirmed reservation attempts in 5 minutes - likely abuse
throw createError({ statusCode: 429, message: 'Too many reservation attempts' })
}
})
The TTL protects honest users from themselves (abandoned carts, failed payments). Rate limiting on reservation attempts protects you from users who've read this article.
Idempotency keys: the payment double-charge problem
The booking flow:
1. User clicks "Pay"
2. Your server calls Stripe
3. Stripe charges the card ✓
4. Your server writes the booking to the database
5. Network timeout - client never gets the response
6. User clicks "Pay" again
7. Your server calls Stripe again → double charge
This happens more than you'd think. Slow connections, impatient users, automatic retries. Idempotency keys solve it: the client generates a unique key per booking attempt. The server caches its response for that key. If the same request arrives twice, it returns the cached response without reprocessing.
export default defineEventHandler(async (event) => {
const idempotencyKey = getHeader(event, 'Idempotency-Key')
if (!idempotencyKey) {
throw createError({ statusCode: 400, message: 'Idempotency-Key header required' })
}
const storage = useStorage('cache')
// Already processed this request - return cached result
const cached = await storage.getItem<object>(`idempotency:${idempotencyKey}`)
if (cached) return cached
const { seatId, paymentToken } = await readBody(event)
const userId = event.context.user!.id
// Run in transaction: verify reservation still valid + mark as sold + create booking
const booking = await db.transaction(async (tx) => {
const [seat] = await tx
.select()
.from(seats)
.where(and(
eq(seats.id, seatId),
eq(seats.reservedBy, userId),
eq(seats.status, 'reserved'),
gt(seats.reservedUntil, new Date()), // reservation hasn't expired
))
.for('update') // lock here - we're doing multi-step logic
if (!seat) {
throw createError({ statusCode: 409, message: 'Reservation expired or invalid' })
}
// Charge the card
const charge = await stripe.paymentIntents.create({
amount: seat.price,
currency: 'eur',
payment_method: paymentToken,
confirm: true,
idempotency_key: idempotencyKey, // pass through to Stripe too
})
// Mark seat as sold
await tx.update(seats)
.set({ status: 'sold' })
.where(eq(seats.id, seatId))
// Create the booking record
const [created] = await tx.insert(bookings).values({
eventId: seat.eventId,
seatId: seat.id,
userId,
idempotencyKey,
status: 'confirmed',
}).returning()
return created
})
// Cache the response - 24h is enough, booking flows don't retry after days
await storage.setItem(`idempotency:${idempotencyKey}`, booking, { ttl: 86_400 })
return booking
})
The client side - generate the key once per booking attempt and keep it across retries:
export function useBooking() {
// Generated once per checkout session
const idempotencyKey = ref(crypto.randomUUID())
const confirmBooking = async (seatId: number, paymentToken: string) => {
return $fetch('/api/bookings/confirm', {
method: 'POST',
headers: { 'Idempotency-Key': idempotencyKey.value },
body: { seatId, paymentToken },
// On network failure, retry - same key means same result
retry: 2,
retryDelay: 500,
})
}
return { confirmBooking, resetKey: () => { idempotencyKey.value = crypto.randomUUID() } }
}
resetKey() creates a new key for the next booking attempt (after a failed payment, for example).
Cache for reads, database for writes
The availability endpoint gets hit constantly - users refresh the seat map, share links, browsers preload. These reads don't need to touch the primary database on every request:
export default defineCachedEventHandler(async (event) => {
const eventId = event.context.params!.id
return db.select({
id: seats.id,
seatNumber: seats.seatNumber,
status: seats.status,
}).from(seats).where(eq(seats.eventId, eventId))
}, {
maxAge: 5, // 5-second cache - balance freshness vs DB load
name: 'seat-availability',
getKey: (event) => event.context.params!.id,
})
5 seconds is intentional. For a concert going on sale, the seat map will show slightly stale data - a seat might appear available for up to 5 seconds after being taken. That's acceptable. Users will see the conflict when they attempt to reserve (the atomic UPDATE returns 0 rows), get a clear error, and can pick a different seat.
What the interview answer gets slightly wrong here: it implies the cache needs to be invalidated on every booking. At 5-second TTL, you don't need manual invalidation - staleness is bounded and acceptable. Aggressive cache invalidation on every booking creates more complexity than it solves.
Handling spikes
The interview answer mentions a "virtual queue or rate limiter." This is vague. In practice, two separate things:
Rate limiting protects the server. From the system patterns article, Nitro middleware handles this:
export default defineEventHandler(async (event) => {
if (!event.path.startsWith('/api/bookings')) return
const ip = getRequestIP(event, { xForwardedFor: true }) ?? 'unknown'
const key = `rate-limit:booking:${ip}:${Math.floor(Date.now() / 60_000)}`
const storage = useStorage('cache')
const count = ((await storage.getItem<number>(key)) ?? 0) + 1
await storage.setItem(key, count, { ttl: 60 })
if (count > 10) { // max 10 booking attempts per minute per IP
throw createError({ statusCode: 429, message: 'Too many booking attempts' })
}
})
A virtual queue is different - it's what Ticketmaster's "waiting room" is. Instead of processing all requests simultaneously, users enter a queue and are admitted in order. This prevents the database from being hammered by 50,000 concurrent users all trying to book at 10:00am exactly.
The Nuxt implementation uses BullMQ:
import { Queue, Worker } from 'bullmq'
import { redis } from './redis'
export const bookingQueue = new Queue('bookings', {
connection: redis,
defaultJobOptions: {
attempts: 1, // no retries for bookings - idempotency handles client-side retries
removeOnComplete: 500,
removeOnFail: 1000,
},
})
// Worker processes one booking at a time per seat
// concurrency: 50 means up to 50 seats can be processed simultaneously
new Worker('bookings', async (job) => {
const { seatId, userId, idempotencyKey } = job.data
return processReservation(seatId, userId, idempotencyKey)
}, {
connection: redis,
concurrency: 50,
})
export default defineEventHandler(async (event) => {
const { seatId } = await readBody(event)
const userId = event.context.user!.id
const idempotencyKey = getHeader(event, 'Idempotency-Key')!
// Add to queue - returns immediately
const job = await bookingQueue.add('reserve', { seatId, userId, idempotencyKey })
// Client polls this job ID for the result
return { jobId: job.id, status: 'queued' }
})
For most applications, rate limiting is sufficient. A virtual queue adds significant complexity (you need SSE or WebSocket for job status updates, a waiting room UI, queue management). Worth it for a Taylor Swift sale. Not worth it for booking appointments at a small business.
The complete flow
Putting it together:
User views seat map
GET /api/events/:id/seats-cached - served from 5-second cache. Stale is acceptable.
User selects seat 14B
POST /api/bookings/reserve - atomic UPDATE WHERE status = 'available'. Returns 200 or 409. No lock held.
Countdown timer starts (10 minutes)
reserved_until set. Background task runs every minute to release expired reservations.
User completes payment
POST /api/bookings/confirm with Idempotency-Key header. Transaction: verify reservation valid + Stripe charge + mark sold + create booking record.
If payment fails or user abandons
After 10 minutes, seats:release-expired task runs. Seat returns to available.
Verdict on the 6-point answer
| Point | Correct? | Notes |
|---|---|---|
| Single source of truth | Yes | The unique constraint is essential. This is the last line of defense. |
| Atomic reservation | Yes | But: optimistic locking (atomic UPDATE) is usually better than SELECT FOR UPDATE. Both work. |
| Short reservation window | Yes | 10 minutes is more realistic than 5 for payment flows. Implemented via reserved_until. |
| Queue spikes | Partial | Rate limiting is always needed. Virtual queue only for very high-traffic sales. |
| Idempotent booking API | Yes | This one is undersold in the answer - it's what prevents the double-charge scenario. |
| Cache for reads, DB for writes | Mostly | 5-second TTL beats manual invalidation. The rule is correct; the implementation matters. |
The answer is solid for an interview. The gap between "interview answer" and "working code" is mainly:
- You need to handle the payment-booking atomicity - marking the seat as sold and creating the booking record must happen in the same transaction as the Stripe charge confirmation. If the DB write fails after the charge, you have a charged user with no booking.
- The idempotency key must be passed to Stripe too - Stripe supports idempotency keys natively. Using the same key means Stripe won't charge twice even if you call their API twice.
- Optimistic locking scales better -
SELECT FOR UPDATEis fine but creates lock queues under high concurrency. The atomic UPDATE approach avoids this entirely.
The fundamental requirement: make the check and the write a single operation. Whether that's SELECT FOR UPDATE inside a transaction or an atomic UPDATE WHERE - you need to eliminate the gap between "is this available?" and "mark it as mine." TTL, idempotency, caching - all of that protects against the failure modes around that one core operation.
Continue Reading
System patterns in Nuxt
Queues, caching, retries, rate limiting, feature flags - five infrastructure patterns every production Nuxt app eventually needs, and why Nitro makes them less painful.
System design vs Nuxt fullstack developer
20 patterns from distributed systems - caching, circuit breakers, CAP theorem, sharding, event-driven architecture - mapped to real implementations in Nuxt and Nitro.