Transactions & Isolation

At 10:00:03 on sale day, Alice and Bob both click seat A-12 of the stadium show. Both see "Seat held — you have 10 minutes to pay". Both pay. At the gate, one of them is turned away. The code that did this had been in production for a year, had 100% test coverage and passed every code review — because the bug only exists when two requests interleave in the database, which never happens in a unit test.

This page explains why, using the vocabulary every database engineer shares: transactions, isolation levels and anomalies. Then it fixes the bug four different ways and builds the seat-hold flow BoxOffice really uses.

ACID, concretely

A transaction groups several reads and writes into one logical unit. The classic guarantees are summarized as ACID¹:

The trap is in the I: most databases do not run transactions as if one at a time by default. PostgreSQL, Oracle and SQL Server default to read committed; MySQL InnoDB defaults to repeatable read. Both permit anomalies — including the one that sold seat A-12 twice.

The double-booking bug

This is the code path, written as the SQL each request sends (the application checks the result of the SELECT before issuing the UPDATE):

BEGIN;  -- PostgreSQL default: READ COMMITTED

SELECT status FROM seats
WHERE event_id = 42 AND seat_id = 'A-12';
-- application: if status != 'AVAILABLE' then ROLLBACK and show "seat taken"

UPDATE seats
SET status = 'HELD', held_by = :customer_id,
    hold_expires_at = now() + interval '10 minutes'
WHERE event_id = 42 AND seat_id = 'A-12';

COMMIT;

Now interleave two requests:

sequenceDiagram
    participant A as Alice (T1)
    participant DB as PostgreSQL
    participant B as Bob (T2)
    A->>DB: SELECT status … A-12
    DB-->>A: AVAILABLE
    B->>DB: SELECT status … A-12
    DB-->>B: AVAILABLE
    A->>DB: UPDATE … SET HELD, held_by = Alice
    Note over DB: T1 locks row A-12
    B->>DB: UPDATE … SET HELD, held_by = Bob
    Note over DB: T2 waits for T1's row lock
    A->>DB: COMMIT
    DB-->>A: ok — "seat held"
    Note over DB: lock released: T2's UPDATE runs<br/>and overwrites Alice's hold
    B->>DB: COMMIT
    DB-->>B: ok — "seat held"

Both requests read AVAILABLE before either wrote; both decided "free"; the second UPDATE overwrote the first. This is the check-then-act race, and the anomaly is called a lost update: Alice's write was silently lost. The row lock did not help, because it only serialized the two UPDATEs — the decision had already been made on stale reads.

If the tickets table has PRIMARY KEY (event_id, seat_id) (see Data Models), the second ticket insert fails at payment confirmation — after the money was taken. The constraint saved the stadium from two people in one seat, but turned the bug into a refund and an angry customer. Constraints are the last line of defense, not the design.

Isolation levels and their anomalies

An anomaly is an outcome that could never happen if transactions ran one at a time (serially). Each weaker isolation level permits some of them in exchange for more concurrency².

The levels, as implemented by PostgreSQL (other databases differ — check the documentation³):

How snapshots work: MVCC

PostgreSQL, MySQL InnoDB, Oracle and most modern engines use multi-version concurrency control (MVCC): an update writes a new version of the row instead of overwriting it, tagged with the transaction that created it. Each transaction reads from a snapshot — the versions committed before it (repeatable read) or before each statement (read committed) started.

The payoff: readers never block writers, and writers never block readers. The cost: old versions accumulate and must be cleaned up (VACUUM in PostgreSQL), and two writers to the same row still serialize on a row lock.

Write skew: the ticket limit

BoxOffice allows at most 6 tickets per customer per event. Alice already holds 4 and opens two browser tabs, each trying to hold 2 more seats:

No row was written by both transactions, so no row lock conflicts and snapshot isolation sees nothing wrong. This is write skew, and it is driven by a phantom: each transaction's count(*) would have changed had it seen the other's writes. Two fixes:

