Transactions and Isolation

A concert has one ticket left. Two fans press "buy" in the same 10 ms. Both requests read remaining = 1, both charge a card, both write remaining = 0. Two people paid for one seat, and the database never reported an error.

Each request was correct on its own; the bug is in how they overlapped. Isolation levels decide which overlaps the database allows.

#The idea

A transaction groups several reads and writes so the database treats them as one unit. ACID names its four promises:

• Atomic: all of the writes happen, or none do. A crash halfway leaves nothing behind.
• Consistent: your rules hold before and after. Mostly your job; constraints such as UNIQUE and CHECK (stock >= 0) help.
• Isolated: concurrent transactions do not see each other's half-done work. How strictly is set by the isolation level; the default is rarely the strictest.
• Durable: once committed, the data survives a crash, because it is in a log on disk (and often on replicas).

Stricter isolation means more waiting or more retries. Choose the weakest level that stops the races your data can have.

#Three races to know

Two users, T1 and T2, and time going down the page.

#Dirty read: seeing work that is later undone

T2 acted on a value that never existed.

#Lost update: read, modify, write, collide

Two likes, count up by one. The concert ticket is this race.

#Write skew: two valid writes, one broken rule

Rule: a team always keeps at least one admin. Ana and Ben are the only two.

Zero admins. Each updated a different row, so row-level checks see no conflict. Only serializable isolation, or a lock on something both touch, catches it.

#Isolation levels

Read committed is a common default. Snapshot isolation gives each transaction a frozen view as of its start; some databases also abort a transaction whose row changed underneath it, others let the later write win. Names differ between products, so check what yours does. Serializable means the result equals some one-at-a-time order, at the cost of more aborts to retry.

#Stopping a lost update

-- read: title = 'Draft', version = 7
UPDATE docs
SET    title = 'Final', version = 8
WHERE  id = 42 AND version = 7;
-- 1 row updated: you won.
-- 0 rows updated: someone wrote first. Re-read and retry, or show a conflict.

For write skew, make the transactions collide on purpose: lock the team row (SELECT … FOR UPDATE on teams) before counting admins, or run that one transaction as serializable.

#Across services: 2PC and sagas

An order touches the orders database, payments and inventory. No single transaction spans them.

Two-phase commit (2PC) adds a coordinator. Phase 1: it asks every participant to prepare (do the work, hold the locks, promise to commit). Phase 2: if all voted yes, it tells them to commit; otherwise to abort. It is atomic, but if the coordinator dies after "prepare", participants hold their locks until it returns. It costs two round trips per transaction, and every participant must support it.

A saga gives up atomicity across services. It runs a chain of local transactions, each publishing an event that triggers the next. If a step fails, compensating actions undo earlier steps: refund the card, release the seat. Others can see the middle states ("paid, not yet reserved"), so design for them.

Each step must update its database and publish an event. As two separate writes, a crash between them loses one. The outbox pattern fixes this: write the event into an outbox table in the same local transaction, and let a relay publish rows from the outbox afterwards.

The usual default across services is a saga with outboxes and idempotent consumers. Reach for 2PC only when the participants are a few databases that support it, in one data center, and a visible in-between state is unacceptable.

#Numbers worth remembering

• A short transaction on one database: about 1 to 10 ms. Locks held that long are cheap; locks held across a network call or a user's think time are not.
• 2PC adds at least two round trips per transaction, plus a durable log write at each step.

#In the interview

Bring this up whenever two users can change the same thing (stock, seats, balances) or one action spans services. Name the race, then the fix: "Two buyers can read the same stock, so I decrement with a conditional update and treat zero rows as sold out."

Follow-ups you should expect:

• "What if the compensation fails?" Retry it; it must be idempotent. After several failures, alert a human.
• "Why not publish the event, then write the database?" A crash between the two leaves an event for an order that does not exist. The outbox makes them one write.

#Common mistakes

#What to practise next

Sagas and outboxes run on message queues and streams. For a design where these choices decide the outcome, work through Design a Payment System.