What causes a double-booking bug?

A check-then-write race on shared state under a weak isolation level. Two transactions both read the inventory row and both see a room available, because under the default Read Committed level a plain SELECT "sees only data committed before the query began" — it does not see the other transaction's not-yet-committed reservation. Both pass the "is a room free?" check, both write a reservation, and the last write wins: the counter records one reservation while two customers were told "confirmed." No error is raised; the invariant reserved ≤ inventory is simply broken.

What is the difference between optimistic and pessimistic locking?

Pessimistic locking assumes conflict and prevents it up front: a transaction takes a lock on the row with SELECT ... FOR UPDATE before touching it, and every other writer waits its turn — correct and simple, but writers serialize and can deadlock. Optimistic concurrency assumes conflict is rare and detects it at the end: you read a version number, do your work without any lock, then UPDATE ... WHERE version = the value you read. If someone else committed first the version moved, your update matches zero rows, and you retry. Optimistic wins under low contention (no lock held, more parallelism) and degrades under high contention exactly when you need it — the retries pile into a storm.

What does SELECT ... FOR UPDATE do?

It locks the rows a SELECT returns so no one else can change them until your transaction ends. PostgreSQL's docs: "FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends." A second transaction that runs SELECT ... FOR UPDATE on the same row "will wait for a concurrent transaction," then re-reads the row the first one left behind — so a check made after the lock sees the truth, and the double-booking race cannot happen.

How does a CHECK constraint prevent overselling?

You store the invariant in the schema itself — CHECK (reserved <= inventory) on the inventory row — and let the database enforce it on every write. Two racing transactions can both increment reserved, but the second one to commit pushes reserved past inventory, the constraint is violated, and that transaction is rolled back automatically. It is the cheapest defense (no lock to hold, no version column to manage, no retry loop to write) and the least portable — the exact constraint syntax and its interaction with isolation levels differ across databases, and it only guards invariants you can express as a row-level check.

What is the difference between a saga and TC/C?

Both coordinate a change that spans multiple services, where a single database transaction cannot reach across the boundary. A saga is "a sequence of local transactions" where each step commits in its own service and, if a later step fails, earlier steps are undone by compensating transactions — explicit "un-do" actions like cancel-reservation that semantically reverse a committed step. TC/C (Try-Confirm/Cancel) splits every step into two phases: Try reserves the resource tentatively (hold the room, do not confirm), and a later Confirm commits it or Cancel releases it. A saga compensates after fully committing each step; TC/C holds a reservation and never fully commits until every participant has said Try succeeded.

Two Writers, One Row

Q: What is the difference between a saga and TC/C?

Both coordinate a change that spans multiple services, where a single database transaction cannot reach across the boundary. A saga is "a sequence of local transactions" where each step commits in its own service and, if a later step fails, earlier steps are undone by compensating transactions — explicit "un-do" actions like cancel-reservation that semantically reverse a committed step. TC/C (Try-Confirm/Cancel) splits every step into two phases: Try reserves the resource tentatively (hold the room, do not confirm), and a later Confirm commits it or Cancel releases it. A saga compensates after fully committing each step; TC/C holds a reservation and never fully commits until every participant has said Try succeeded.

The last hotel room, two customers, both screens say “1 available,” both click book. Walk the interleaving that double-books, then hold the count with three defenses — a pessimistic lock, an optimistic version check, a database constraint — and when the row spans two services, a saga.

Concept · Systems. The source ↗

A free, interactive, animated visual explainer of Two Writers, One Row — built to be understood, not skimmed.

Questions

What causes a double-booking bug?: A check-then-write race on shared state under a weak isolation level. Two transactions both read the inventory row and both see a room available, because under the default Read Committed level a plain SELECT "sees only data committed before the query began" — it does not see the other transaction's not-yet-committed reservation. Both pass the "is a room free?" check, both write a reservation, and the last write wins: the counter records one reservation while two customers were told "confirmed." No error is raised; the invariant reserved ≤ inventory is simply broken.
What is the difference between optimistic and pessimistic locking?: Pessimistic locking assumes conflict and prevents it up front: a transaction takes a lock on the row with SELECT ... FOR UPDATE before touching it, and every other writer waits its turn — correct and simple, but writers serialize and can deadlock. Optimistic concurrency assumes conflict is rare and detects it at the end: you read a version number, do your work without any lock, then UPDATE ... WHERE version = the value you read. If someone else committed first the version moved, your update matches zero rows, and you retry. Optimistic wins under low contention (no lock held, more parallelism) and degrades under high contention exactly when you need it — the retries pile into a storm.
What does SELECT ... FOR UPDATE do?: It locks the rows a SELECT returns so no one else can change them until your transaction ends. PostgreSQL's docs: "FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends." A second transaction that runs SELECT ... FOR UPDATE on the same row "will wait for a concurrent transaction," then re-reads the row the first one left behind — so a check made after the lock sees the truth, and the double-booking race cannot happen.
How does a CHECK constraint prevent overselling?: You store the invariant in the schema itself — CHECK (reserved <= inventory) on the inventory row — and let the database enforce it on every write. Two racing transactions can both increment reserved, but the second one to commit pushes reserved past inventory, the constraint is violated, and that transaction is rolled back automatically. It is the cheapest defense (no lock to hold, no version column to manage, no retry loop to write) and the least portable — the exact constraint syntax and its interaction with isolation levels differ across databases, and it only guards invariants you can express as a row-level check.
What is the difference between a saga and TC/C?: Both coordinate a change that spans multiple services, where a single database transaction cannot reach across the boundary. A saga is "a sequence of local transactions" where each step commits in its own service and, if a later step fails, earlier steps are undone by compensating transactions — explicit "un-do" actions like cancel-reservation that semantically reverse a committed step. TC/C (Try-Confirm/Cancel) splits every step into two phases: Try reserves the resource tentatively (hold the room, do not confirm), and a later Confirm commits it or Cancel releases it. A saga compensates after fully committing each step; TC/C holds a reservation and never fully commits until every participant has said Try succeeded.

Two Writers, One Row

Questions

Related explainers