• Serializable isolation: PostgreSQL's serializable snapshot isolation (SSI)⁴ tracks read/write dependencies and aborts one transaction with ERROR: could not serialize access due to read/write dependencies among transactions (SQLSTATE 40001). The application must retry it.
• Materialize the conflict: make both transactions lock the same row first, e.g. a per-customer-per-event row:

BEGIN;
SELECT tickets_held FROM customer_event_limits
WHERE customer_id = 7 AND event_id = 42
FOR UPDATE;                         -- the second tab waits here
-- application: if tickets_held + 2 > 6 then ROLLBACK
UPDATE seats SET status = 'HELD', held_by = 7,
                 hold_expires_at = now() + interval '10 minutes'
WHERE event_id = 42 AND seat_id IN ('B-1', 'B-2') AND status = 'AVAILABLE';
UPDATE customer_event_limits SET tickets_held = tickets_held + 2
WHERE customer_id = 7 AND event_id = 42;
COMMIT;

Four ways to fix the double booking

All four work on any mainstream relational database at read committed, which is why they are preferred over "just raise the isolation level".

1. Atomic conditional update (compare-and-set)

Put the check inside the write, so check and act are one atomic statement:

UPDATE seats
SET status = 'HELD', held_by = :customer_id,
    hold_expires_at = now() + interval '10 minutes'
WHERE event_id = 42 AND seat_id = 'A-12'
  AND status = 'AVAILABLE';
-- 1 row updated → the hold is yours; 0 rows → someone else got it

When Bob's UPDATE waits for Alice's lock and then runs, PostgreSQL re-checks the WHERE clause against the new row version: status is now HELD, so 0 rows are updated. One round trip, no explicit lock, no retry loop. This is the BoxOffice default.

2. Pessimistic locking: SELECT … FOR UPDATE

Lock the row when you read it, so nobody can change it until you commit:

BEGIN;
SELECT status FROM seats
WHERE event_id = 42 AND seat_id = 'A-12'
FOR UPDATE;                 -- Bob blocks here until Alice commits
-- application: if status != 'AVAILABLE' then ROLLBACK
UPDATE seats SET status = 'HELD', held_by = :customer_id,
                 hold_expires_at = now() + interval '10 minutes'
WHERE event_id = 42 AND seat_id = 'A-12';
COMMIT;

Use it when the decision needs application logic between the read and the write (pricing rules, the ticket limit). Variants matter under load:

• FOR UPDATE NOWAIT fails immediately instead of waiting — good for "seat taken, choose another".
• FOR UPDATE SKIP LOCKED skips rows other transactions hold — perfect for "best available": hand each fan different free seats without them queuing on the same rows.

SELECT seat_id FROM seats
WHERE event_id = 42 AND section = 'B' AND status = 'AVAILABLE'
ORDER BY seat_id
LIMIT 2
FOR UPDATE SKIP LOCKED;

When one transaction locks several rows (a group of 4 seats), always lock them in the same order (e.g. ORDER BY seat_id). Otherwise T1 locks A-1 then waits for A-2 while T2 locks A-2 then waits for A-1 — a deadlock; the database kills one of them.

3. Optimistic locking with a version column

Do not lock at all; detect conflicts at write time with a version counter:

SELECT status, version FROM seats
WHERE event_id = 42 AND seat_id = 'A-12';     -- AVAILABLE, version 7

UPDATE seats
SET status = 'HELD', held_by = :customer_id, version = version + 1,
    hold_expires_at = now() + interval '10 minutes'
WHERE event_id = 42 AND seat_id = 'A-12' AND version = 7;
-- 0 rows → somebody changed the seat since you read it: reload and retry

JPA/Hibernate does this automatically with an @Version field and throws OptimisticLockException. It shines when conflicts are rare (editing an event description) and holds no locks during user think time. It is a poor fit for a flash sale, where thousands of fans collide on the same few rows and most attempts fail and retry.

4. Let a constraint decide

