Postgres in depth · Chapter 38 of 65
The last coupon
By the end of this chapter you can say what each isolation level promises, spot a lost update and a write skew, and pick the statement or the level that stops them. 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
- 3 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
TEACHERSDAY gives 25% off, 20 times. On Teachers' Day, Sana found 21 orders with it, and Kabir paid the difference. Each checkout read the coupon, saw a use left, and wrote it back. Two of them did it at the same moment.
Chapter 37 made each checkout one transaction, but a transaction alone does not stop this: both transactions were complete and correct on their own. What went wrong is what each one could see of the other while both ran. That is isolation, the I in ACID, and Postgres lets you choose how much of it you get.
What you build: 3 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1 · interview question
Interview: redeem.sql, the last coupon use
TEACHERSDAY can be used 20 times (max_uses). Two students may press Pay at the same moment for the last use. Write the redemption as one statement: add 1 to TEACHERSDAY's used_count only while used_count is below max_uses.
Return two columns, code and used_count (the new count), one row when the use was granted. When the coupon is used up, change nothing and return no rows.
Careful: the Check runs your file while another session is redeeming the same last use. Reading the count first and writing a number worked out from it gives the use away twice.
- Task 2
report.sql: one snapshot for Arjun's report
Arjun's month-end report runs several queries, and their numbers must agree even though orders keep arriving. Start a transaction in which every query sees the same moment, then count the paid orders.
Return two columns: paid_orders (the number of orders with status paid) and revenue (the sum of their total).
Leave the transaction open (no COMMIT): the Check commits a new paid order from another session, then runs Arjun's next query inside your transaction, and it must still see the old numbers.
- Task 3
book-class.sql: no double booking
Book React from Zero: live Q&A 3 for Neha Kapoor on 2026-10-01 from 19:00 to 20:30 (+05:30): a live_classes row for the course React from Zero with Neha as instructor, inserted only if Neha has no live class that overlaps that time (another class starts before 20:30 and ends after 19:00).
Commit it. The Check runs your file three ways: alone (the class is booked), when Neha is already busy (nothing is booked), and while another session books Neha from 19:30 to 21:00 at the same moment (at most one of the two may go in).
Careful: in the default isolation level both bookings check, both see no clash, and both go in. Your file may fail with a serialization error in the race: that is the database refusing the second booking, and it is allowed.
What you will learn
- Read committed: Postgres's default, where every statement sees a fresh snapshot.
- The lost update: read, change in the app, write back, and lose a write.
- Repeatable read: one snapshot for the whole transaction, and the error it raises.
- Write skew and serializable: two correct transactions that break a rule together, and the level that catches them.
- Retries: what to do with a serialization failure.
More in Safety, security and storage
Build The last coupon 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.