Lesson 10 — Transactions

Stack: PostgreSQL · Project: TicketFlow Status: Published — the most important lesson of the data module Prerequisite: Lesson 09 — Indexes

Objectives

1. Explain ACID with TicketFlow examples, not textbook definitions.
2. Choose an isolation level knowing which anomaly each one prevents.
3. Use select_for_update and atomic transactions in Django for concurrent reservations.
4. Write the definitive reservation transaction (the one that survives the sales spike).

1. ACID in a real transaction

The reservation transaction groups: INSERT reservation + INSERT items + (later) UPDATE payment. Without a transaction:

• Atomicity: the item's INSERT fails → an orphaned reservation is left behind. With a transaction: full rollback or nothing.
• Consistency: the constraints (I1) are checked at commit — the transaction never leaves the DB in an impossible state.
• Isolation: two concurrent reservations of the same seat don't see each other halfway (the heart of this lesson).
• Durability: the confirmed COMMIT survives a restart (PostgreSQL's WAL).

In Django: from django.db import transaction + with transaction.atomic(): — the whole block or nothing. Exceptions roll back; return commits.

2. Isolation levels: which anomaly each one eliminates

The non-repeatable read in TicketFlow: you read the gift card's balance (100), another process spends 80, you spend 70 with the stale balance → -50. It is not the level's fault by itself: it is check-then-act without a lock (Lesson 03). Levels control reads; concurrent writes are controlled with locks.

Practical decision: stay on READ COMMITTED + explicit locks (SELECT... FOR UPDATE) on the critical flow. SERIALIZABLE only if the domain demands it and you accept retries on serialization_failure.

3. Locks: the SELECT FOR UPDATE

with transaction.atomic():
    seat = Seat.objects.select_for_update().get(pk=seat_id)   # row locked
    if seat.is_free():
        item = ReservationItem.objects.create(...)             # nobody else touches this row here

FOR UPDATE locks the chosen row until commit/rollback: the second request waits on its SELECT and, when it wakes, sees the already-committed state. Variants you must know:

• nowait=True: doesn't wait, raises an error (to answer 409 instantly).
• skip_locked=True: skips locked rows (the queue-worker pattern: each worker takes different reservations — Lesson 29).
• Deadlock: two transactions locking seats in different orders — Lesson 03's global-order rule reappears: always lock in order (.order_by("id") on the queryset's select_for_update).

4. The definitive reservation transaction

from django.db import transaction, IntegrityError

@transaction.atomic
def reserve(event_id, seat_ids, user):
    # 1. Lock seats in deterministic order (anti-deadlock)
    seats = list(
        Seat.objects.select_for_update()
        .filter(pk__in=seat_ids, event_id=event_id)
        .order_by("id")
    )
    if len(seats) != len(seat_ids):
        raise Conflict("nonexistent seat")

    # 2. Business rules with the rows already locked
    if any(s.is_reserved() for s in seats):
        raise Conflict("seat taken")
    event_must_be_open(seats[0].event)      # I4

    # 3. Atomic writes
    r = Reservation.objects.create(
        user=user, event=seats[0].event,
        status=ReservationState.PENDING_PAYMENT,
        expires_at=now() + timedelta(minutes=10),  # I2
    )
    ReservationItem.objects.bulk_create([
        ReservationItem(reservation=r, seat=s, price_at_purchase=s.price()) for s in seats
    ])
    return r

Why it works without races: the lock turns check-then-act into check-then-act with mutual exclusion. Lesson 00b's UNIQUE constraint stays as the safety net (defense in depth): if some path skips the lock, the DB rejects with IntegrityError → 409.

Golden scope rule: inside atomic() there is no external HTTP, no sleep, no slow calls (gateway). The transaction holds locks: be brief and release. External things go after (outbox/queue: Lessons 30/32).

Self-assessment

1. Which ACID guarantee fails if the process dies between the reservation INSERT and the items INSERT without a transaction? And with one?
2. Why doesn't "upgrading to SERIALIZABLE" fix the gift card balance by itself? What is the right answer?
3. What does the second request do exactly when the first holds FOR UPDATE on the row? With nowait? With skip_locked?
4. Why does select_for_update().order_by("id") prevent deadlocks in multi-seat reservations?
5. Which operations NEVER go inside transaction.atomic(), and why?

Continue with the exercises. The solutions only after trying it yourself.