Make the invariant a unique constraint, and let the database reject the loser. With a separate holds table, an expired hold can even be taken over atomically:

CREATE TABLE seat_holds (
    event_id   BIGINT NOT NULL,
    seat_id    TEXT   NOT NULL,
    customer_id BIGINT NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (event_id, seat_id)
);

INSERT INTO seat_holds (event_id, seat_id, customer_id, expires_at)
VALUES (42, 'A-12', :customer_id, now() + interval '10 minutes')
ON CONFLICT (event_id, seat_id) DO UPDATE
    SET customer_id = EXCLUDED.customer_id, expires_at = EXCLUDED.expires_at
    WHERE seat_holds.expires_at < now();   -- only if the old hold expired
-- 1 row → hold acquired; 0 rows → a live hold exists

Comparing the fixes

private void holdNaive(long eventId, List<Long> seatIds, UUID holdId) {
    var seats = read(eventId, seatIds, "");
    requireAvailable(seats, seatIds);
    // race window: another transaction may have read the same AVAILABLE seats and
    // be about to run this same UPDATE — both will "win"
    jdbc.sql("UPDATE inventory.seats SET status = 'HELD', hold_id = :holdId WHERE id IN (:ids)")
            .param("holdId", holdId).param("ids", seatIds)
            .update();
}
private void holdPessimistic(long eventId, List<Long> seatIds, UUID holdId) {
    // row locks until commit; a second transaction on the same seats WAITS here.
    // Locking in id order (ORDER BY id) prevents deadlocks between overlapping holds.
    var seats = read(eventId, seatIds, "ORDER BY id FOR UPDATE");
    requireAvailable(seats, seatIds);
    jdbc.sql("UPDATE inventory.seats SET status = 'HELD', hold_id = :holdId, version = version + 1 WHERE id IN (:ids)")
            .param("holdId", holdId).param("ids", seatIds)
            .update();
}
private void holdOptimistic(long eventId, List<Long> seatIds, UUID holdId) {
    var seats = read(eventId, seatIds, "");                      // no lock
    requireAvailable(seats, seatIds);
    for (var seat : seats) {
        int changed = jdbc.sql("""
                UPDATE inventory.seats
                SET status = 'HELD', hold_id = :holdId, version = version + 1
                WHERE id = :id AND version = :version AND status = 'AVAILABLE'""")
                .param("holdId", holdId).param("id", seat.id()).param("version", seat.version())
                .update();
        if (changed == 0) {
            // someone changed this seat after we read it: give up (the exception rolls back)
            throw new ResponseStatusException(HttpStatus.CONFLICT, "seat " + seat.id() + " was just taken");
        }
    }
}

Seat holds, end to end

A hold is a lease: a temporary, expiring claim. The seat's life cycle:

stateDiagram-v2
    [*] --> AVAILABLE
    AVAILABLE --> HELD: hold (conditional UPDATE)
    HELD --> SOLD: payment confirmed
    HELD --> AVAILABLE: hold expired or cancelled
    SOLD --> AVAILABLE: refund (rare, audited)
    SOLD --> [*]

Two design decisions make it robust:

1. Lazy expiry. The hold query treats an expired hold as available, so a seat is never stuck because a cleanup job is late:

   InventoryRepository.java (Spring Data JPA)

   @Modifying
   @Query("""
       UPDATE Seat s
          SET s.status = 'HELD', s.heldBy = :customerId, s.holdExpiresAt = :expiresAt
        WHERE s.eventId = :eventId AND s.seatId = :seatId
          AND (s.status = 'AVAILABLE'
               OR (s.status = 'HELD' AND s.holdExpiresAt < CURRENT_TIMESTAMP))
       """)
   int tryHold(@Param("eventId") long eventId, @Param("seatId") String seatId,
               @Param("customerId") long customerId, @Param("expiresAt") Instant expiresAt);
   

   The service throws SeatTakenException when tryHold returns 0. A background job still flips expired holds back to AVAILABLE (using the partial index on hold_expires_at) so the seat map stays accurate.

