The Professor
Index Course index

Module 2 · Databases

Lesson 10 — Transactions

ACID, isolation levels, locks and optimistic/pessimistic concurrency in seat reservation.

Published
In this lesson
  1. Exercise 1 — The race
  2. Exercise 2 — FOR UPDATE
  3. Exercise 3 — Deadlock
  4. Exercise 4 — Isolation
  5. Exercise 5 — The definitive transaction
  6. Professor's summary

Exercise 1 — The race

  1. With the constraint: 1 reservation created + 1 IntegrityError (the DB rejects the loser). The data survives; the loser's experience depends on your handling (409, not 500).
  2. Without the constraint: 2 reservations for the same seat — the business sold the same chair twice. This is THE experiment of the course: integrity lives in the DB, not in the code's good intentions (00b/03/10, the triad).

Exercise 2 — FOR UPDATE

  1. Thread 2 waits on its SELECT until 1 commits; waking up it sees the seat taken → its business branch raises the conflict. 1 reservation. 2. nowait raises DatabaseError/OperationalError (psycopg: could not obtain lock) — translate it to 409 Conflict with a domain message. 3. With skip_locked each thread takes its free row without blocking: that is the workers pattern (29).

Exercise 3 — Deadlock

  1. PostgreSQL detects the circle and kills ONE transaction: Deadlock detected (psycopg: DeadlockDetected), rolling back the victim. The other continues.
  2. With the global order by id, the circle never forms: the second thread waits on the first lock at the same seat and then takes the other one, already free.
  3. Retry: catch django.db.utils.OperationalError (or SQLSTATE 40P01) and re-run the function once after 50-100ms. With the global order it won't be needed, but the safety net is free — and the pattern reappears with SERIALIZABLE.

Exercise 4 — Isolation

  1. READ COMMITTED: A sees the new price on its second read inside the same transaction (every statement reads the freshest snapshot): non-repeatable reads, exactly as the level promises.
  2. REPEATABLE READ: A keeps seeing the old price (snapshot taken at transaction start).
  3. Rule: a point lock (FOR UPDATE) for specific rows you are about to write; SERIALIZABLE/REPEATABLE READ for complex multi-row invariants that accept retries. TicketFlow: READ COMMITTED + locks — the 95% recipe of backends.

Exercise 5 — The definitive transaction

  1. 5 threads / 5 seats: 5 reservations 201; the locks are on different rows → no contention (if you locked the whole TABLE, you would see them serialize — that is the difference between a row lock and a table lock).
  2. Same seat: 1×201 and 1×409 with a domain body (seat_already_reserved).
  3. Expected queries: ~4-6 (SELECT for update, checks, INSERT reservation, INSERT items). Total time of the 5: in the tens of ms (parallel per-row locks don't fight).

Professor's summary

  • atomic() for atomicity; FOR UPDATE for exclusion; global order to avoid the deadly embrace; the constraint as safety net; slow things OUT of the transaction.
  • READ COMMITTED + explicit locks is the 95% recipe; SERIALIZABLE is paid for with retries.
  • This lesson closes the circle 00b opened: invariant I1 is now demonstrable under real concurrency.

After the correction: Lesson 11 — Versioned migrations.

^ top