Transactions From the Application Side

The engine gives the service atomicity and isolation. The service decides where a transaction begins, what it covers and when it ends, and those three decisions are the difference between "the order and its tickets exist together" and "the order exists and the tickets are missing." Stagedoor's place_order writes one orders row, two or more tickets rows and one outbox row, and a buyer who sees the first without the others has a receipt for tickets that do not exist. The transaction is the promise that this cannot happen, and it is only as good as its boundaries.

The rule that follows is the unit of work: one domain operation, one transaction, opened as late as possible, committed as soon as possible, and never spanning a call to anything outside the database. Every mistake in this topic is a boundary in the wrong place: a transaction that began at the top of the request, one that stayed open through a 3-second Payrail call, one that continued after a statement failed, and one that assumed two statements saw one world when Read Committed promised nothing of the kind. The last of these is how seat 14C was sold twice, and Topic 34 closes it.

The Unit of Work

A unit of work is the set of writes that must all happen or all not happen. For place_order it is three inserts: the order, its tickets, and the outbox row that Chapter 7 turns into the job that renders the tickets and sends the email. If the third insert fails, the first two must not exist, because an order with no outbox row is an order whose buyer never receives a ticket or an email. One transaction around exactly those statements is all the mechanism there is; Postgres either commits all three or, on any error or disconnect, rolls all three back, and no other transaction ever sees the order without its tickets.

async def place_order(svc, buyer, hold_ids) -> Order:
    holds = await svc.holds.get_many(hold_ids)          # a read: no transaction yet
    order = Order.from_holds(buyer, holds)                # decide, in memory

    async with svc.pool.connection() as conn, conn.transaction():
        await svc.orders.insert(conn, order)
        await svc.tickets.insert_for(conn, order, holds)
        await svc.outbox.add(conn, "render_tickets", order.id)
    # committed here, or rolled back if anything above raised

    return order                                          # Payrail is called outside this transaction, never inside it

Read the block for what is inside the transaction and what is not. The read of the holds and the decision about the order happen first, with no connection held. The connection is taken and the transaction opened only for the three inserts, and the block ends, committing, before the function returns. Nothing in the block leaves the process. The charge to Payrail is not inside the block at all: the order commits as pending and the call is made afterwards, with no connection in hand, which is the arrangement Chapter 7 explains. The transaction lasts the few milliseconds of three inserts and a commit, and the connection is back in the pool before the response is serialized.

Begin Late, Commit Early

A transaction holds two things for as long as it is open: every row lock it has taken, and a snapshot that tells Postgres which old row versions it might still need. Both are cheap for 5 milliseconds and expensive for 3 seconds. A seat row locked for 3 seconds during the on-sale minute is a seat that every other buyer's hold request waits on. A snapshot held for 3 seconds is 3 seconds during which vacuum cannot remove any row version newer than it on any table in the database, and PostgreSQL Deep Dive's chapter on vacuum explains what a long-lived snapshot costs the whole cluster. Neither cost is visible from inside the handler, and both scale with the number of handlers doing it at once.

So the reads that do not need to be consistent with the writes happen before BEGIN, and every call that leaves the process happens after COMMIT or before BEGIN, never between them. Stagedoor's first checkout called Payrail inside the transaction so that a declined card could roll the order back, which sounds tidy and was the bug: 400 milliseconds at the median and 3 seconds at the timeout, with the order row locked and the connection pinned the whole time. The tidy version and the correct version differ by one line: commit the order as pending, call Payrail, then update it to paid or failed in a second, separate transaction. Chapter 10 makes that second step safe when the answer is lost.

Read Committed and What It Does Not Promise

Postgres's default isolation level is Read Committed, and its promise is precise: each statement sees the data that was committed before that statement began. Not before the transaction began; before the statement. Two statements in one transaction can see two different worlds if another transaction committed between them, and that is not a bug in Postgres, it is the definition of the level. A transaction that runs SELECT status FROM seats WHERE id = 3117 and then UPDATE seats SET status = 'held' WHERE id = 3117 has run two statements with a gap between them, and in that gap another transaction can read the same row, see the same available, and write the same held.

