Building it and loading real data

Design is a claim; building is the proof. This lesson creates the schema, loads realistic data, and — the heart of it — writes the booking as one atomic transaction and then proves that two customers cannot book the same seat. Every command here was run against PostgreSQL 16; the outputs are real.

Create it and load data

The tables from the design lesson go into a database of their own:

createdb cinema        # or: CREATE DATABASE cinema;
psql -d cinema -f schema.sql

Then realistic seed data (module 9's rules: real-looking, in dependency order — screens and movies before shows, before bookings). The seats are generated, not typed by hand, with generate_series:

INSERT INTO screens (name, total_seats) VALUES ('Screen 1', 100), ('Screen 2', 80), ('Screen 3 (IMAX)', 150);

-- 100 seats for screen 1: rows A-J, ten each
INSERT INTO seats (screen_id, row_label, seat_num)
SELECT 1, chr(64 + r), n
FROM generate_series(1,10) AS r, generate_series(1,10) AS n;

chr(64 + r) turns 1→'A', 2→'B' and so on; the cross join of ten rows and ten numbers is a hundred seats in one statement. Load all three screens the same way and the check confirms it:

 movies | seats | shows | customers
--------+-------+-------+-----------
      5 |   330 |     5 |         4

330 seats across the three screens, five movies, five shows. A small, fixed, quotable dataset — module 9's advice on seed data.

The booking, as one atomic transaction

This is the core operation, and it must be all-or-nothing: create the booking row and claim every seat, together, or do nothing. A single transaction with a CTE does it in one round trip:

BEGIN;
WITH chosen AS (
  SELECT s.id AS seat_id FROM seats s
  WHERE s.screen_id = (SELECT screen_id FROM shows WHERE id = 1)
    AND (s.row_label, s.seat_num) IN (('A',2), ('A',3))
),
new_booking AS (
  INSERT INTO bookings (show_id, customer_id, amount_paise)
  SELECT 1, 1, (SELECT count(*) FROM chosen) * (SELECT price_paise FROM shows WHERE id = 1)
  RETURNING id
)
INSERT INTO booking_seats (booking_id, show_id, seat_id)
SELECT (SELECT id FROM new_booking), 1, seat_id FROM chosen;
COMMIT;

Read it top to bottom: find the chosen seats, insert the booking (computing the amount from the seat count times the show price), and link every chosen seat to it — one transaction, so a failure anywhere rolls back the whole thing. The result:

 id | amount_paise |  seats
----+--------------+---------
  1 |        50000 | {A2,A3}

Two seats at ₹250 = ₹500, recorded as 50000 paise. The amount was computed in the database from the truth, not passed in by the client — so it cannot disagree with the seats booked.

Proving double-booking is impossible

Now the requirement that justifies the whole relational choice. A second customer tries to book seats A3 and A4 for the same show — but A3 is already taken:

BEGIN;
WITH chosen AS (
  SELECT s.id FROM seats s
  WHERE s.screen_id = (SELECT screen_id FROM shows WHERE id = 1)
    AND (s.row_label, s.seat_num) IN (('A',3), ('A',4))
), new_booking AS (
  INSERT INTO bookings (show_id, customer_id, amount_paise)
  SELECT 1, 2, ... RETURNING id
)
INSERT INTO booking_seats (booking_id, show_id, seat_id)
SELECT (SELECT id FROM new_booking), 1, id FROM chosen;
COMMIT;

ERROR:  duplicate key value violates unique constraint "booking_seats_show_id_seat_id_key"
DETAIL:  Key (show_id, seat_id)=(1, 3) already exists.
ROLLBACK

The database refused it. And look at what rolled back: not just the A3 insert — the entire transaction, including the booking row and the A4 claim. The second customer gets a clean failure, seat A4 is not left half-booked to a booking that never completed, and seat A3 is still cleanly the first customer's. This is atomicity and the UNIQUE constraint working together, and it is precisely the guarantee the brief demanded.

Why this beats "check then insert" in application code. Had the app done SELECT ... WHERE seat free then INSERT, two concurrent requests could both pass the check before either inserts — the lost-update race from module 6 — and both book A3. The constraint closes that window at the database level: even if both transactions reach the INSERT at the same instant, one commits and the other hits the UNIQUE violation. The correctness does not depend on application timing at all.

Handling the failure in application code

The database guarantees correctness; the application's job is to turn the error into a good user experience:

try:
    with conn.transaction():
        # ... the booking transaction above ...
        pass
except psycopg.errors.UniqueViolation:
    # someone took one of these seats first
    return "Sorry — one of those seats was just booked. Please pick again."

Catch the specific UniqueViolation, refresh the seat map, and let the customer choose again. Note this is a legitimate failure to handle, not a bug to prevent — under concurrency, someone will lose the race, and the system stays correct precisely because it does.

Concurrency, honestly

Run the two booking transactions genuinely simultaneously and the outcome is the same: the UNIQUE constraint serialises the conflicting INSERTs, one commits, the other blocks briefly then fails with the violation (or, if the first rolls back, succeeds). This is the module-6 machinery — READ COMMITTED, row locks on the unique index — doing its job. You do not need SERIALIZABLE here, because the UNIQUE constraint is the specific guarantee you need, and it holds at the default isolation level.

What is built

A working booking data layer: a schema that models the domain, realistic data, an atomic booking operation, and a proven guarantee against double-booking. What is missing is everything transient and fast — the seat holds during checkout, the availability cache, the rate limits — which is the Redis layer in the final lesson. First, the reads the application needs.

Check your work

How seats are loaded. Generated with generate_series and chr(64+r) for row labels, not typed by hand.

Why the booking is one transaction. It must create the booking and claim every seat together, all-or-nothing.

Why the amount is computed in the database. From seat count times show price, so it cannot disagree with the seats booked.

What happens when the second customer books an already-taken seat. ERROR: duplicate key value violates unique constraint, and the whole transaction rolls back.

What rolls back on that error. The entire transaction — the booking row and the other seat claim too, so nothing is left half-booked.

Why this beats check-then-insert in application code. That is the lost-update race; the constraint closes the window regardless of timing.

What the application should do with the error. Catch the specific UniqueViolation, refresh the seats, ask the customer to pick again — a legitimate failure, not a bug.

Why SERIALIZABLE is not needed. The UNIQUE constraint is the exact guarantee required, and it holds at the default READ COMMITTED.

Practice

1. Create the schema and load the seed data. Confirm the row counts.
2. Generate the seats for a screen with generate_series and chr. Verify none collide via the UNIQUE.
3. Run the booking transaction and read back the booking with its seats and amount.
4. Attempt to book an already-taken seat and read the exact error. Confirm the whole transaction rolled back (the other seat is still free).
5. Deliberately write the "check then insert" version in two shells and interleave them to book the same seat twice. Then rely on the constraint and confirm it cannot happen.
6. Open two psql sessions, BEGIN both, and have each try to claim the same seat. Observe one block and then fail.
7. Write the application-side handler that catches UniqueViolation and reasons about the retry.
8. Book seats across two different shows for the same physical seat and confirm it is allowed (the constraint is per-show).

Official documentation

• PostgreSQL — generate_series — Generating the seats.
• PostgreSQL — WITH (CTEs) — The multi-step booking in one statement.
• PostgreSQL — Transactions — Atomicity of the booking.
• PostgreSQL — Concurrency control — Why the constraint holds under concurrent inserts.
• psycopg — Handling errors — Catching a specific UniqueViolation.

Next: writing the queries the application needs.