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.

  1. 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.

  2. 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.

  3. 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.