Postgres in depth · Chapter 39 of 65
100 bootcamp seats
By the end of this chapter you can lock exactly the rows you need, build a queue that many workers share, choose whether to wait or fail, and keep two transactions from deadlocking. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Safety, security and storage
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Kabir announces LCO's first live bootcamp: 100 seats, opening at 9:00. At 9:00:01, two thousand students press Claim. Priya wants every seat given exactly once, nobody waiting behind anybody else's checkout, and the nightly certificate job never running twice.
Chapter 38 showed that two writers of the same row queue up, and that serializable can refuse a transaction. Neither hands out seats well: a queue of two thousand behind seat 1 is slow, and refusing most claims is rude. You need to say which rows you are taking, and what to do when someone else has them.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
claim-seat.sql: claim the first free seat
The bootcamp_seats table (the lesson shows how to make it) has seats 1 to 100; Aarav Shah has seat 7 and Diya Patel seat 12. Give Yash Verma the lowest-numbered free seat (student_id empty), with claimed_at 2026-10-12 09:00:00+05:30.
Return one column, seat_no: the seat he got.
Careful: hundreds of students claim at the same moment. The Check runs your file while another session is in the middle of claiming seat 1: your file must not wait for that seat, and must give Yash seat 2 instead. A seat number typed by hand is wrong.
- Task 2
fix-claim-time.sql: edit a seat, or fail at once
Sana corrects Diya Patel's claimed_at to 2026-10-10 11:00:00+05:30 from the admin screen. In one transaction, first lock Diya's seat row, then change it, then commit.
If another session is changing that seat right now, the file must fail at once with an error instead of waiting, so the screen can say "someone else is editing this seat". Nothing changes then.
Careful: a plain UPDATE, or a plain lock, waits as long as the other session takes. The Check holds Diya's seat in another session and times how long your file waits.
- Task 3
nightly-job.sql: one certificate job at a time
The nightly certificate job must never run twice at once. Its first step tries to take the advisory lock with the key 39001, without waiting.
Return one column, got_lock: true when this session now holds the lock, false when another run of the job holds it.
The lock must stay held after the statement ends, until the job unlocks it or its session ends: the job runs many transactions.
- Task 4 · interview question
Interview: swap-seats.sql, a swap without a deadlock
Aarav Shah and Diya Patel want to swap seats: afterwards Diya has Aarav's old seat (7) and Aarav has Diya's (12). Do it in one transaction.
Lock both seat rows before changing them, always in seat order (the lower seat number first), the same order every other transaction on these seats uses. Find the seats by the students' names.
Careful: the Check runs your file while another session locks seat 7 and then wants seat 12. A swap that takes seat 12 first holds what the other needs and waits for what it holds: a deadlock, and Postgres kills one of them.
What you will learn
- Row locks: FOR UPDATE and its weaker cousins, and where Postgres keeps them.
- NOWAIT and lock_timeout: fail instead of waiting.
- SKIP LOCKED: a queue that many workers share.
- Advisory locks: locks on a number your app chooses.
- Deadlocks: how they happen and the rule that prevents them.
More in Safety, security and storage
Build "100 bootcamp seats" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.