2. Confirm conditionally, too. Payment confirmation runs UPDATE seats SET status = 'SOLD' WHERE … AND status = 'HELD' AND held_by = :customer_id AND hold_expires_at > now(). If the hold expired while the fan was paying, 0 rows are updated and the payment is refunded or the fan is offered another seat. Never assume the state you saw minutes ago is still true.

Contention: why hot rows cap throughput

A row lock behaves like a server that serves one transaction at a time. If each transaction holds it for t seconds, that row admits at most 1 / t transactions per second, no matter how big the machine is (Little's Law, from Queueing). The plot compares three designs under illustrative assumptions (load spread evenly over the contended rows; the database itself tops out at 10,000 transactions per second):

The lessons:

• Never keep a global counter in the hot path. Decrementing events.seats_left in every hold serializes the whole on-sale on one row: at 5 ms per transaction, 200 holds per second, for 2 million fans. Compute "seats left" asynchronously (or with per-section counters) and show it from a cache.
• Keep transactions short. A transaction that calls the payment provider (hundreds of milliseconds to seconds) while holding a seat lock multiplies lock time by 100. Hold the seat, commit, call the provider outside any transaction, then confirm in a new short transaction.
• Fine-grained rows spread contention. One row per seat means fans only collide when they want the same seat.

Try it. The flash-sale scenario starts without a waiting room: app writes straight to inventory-db. Run it, then raise the app replicas until the app is no longer the bottleneck: when the sale opens (t = 60 s) inventory-db saturates on writes (a write costs 3 reads' worth of capacity) and p99 explodes. Then open it in the full playground (↗) and press Show a possible solution: a waiting-room queue drained by order workers keeps the writes reaching inventory-db — the concurrent hold transactions — below what the database can serve. (The simulator models capacity, not locks: the row contention above is the part you reason about by hand.)

Transactions across services

The hold lives in the inventory database, the order in the order database, and the payment at an external provider. No single ACID transaction spans them.

• Two-phase commit (2PC) coordinates several databases: a coordinator asks every participant to prepare (promise to commit), then tells all of them to commit. It is atomic, but a participant that prepared must wait — holding locks — until the coordinator answers; if the coordinator crashes at the wrong moment, they block. It is used inside some databases and with XA transactions, rarely across microservices, and never across an external payment provider.
• Sagas split the business transaction into local transactions, each with a compensation (release the hold, refund the payment), coordinated by events or an orchestrator. Combined with the transactional outbox (write the event in the same local transaction as the state change), they give atomicity eventually. See Messaging patterns.
• Calls to the payment provider carry an idempotency key, so a retry never charges twice. See Integrations.

How it connects

flowchart LR
    ACID["ACID"] --> ISO["isolation levels<br/>MVCC snapshots"]
    ISO --> ANOM["anomalies: lost update,<br/>write skew"]
    ANOM --> BUG["the double-booking bug<br/>check-then-act race"]
    BUG --> FIX["conditional UPDATE, FOR UPDATE,<br/>version column, unique constraint"]
    FIX --> HOLD["seat holds as leases<br/>lazy expiry + cleanup job"]
    FIX --> CONT["hot rows cap throughput<br/>at 1 / lock time"]
    CONT --> WR["waiting room<br/>→ Messaging"]
    BUG -.-> DIST["consistency models<br/>→ Distributed"]
    ACID --> XS["across services: saga, outbox,<br/>idempotency keys"]

Palette components: sql (the inventory database decides: conditional updates, row locks, constraints) · app (inventory answers 409 to the losers; transactions stay short) · queue (the waiting room bounds contention on hot rows) · scheduler (the job that flips expired holds back) · stream (outbox events between order, inventory and payment) · payment (called with an idempotency key, never inside a transaction). Scenarios: flash-sale, payment-system. Continues in: Replication, Distributed — transactions, Messaging — patterns, Compute — schedulers, Case study: ticketing, Hands-on 6.