That is exactly what happened to 14C, and the reason it matters here is the wrong fix it invites. Wrapping the two statements in a transaction does nothing, because they were already in one. The transaction gives atomicity, so that the update and the insert of the hold commit together; it does not give the second statement the first statement's view of the world. The fixes are Topic 34's: lock the row in the first statement so that the second reader waits, or make the write conditional on what was read, or let a constraint refuse the second hold. Read Committed is the right default for a service, because it never blocks readers and never aborts a transaction for seeing something new; the price is that the service, not the engine, decides which reads must agree with which writes.

When to Raise Isolation

Two operations in Stagedoor need more than the default. The organizer's sales report runs five queries, one per section of the seat map, and adds them up; under Read Committed a sale that commits between the second and third query is counted in some sections' totals and not in others, and the report does not add up. Repeatable Read fixes it: the transaction takes one snapshot at its first statement and every query sees that snapshot, so the report is a consistent picture of one instant. It costs nothing on the primary for a read-only transaction, and Topic 36 runs it on the replica.

The rule "no more than four tickets per buyer per event" is different. It spans rows: to enforce it the service reads the buyer's existing tickets, counts, and inserts, and two concurrent orders for the same buyer can each count two existing tickets, each insert two more, and leave the buyer with six. A row lock does not help, because there is no single row to lock. Serializable is the level for this: Postgres tracks what each transaction read and wrote and aborts one of any two whose interleaving could not have happened in some serial order. The abort is the demand it makes of the caller. The error, SQLSTATE 40001, means "this transaction lost; run it again from the start," and a service that raises the level without a retry loop around the unit of work has turned a correct mechanism into a 500 that appears only under contention. Three attempts with a short pause is the loop; the whole unit, including the reads, is what it repeats.

The Idle-in-Transaction Trap

The most common transaction bug is not written by the developer; it is installed by a framework. A middleware that opens a transaction on connection checkout and commits on return sounds like a convenience: every handler runs inside a transaction, nobody forgets. What it means is that every request is a transaction, the connection is held from the first line of the handler to the last, and the await httpx.post(...) to Payrail in the middle of the handler is a snapshot and every acquired lock held for the duration of a network call. Postgres reports the session as idle in transaction, which is the state of a connection that has an open transaction and is running nothing, and on the on-sale evening Marek counted 140 of them.

psycopg itself will do a milder version of this if allowed: a connection that is not in autocommit mode begins a transaction implicitly on its first statement and keeps it open until the code commits, so a lazy read at the top of the handler opens a transaction that the handler never meant to have. The fix in both cases is the same: no transaction that the domain did not open explicitly, around the unit of work only. The database-side backstop is idle_in_transaction_session_timeout, which terminates a session that sits idle inside a transaction for longer than the limit; Stagedoor sets it to 10 seconds for the application role, so that a bug of this shape kills its own connection instead of holding a lock for the length of a stuck Payrail call. It is a safety net, not a design, and a session it terminates has lost its transaction.

Errors Inside a Transaction

After any statement fails inside a transaction, Postgres refuses every further statement on that transaction until it is rolled back, with the message current transaction is aborted, commands ignored until end of transaction block. A handler that catches the unique violation from the tickets insert, logs it and carries on to insert the outbox row gets that error on the outbox insert, and the log now shows a failure on a statement that had nothing wrong with it. The debugging session that follows is spent on the wrong line.

The pattern that avoids it is the one in the code above: the transaction block rolls back on any exception that leaves it, and the domain decides what to do after the block, outside the transaction, with the connection released. A duplicate ticket becomes a 409 by mapping the exception after the rollback; it is never handled by continuing inside the transaction. Where a partial failure must be survivable inside one transaction, Postgres offers savepoints, which the driver exposes as a nested transaction block, and the failed statement's savepoint is rolled back while the outer transaction continues. That is the exception, used once or twice in a codebase; the rule is that the block fails whole.